sql -- SQL string composition

.. index:: double: Binding; Client-Side

.. module:: psycopg.sql

The module contains objects and functions useful to generate SQL dynamically, in a convenient and safe way. SQL identifiers (e.g. names of tables and fields) cannot be passed to the ~psycopg.Cursor.execute() method like query arguments::

# This will not work
table_name = 'my_table'
cur.execute("INSERT INTO %s VALUES (%s, %s)", [table_name, 10, 20])

The SQL query should be composed before the arguments are merged, for instance::

# This works, but it is not optimal
table_name = 'my_table'
cur.execute(
    "INSERT INTO %s VALUES (%%s, %%s)" % table_name,
    [10, 20])

This sort of works, but it is an accident waiting to happen: the table name may be an invalid SQL literal and need quoting; even more serious is the security problem in case the table name comes from an untrusted source.

.. note::

The low-level `~psycopg.pq.Escaping.escape_identifier()` function can be
used to escape a single identifier, but it operates on :sql:`bytes` and
is cumbersome to use in practice. The `!psycopg.sql` module, described
below, is the recommended approach for building dynamic queries safely.

The objects exposed by the !psycopg.sql module allow generating SQL statements on the fly, separating clearly the variable parts of the statement from the query parameters::

from psycopg import sql

cur.execute(
    sql.SQL("INSERT INTO {} VALUES (%s, %s)")
        .format(sql.Identifier('my_table')),
    [10, 20])

Module usage

Usually you should express the template of your query as an SQL instance with {}-style placeholders and use ~SQL.format() to merge the variable parts into them, all of which must be Composable subclasses. You can still have %s-style placeholders in your query and pass values to ~psycopg.Cursor.execute(): such value placeholders will be untouched by !format()::

query = sql.SQL("SELECT {field} FROM {table} WHERE {pkey} = %s").format(
    field=sql.Identifier('my_name'),
    table=sql.Identifier('some_table'),
    pkey=sql.Identifier('id'))

The resulting object is meant to be passed directly to cursor methods such as ~psycopg.Cursor.execute(), ~psycopg.Cursor.executemany(), ~psycopg.Cursor.copy(), but can also be used to compose a query as a Python string, using the ~Composable.as_string() method::

cur.execute(query, (42,))
full_query = query.as_string(cur)

If part of your query is a variable sequence of arguments, such as a comma-separated list of field names, you can use the SQL.join() method to pass them to the query::

query = sql.SQL("SELECT {fields} FROM {table}").format(
    fields=sql.SQL(',').join([
        sql.Identifier('field1'),
        sql.Identifier('field2'),
        sql.Identifier('field3'),
    ]),
    table=sql.Identifier('some_table'))

!sql objects

The !sql objects are in the following inheritance hierarchy:

| Composable: the base class exposing the common interface | |__ SQL: a literal snippet of an SQL query | |__ Identifier: a PostgreSQL identifier or dot-separated sequence of identifiers | |__ Literal: a value hardcoded into a query | |__ Placeholder: a %s\ -style placeholder whose value will be added later e.g. by ~psycopg.Cursor.execute() | |__ Composed: a sequence of !Composable instances.

.. autoclass:: Composable()

.. automethod:: as_string

.. versionchanged:: 3.2

    The `!context` parameter is optional.

    .. warning::

        If a context is not specified, the results are "generic" and not
        tailored for a specific target connection. Details such as the
        connection encoding and escaping style will not be taken into
        account.

.. automethod:: as_bytes

.. versionchanged:: 3.2

    The `!context` parameter is optional. See `as_string` for details.

.. autoclass:: SQL

.. versionchanged:: 3.1

    The input object should be a `~typing.LiteralString`. See :pep:`675`
    for details.

.. automethod:: format

.. automethod:: join

    .. versionchanged:: 3.3

        Added support for `~string.templatelib.Template` sequences.
        See :ref:`nested template strings <tstring-template-nested>`.

.. autoclass:: Identifier

.. autoclass:: Literal

.. versionchanged:: 3.1
    Add a type cast to the representation if useful in ambiguous context
    (e.g. ``'2000-01-01'::date``)

.. autoclass:: Placeholder

.. autoclass:: Composed

.. automethod:: join

Utility functions

.. autofunction:: as_string

.. versionadded:: 3.3

.. autofunction:: as_bytes

.. versionadded:: 3.3

.. data:: NULL DEFAULT

`sql.SQL` objects often useful in queries.

.. autofunction:: quote