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): ...