Python API Reference

User API

sqeleton.table(*path: str, schema: Type[TableType] | CaseAwareMapping | dict | None = None) → TablePath

Defines a table with a path (dotted name), and optionally a schema.

Parameters:

path – A list of names that make up the path to the table.
schema – a dictionary of {name: type}

Inline raw SQL code.

It allows users to use features and syntax that Sqeleton doesn’t yet support.

It’s the user’s responsibility to make sure the contents of the string given to code() are correct and safe for execution.

Strings given to code() are actually templates, and can embed query expressions given as arguments:

Parameters:

code – template string of SQL code. Templated variables are signified with ‘{var}’.
kw – optional parameters for SQL template.

Examples

# SELECT b, <x> FROM tmp WHERE <y>
table('tmp').select(this.b, code("<x>")).where(code("<y>"))

def tablesample(tbl, size):
    return code("SELECT * FROM {tbl} TABLESAMPLE BERNOULLI ({size})", tbl=tbl, size=size)

nonzero = table('points').where(this.x > 0, this.y > 0)

# SELECT * FROM points WHERE (x > 0) AND (y > 0) TABLESAMPLE BERNOULLI (10)
sample_expr = tablesample(nonzero)

sqeleton.connect: alias of <sqeleton.databases._connect.Connect object>

sqeleton.SKIP = SKIP

sqeleton.this = <sqeleton.queries.ast_classes.This object>

Builder object for accessing table attributes.

Automatically evaluates to the the ‘top-most’ table during compilation.

Database

exception sqeleton.databases.base.ConnectError

exception sqeleton.databases.base.QueryError

class sqeleton.databases.base.QueryResult(rows: list = <object object at 0x7f70b309e820>, columns: list = None)

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.databases.base.ThreadLocalInterpreter(compiler: Compiler, gen: Generator)

An interpeter used to execute a sequence of queries within the same thread and cursor.

Useful for cursor-sensitive operations, such as creating a temporary table.

class sqeleton.databases.base.Mixin_Schema

table_information() → TablePath: Query to return a table of schema information about existing tables

list_tables(table_schema: str, like: Compilable | None = None) → Select

Query to select the list of tables in the schema. (query return type: table[str])

If ‘like’ is specified, the value is applied to the table name, using the ‘like’ operator.

class sqeleton.databases.base.Mixin_RandomSample

random_sample_n(tbl: AbstractTable, size: int) → AbstractTable: Take a random sample of the given size, i.e. return ‘size’ amount of rows

random_sample_ratio_approx(tbl: AbstractTable, ratio: float) → AbstractTable

Take a random sample of the approximate size determined by the ratio (0..1), ratio of 0 means no rows, and 1 means all rows

i.e. the actual mount of rows returned may vary by standard deviation.

class sqeleton.databases.base.Mixin_OptimizerHints

optimizer_hints(hints: str) → str

Creates a compatible optimizer_hints string

Parameters:: hints (optimizer_hints - string of optimizer) –

class sqeleton.databases.base.BaseDialect

offset_limit(offset: int | None = None, limit: int | None = None): Provide SQL fragment for limit and offset inside a select

concat(items: List[str]) → str: Provide SQL for concatenating a bunch of columns into a string

is_distinct_from(a: str, b: str) → str: Provide SQL for a comparison where NULL = NULL is true

timestamp_value(t: datetime) → str: Provide SQL for the given timestamp value

uuid_value(u: UUID) → str: Provide SQL for the given UUID value

random() → str: Provide SQL for generating a random number betweein 0..1

current_timestamp() → str: Provide SQL for returning the current timestamp, aka now

explain_as_text(query: str) → str: Provide SQL for explaining a query, returned as table(varchar)

classmethod load_mixins(*abstract_mixins) → Any: Load a list of mixins that implement the given abstract mixins

class sqeleton.databases.base.Database(*args, **kwds)

Base abstract class for databases.

Used for providing connection code and implementation specific SQL utilities.

Instanciated using connect()

property name: The name of the database

Query the given SQL code/AST, and attempt to convert the result to type ‘res_type’

If given a generator:: It will execute all the yielded sql queries with the same thread and cursor. The results of the queries are returned by the yield stmt (using the .send() mechanism). It’s a cleaner approach than exposing cursors, but may not be enough in all cases.

