GitHub

statement

API reference for statement.

SQL statements and fragments (Effect's effect/sql/Statement).

A client (SqlClient) is a Constructor: call it with SQL text and values to get a Statement, an effect that runs it::

sql = yield from SqlClient()
users = yield from sql("SELECT * FROM users WHERE id = {id}", id=user_id)

Python 3.13 has no tagged templates, so the text is a str.format-style template whose placeholders are always bound parameters (or fragments and helpers placed as they are). The text must be a string literal: its type is LiteralString, so pyright rejects text built at runtime, and sql.unsafe(text, params) is the one way to run such text. On Python 3.14, a t-string is accepted too: sql(t"... {user_id}").

  • Fragment and its segments (Literal, Identifier, Parameter, ArrayHelper, the record helpers, Custom)
  • and_, or_, csv, join, in_, insert, update, update_values: helpers, also on every constructor
  • Compiler, make_compiler, make_compiler_sqlite, default_escape: rendering fragments for a dialect
  • default_transforms: renaming result columns (fx.case has the usual renamings)
  • CurrentTransformer, SpanPropagationEnabled: references that steer how statements run

Values

AttributeTypeDescription
and_(Sequence[LiteralString | Fragment]) -> Fragment

Clauses joined with AND (1=1 for none).

or_(Sequence[LiteralString | Fragment]) -> Fragment

Clauses joined with OR (1=1 for none).

Types

TypeDefinitionDescription
Compiledtype Compiled = tuple[str, tuple[object, ...]]

SQL text and its parameters, in placeholder order.

Dialecttype Dialect = Literal['sqlite', 'pg', 'mysql', 'mssql', 'clickhouse']
Placeholdertype Placeholder = Callable[[object], str]

Renders the placeholder of the next parameter.

Helpertype Helper = ArrayHelper | RecordInsertHelper | RecordUpdateHelper | RecordUpdateHelperSingle | Identifier | Custom[Any, Any, Any, Any]

The segments a template places as they are (not as parameters).

Segmenttype Segment = Literal | Identifier | Parameter | ArrayHelper | RecordInsertHelper | RecordUpdateHelper | RecordUpdateHelperSingle | Custom[Any, Any, Any, Any]
SqlTexttype SqlText = LiteralString

The SQL text of a statement: a string literal with {} placeholders (a t-string too on Python 3.14).

SpanAttributestype SpanAttributes = Sequence[tuple[str, object]]
Transformertype Transformer = Callable[[Statement[Any], Constructor, Fiber[Any, Any], Span], Effect[Statement[Any]]]

Rewrites or wraps a statement before it runs, given the constructor of its client, the running fiber and the statement's span.

PrimitiveKindtype PrimitiveKind = Literal['string', 'int', 'float', 'boolean', 'datetime', 'date', 'null', 'bytes']

Classes

Functions

default_escape

default_escape(c) -> Callable[[str], str]

Escape names with the quote c: wrapped in it, c doubled, and each dot a boundary between quoted parts (a.b is "a"."b").

ParameterTypeDescription
cstr

Returns Callable[[str], str]

def default_escape(c: str) -> Callable[[str], str]:
    """Escape names with the quote ``c``: wrapped in it, ``c`` doubled, and
    each dot a boundary between quoted parts (``a.b`` is ``"a"."b"``)."""
    double = c + c
    dot = c + "." + c

    def escape(name: str) -> str:
        return c + name.replace(c, double).replace(".", dot) + c

    return escape

make_compiler

make_compiler(*, dialect, placeholder, on_identifier, on_record_update, on_custom, on_insert=None, on_record_update_single=None) -> Compiler

A compiler for dialect from its rendering callbacks (see CompilerOptions).

ParameterTypeDescription
dialectDialect
placeholderCallable[[int, object], str]
on_identifierCallable[[str, bool], str]
on_record_updateCallable[[str, str, str, Sequence[Sequence[object]], Compiled | None], Compiled]
on_customCallable[[Custom[Any, Any, Any, Any], Placeholder, bool], Compiled]
on_insertCallable[[Sequence[str], str, Sequence[Sequence[object]], Compiled | None], Compiled] | None = None
on_record_update_singleCallable[[Sequence[str], Sequence[object], Compiled | None], Compiled] | None = None

Returns Compiler

def make_compiler(
    *,
    dialect: Dialect,
    placeholder: Callable[[int, object], str],
    on_identifier: Callable[[str, bool], str],
    on_record_update: Callable[
        [str, str, str, Sequence[Sequence[object]], Compiled | None], Compiled
    ],
    on_custom: Callable[[Custom[Any, Any, Any, Any], Placeholder, bool], Compiled],
    on_insert: Callable[[Sequence[str], str, Sequence[Sequence[object]], Compiled | None], Compiled]
    | None = None,
    on_record_update_single: Callable[[Sequence[str], Sequence[object], Compiled | None], Compiled]
    | None = None,
) -> Compiler:
    """A compiler for ``dialect`` from its rendering callbacks (see
    ``CompilerOptions``)."""
    return Compiler(
        CompilerOptions(
            dialect=dialect,
            placeholder=placeholder,
            on_identifier=on_identifier,
            on_record_update=on_record_update,
            on_custom=on_custom,
            on_insert=on_insert,
            on_record_update_single=on_record_update_single,
        )
    )

make_compiler_sqlite

make_compiler_sqlite(transform=None) -> Compiler

SQLite's compiler: ? placeholders and "quoted" names, renamed with transform unless a statement runs without transforms.

ParameterTypeDescription
transformCallable[[str], str] | None = None

Returns Compiler

def make_compiler_sqlite(transform: Callable[[str], str] | None = None) -> Compiler:
    """SQLite's compiler: ``?`` placeholders and ``"quoted"`` names, renamed
    with ``transform`` unless a statement runs without transforms."""

    def on_identifier(value: str, without_transform: bool) -> str:
        if transform is None or without_transform:
            return _escape_sqlite(value)
        return _escape_sqlite(transform(value))

    return make_compiler(
        dialect="sqlite",
        placeholder=lambda _index, _value: "?",
        on_identifier=on_identifier,
        on_record_update=_nothing,
        on_custom=_nothing,
    )

array_helper

array_helper(value) -> ArrayHelper
ParameterTypeDescription
valueSequence[object]

Returns ArrayHelper

def array_helper(value: Sequence[object]) -> ArrayHelper:
    return ArrayHelper(_values(value))

csv

csv(values, prefix=None) -> Fragment

Comma-separated values, after prefix and a space (ORDER BY, GROUP BY); nothing at all for no values.

ParameterTypeDescription
valuesSequence[LiteralString | Fragment]
prefixLiteralString | None = None

Returns Fragment

def csv(
    values: Sequence[LiteralString | Fragment], prefix: LiteralString | None = None
) -> Fragment:
    """Comma-separated values, after ``prefix`` and a space (``ORDER BY``,
    ``GROUP BY``); nothing at all for no values."""
    if not values:
        return _EMPTY_FRAGMENT
    if prefix is None:
        return _csv_raw(values)
    return Fragment((Literal(f"{prefix} "), *_csv_raw(values).segments))

custom

custom[K: str, A, B, C](kind) -> Callable[[A, B, C], Custom[K, A, B, C]]

The constructor of kind's custom segments.

ParameterTypeDescription
kindK

Returns Callable[[A, B, C], Custom[K, A, B, C]]

def custom[K: str, A, B, C](kind: K) -> Callable[[A, B, C], Custom[K, A, B, C]]:
    """The constructor of ``kind``'s custom segments."""
    return lambda a, b, c: Custom(kind, a, b, c)

fragment

fragment(segments) -> Fragment
ParameterTypeDescription
segmentsSequence[Segment]

Returns Fragment

def fragment(segments: Sequence[Segment]) -> Fragment:
    return Fragment(segments)

identifier

identifier(value) -> Identifier
ParameterTypeDescription
valuestr

Returns Identifier

def identifier(value: str) -> Identifier:
    return Identifier(value)

in_

in_(values: Sequence[object], /) -> ArrayHelper
in_(column: str, values: Sequence[object], /) -> Fragment

in_(values): the list (a, b, ...); in_(column, values): column IN (...), or 1=0 for no values.

ParameterTypeDescription
args= ()
@overload
def in_(values: Sequence[object], /) -> ArrayHelper: ...
@overload
def in_(column: str, values: Sequence[object], /) -> Fragment: ...
def in_(*args: Any) -> ArrayHelper | Fragment:
    """``in_(values)``: the list ``(a, b, ...)``; ``in_(column, values)``:
    ``column IN (...)``, or ``1=0`` for no values."""
    if len(args) == 1:
        return array_helper(args[0])
    column, values = args
    items = _values(values)
    if not items:
        return _NEVER
    return Fragment((Identifier(column), Literal(" IN "), ArrayHelper(items)))

insert

insert(value) -> RecordInsertHelper

Rows to insert (one mapping, or several).

ParameterTypeDescription
valueMapping[str, object] | Sequence[Mapping[str, object]]

Returns RecordInsertHelper

def insert(value: Mapping[str, object] | Sequence[Mapping[str, object]]) -> RecordInsertHelper:
    """Rows to insert (one mapping, or several)."""
    return record_insert_helper([value] if isinstance(value, Mapping) else value)

is_custom

is_custom(kind) -> Callable[[object], TypeIs[Custom[Any, Any, Any, Any]]]

A guard for custom segments of kind.

ParameterTypeDescription
kindstr

Returns Callable[[object], TypeIs[Custom[Any, Any, Any, Any]]]

def is_custom(kind: str) -> Callable[[object], TypeIs[Custom[Any, Any, Any, Any]]]:
    """A guard for custom segments of ``kind``."""

    def guard(u: object) -> TypeIs[Custom[Any, Any, Any, Any]]:
        return isinstance(u, Custom) and cast("Custom[Any, Any, Any, Any]", u).kind == kind

    return guard

is_fragment

is_fragment(u) -> TypeIs[Fragment]
ParameterTypeDescription
uobject

Returns TypeIs[Fragment]

def is_fragment(u: object) -> TypeIs[Fragment]:
    return isinstance(u, Fragment)

is_segment

is_segment(u) -> TypeIs[Segment]
ParameterTypeDescription
uobject

Returns TypeIs[Segment]

def is_segment(u: object) -> TypeIs[Segment]:
    return isinstance(u, _SEGMENT_TYPES)

join

join(separator, add_parens=True, fallback='') -> Callable[[Sequence[LiteralString | Fragment]], Fragment]

Join clauses with separator: several in parentheses (unless add_parens=False), one as it is, none as fallback.

ParameterTypeDescription
separatorLiteralString
add_parensbool = True
fallbackLiteralString = ''

Returns Callable[[Sequence[LiteralString | Fragment]], Fragment]

def join(
    separator: LiteralString, add_parens: bool = True, fallback: LiteralString = ""
) -> Callable[[Sequence[LiteralString | Fragment]], Fragment]:
    """Join clauses with ``separator``: several in parentheses (unless
    ``add_parens=False``), one as it is, none as ``fallback``."""
    separator_segment = Literal(separator)
    fallback_fragment = Fragment((Literal(fallback),))

    def joined(clauses: Sequence[LiteralString | Fragment]) -> Fragment:
        if not clauses:
            return fallback_fragment
        if len(clauses) == 1:
            return Fragment(_clause(clauses[0]))
        segments: list[Segment] = [Literal("(")] if add_parens else []
        segments.extend(_clause(clauses[0]))
        for clause in clauses[1:]:
            segments.append(separator_segment)
            segments.extend(_clause(clause))
        if add_parens:
            segments.append(Literal(")"))
        return Fragment(segments)

    return joined

literal

literal(value, params=None) -> Literal

SQL text from a string literal (the only way plain text becomes SQL, with unsafe).

ParameterTypeDescription
valueLiteralString
paramsSequence[object] | None = None

Returns Literal

def literal(value: LiteralString, params: Sequence[object] | None = None) -> Literal:
    """SQL text from a string literal (the only way plain text becomes SQL,
    with ``unsafe``)."""
    return Literal(value, None if params is None else tuple(params))

parameter

parameter(value) -> Parameter
ParameterTypeDescription
valueobject

Returns Parameter

def parameter(value: object) -> Parameter:
    return Parameter(value)

record_insert_helper

record_insert_helper(value) -> RecordInsertHelper
ParameterTypeDescription
valueSequence[Mapping[str, object]]

Returns RecordInsertHelper

def record_insert_helper(value: Sequence[Mapping[str, object]]) -> RecordInsertHelper:
    rows = tuple(value)
    if not rows:
        raise ValueError("insert needs at least one row")
    return RecordInsertHelper(rows)

record_update_helper

record_update_helper(value, alias) -> RecordUpdateHelper
ParameterTypeDescription
valueSequence[Mapping[str, object]]
aliasstr

Returns RecordUpdateHelper

def record_update_helper(value: Sequence[Mapping[str, object]], alias: str) -> RecordUpdateHelper:
    rows = tuple(value)
    if not rows:
        raise ValueError("update_values needs at least one row")
    return RecordUpdateHelper(rows, alias)

record_update_helper_single

record_update_helper_single(value, omit) -> RecordUpdateHelperSingle
ParameterTypeDescription
valueMapping[str, object]
omitSequence[str]

Returns RecordUpdateHelperSingle

def record_update_helper_single(
    value: Mapping[str, object], omit: Sequence[str]
) -> RecordUpdateHelperSingle:
    return RecordUpdateHelperSingle(value, _names(omit))

update

update(value, omit=()) -> RecordUpdateHelperSingle

One row's SET assignments, without the omit columns.

ParameterTypeDescription
valueMapping[str, object]
omitSequence[str] = ()

Returns RecordUpdateHelperSingle

def update(value: Mapping[str, object], omit: Sequence[str] = ()) -> RecordUpdateHelperSingle:
    """One row's ``SET`` assignments, without the ``omit`` columns."""
    return record_update_helper_single(value, omit)

update_values

update_values(value, alias) -> RecordUpdateHelper

Several rows to update from a VALUES list named alias. Not supported by SQLite.

ParameterTypeDescription
valueSequence[Mapping[str, object]]
aliasstr

Returns RecordUpdateHelper

def update_values(value: Sequence[Mapping[str, object]], alias: str) -> RecordUpdateHelper:
    """Several rows to update from a ``VALUES`` list named ``alias``. Not
    supported by SQLite."""
    return record_update_helper(value, alias)

make

make(acquirer, compiler, span_attributes, transform_rows, borrower=None) -> Constructor

A statement constructor running on acquirer (or borrower, for single statements).

ParameterTypeDescription
acquirerAcquirer
compilerCompiler
span_attributesSpanAttributes
transform_rowsTransformRows | None
borrowerBorrower | None = None

Returns Constructor

def make(
    acquirer: Acquirer,
    compiler: Compiler,
    span_attributes: SpanAttributes,
    transform_rows: TransformRows | None,
    borrower: Borrower | None = None,
) -> Constructor:
    """A statement constructor running on ``acquirer`` (or ``borrower``,
    for single statements)."""
    return Constructor(acquirer, compiler, span_attributes, transform_rows, borrower)

span_name

span_name(attributes) -> str

The name of a client's statement spans: its span_target, else db.system.name, else sql.execute.

ParameterTypeDescription
attributesSpanAttributes

Returns str

def span_name(attributes: SpanAttributes) -> str:
    """The name of a client's statement spans: its ``span_target``, else
    ``db.system.name``, else ``sql.execute``."""
    target = span_target(attributes)
    if target is not None:
        return target
    return _non_empty_string(_span_attribute(attributes, "db.system.name")) or "sql.execute"

span_target

span_target(attributes) -> str | None

The OpenTelemetry target of a client's spans: db.namespace, else server.address[:server.port], else None.

ParameterTypeDescription
attributesSpanAttributes

Returns str | None

def span_target(attributes: SpanAttributes) -> str | None:
    """The OpenTelemetry target of a client's spans: ``db.namespace``, else
    ``server.address[:server.port]``, else ``None``."""
    namespace = _non_empty_string(_span_attribute(attributes, "db.namespace"))
    if namespace is not None:
        return namespace
    address = _non_empty_string(_span_attribute(attributes, "server.address"))
    if address is not None:
        port = _span_attribute(attributes, "server.port")
        return address if port is None else f"{address}:{port}"
    return None

default_transforms

default_transforms(transformer, nested=True) -> RowTransforms

Rename every key of each row with transformer; with nested (the default), also the keys of mappings inside a row and of lists of mappings inside a row. Lists, None and scalars among the rows stay as they are.

ParameterTypeDescription
transformerCallable[[str], str]
nestedbool = True

Returns RowTransforms

def default_transforms(transformer: Callable[[str], str], nested: bool = True) -> RowTransforms:
    """Rename every key of each row with ``transformer``; with ``nested``
    (the default), also the keys of mappings inside a row and of lists of
    mappings inside a row. Lists, ``None`` and scalars among the rows stay
    as they are."""

    keys = _KeyTransform(transformer, nested)
    return RowTransforms(keys.transform_value, keys.transform_object, keys.array)

primitive_kind

primitive_kind(value) -> PrimitiveKind

The kind of a bound value, for drivers that type their parameters; other values count as strings.

ParameterTypeDescription
valueobject

Returns PrimitiveKind

def primitive_kind(value: object) -> PrimitiveKind:  # noqa: PLR0911
    """The kind of a bound value, for drivers that type their parameters;
    other values count as strings."""
    match value:
        case None:
            return "null"
        case bool():
            return "boolean"
        case int():
            return "int"
        case float():
            return "float"
        case str():
            return "string"
        case datetime():
            return "datetime"
        case date():
            return "date"
        case bytes() | bytearray() | memoryview():
            return "bytes"
        case _:
            return "string"