sqltrack.util module

class sqltrack.util.SQL(obj: LiteralString)

Bases: Composable

A Composable representing a snippet of SQL statement.

!SQL exposes join() and format() methods useful to create a template where to merge variable parts of a query (for instance field or table names).

The !obj string doesn’t undergo any form of escaping, so it is not suitable to represent variable identifiers or values: you should only use it to pass constant strings representing templates or snippets of SQL statements; use other objects such as Identifier or Literal to represent variable parts.

Example:

>>> query = sql.SQL("SELECT {0} FROM {1}").format(
...    sql.SQL(', ').join([sql.Identifier('foo'), sql.Identifier('bar')]),
...    sql.Identifier('table'))
>>> print(query.as_string(conn))
SELECT "foo", "bar" FROM "table"

as_bytes(context: AdaptContext | None) → bytes

Return the value of the object as bytes.

Parameters:

context (connection or cursor) – the context to evaluate the object into.

The method is automatically invoked by ~psycopg.Cursor.execute(), ~psycopg.Cursor.executemany(), ~psycopg.Cursor.copy() if a !Composable is passed instead of the query string.

as_string(context: AdaptContext | None) → str

Return the value of the object as string.

Parameters:

context (connection or cursor) – the context to evaluate the string into.

format(*args: Any, **kwargs: Any) → Composed

Merge Composable objects into a template.

Parameters:

• args – parameters to replace to numbered ({0}, {1}) or auto-numbered ({}) placeholders

• kwargs – parameters to replace to named ({name}) placeholders

Returns:

the union of the !SQL string with placeholders replaced

Return type:

Composed

The method is similar to the Python str.format() method: the string template supports auto-numbered ({}), numbered ({0}, {1}…), and named placeholders ({name}), with positional arguments replacing the numbered placeholders and keywords replacing the named ones. However placeholder modifiers ({0!r}, {0:<10}) are not supported.

If a !Composable objects is passed to the template it will be merged according to its as_string() method. If any other Python object is passed, it will be wrapped in a Literal object and so escaped according to SQL rules.

Example:

>>> print(sql.SQL("SELECT * FROM {} WHERE {} = %s")
...     .format(sql.Identifier('people'), sql.Identifier('id'))
...     .as_string(conn))
SELECT * FROM "people" WHERE "id" = %s

>>> print(sql.SQL("SELECT * FROM {tbl} WHERE name = {name}")
...     .format(tbl=sql.Identifier('people'), name="O'Rourke"))
...     .as_string(conn))
SELECT * FROM "people" WHERE name = 'O''Rourke'

join(seq: Iterable[Composable]) → Composed

Join a sequence of Composable.

Parameters:

seq (iterable of !Composable) – the elements to join.

Use the !SQL object’s string to separate the elements in !seq. Note that Composed objects are iterable too, so they can be used as argument for this method.

Example:

>>> snip = sql.SQL(', ').join(
...     sql.Identifier(n) for n in ['foo', 'bar', 'baz'])
>>> print(snip.as_string(conn))
"foo", "bar", "baz"

sqltrack.util.coalesce(*values) → object

Returns the first none-None value.