select_table_schema(path: Tuple[str, ...]) → str: Provide SQL for selecting the table schema as (name, type, date_prec, num_prec)

query_table_schema(path: Tuple[str, ...]) → Dict[str, tuple]

Query the database for the schema of the table in ‘path’, and return {column: tuple, …} where the tuple is (table_name, col_name, type_repr, datetime_precision?, numeric_precision?, numeric_scale?)

Use the method .process_query_table_schema() to convert the tuples into types.

Note: This method is used instead of select_table_schema(),: because not all databases support accessing the schema using a SQL query.

select_table_unique_columns(path: Tuple[str, ...]) → str: Provide SQL for selecting the names of unique columns in the table

query_table_unique_columns(path: Tuple[str, ...]) → List[str]: Query the table for its unique columns for table in ‘path’, and return {column}

process_query_table_schema(path: Tuple[str], raw_schema: Dict[str, Tuple], refine: bool = True, refine_where: str | None = None) → Tuple[Dict[str, ColType], list | None]

Process the result of query_table_schema().

We are doing it here, separately, because:

parse_type() may throw an exception. Therefor, some users may want to query each column separately.
Refining the column types may query the database to sample values, and some users may want to do so in separate threads.

If refine is True, it samples the table data to refine the column types when possible. Use refine_where to filter the rows that may be sampled.

parse_table_name(name: str) → Tuple[str, ...]: Parse the given table name into a DbPath

close(): Close connection(s) to the database instance. Querying will stop functioning.

classmethod load_mixins(*abstract_mixins) → type: Extend the dialect with a list of mixins that implement the given abstract mixins.

class sqeleton.databases.base.ThreadedDatabase(thread_count=1)

Access the database through singleton threads.

Used for database connectors that do not support sharing their connection between different threads.

abstract create_connection(): Return a connection instance, that supports the .cursor() method.

close(): Close connection(s) to the database instance. Querying will stop functioning.

property is_autocommit: bool: Return whether the database autocommits changes. When false, COMMIT statements are skipped.

Queries

sqeleton.queries.api.join(*tables: ITable) → Join

Inner-join a sequence of table expressions”

When joining, it’s recommended to use explicit tables names, instead of this, in order to avoid potential name collisions.

Example

person = table('person')
city = table('city')

name_and_city = (
    join(person, city)
    .on(person['city_id'] == city['id'])
    .select(person['id'], city['name'])
)

sqeleton.queries.api.leftjoin(*tables: ITable) → Join

Left-joins a sequence of table expressions.

Internals

This section is for developers who wish to improve sqeleton, or to extend it within their own project.

Regular users might also find it useful for debugging and understanding, especially at this early stage of the project.

Query ASTs

class sqeleton.queries.ast_classes.Root: Nodes inheriting from Root can be used as root statements in SQL (e.g. SELECT yes, RANDOM() no)

exception sqeleton.queries.ast_classes.QueryBuilderError

exception sqeleton.queries.ast_classes.QB_TypeError

sqeleton.queries.ast_classes.cache(user_function, /): Simple lightweight unbounded cache. Sometimes called “memoize”.

class sqeleton.queries.ast_classes.CompilableNode: Base class for query expression nodes

class sqeleton.queries.ast_classes.ExprNode: Base class for query expression nodes

Raw SQL code

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

An alias of a column

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.queries.ast_classes.ITable

Interface for tabular objects, with focus on SQL operations

select(*exprs: ExprNode | str | bytes | bool | int | float | datetime | ArithString | None | dict | list, distinct: bool = SKIP, optimizer_hints=SKIP, **named_exprs) → Select: Create a new table with the specified fields

where(*exprs) → Select

Create a filter for the table, using a WHERE clause.

Parameters:: exprs – A list of expressions to filter the table with. Uses of SKIP will be ignored.
Returns:: A new table expression with the filter applied.

order_by(*exprs)

Order the table by the given expressions, using the ORDER BY clause.

Parameters:: exprs – A list of expressions to order by. Uses of SKIP will be ignored.
Returns:: A new table expression with the order applied.

limit(limit: int)

Limit the number of rows returned by the query, using the LIMIT clause.

Parameters:: limit – The maximum number of rows to return. SKIP will set no limit.

