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=())[source]#

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)[source]#

Bases: MappedColumn[V]

Define table column within a table schema.

Parameters:

  • arg (Any)

  • kw (Any)

classmethod cast_sqla_attr(attr)[source]#

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[source]#

Bases: ColumnsClauseRole, ExpressionElementRole

Mix sqlalchemy classes to mitigate typing mishabs.

class Data(*args, **kwargs)[source]#

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)[source]#

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)[source]#

Bases: Data[S_cov]

SQL data defined by a query.

Parameters:
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)[source]#

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)[source]#

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[source]#

Bases: Generic[S_cov]

Schema for an entire SQL database.

classmethod schema_dict()[source]#

Return dict with all sub-schemas.

Return type:

dict[str | None, Schema | None]

classmethod default()[source]#

Return default schema.

Return type:

Schema[S_cov, Self]

classmethod defaults()[source]#

Return mapping of schema types to default schema names.

Return type:

dict[type[DeclarativeBase], Schema]

classmethod metadata()[source]#

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)[source]#

Bases: Generic[S_cov, DS]

Active connection to a SQL server.

Parameters:
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()[source]#

Set this instance as the current SQL context.

Return type:

None

default_table(schema)[source]#

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)[source]#

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)[source]#

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]][source]#
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)[source]#

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]