Python API Reference

User API

Database

exception sqeleton.databases.base.ConnectError

exception sqeleton.databases.base.QueryError

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() → Compilable

Query to return a table of schema information about existing tables

list_tables(table_schema: str, like: Optional[Compilable] = 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.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), where 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: Optional[int] = None, limit: Optional[int] = 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

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.QueryResult(rows: list[Any] = <object object at 0x7f9256cef120>, columns: (list[Any]+NoneType) = None)

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()

query(sql_ast: Union[ExprNode, str, bool, int, float, datetime, ArithString, None, Generator], res_type: Optional[type] = None)

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 a 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 table for its schema for table in ‘path’, and return {column: tuple} where the tuple is (table_name, col_name, type_repr, datetime_precision?, numeric_precision?, numeric_scale?)

Note: This method exists instead of select_table_schema(), just 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}

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)

Left-joins a sequence of table expressions.

See Also: join()

sqeleton.queries.api.rightjoin(*tables: ITable)

Right-joins a sequence of table expressions.

See Also: join()

sqeleton.queries.api.outerjoin(*tables: ITable)

Outer-joins a sequence of table expressions.

See Also: join()

sqeleton.queries.api.cte(expr: Optional[Union[ExprNode, str, bool, int, float, datetime, ArithString]], *, name: Optional[str] = None, params: Optional[Sequence[str]] = None)

Define a CTE

sqeleton.queries.api.table(*path: str, schema: Optional[Union[dict, CaseAwareMapping]] = 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}

sqeleton.queries.api.or_(*exprs: Optional[Union[ExprNode, str, bool, int, float, datetime, ArithString]])

Apply OR between a sequence of boolean expressions

sqeleton.queries.api.and_(*exprs: Optional[Union[ExprNode, str, bool, int, float, datetime, ArithString]])

Apply AND between a sequence of boolean expressions

sqeleton.queries.api.sum_(expr: Optional[Union[ExprNode, str, bool, int, float, datetime, ArithString]])

Call SUM(expr)

sqeleton.queries.api.avg(expr: Optional[Union[ExprNode, str, bool, int, float, datetime, ArithString]])

Call AVG(expr)

sqeleton.queries.api.min_(expr: Optional[Union[ExprNode, str, bool, int, float, datetime, ArithString]])

Call MIN(expr)

sqeleton.queries.api.max_(expr: Optional[Union[ExprNode, str, bool, int, float, datetime, ArithString]])

Call MAX(expr)

sqeleton.queries.api.exists(expr: Optional[Union[ExprNode, str, bool, int, float, datetime, ArithString]])

Call EXISTS(expr)

sqeleton.queries.api.if_(cond: Optional[Union[ExprNode, str, bool, int, float, datetime, ArithString]], then: Optional[Union[ExprNode, str, bool, int, float, datetime, ArithString]], else_: Optional[Union[ExprNode, str, bool, int, float, datetime, ArithString]] = None)

Conditional expression, shortcut to when-then-else.

Example

# SELECT CASE WHEN b THEN c ELSE d END FROM foo
table('foo').select(if_(b, c, d))

sqeleton.queries.api.when(*when_exprs: Optional[Union[ExprNode, str, bool, int, float, datetime, ArithString]])

Start a when-then expression

Example

# SELECT CASE
#   WHEN (type = 'text') THEN text
#   WHEN (type = 'number') THEN number
#   ELSE 'unknown type' END
# FROM foo
rows = table('foo').select(
        when(this.type == 'text').then(this.text)
        .when(this.type == 'number').then(this.number)
        .else_('unknown type')
    )

sqeleton.queries.api.coalesce(*exprs)

Returns a call to COALESCE

sqeleton.queries.api.current_timestamp()

Returns CURRENT_TIMESTAMP() or NOW()

sqeleton.queries.api.code(code: str, **kw: Dict[str, Optional[Union[ExprNode, str, bool, int, float, datetime, ArithString]]]) → Code

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)

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

exception sqeleton.queries.ast_classes.QueryBuilderError

exception sqeleton.queries.ast_classes.QB_TypeError

class sqeleton.queries.ast_classes.ExprNode

Base class for query expression nodes

class sqeleton.queries.ast_classes.Code(code: str = <object object at 0x7f9256cef120>, args: (dict[(str*(str+int+float+bool+ArithString+NoneType+datetime+ExprNode))]+NoneType) = None)