join(target: ITable) → Join

Join this table with the target table.

Parameters:: target – The table to join with.
Returns:: A new table expression representing the join.

group_by(*keys) → GroupBy

Group according to the given keys, using the GROUP BY clause.

Must be followed by a call to :ref:GroupBy.agg()

Parameters:: keys – A list of expressions to group by. Uses of SKIP will be ignored.
Returns:: A new table expression with the group-by applied.

count() → Select: SELECT COUNT(*) FROM self

union(other: ITable) → TableOp: SELECT * FROM self UNION other

union_all(other: ITable) → TableOp: SELECT * FROM self UNION ALL other

minus(other: ITable) → TableOp: SELECT * FROM self EXCEPT other

intersect(other: ITable) → TableOp: SELECT * FROM self INTERSECT other

alias(name: str) → TableAlias: Create an alias for the table. If there already is an alias, it will be replaced.

class sqeleton.queries.ast_classes.Concat(exprs: list = <object object>, sep: str | None = None)

Concatenate expressions, with an optional seperator.

type: alias of str

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

Count the number of rows in a table

type: alias of int

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

Check if the expression matches the regex pattern

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

Call a function with the given arguments

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

A ‘when/then’ clause in a case-when expression

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

A case-when expression

Add a new ‘when’ clause to the case expression

Must be followed by a call to .then()

Add an ‘else’ clause to the case expression.

Can only be called once per case-when!

Parameters:: then – The expression to return if none of the ‘when’ clauses match.
Returns:: A new case-when expression with the ‘else’ clause added.

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

Partial case-when, used for query-building

then(then: ExprNode | str | bytes | bool | int | float | datetime | ArithString | None | dict | list) → CaseWhen: Add a ‘then’ clause after a ‘when’ was added.

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

Check if two expressions are distinct from each other (i.e. not equal, treating NULL as a value)

type: alias of bool

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

Binary operation on expressions

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

Unary operation on an expression

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

Binary boolean operation on expressions

type: alias of bool

class sqeleton.queries.ast_classes.Column(source_table: ~sqeleton.queries.ast_classes.ITable = <object object>, name: str = <object object>)

A column in a table

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.queries.ast_classes.ExprTable

class sqeleton.queries.ast_classes.TablePath(path: ~typing.Tuple[str, ...] = <object object>, schema: ~sqeleton.utils.CaseAwareMapping | None = None)

A path to a table in a database

create(source_table: ITable | None = None, *, if_not_exists: bool = False, primary_keys: List[str] | None = None)

Returns a query expression to create a new table.

Parameters:

source_table – a table expression to use for initializing the table. If not provided, the table must have a schema specified.
if_not_exists – Add a ‘if not exists’ clause or not. (note: not all dbs support it!)
primary_keys – List of column names which define the primary key

drop(if_exists: bool = False)

Returns a query expression to delete the table.

Parameters:: if_not_exists – Add a ‘if not exists’ clause or not. (note: not all dbs support it!)

truncate(): Returns a query expression to truncate the table. (remove all rows)

Returns a query expression to delete rows from the table.

Parameters:: where_exprs – A list of expressions to filter the rows to delete. Uses of SKIP will be ignored. If no where expressions are provided, all rows will be deleted.
Returns:: A new query expression to delete rows from the table.

Returns a query expression to update fields in the table.

Parameters:

where_exprs – A list of expressions to filter the rows to update. Uses of SKIP will be ignored. If no where expressions are provided, all rows will be updated.
kv – A dictionary of column names and values to update. The values can be expressions.
Returns – A new query expression to update fields in the table.

insert_rows(rows: Sequence, *, columns: List[str] | None = None)

Returns a query expression to insert rows to the table, given as Python values.

Parameters:

rows – A list of tuples. Must all have the same width.
columns – Names of columns being populated. If specified, must have the same length as the tuples.

insert_row(*values, columns: List[str] | None = None, **kw)

Returns a query expression to insert a single row to the table, given as Python values.

Parameters:: columns – Names of columns being populated. If specified, must have the same length as ‘values’

Returns a query expression to insert rows to the table, given as a query expression.

Parameters:: expr – query expression to from which to read the rows

