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}").
Fragmentand its segments (Literal,Identifier,Parameter,ArrayHelper, the record helpers,Custom)and_,or_,csv,join,in_,insert,update,update_values: helpers, also on every constructorCompiler,make_compiler,make_compiler_sqlite,default_escape: rendering fragments for a dialectdefault_transforms: renaming result columns (fx.casehas the usual renamings)CurrentTransformer,SpanPropagationEnabled: references that steer how statements run
Values#
| Attribute | Type | Description |
|---|---|---|
and_ | (Sequence[LiteralString | Fragment]) -> Fragment | Clauses joined with |
or_ | (Sequence[LiteralString | Fragment]) -> Fragment | Clauses joined with |
Types#
| Type | Definition | Description |
|---|---|---|
Compiled | type Compiled = tuple[str, tuple[object, ...]] | SQL text and its parameters, in placeholder order. |
Dialect | type Dialect = Literal['sqlite', 'pg', 'mysql', 'mssql', 'clickhouse'] | |
Placeholder | type Placeholder = Callable[[object], str] | Renders the placeholder of the next parameter. |
Helper | type Helper = ArrayHelper | RecordInsertHelper | RecordUpdateHelper | RecordUpdateHelperSingle | Identifier | Custom[Any, Any, Any, Any] | The segments a template places as they are (not as parameters). |
Segment | type Segment = Literal | Identifier | Parameter | ArrayHelper | RecordInsertHelper | RecordUpdateHelper | RecordUpdateHelperSingle | Custom[Any, Any, Any, Any] | |
SqlText | type SqlText = LiteralString | The SQL text of a statement: a string literal with |
SpanAttributes | type SpanAttributes = Sequence[tuple[str, object]] | |
Transformer | type 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. |
PrimitiveKind | type 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").
| Parameter | Type | Description |
|---|---|---|
c | str |
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 escapemake_compiler#
make_compiler(*, dialect, placeholder, on_identifier, on_record_update, on_custom, on_insert=None, on_record_update_single=None) -> CompilerA compiler for dialect from its rendering callbacks (see
CompilerOptions).
| Parameter | Type | Description |
|---|---|---|
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 |
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) -> CompilerSQLite's compiler: ? placeholders and "quoted" names, renamed
with transform unless a statement runs without transforms.
| Parameter | Type | Description |
|---|---|---|
transform | Callable[[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| Parameter | Type | Description |
|---|---|---|
value | Sequence[object] |
Returns ArrayHelper
def array_helper(value: Sequence[object]) -> ArrayHelper:
return ArrayHelper(_values(value))csv#
csv(values, prefix=None) -> FragmentComma-separated values, after prefix and a space (ORDER BY,
GROUP BY); nothing at all for no values.
| Parameter | Type | Description |
|---|---|---|
values | Sequence[LiteralString | Fragment] | |
prefix | LiteralString | 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.
| Parameter | Type | Description |
|---|---|---|
kind | K |
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| Parameter | Type | Description |
|---|---|---|
segments | Sequence[Segment] |
Returns Fragment
def fragment(segments: Sequence[Segment]) -> Fragment:
return Fragment(segments)identifier#
identifier(value) -> Identifier| Parameter | Type | Description |
|---|---|---|
value | str |
Returns Identifier
def identifier(value: str) -> Identifier:
return Identifier(value)in_#
in_(values: Sequence[object], /) -> ArrayHelper
in_(column: str, values: Sequence[object], /) -> Fragmentin_(values): the list (a, b, ...); in_(column, values):
column IN (...), or 1=0 for no values.
| Parameter | Type | Description |
|---|---|---|
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) -> RecordInsertHelperRows to insert (one mapping, or several).
| Parameter | Type | Description |
|---|---|---|
value | Mapping[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.
| Parameter | Type | Description |
|---|---|---|
kind | str |
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 guardis_fragment#
is_fragment(u) -> TypeIs[Fragment]| Parameter | Type | Description |
|---|---|---|
u | object |
Returns TypeIs[Fragment]
def is_fragment(u: object) -> TypeIs[Fragment]:
return isinstance(u, Fragment)is_segment#
is_segment(u) -> TypeIs[Segment]| Parameter | Type | Description |
|---|---|---|
u | object |
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.
| Parameter | Type | Description |
|---|---|---|
separator | LiteralString | |
add_parens | bool = True | |
fallback | LiteralString = '' |
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 joinedliteral#
literal(value, params=None) -> LiteralSQL text from a string literal (the only way plain text becomes SQL,
with unsafe).
| Parameter | Type | Description |
|---|---|---|
value | LiteralString | |
params | Sequence[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| Parameter | Type | Description |
|---|---|---|
value | object |
Returns Parameter
def parameter(value: object) -> Parameter:
return Parameter(value)record_insert_helper#
record_insert_helper(value) -> RecordInsertHelper| Parameter | Type | Description |
|---|---|---|
value | Sequence[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| Parameter | Type | Description |
|---|---|---|
value | Sequence[Mapping[str, object]] | |
alias | str |
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| Parameter | Type | Description |
|---|---|---|
value | Mapping[str, object] | |
omit | Sequence[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=()) -> RecordUpdateHelperSingleOne row's SET assignments, without the omit columns.
| Parameter | Type | Description |
|---|---|---|
value | Mapping[str, object] | |
omit | Sequence[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) -> RecordUpdateHelperSeveral rows to update from a VALUES list named alias. Not
supported by SQLite.
| Parameter | Type | Description |
|---|---|---|
value | Sequence[Mapping[str, object]] | |
alias | str |
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) -> ConstructorA statement constructor running on acquirer (or borrower,
for single statements).
| Parameter | Type | Description |
|---|---|---|
acquirer | Acquirer | |
compiler | Compiler | |
span_attributes | SpanAttributes | |
transform_rows | TransformRows | None | |
borrower | Borrower | 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) -> strThe name of a client's statement spans: its span_target, else
db.system.name, else sql.execute.
| Parameter | Type | Description |
|---|---|---|
attributes | SpanAttributes |
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 | NoneThe OpenTelemetry target of a client's spans: db.namespace, else
server.address[:server.port], else None.
| Parameter | Type | Description |
|---|---|---|
attributes | SpanAttributes |
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 Nonedefault_transforms#
default_transforms(transformer, nested=True) -> RowTransformsRename 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.
| Parameter | Type | Description |
|---|---|---|
transformer | Callable[[str], str] | |
nested | bool = 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) -> PrimitiveKindThe kind of a bound value, for drivers that type their parameters; other values count as strings.
| Parameter | Type | Description |
|---|---|---|
value | object |
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"On This Page
ValuesTypesClassesFunctionsdefault_escapemake_compilermake_compiler_sqlitearray_helpercsvcustomfragmentidentifierin_insertis_customis_fragmentis_segmentjoinliteralparameterrecord_insert_helperrecord_update_helperrecord_update_helper_singleupdateupdate_valuesmakespan_namespan_targetdefault_transformsprimitive_kind