class sqeleton.queries.ast_classes.Alias(expr: (str+int+float+bool+ArithString+NoneType+datetime+ExprNode) = <object object at 0x7f9256cef120>, name: str = <object object at 0x7f9256cef120>)

class sqeleton.queries.ast_classes.ITable

select(*exprs, distinct=SKIP, optimizer_hints=SKIP, **named_exprs) → ITable

Create a new table with the specified fields

where(*exprs)

Filter the rows, based on the given predicates. (aka Selection)

order_by(*exprs)

Order the rows lexicographically, according to the given expressions.

limit(limit: int)

Stop yielding rows after the given limit. i.e. take the first ‘n=limit’ rows

join(target: ITable)

Join this table with the target table.

group_by(*keys) → GroupBy

Group according to the given keys.

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

count()

SELECT count() FROM self

union(other: ITable)

SELECT * FROM self UNION other

union_all(other: ITable)

SELECT * FROM self UNION ALL other

minus(other: ITable)

SELECT * FROM self EXCEPT other

intersect(other: ITable)

SELECT * FROM self INTERSECT other

class sqeleton.queries.ast_classes.Concat(exprs: list[Any] = <object object at 0x7f9256cef120>, sep: (str+NoneType) = None)

class sqeleton.queries.ast_classes.Count(expr: NoneType + str + int + float + bool + ArithString + NoneType + datetime + ExprNode = None, distinct: bool = False)

type

alias of int

class sqeleton.queries.ast_classes.TestRegex(string: (str+int+float+bool+ArithString+NoneType+datetime+ExprNode) = <object object at 0x7f9256cef120>, pattern: (str+int+float+bool+ArithString+NoneType+datetime+ExprNode) = <object object at 0x7f9256cef120>)

class sqeleton.queries.ast_classes.Func(name: str = <object object at 0x7f9256cef120>, args: Sequence[(str+int+float+bool+ArithString+NoneType+datetime+ExprNode)] = <object object at 0x7f9256cef120>)

class sqeleton.queries.ast_classes.WhenThen(when: (str+int+float+bool+ArithString+NoneType+datetime+ExprNode) = <object object at 0x7f9256cef120>, then: (str+int+float+bool+ArithString+NoneType+datetime+ExprNode) = <object object at 0x7f9256cef120>)

class sqeleton.queries.ast_classes.CaseWhen(cases: Sequence[WhenThen] = <object object at 0x7f9256cef120>, else_expr: (NoneType+(str+int+float+bool+ArithString+NoneType+datetime+ExprNode)) = None)

when(*whens: Optional[Union[ExprNode, str, bool, int, float, datetime, ArithString]]) → QB_When

Add a new ‘when’ clause to the case expression

Must be followed by a call to .then()

else_(then: Optional[Union[ExprNode, str, bool, int, float, datetime, ArithString]])

Add an ‘else’ clause to the case expression.

Can only be called once!

class sqeleton.queries.ast_classes.QB_When(casewhen: CaseWhen = <object object>, when: (str+int+float+bool+ArithString+NoneType+datetime+ExprNode) = <object object>)

Partial case-when, used for query-building

then(then: Optional[Union[ExprNode, str, bool, int, float, datetime, ArithString]]) → CaseWhen

Add a ‘then’ clause after a ‘when’ was added.

class sqeleton.queries.ast_classes.IsDistinctFrom(a: (str+int+float+bool+ArithString+NoneType+datetime+ExprNode) = <object object at 0x7f9256cef120>, b: (str+int+float+bool+ArithString+NoneType+datetime+ExprNode) = <object object at 0x7f9256cef120>)

type

alias of bool

class sqeleton.queries.ast_classes.BinOp(op: str = <object object at 0x7f9256cef120>, args: Sequence[(str+int+float+bool+ArithString+NoneType+datetime+ExprNode)] = <object object at 0x7f9256cef120>)

class sqeleton.queries.ast_classes.UnaryOp(op: str = <object object at 0x7f9256cef120>, expr: (str+int+float+bool+ArithString+NoneType+datetime+ExprNode) = <object object at 0x7f9256cef120>)

class sqeleton.queries.ast_classes.BinBoolOp(op: str = <object object>, args: Sequence[(str+int+float+bool+ArithString+NoneType+datetime+ExprNode)] = <object object>)