time_travel(*, before: bool = False, timestamp: datetime | None = None, offset: int | None = None, statement: str | None = None) → Compilable

Selects historical data from the table

Parameters:

before – If false, inclusive of the specified point in time. If True, only return the time before it. (at/before)
timestamp – A constant timestamp
offset – the time ‘offset’ seconds before now
statement – identifier for statement, e.g. query ID

Must specify exactly one of timestamp, offset or statement.

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.queries.ast_classes.ForeignKey(table: ~sqeleton.queries.ast_classes.TablePath = <object object>, field: str = <object object>)

A foreign key constraint

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.queries.ast_classes.TableAlias(source_table: ~sqeleton.queries.ast_classes.ITable = <object object>, name: str = <object object>)

An alias for a table

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.queries.ast_classes.Exists(expr: ~sqeleton.queries.ast_classes.ITable = <object object>)

Check if a subquery returns any rows

type: alias of bool

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

A join operation between two tables

on(*exprs) → Join: Add an ON clause, for filtering the result of the cartesian product (i.e. the JOIN)

Select fields to return from the JOIN operation

See Also: ITable.select()

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

A group-by operation using the GROUP BY clause

having(*exprs) → GroupBy

Add a ‘HAVING’ clause to the group-by

Parameters:: exprs – A list of expressions to filter the groups. Uses of SKIP will be ignored.
Returns:: A new group-by expression with the filter applied.

agg(*exprs, **named_exprs) → GroupBy

Select aggregated fields for the group-by.

Parameters:

exprs – A list of expressions to aggregate. Uses of SKIP will be ignored.
named_exprs – A dictionary of named expressions to aggregate. Uses of SKIP will be ignored.

Returns:

A new group-by expression with the aggregated fields applied.

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.queries.ast_classes.TableOp(op: str = <object object>, table1: ~sqeleton.queries.ast_classes.ITable = <object object>, table2: ~sqeleton.queries.ast_classes.ITable = <object object>)

A binary operation between two tables

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.queries.ast_classes.Desc(expr: ~sqeleton.queries.ast_classes.ExprNode = <object object>)

A descending order

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

A SELECT statement

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

A common table expression (i.e. shared subquery)

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.queries.ast_classes.Wildcard(exclude: ~typing.List[str] = <object object>)

Wildcard for selecting all columns

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.queries.ast_classes.This

Builder object for accessing table attributes.

Automatically evaluates to the the ‘top-most’ table during compilation.

Check if an expression is in a list of values

type: alias of bool

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

Check if an expression is in a table

type: alias of bool

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

Cast an expression to a different type

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.queries.ast_classes.Random

A random number

type: alias of float

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.queries.ast_classes.ConstantTable(rows: ~typing.Sequence[~typing.Sequence] = <object object>)

A table of constant values

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.queries.ast_classes.Explain(select: ~sqeleton.queries.ast_classes.Select = <object object>)

An EXPLAIN statement

type: alias of str

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.queries.ast_classes.CurrentTimestamp

The current timestamp

type: alias of datetime

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.queries.ast_classes.TimeTravel(table: ~sqeleton.queries.ast_classes.TablePath = <object object>, before: bool = False, timestamp: ~datetime.datetime | None = None, offset: int | None = None, statement: str | None = None)

A time-travel operation

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.queries.ast_classes.Statement

a CREATE TABLE statement

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.queries.ast_classes.DropTable(path: ~sqeleton.queries.ast_classes.TablePath = <object object>, if_exists: bool = False)

a DROP TABLE statement

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.queries.ast_classes.TruncateTable(path: ~sqeleton.queries.ast_classes.TablePath = <object object>)

a TRUNCATE TABLE statement

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.queries.ast_classes.Statement_MaybeReturning

returning(*exprs)

Add a ‘RETURNING’ clause to the current node.

Note: Not all databases support this feature!

a DELETE FROM statement

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

an UPDATE statement

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

an INSERT INTO statement

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.queries.ast_classes.Commit

Generate a COMMIT statement, if we’re in the middle of a transaction, or in auto-commit. Otherwise SKIP.

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.queries.ast_classes.Param(name: str = <object object>)

A value placeholder, to be specified at compilation time using the cv_params context variable.

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

Query Compiler

exception sqeleton.queries.compiler.CompileError

