Template string queries#

New in version 3.3.

Warning

This is an experimental feature, still under active development and only documented here for preview. Details may change before Psycopg 3.3 release.

Template strings are a Python language feature under active development too, planned for release in Python 3.14. Template string queries are currently tested in Python 3.14 beta 1.

Psycopg can process queries expressed as template strings defined in PEP 750 and implemented for the first time in Python 3.14.

Template strings are similar to f-strings: they are string literals interspersed with variables or expressions marked by {}. They use a t prefix instead of f, and can be used to express queries:

cursor.execute(t"SELECT * FROM mytable WHERE id = {id}")

The difference between the two is that f-strings are immediately evaluated by Python and passed to the rest of the program as an already formatted regular string; t-strings, instead, are evaluated by Psycopg, which has a chance to process query parameters in a safe way.

For example you can pass to a query some strings parameters, which may contain unsafe characters such as ', or come from untrusted sources such as web form, and leave Psycopg to perform the right processing:

cursor.execute(
    t"INSERT INTO mytable (first_name, last_name) VALUES ({first_name}, {last_name})"
)

This statement has the same effect as a classic:

cursor.execute(
    "INSERT INTO mytable (first_name, last_name) VALUES (%s, %s)",
    (first_name, last_name),
)

as Psycopg will be able to send parameters separately from the query, or to compose the query on the client side using safe escaping rules, according to the type of cursor used, but it is has the same readability of f-strings.

Format specifiers#

Format specifiers can be associated to template strings interpolation using a : in the placeholder, for example in {id:b}. Psycopg supports a few format specifiers specifying how to pass a parameter to the server and a few format specifiers specifying how to compose query parts on the client, in a way similar to what can be obtained using the psycopg.sql objects.

The supported specifiers for parameter formats are:

  • s: automatic parameter format, similar to using %s in a classic query. This is the same effect of using no format specifier.

  • b: use the binary format to pass the parameter, similar to using %b in a classic query.

  • t: use the text format to pass the parameter, similar to using %t in a classic query.

The supported specifiers for query composition are:

  • i: the parameter is an identifier, for example a table or column name. The parameter must be a string or a sql.Identifier instance.

  • l: the parameter is a literal value, which will be composed to the query on the client. This allows to parametrize statements that don’t support parametrization in PostgreSQL.

  • q: the parameter is a snippet of statement to be included verbatim in the query. The parameter must be another template string or a sql.SQL instance.

Example: NOTIFY#

The NOTIFY command takes a channel parameter (an identifier, so it must be quoted with double quotes if it contains any non-alphanumeric character), and a payload parameter as a string (which must be escaped with string syntax, hence with single quotes).

The NOTIFY command cannot be parametrized by PostgreSQL, so it must be composed entirely on the client side. Using template strings this could be as simple as:

def send_notify(conn: Connection, channel: str, payload: str) -> None:
    conn.execute(t"NOTIFY {channel:i}, {payload:l}")

Calling the function with channel foo.bar and payload O'Reilly will result in executing the statement NOTIFY "foo.bar", 'O''Reilly'.

Example: nested templates#

A string template merges literal parts of the query with parameter. It is also possible to pass templates to templates in order to compose more and more complex and dynamic SQL statements.

For example let’s say we have an User Python object mapping to an users database table. We might want to implement a search() function to return users by a list of ids, by names pattern, by group. This function might be written as:

def search_users(
    conn: Connection,
    ids: Sequence[int] | None = None,
    name_pattern: str | None = None,
    group_id: int | None = None,
) -> list[User]:
    filters = []
    if ids is not None:
        filters.append(t"u.id = any({list(ids)})")
    if name_pattern is not None:
        filters.append(t"u.name ~* {name_pattern}")
    if group_id is not None:
        filters.append(t"u.group_id = {group_id}")

    if not filters:
        raise TypeError("please specify at least one search parameter")
    joined = sql.SQL(" AND ").join(filters)

    cur = conn.cursor(row_factory=class_row(User))
    cur.execute(t"SELECT * FROM users AS u WHERE {joined:q}")
    return cur.fetchall()

In this example we have used the sql.SQL.join() method overload that takes a list of templates and returns a joined template in order to create an AND-ed sequence of conditions.