psycopg2.sql
– SQL string composition¶
New in version 2.7.
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 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. 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),
[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])
The objects exposed by the sql
module can be used to compose a query as a
Python string (using the as_string()
method) or passed directly
to cursor methods such as execute()
, executemany()
,
copy_expert()
.