class sqeleton.queries.compiler.CompiledCode(code: str = <object object at 0x7f70b309e820>, args: List[Any] = <object object at 0x7f70b309e820>, type: Union[type, NoneType] = <object object at 0x7f70b309e820>)

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.queries.compiler.Compiler(database: sqeleton.abcs.database_types.AbstractDatabase = <object object at 0x7f70b309e820>, in_select: bool = False, in_join: bool = False, _table_context: List = <factory>, _subqueries: Dict[str, Any] = <factory>, _args: Dict[str, Any] = <factory>, _args_enabled: bool = False, _is_root: bool = True, _counter: List = <factory>)

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.queries.compiler.SelectCompiler(c: sqeleton.queries.compiler.Compiler = <object object at 0x7f70b309e820>)

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

ABCS

class sqeleton.abcs.database_types.PrecisionType(precision: int = <object object at 0x7f70b309e820>, rounds: Union[bool, sqeleton.utils.Unknown] = Unknown)

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.abcs.database_types.TemporalType(precision: int = <object object>, rounds: bool | ~sqeleton.utils.Unknown = Unknown)

class sqeleton.abcs.database_types.Timestamp(precision: int = <object object>, rounds: bool | ~sqeleton.utils.Unknown = Unknown)

class sqeleton.abcs.database_types.TimestampTZ(precision: int = <object object>, rounds: bool | ~sqeleton.utils.Unknown = Unknown)

class sqeleton.abcs.database_types.Datetime(precision: int = <object object>, rounds: bool | ~sqeleton.utils.Unknown = Unknown)

class sqeleton.abcs.database_types.Date(precision: int = <object object>, rounds: bool | ~sqeleton.utils.Unknown = Unknown)

class sqeleton.abcs.database_types.NumericType(precision: int = <object object at 0x7f70b309e820>)

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.abcs.database_types.FractionalType(precision: int = <object object>)

class sqeleton.abcs.database_types.Float(precision: int = <object object>)

python_type: alias of float

class sqeleton.abcs.database_types.IKey

Interface for ColType, for using a column as a key in table.

abstract property python_type: type: Return the equivalent Python type of the key

class sqeleton.abcs.database_types.Decimal(precision: int = <object object>)

property python_type: type: Return the equivalent Python type of the key

class sqeleton.abcs.database_types.StringType

python_type: alias of str

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.abcs.database_types.ColType_UUID

python_type: alias of ArithUUID

class sqeleton.abcs.database_types.ColType_Alphanum

python_type: alias of ArithAlphanumeric

class sqeleton.abcs.database_types.Native_UUID

class sqeleton.abcs.database_types.String_UUID

class sqeleton.abcs.database_types.String_Alphanum

class sqeleton.abcs.database_types.String_VaryingAlphanum

class sqeleton.abcs.database_types.String_FixedAlphanum(length: int = <object object at 0x7f70b309e820>)

class sqeleton.abcs.database_types.Text

class sqeleton.abcs.database_types.Integer(precision: int = 0, python_type: type = <class 'int'>)

class sqeleton.abcs.database_types.UnknownColType(text: str = <object object at 0x7f70b309e820>)

asdict(): Returns a dict of {name: value, …}

asitems(): Yields a list of tuples [(name, value), …]

class sqeleton.abcs.database_types.AbstractDialect

Dialect-dependent query expressions

abstract property name: str: Name of the dialect

abstract classmethod load_mixins(*abstract_mixins) → Any: Load a list of mixins that implement the given abstract mixins

abstract property ROUNDS_ON_PREC_LOSS: bool: True if db rounds real values when losing precision, False if it truncates.

abstract quote(s: str): Quote SQL name

abstract concat(items: List[str]) → str: Provide SQL for concatenating a bunch of columns into a string

abstract is_distinct_from(a: str, b: str) → str: Provide SQL for a comparison where NULL = NULL is true

abstract to_string(s: str) → str: Provide SQL for casting a column to string

abstract random() → str: Provide SQL for generating a random number betweein 0..1

abstract current_timestamp() → str: Provide SQL for returning the current timestamp, aka now

abstract offset_limit(offset: int | None = None, limit: int | None = None): Provide SQL fragment for limit and offset inside a select

abstract explain_as_text(query: str) → str: Provide SQL for explaining a query, returned as table(varchar)