type

alias of bool

class sqeleton.queries.ast_classes.Column(source_table: ITable = <object object at 0x7f9256cef120>, name: str = <object object at 0x7f9256cef120>)

class sqeleton.queries.ast_classes.TablePath(path: tuple[str] = <object object at 0x7f9256cef120>, schema: (NoneType+CaseAwareMapping) = None)

create(source_table: Optional[ITable] = None, *, if_not_exists: bool = False, primary_keys: Optional[List[str]] = 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=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)

insert_rows(rows: Sequence, *, columns: Optional[List[str]] = 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: Optional[List[str]] = None)

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’

insert_expr(expr: Optional[Union[ExprNode, str, bool, int, float, datetime, ArithString]])

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: Optional[datetime] = None, offset: Optional[int] = None, statement: Optional[str] = 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.

class sqeleton.queries.ast_classes.TableAlias(source_table: ITable = <object object at 0x7f9256cef120>, name: str = <object object at 0x7f9256cef120>)

class sqeleton.queries.ast_classes.Join(source_tables: Sequence[ITable] = <object object at 0x7f9256cef120>, op: (str+NoneType) = None, on_exprs: (Sequence[(str+int+float+bool+ArithString+NoneType+datetime+ExprNode)]+NoneType) = None, columns: (Sequence[(str+int+float+bool+ArithString+NoneType+datetime+ExprNode)]+NoneType) = None)

on(*exprs) → Join

Add an ON clause, for filtering the result of the cartesian product (i.e. the JOIN)

select(*exprs, **named_exprs) → ITable

Select fields to return from the JOIN operation

See Also: ITable.select()

class sqeleton.queries.ast_classes.GroupBy(table: ITable = <object object at 0x7f9256cef120>, keys: (Sequence[(str+int+float+bool+ArithString+NoneType+datetime+ExprNode)]+NoneType) = None, values: (Sequence[(str+int+float+bool+ArithString+NoneType+datetime+ExprNode)]+NoneType) = None, having_exprs: (Sequence[(str+int+float+bool+ArithString+NoneType+datetime+ExprNode)]+NoneType) = None)

having(*exprs)

Add a ‘HAVING’ clause to the group-by

agg(*exprs)

Select aggregated fields for the group-by.

class sqeleton.queries.ast_classes.TableOp(op: str = <object object at 0x7f9256cef120>, table1: ITable = <object object at 0x7f9256cef120>, table2: ITable = <object object at 0x7f9256cef120>)

class sqeleton.queries.ast_classes.Select(table: NoneType + str + int + float + bool + ArithString + NoneType + datetime + ExprNode = None, columns: Sequence[str + int + float + bool + ArithString + NoneType + datetime + ExprNode] + NoneType = None, where_exprs: Sequence[str + int + float + bool + ArithString + NoneType + datetime + ExprNode] + NoneType = None, order_by_exprs: Sequence[str + int + float + bool + ArithString + NoneType + datetime + ExprNode] + NoneType = None, group_by_exprs: Sequence[str + int + float + bool + ArithString + NoneType + datetime + ExprNode] + NoneType = None, having_exprs: Sequence[str + int + float + bool + ArithString + NoneType + datetime + ExprNode] + NoneType = None, limit_expr: NoneType + int = None, distinct: bool = False, optimizer_hints: Sequence[str + int + float + bool + ArithString + NoneType + datetime + ExprNode] + NoneType = None)

class sqeleton.queries.ast_classes.Cte(source_table: (str+int+float+bool+ArithString+NoneType+datetime+ExprNode) = <object object at 0x7f9256cef120>, name: (str+NoneType) = None, params: (Sequence[str]+NoneType) = None)

class sqeleton.queries.ast_classes.This

Builder object for accessing table attributes.

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

class sqeleton.queries.ast_classes.In(expr: (str+int+float+bool+ArithString+NoneType+datetime+ExprNode) = <object object at 0x7f9256cef120>, list: Sequence[(str+int+float+bool+ArithString+NoneType+datetime+ExprNode)] = <object object at 0x7f9256cef120>)

type

alias of bool

