Builds statements for one client: call it with SQL text and values, and use its helpers for names, lists and records::
sql("SELECT * FROM users WHERE id = {id}", id=user_id)
sql("SELECT * FROM {table} WHERE {cond}", table=sql.identifier("users"),
cond=sql.and_([sql("age > {}", 18), sql.in_("role", roles)]))
sql("INSERT INTO users {}", sql.insert({"name": "Ada"}).returning("*"))The text must be a string literal (LiteralString; a t-string on
Python 3.14): every value is bound as a parameter, unless it is a
fragment or a helper, which are placed as they are. Write {{ and
}} for braces. unsafe is the only way to run text built at
runtime.
Attributes#
| Attribute | Type | Description |
|---|---|---|
acquirer | = acquirer | |
compiler | = compiler | |
span_attributes | = tuple(span_attributes) | |
transform_rows | = transform_rows | |
borrower | = borrower | |
dialect | Dialect | The SQL dialect the client compiles statements for
( |
Functions#
__init__#
__init__(acquirer, compiler, span_attributes, transform_rows, borrower=None) -> None| Parameter | Type | Description |
|---|---|---|
acquirer | Acquirer | |
compiler | Compiler | |
span_attributes | SpanAttributes | |
transform_rows | TransformRows | None | |
borrower | Borrower | None = None |
def __init__(
self,
acquirer: Acquirer,
compiler: Compiler,
span_attributes: SpanAttributes,
transform_rows: TransformRows | None,
borrower: Borrower | None = None,
) -> None:
self.acquirer = acquirer
self.compiler = compiler
self.span_attributes = tuple(span_attributes)
self.transform_rows = transform_rows
self.borrower = borrower__call__#
__call__(template, *args, **kwargs) -> Statement[Row]| Parameter | Type | Description |
|---|---|---|
template | SqlText | |
args | object = () | |
kwargs | object = {} |
Returns Statement[Row]
def __call__(self, template: SqlText, /, *args: object, **kwargs: object) -> Statement[Row]:
return self._statement(segments_of(template, args, kwargs))unsafe#
unsafe(sql, params=None) -> Statement[Row]A statement of SQL text built at runtime, with params bound to
its placeholders. Never build the text from untrusted input.
| Parameter | Type | Description |
|---|---|---|
sql | str | |
params | Sequence[object] | None = None |
Returns Statement[Row]
def unsafe(self, sql: str, params: Sequence[object] | None = None) -> Statement[Row]:
"""A statement of SQL text built at runtime, with ``params`` bound to
its placeholders. Never build the text from untrusted input."""
return self._statement((literal_unsafe(sql, params),))literal#
literal(sql) -> FragmentSQL text as a fragment.
| Parameter | Type | Description |
|---|---|---|
sql | LiteralString |
Returns Fragment
@staticmethod
def literal(sql: LiteralString) -> Fragment:
"""SQL text as a fragment."""
return Fragment((literal(sql),))identifier#
identifier(name) -> IdentifierA name, escaped (Effect's sql("name")).
| Parameter | Type | Description |
|---|---|---|
name | str |
Returns Identifier
@staticmethod
def identifier(name: str) -> Identifier:
"""A name, escaped (Effect's ``sql("name")``)."""
return identifier(name)in_#
in_(values: Sequence[object], /) -> ArrayHelper
in_(column: str, values: Sequence[object], /) -> Fragmentin_(values): (a, b, ...); in_(column, values):
"column" IN (...), or 1=0 for no values.
| Parameter | Type | Description |
|---|---|---|
args | = () |
@overload
@staticmethod
def in_(values: Sequence[object], /) -> ArrayHelper: ...
@overload
@staticmethod
def in_(column: str, values: Sequence[object], /) -> Fragment: ...
@staticmethod
def in_(*args: Any) -> ArrayHelper | Fragment:
"""``in_(values)``: ``(a, b, ...)``; ``in_(column, values)``:
``"column" IN (...)``, or ``1=0`` for no values."""
if len(args) == 1:
return in_(args[0])
column, values = args
return in_(column, values)insert#
insert(value) -> RecordInsertHelper(columns) VALUES (...) for one row or several.
| Parameter | Type | Description |
|---|---|---|
value | Mapping[str, object] | Sequence[Mapping[str, object]] |
Returns RecordInsertHelper
@staticmethod
def insert(value: Mapping[str, object] | Sequence[Mapping[str, object]]) -> RecordInsertHelper:
"""``(columns) VALUES (...)`` for one row or several."""
return insert(value)update#
update(value, omit=()) -> RecordUpdateHelperSinglecolumn = value, ... for one row, without the omit
columns.
| Parameter | Type | Description |
|---|---|---|
value | Mapping[str, object] | |
omit | Sequence[str] = () |
Returns RecordUpdateHelperSingle
@staticmethod
def update(value: Mapping[str, object], omit: Sequence[str] = ()) -> RecordUpdateHelperSingle:
"""``column = value, ...`` for one row, without the ``omit``
columns."""
return update(value, omit)update_values#
update_values(value, alias) -> RecordUpdateHelperSeveral rows to update, as a VALUES list named alias. Not
supported by SQLite.
| Parameter | Type | Description |
|---|---|---|
value | Sequence[Mapping[str, object]] | |
alias | str |
Returns RecordUpdateHelper
@staticmethod
def update_values(value: Sequence[Mapping[str, object]], alias: str) -> RecordUpdateHelper:
"""Several rows to update, as a ``VALUES`` list named ``alias``. Not
supported by SQLite."""
return update_values(value, alias)and_#
and_(clauses) -> Fragment(a AND b ...); 1=1 for no clauses.
| Parameter | Type | Description |
|---|---|---|
clauses | Sequence[LiteralString | Fragment] |
Returns Fragment
@staticmethod
def and_(clauses: Sequence[LiteralString | Fragment]) -> Fragment:
"""``(a AND b ...)``; ``1=1`` for no clauses."""
return and_(clauses)or_#
or_(clauses) -> Fragment(a OR b ...); 1=1 for no clauses.
| Parameter | Type | Description |
|---|---|---|
clauses | Sequence[LiteralString | Fragment] |
Returns Fragment
@staticmethod
def or_(clauses: Sequence[LiteralString | Fragment]) -> Fragment:
"""``(a OR b ...)``; ``1=1`` for no clauses."""
return or_(clauses)csv#
csv(values, prefix=None) -> Fragmenta,b,... after prefix (ORDER BY); nothing for none.
| Parameter | Type | Description |
|---|---|---|
values | Sequence[LiteralString | Fragment] | |
prefix | LiteralString | None = None |
Returns Fragment
@staticmethod
def csv(
values: Sequence[LiteralString | Fragment], prefix: LiteralString | None = None
) -> Fragment:
"""``a,b,...`` after ``prefix`` (``ORDER BY``); nothing for none."""
return csv(values, prefix)join#
join(separator, add_parens=True, fallback='') -> Callable[[Sequence[LiteralString | Fragment]], Fragment]A function joining 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]
@staticmethod
def join(
separator: LiteralString, add_parens: bool = True, fallback: LiteralString = ""
) -> Callable[[Sequence[LiteralString | Fragment]], Fragment]:
"""A function joining clauses with ``separator``: several in
parentheses (unless ``add_parens=False``), one as it is, none as
``fallback``."""
return join(separator, add_parens, fallback)on_dialect#
on_dialect[T](*, sqlite, pg, mysql, mssql, clickhouse) -> TThe result of the function for the client's dialect.
| Parameter | Type | Description |
|---|---|---|
sqlite | Callable[[], T] | |
pg | Callable[[], T] | |
mysql | Callable[[], T] | |
mssql | Callable[[], T] | |
clickhouse | Callable[[], T] |
Returns T
def on_dialect[T](
self,
*,
sqlite: Callable[[], T],
pg: Callable[[], T],
mysql: Callable[[], T],
mssql: Callable[[], T],
clickhouse: Callable[[], T],
) -> T:
"""The result of the function for the client's dialect."""
branches: dict[str, Callable[[], T]] = {
"sqlite": sqlite,
"pg": pg,
"mysql": mysql,
"mssql": mssql,
"clickhouse": clickhouse,
}
return branches[self.dialect]()on_dialect_or_else#
on_dialect_or_else[T](*, or_else, sqlite=None, pg=None, mysql=None, mssql=None, clickhouse=None) -> TThe result of the function for the client's dialect, or of
or_else when it has none.
| Parameter | Type | Description |
|---|---|---|
or_else | Callable[[], T] | |
sqlite | Callable[[], T] | None = None | |
pg | Callable[[], T] | None = None | |
mysql | Callable[[], T] | None = None | |
mssql | Callable[[], T] | None = None | |
clickhouse | Callable[[], T] | None = None |
Returns T
def on_dialect_or_else[T](
self,
*,
or_else: Callable[[], T],
sqlite: Callable[[], T] | None = None,
pg: Callable[[], T] | None = None,
mysql: Callable[[], T] | None = None,
mssql: Callable[[], T] | None = None,
clickhouse: Callable[[], T] | None = None,
) -> T:
"""The result of the function for the client's dialect, or of
``or_else`` when it has none."""
branches: dict[str, Callable[[], T] | None] = {
"sqlite": sqlite,
"pg": pg,
"mysql": mysql,
"mssql": mssql,
"clickhouse": clickhouse,
}
branch = branches[self.dialect]
return (or_else if branch is None else branch)()