abstract timestamp_value(t: datetime) → str: Provide SQL for the given timestamp value

abstract uuid_value(t: UUID) → str: Provide SQL for the given UUID value

abstract set_timezone_to_utc() → str: Provide SQL for setting the session timezone to UTC

abstract parse_type(table_path: Tuple[str, ...], col_name: str, type_repr: str, datetime_precision: int | None = None, numeric_precision: int | None = None, numeric_scale: int | None = None) → ColType: Parse type info as returned by the database

class sqeleton.abcs.database_types.AbstractDatabase(*args, **kwds)

abstract property name: str: The name of the database

abstract property dialect: T_Dialect: The dialect of the database. Used internally by Database, and also available publicly.

abstract classmethod load_mixins(*abstract_mixins) → type: Extend the dialect with a list of mixins that implement the given abstract mixins.

abstract property CONNECT_URI_HELP: str: Example URI to show the user in help and error messages

abstract property CONNECT_URI_PARAMS: List[str]: List of parameters given in the path of the URI

abstract query_table_schema(path: Tuple[str, ...]) → Dict[str, tuple]

Query the database for the schema of the table in ‘path’, and return {column: tuple, …} where the tuple is (table_name, col_name, type_repr, datetime_precision?, numeric_precision?, numeric_scale?)

Use the method .process_query_table_schema() to convert the tuples into types.

Note: This method is used instead of select_table_schema(),: because not all databases support accessing the schema using a SQL query.

abstract select_table_unique_columns(path: Tuple[str, ...]) → str: Provide SQL for selecting the names of unique columns in the table

abstract query_table_unique_columns(path: Tuple[str, ...]) → List[str]: Query the table for its unique columns for table in ‘path’, and return {column}

abstract process_query_table_schema(path: Tuple[str, ...], raw_schema: Dict[str, tuple], refine: bool = True, refine_where: str | None = None) → Tuple[Dict[str, ColType], list | None]

Process the result of query_table_schema().

We are doing it here, separately, because:

parse_type() may throw an exception. Therefor, some users may want to query each column separately.
Refining the column types may query the database to sample values, and some users may want to do so in separate threads.

If refine is True, it samples the table data to refine the column types when possible. Use refine_where to filter the rows that may be sampled.

abstract parse_table_name(name: str) → Tuple[str, ...]: Parse the given table name into a DbPath

abstract close(): Close connection(s) to the database instance. Querying will stop functioning.

abstract property is_autocommit: bool: Return whether the database autocommits changes. When false, COMMIT statements are skipped.

class sqeleton.abcs.database_types.AbstractTable

abstract select(*exprs, distinct=False, **named_exprs) → AbstractTable

Choose new columns, based on the old ones. (aka Projection)

Parameters:

exprs – List of expressions to constitute the columns of the new table. If not provided, returns all columns in source table (i.e. select *)
distinct – ‘select’ or ‘select distinct’
named_exprs – More expressions to constitute the columns of the new table, aliased to keyword name.

abstract where(*exprs) → AbstractTable: Filter the rows, based on the given predicates. (aka Selection)

abstract order_by(*exprs) → AbstractTable: Order the rows lexicographically, according to the given expressions.

abstract limit(limit: int) → AbstractTable: Stop yielding rows after the given limit. i.e. take the first ‘n=limit’ rows

abstract join(target) → AbstractTable

Join the current table with the target table, returning a new table containing both side-by-side.

When joining, it’s recommended to use explicit tables names, instead of this, in order to avoid potential name collisions.

Example

person = table('person')
city = table('city')

name_and_city = (
    person
    .join(city)
    .on(person['city_id'] == city['id'])
    .select(person['id'], city['name'])
)

abstract group_by(*keys)

Behaves like in SQL, except for a small change in syntax:

A call to .agg() must follow every call to .group_by().

Example

# SELECT a, sum(b) FROM tmp GROUP BY 1
table('tmp').group_by(this.a).agg(this.b.sum())

# SELECT a, sum(b) FROM a GROUP BY 1 HAVING (b > 10)
(table('tmp')
    .group_by(this.a)
    .agg(this.b.sum())
    .having(this.b > 10)
)

abstract count() → int: SELECT count() FROM self

