GitHub

Constructor

API reference for Constructor.

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

AttributeTypeDescription
acquirer= acquirer
compiler= compiler
span_attributes= tuple(span_attributes)
transform_rows= transform_rows
borrower= borrower
dialectDialect

The SQL dialect the client compiles statements for ("sqlite", "pg", "mysql", "mssql" or "clickhouse").

Functions

__init__

__init__(acquirer, compiler, span_attributes, transform_rows, borrower=None) -> None
ParameterTypeDescription
acquirerAcquirer
compilerCompiler
span_attributesSpanAttributes
transform_rowsTransformRows | None
borrowerBorrower | 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]
ParameterTypeDescription
templateSqlText
argsobject = ()
kwargsobject = {}

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.

ParameterTypeDescription
sqlstr
paramsSequence[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) -> Fragment

SQL text as a fragment.

ParameterTypeDescription
sqlLiteralString

Returns Fragment

@staticmethod
def literal(sql: LiteralString) -> Fragment:
    """SQL text as a fragment."""
    return Fragment((literal(sql),))

identifier

identifier(name) -> Identifier

A name, escaped (Effect's sql("name")).

ParameterTypeDescription
namestr

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], /) -> Fragment

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

ParameterTypeDescription
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.

ParameterTypeDescription
valueMapping[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=()) -> RecordUpdateHelperSingle

column = value, ... for one row, without the omit columns.

ParameterTypeDescription
valueMapping[str, object]
omitSequence[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) -> RecordUpdateHelper

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

ParameterTypeDescription
valueSequence[Mapping[str, object]]
aliasstr

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.

ParameterTypeDescription
clausesSequence[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.

ParameterTypeDescription
clausesSequence[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) -> Fragment

a,b,... after prefix (ORDER BY); nothing for none.

ParameterTypeDescription
valuesSequence[LiteralString | Fragment]
prefixLiteralString | 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.

ParameterTypeDescription
separatorLiteralString
add_parensbool = True
fallbackLiteralString = ''

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

The result of the function for the client's dialect.

ParameterTypeDescription
sqliteCallable[[], T]
pgCallable[[], T]
mysqlCallable[[], T]
mssqlCallable[[], T]
clickhouseCallable[[], 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) -> T

The result of the function for the client's dialect, or of or_else when it has none.

ParameterTypeDescription
or_elseCallable[[], T]
sqliteCallable[[], T] | None = None
pgCallable[[], T] | None = None
mysqlCallable[[], T] | None = None
mssqlCallable[[], T] | None = None
clickhouseCallable[[], 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)()