selecting

This module provide utility functions for select operation.

sqlalchemy_mate.crud.selecting.count_row(engine: Engine, table: Table) → int

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: Engine, table: Table, id_) → Optional[Row]

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: Engine, table: Table) → Result

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: Engine, column: Column) → list

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: Engine, columns: List[Column]) → List[tuple]

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: Engine, column: Column) → list

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: Engine, columns: List[Column]) → List[tuple]

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: Engine, table: Optional[Table] = None, columns: Optional[List[Column]] = None, limit: Optional[int] = None, perc: Optional[int] = None) → Result

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: Result) → Iterable[tuple]

Yield rows in tuple values view.

sqlalchemy_mate.crud.selecting.yield_dict(result: Result) → Iterable[dict]

Yield rows in dict view.