abstract union(other: AbstractTable): SELECT * FROM self UNION other

abstract union_all(other: AbstractTable): SELECT * FROM self UNION ALL other

abstract minus(other: AbstractTable): SELECT * FROM self EXCEPT other

abstract intersect(other: AbstractTable): SELECT * FROM self INTERSECT other

class sqeleton.abcs.mixins.AbstractMixin: A mixin for a database dialect

class sqeleton.abcs.mixins.AbstractMixin_NormalizeValue

abstract normalize_timestamp(value: str, coltype: TemporalType) → str

Creates an SQL expression, that converts ‘value’ to a normalized timestamp.

The returned expression must accept any SQL datetime/timestamp, and return a string.

Date format: YYYY-MM-DD HH:mm:SS.FFFFFF

Precision of dates should be rounded up/down according to coltype.rounds

abstract normalize_number(value: str, coltype: FractionalType) → str

Creates an SQL expression, that converts ‘value’ to a normalized number.

The returned expression must accept any SQL int/numeric/float, and return a string.

Floats/Decimals are expected in the format “I.P”

Where I is the integer part of the number (as many digits as necessary), and must be at least one digit (0). P is the fractional digits, the amount of which is specified with coltype.precision. Trailing zeroes may be necessary. If P is 0, the dot is omitted.

Note: We use ‘precision’ differently than most databases. For decimals, it’s the same as numeric_scale, and for floats, who use binary precision, it can be calculated as log10(2**numeric_precision).

normalize_boolean(value: str, _coltype: Boolean) → str: Creates an SQL expression, that converts ‘value’ to either ‘0’ or ‘1’.

normalize_uuid(value: str, coltype: ColType_UUID) → str: Creates an SQL expression, that strips uuids of artifacts like whitespace.

normalize_value_by_type(value: str, coltype: ColType) → str

Creates an SQL expression, that converts ‘value’ to a normalized representation.

The returned expression must accept any SQL value, and return a string.

The default implementation dispatches to a method according to coltype:

TemporalType    -> normalize_timestamp()
FractionalType  -> normalize_number()
*else*          -> to_string()

(`Integer` falls in the *else* category)

class sqeleton.abcs.mixins.AbstractMixin_MD5

Methods for calculating an MD6 hash as an integer.

abstract md5_as_int(s: str) → str: Provide SQL for computing md5 and returning an int

class sqeleton.abcs.mixins.AbstractMixin_Schema

Methods for querying the database schema

TODO: Move AbstractDatabase.query_table_schema() and friends over here

table_information() → Compilable: Query to return a table of schema information about existing tables

abstract list_tables(table_schema: str, like: Compilable | None = None) → Compilable

Query to select the list of tables in the schema. (query return type: table[str])

If ‘like’ is specified, the value is applied to the table name, using the ‘like’ operator.

class sqeleton.abcs.mixins.AbstractMixin_Regex

abstract test_regex(string: Compilable, pattern: Compilable) → Compilable: Tests whether the regex pattern matches the string. Returns a bool expression.

class sqeleton.abcs.mixins.AbstractMixin_RandomSample

abstract random_sample_n(tbl: str, size: int) → str: Take a random sample of the given size, i.e. return ‘size’ amount of rows

abstract random_sample_ratio_approx(tbl: str, ratio: float) → str

Take a random sample of the approximate size determined by the ratio (0..1), ratio of 0 means no rows, and 1 means all rows

i.e. the actual mount of rows returned may vary by standard deviation.

class sqeleton.abcs.mixins.AbstractMixin_TimeTravel

abstract time_travel(table: Compilable, before: bool = False, timestamp: Compilable | None = None, offset: Compilable | None = None, statement: Compilable | None = None) → Compilable

Selects historical data from a table

Parameters:

querying (table - The name of the table whose history we're) –
timestamp (timestamp - A constant) –
now (offset - the time 'offset' seconds before) –
statement (statement - identifier for) –
ID (e.g. query) –

Must specify exactly one of timestamp, offset or statement.

class sqeleton.abcs.mixins.AbstractMixin_OptimizerHints

abstract optimizer_hints(optimizer_hints: str) → str

Creates a compatible optimizer_hints string

Parameters:: hints (optimizer_hints - string of optimizer) –