# 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. The
name should be escaped using quote_ident():
# This works, but it is not optimal
table_name = 'my_table'
cur.execute(
    "insert into %s values (%%s, %%s)" % ext.quote_ident(table_name, cur),
    [10, 20])
This is now safe, but it somewhat ad-hoc. In case, for some reason, it is
necessary to include a value in the query string (as opposite as in a value)
the merging rule is still different (adapt() should be
used…). It is also still relatively dangerous: if quote_ident() is
forgotten somewhere, the program will usually work, but will eventually crash
in the presence of a table or field name with containing characters to escape,
or will present a potentially exploitable weakness.
The objects exposed by the psycopg2.sql module allow generating SQL
statements on the fly, separating clearly the variable parts of the statement
from the query parameters:
from psycopg2 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 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
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
execute(), executemany(), copy_expert(), but can
also be used to compose a query as a Python string, using the
as_string() method:
cur.execute(query, (42,))
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'))
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 execute()
|__ Composed: a sequence of Composable instances.
class psycopg2.sql.Composable(wrapped)¶
Abstract base class for objects that can be used to compose an SQL string.
Composable objects can be passed directly to execute(),
executemany(), copy_expert() in place of the query
string.
Composable objects can be joined using the + operator: the result
will be a Composed instance containing the objects joined. The operator
* is also supported with an integer argument: the result is a
Composed instance containing the left argument repeated as many times as
requested.
as_string(context)¶
Return the string value of the object.
Parameters:
context (connection or cursor) – the context to evaluate the string into.
The method is automatically invoked by execute(),
executemany(), copy_expert() if a Composable is
passed instead of the query string.
class psycopg2.sql.SQL(string)¶
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 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"
args (Composable) – parameters to replace to numbered
({0}, {1}) or auto-numbered ({}) placeholders
kwargs (Composable) – 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. Only Composable objects can be passed to the
template.
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 {pkey} = %s")
...     .format(tbl=sql.Identifier('people'), pkey=sql.Identifier('id'))
...     .as_string(conn))
select * from "people" where "id" = %s
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"
class psycopg2.sql.Identifier(*strings)¶
A Composable representing an SQL identifier or a dot-separated sequence.
Identifiers usually represent names of database objects, such as tables or
fields. PostgreSQL identifiers follow different rules than SQL string
literals for escaping (e.g. they use double quotes instead of single).
Example:
>>> t1 = sql.Identifier("foo")
>>> t2 = sql.Identifier("ba'r")
>>> t3 = sql.Identifier('ba"z')
>>> print(sql.SQL(', ').join([t1, t2, t3]).as_string(conn))
"foo", "ba'r", "ba""z"
Multiple strings can be passed to the object to represent a qualified name,
i.e. a dot-separated sequence of identifiers.
Example:
>>> query = sql.SQL("select {} from {}").format(
...     sql.Identifier("table", "field"),
...     sql.Identifier("schema", "table"))
>>> print(query.as_string(conn))
select "table"."field" from "schema"."table"
Changed in version 2.8: added support for multiple strings.
strings¶
A tuple with the strings wrapped by the Identifier.
New in version 2.8: previous verions only had a string attribute. The attribute
still exists but is deprecate and will only work if the
Identifier wraps a single string.
class psycopg2.sql.Literal(wrapped)¶
A Composable representing an SQL value to include in a query.
Usually you will want to include placeholders in the query and pass values
as execute() arguments. If however you really really need to
include a literal value in the query you can use this object.
The string returned by as_string() follows the normal adaptation
rules for Python objects.
Example:
>>> s1 = sql.Literal("foo")
>>> s2 = sql.Literal("ba'r")
>>> s3 = sql.Literal(42)
>>> print(sql.SQL(', ').join([s1, s2, s3]).as_string(conn))
'foo', 'ba''r', 42
class psycopg2.sql.Placeholder(name=None)¶
A Composable representing a placeholder for query parameters.
If the name is specified, generate a named placeholder (e.g. %(name)s),
otherwise generate a positional placeholder (e.g. %s).
The object is useful to generate SQL queries with a variable number of
arguments.
Examples:
>>> names = ['foo', 'bar', 'baz']
>>> q1 = sql.SQL("insert into table ({}) values ({})").format(
...     sql.SQL(', ').join(map(sql.Identifier, names)),
...     sql.SQL(', ').join(sql.Placeholder() * len(names)))
>>> print(q1.as_string(conn))
insert into table ("foo", "bar", "baz") values (%s, %s, %s)
>>> q2 = sql.SQL("insert into table ({}) values ({})").format(
...     sql.SQL(', ').join(map(sql.Identifier, names)),
...     sql.SQL(', ').join(map(sql.Placeholder, names)))
>>> print(q2.as_string(conn))
insert into table ("foo", "bar", "baz") values (%(foo)s, %(bar)s, %(baz)s)
class psycopg2.sql.Composed(seq)¶
A Composable object made of a sequence of Composable.
The object is usually created using Composable operators and methods.
However it is possible to create a Composed directly specifying a
sequence of Composable as arguments.
Example:
>>> comp = sql.Composed(
...     [sql.SQL("insert into "), sql.Identifier("table")])
>>> print(comp.as_string(conn))
insert into "table"
Composed objects are iterable (so they can be used in SQL.join for
instance).
seq¶
The list of the content of the Composed.
join(joiner)¶
Return a new Composed interposing the joiner with the Composed items.
The joiner must be a SQL or a string which will be interpreted as
an SQL.
Example:
>>> fields = sql.Identifier('foo') + sql.Identifier('bar')  # a Composed
>>> print(fields.join(', ').as_string(conn))
"foo", "bar"