class sqeleton.queries.ast_classes.Cast(expr: (str+int+float+bool+ArithString+NoneType+datetime+ExprNode) = <object object at 0x7f9256cef120>, target_type: (str+int+float+bool+ArithString+NoneType+datetime+ExprNode) = <object object at 0x7f9256cef120>)

class sqeleton.queries.ast_classes.Random

type

alias of float

class sqeleton.queries.ast_classes.ConstantTable(rows: Sequence[Sequence[Any]] = <object object at 0x7f9256cef120>)

class sqeleton.queries.ast_classes.Explain(select: Select = <object object at 0x7f9256cef120>)

type

alias of str

class sqeleton.queries.ast_classes.CurrentTimestamp

type

alias of datetime

class sqeleton.queries.ast_classes.TimeTravel(table: TablePath = <object object at 0x7f9256cef120>, before: bool = False, timestamp: (datetime+NoneType) = None, offset: (NoneType+int) = None, statement: (str+NoneType) = None)

class sqeleton.queries.ast_classes.Statement

class sqeleton.queries.ast_classes.CreateTable(path: TablePath = <object object at 0x7f9256cef120>, source_table: (NoneType+(str+int+float+bool+ArithString+NoneType+datetime+ExprNode)) = None, if_not_exists: bool = False, primary_keys: (NoneType+list[str]) = None)

class sqeleton.queries.ast_classes.DropTable(path: TablePath = <object object at 0x7f9256cef120>, if_exists: bool = False)

class sqeleton.queries.ast_classes.TruncateTable(path: TablePath = <object object at 0x7f9256cef120>)

class sqeleton.queries.ast_classes.InsertToTable(path: TablePath = <object object at 0x7f9256cef120>, expr: (str+int+float+bool+ArithString+NoneType+datetime+ExprNode) = <object object at 0x7f9256cef120>, columns: (NoneType+list[str]) = None, returning_exprs: (NoneType+list[str]) = None)

returning(*exprs)

Add a ‘RETURNING’ clause to the insert expression.

Note: Not all databases support this feature!

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.

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.

Query Compiler

exception sqeleton.queries.compiler.CompileError

class sqeleton.queries.compiler.Root

Nodes inheriting from Root can be used as root statements in SQL (e.g. SELECT yes, RANDOM() no)

class sqeleton.queries.compiler.Compiler(database: AbstractDatabase = <object object at 0x7f9256cef120>, params: dict[(Any*Any)] = <factory>, in_select: bool = False, in_join: bool = False, _table_context: list[Any] = <factory>, _subqueries: dict[(str*Any)] = <factory>, root: bool = True, _counter: list[Any] = <factory>)

ABCS

class sqeleton.abcs.database_types.PrecisionType(precision: int = <object object at 0x7f9256cef120>, rounds: (Unknown+bool) = Unknown)

class sqeleton.abcs.database_types.TemporalType(precision: int = <object object>, rounds: (Unknown+bool) = Unknown)

class sqeleton.abcs.database_types.Timestamp(precision: int = <object object>, rounds: (Unknown+bool) = Unknown)

class sqeleton.abcs.database_types.TimestampTZ(precision: int = <object object>, rounds: (Unknown+bool) = Unknown)

class sqeleton.abcs.database_types.Datetime(precision: int = <object object>, rounds: (Unknown+bool) = Unknown)

class sqeleton.abcs.database_types.Date(precision: int = <object object>, rounds: (Unknown+bool) = Unknown)

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

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

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 0x7f9256cef120>)

class sqeleton.abcs.database_types.Text

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

python_type

alias of int

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

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: Optional[int] = None, limit: Optional[int] = 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 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: Optional[int] = None, numeric_precision: Optional[int] = None, numeric_scale: Optional[int] = None) → ColType

Parse type info as returned by the database

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

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 table for its schema for table in ‘path’, and return {column: tuple} where the tuple is (table_name, col_name, type_repr, datetime_precision?, numeric_precision?, numeric_scale?)

Note: This method exists instead of select_table_schema(), just 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 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: ITable)

SELECT * FROM self UNION other

abstract union_all(other: ITable)

SELECT * FROM self UNION ALL other

abstract minus(other: ITable)

SELECT * FROM self EXCEPT other

abstract intersect(other: ITable)

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: Optional[Compilable] = 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), where 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: Optional[Compilable] = None, offset: Optional[Compilable] = None, statement: Optional[Compilable] = 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) –