py_research.sql module

Connect to an SQL server, validate its schema and perform cached analysis queries.

class ColRef(class_, key, parententity, comparator, impl=None, of_type=None, extra_criteria=())

Bases: InstrumentedAttribute[V], Generic[V, S_contra]

Reference a column by scheme type, name and value type.

Parameters:

• class_ (_ExternalEntityType[_O]) –

• key (str) –

• parententity (_InternalEntityType[_O]) –

• comparator (interfaces.PropComparator[_T_co]) –

• impl (Optional[AttributeImpl]) –

• of_type (Optional[_InternalEntityType[Any]]) –

• extra_criteria (Tuple[ColumnElement[bool], ...]) –

class Col(*arg, **kw)

Bases: MappedColumn[V]

Define table column within a table schema.

Parameters:

• arg (Any) –

• kw (Any) –

classmethod cast_sqla_attr(attr)

Cast a sqlalchemy attr object to this type.

Parameters:

attr (MappedColumn) –

Return type:

Self

column: Column[_T]

foreign_keys: Set[ForeignKey] | None

deferred: _NoArg | bool

deferred_group: str | None

deferred_raiseload: bool

active_history

class ColumnClause

Bases: ColumnsClauseRole, ExpressionElementRole

Mix sqlalchemy classes to mitigate typing mishabs.

class Data(*args, **kwargs)

Bases: Protocol[S_cov]

SQL table-shaped data.

property columns: Mapping[str, ColumnElement]

Return all columns within this data.

property name: str | None

Data object’s name, if any.

class Table(sqla_table)

Bases: Data[S_cov]

Reference to a manifested SQL table.

Parameters:

sqla_table (Table) –

sqla_table: Table

property columns: dict[str, Column]

Return all columns within this data.

property name: str

Data object’s name, if any.

class Query(sel, query_name, schema=None, _subquery=None)

Bases: Data[S_cov]

SQL data defined by a query.

Parameters:

• sel (Select) –

• query_name (dataclasses.InitVar[str]) –

• schema (type[S_cov] | None) –

• _subquery (Subquery | None) –

sel: Select

SQLAlchemy select statement.

query_name: dataclasses.InitVar[str]

Name of this query.

schema: type[S_cov] | None = None

Schema of this query’s result.

property name: str

Name of this query.

property columns: dict[str, ColumnElement]

Return all columns within this data.

class DeferredQuery(func, schema=None, _subquery=None)

Bases: Data[S_cov]

SQL data defined by a query or table-returning Python function.

Parameters:

• func (Callable[[], Select | Table]) –

• schema (type[S_cov] | None) –

• _subquery (Subquery | Table | None) –

func: Callable[[], Select | Table]

Function returning a SQL query or table.

schema: type[S_cov] | None = None

Schema of this query’s result.

property columns: dict[str, ColumnElement]

Return all columns within this data.

property subquery: Subquery | Table

Return subquery or table defined by this query.

property name: str | None

Data object’s name, if any.

class Schema(schema_def=None, db_schema=None, default=False, name=None)

Bases: Generic[S_cov, DS]

SQL schema defining multiple related tables.

Parameters:

• schema_def (type[S_cov] | None) –

• db_schema (type[DS] | None) –

• default (bool) –

• name (str | None) –

schema_def: type[S_cov] | None = None

Schema base class for this schema’s tables.

db_schema: type[DS] | None = None

DBSchema instance this schema is defined in.

default: bool = False

Whether this is the default schema for its schema type.

name: str | None = None

Name of this schema.

property tables: dict[str, Table]

Tables defined by this schema.

class DBSchema

Bases: Generic[S_cov]

Schema for an entire SQL database.

classmethod schema_dict()

Return dict with all sub-schemas.

Return type:

dict[str | None, Schema | None]

classmethod default()

Return default schema.

Return type:

Schema[S_cov, Self]

classmethod defaults()

Return mapping of schema types to default schema names.

Return type:

dict[type[DeclarativeBase], Schema]

classmethod metadata()

Return metadata object containing all this DB’s schemas and tables.

Return type:

MetaData

class DB(url, schema=<class 'py_research.sql.DBSchema'>, tag='YYYY-MM-dd', validate=True)

Bases: Generic[S_cov, DS]

Active connection to a SQL server.

Parameters:

• url (str | URL) –

• schema (type[DBSchema[S_cov]]) –

• tag (str) –

• validate (bool) –

url: str | URL

Connection URL.

schema

Schema for this DB.

alias of DBSchema

tag: str = 'YYYY-MM-dd'

Tag for this DB session. Used to identify automatically created tables.

validate: bool = True

Whether to validate the DB schema on connection.

activate()

Set this instance as the current SQL context.

Return type:

None

default_table(schema)

Return default table for given table schema.

Parameters:

schema (type[S]) – Schema of the table to return.

Returns:

Default table for given schema.

Return type:

Table[S]

to_table(src, schema=None, name=None, with_external_fk=False)

Transfer dataframe or sql query results to manifested SQL table.

Parameters:

• src (DataFrame | Query[S_cov] | DeferredQuery[S_cov]) – Source / definition of data.

• schema (type[S_cov] | None) – Schema of the table to create.

• name (str | None) – Name of the table to create.

• with_external_fk (bool) – Whether to include foreign keys to external tables.

Returns:

Reference to manifested SQL table.

Return type:

Table[S_cov]

to_df(q)

Transfer manifested table or query results to local dataframe.

Parameters:

q (Data) – Source / definition of data.

Returns:

Dataframe containing the data.

Return type:

DataFrame

query(func: Callable[[Params], Select | Table]) → Callable[[Params], Query[S]]

query(*, defer: Literal[True] = False, schema: type[S] = None) → Callable[[Callable[[Params], Select | Table]], Callable[[Params], DeferredQuery[S]]]

query(*, defer: Literal[True] = False, schema: None = None) → Callable[[Callable[[Params], Select | Table]], Callable[[Params], DeferredQuery[Any]]]

query(*, defer: Literal[False] = False, schema: type[S] = None) → Callable[[Callable[[Params], Select | Table]], Callable[[Params], Query[S]]]

query(*, defer: Literal[False] = False, schema: None = None) → Callable[[Callable[[Params], Select | Table]], Callable[[Params], Query[Any]]]

Decorator to transform query-returning function into a referencable query.

Parameters:

• func – Function returning a sqla.Selectable.

• defer – Whether to defer execution of the query.

• schema – Schema of the query’s result.

Returns:

Decorated function.

default_table(schema)

Return default table for given table schema.

Parameters:

schema (type[S]) – Schema of the table to return.

Returns:

Reference to default table for given schema.

Return type:

DeferredQuery[S]