selecting

This module provide utility functions for select operation.

sqlalchemy_mate.crud.selecting.count_row(engine: sqlalchemy.engine.base.Engine, table: sqlalchemy.sql.schema.Table) int[source]

Return number of rows in a table.

Example:

import sqlalchemy as sa
import sqlalchemy_mate as sam

t_users = sa.Table(...)
engine = sa.create_engine(...)
sam.selecting.count_row(engine, t_user)

中文文档

返回一个表中的行数。

sqlalchemy_mate.crud.selecting.by_pk(engine: sqlalchemy.engine.base.Engine, table: sqlalchemy.sql.schema.Table, id_) Optional[sqlalchemy.engine.row.Row][source]

Return single row or None by primary key values.

Parameters

id – single value if table has only one primary key, tuple / list of values if table has multiple primary keys, positioning is sensitive.

Example:

import sqlalchemy as sa
import sqlalchemy_mate as sam

t_users = sa.Table
    "users", metadata,
    Column("id", sa.Integer, primary_key=True),
    ...
)
engine = sa.create_engine(...)
row = sam.selecting.by_pk(engine, t_user, 1) # one row or None
print(row._fields)      # keys
print(tuple(row))       # values
print(row._asdict())    # dict view
sqlalchemy_mate.crud.selecting.select_all(engine: sqlalchemy.engine.base.Engine, table: sqlalchemy.sql.schema.Table) sqlalchemy.engine.result.Result[source]

Select all rows from a table.

Example:

for row in sam.selecting.select_all(engine, t_users):
    ...
sqlalchemy_mate.crud.selecting.select_single_column(engine: sqlalchemy.engine.base.Engine, column: sqlalchemy.sql.schema.Column) list[source]

Select data from single column.

Example:

id_list = sam.selecting.select_all(engine, t_users.c.id)
sqlalchemy_mate.crud.selecting.select_many_column(engine: sqlalchemy.engine.base.Engine, columns: List[sqlalchemy.sql.schema.Column]) List[tuple][source]

Select data from multiple columns.

Example:

dataframe = sam.selecting.select_all(engine, [t_users.c.id, t_users.c.name])
sqlalchemy_mate.crud.selecting.select_single_distinct(engine: sqlalchemy.engine.base.Engine, column: sqlalchemy.sql.schema.Column) list[source]

Select distinct data from single column.

Example:

unique_name_list = sam.selecting.select_all(engine, t_users.c.name)
sqlalchemy_mate.crud.selecting.select_many_distinct(engine: sqlalchemy.engine.base.Engine, columns: List[sqlalchemy.sql.schema.Column]) List[tuple][source]

Select distinct data from multiple columns.

Example:

dataframe = sam.selecting.select_many_distinct(engine, [t_users.c.id, t_users.c.name])
sqlalchemy_mate.crud.selecting.select_random(engine: sqlalchemy.engine.base.Engine, table: Optional[sqlalchemy.sql.schema.Table] = None, columns: Optional[List[sqlalchemy.sql.schema.Column]] = None, limit: Optional[int] = None, perc: Optional[int] = None) sqlalchemy.engine.result.Result[source]

Randomly select some rows from table.

Parameters

perc – int from 1 ~ 99. (means 1% ~ 99%)

Example:

# randomly select 100 users
for row in sam.selecting.select_random(engine, t_users, limit=100):
    ...

# randomly select 5% rows from users table
for row in sam.selecting.select_random(engine, t_users, perc=5):
    ...
sqlalchemy_mate.crud.selecting.yield_tuple(result: sqlalchemy.engine.result.Result) Iterable[tuple][source]

Yield rows in tuple values view.

sqlalchemy_mate.crud.selecting.yield_dict(result: sqlalchemy.engine.result.Result) Iterable[dict][source]

Yield rows in dict view.