sql
– SQL string composition¶
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 escape_identifier()
:
from psycopg3.pq import Escaping
# This works, but it is not optimal
table_name = 'my_table'
cur.execute(
"insert into %s values (%%s, %%s)" % Escaping.escape_identifier(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. It is also still relatively dangerous: if
escape_identifier()
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 psycopg3.sql
module allow generating SQL
statements on the fly, separating clearly the variable parts of the statement
from the query parameters:
from psycopg3 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()
, but can also be used to compose a query as a Python
string, using the 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-
class
psycopg3.sql.
Composable
¶ Abstract base class for objects that can be used to compose an SQL string.
Composable
objects can be passed directly toexecute()
,executemany()
,copy()
in place of the query string.Composable
objects can be joined using the+
operator: the result will be aComposed
instance containing the objects joined. The operator*
is also supported with an integer argument: the result is aComposed
instance containing the left argument repeated as many times as requested.-
as_string
(context: Optional[psycopg3.proto.AdaptContext]) → str¶ Return the value of the object as string.
- Parameters
context (
connection
orcursor
) – the context to evaluate the string into.
-
-
class
psycopg3.sql.
SQL
(obj: str)¶ A
Composable
representing a snippet of SQL statement.SQL
exposesjoin()
andformat()
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
orLiteral
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"
-
format
(*args: Any, **kwargs: Any) → psycopg3.sql.Composed¶ Merge
Composable
objects into a template.- Parameters
args – parameters to replace to numbered (
{0}
,{1}
) or auto-numbered ({}
) placeholderskwargs – parameters to replace to named (
{name}
) placeholders
- Returns
the union of the
SQL
string with placeholders replaced- Return type
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 itsas_string()
method. If any other Python object is passed, it will be wrapped in aLiteral
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: Sequence[psycopg3.sql.Composable]) → psycopg3.sql.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 thatComposed
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
psycopg3.sql.
Identifier
(*strings: str)¶ 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"
-
class
psycopg3.sql.
Literal
(obj: Any)¶ 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
psycopg3.sql.
Placeholder
(name: str = '', format: psycopg3.adapt.Format = <Format.AUTO: 's'>)¶ A
Composable
representing a placeholder for query parameters.If the name is specified, generate a named placeholder (e.g.
%(name)s
,%(name)b
), otherwise generate a positional placeholder (e.g.%s
,%b
).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
psycopg3.sql.
Composed
(seq: Sequence[Any])¶ A
Composable
object made of a sequence ofComposable
.The object is usually created using
Composable
operators and methods. However it is possible to create aComposed
directly specifying a sequence of objects as arguments: if they are notComposable
they will be wrapped in aLiteral
.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 inSQL.join
for instance).-
join
(joiner: Union[SQL, str]) → psycopg3.sql.Composed¶ Return a new
Composed
interposing the joiner with theComposed
items.The joiner must be a
SQL
or a string which will be interpreted as anSQL
.Example:
>>> fields = sql.Identifier('foo') + sql.Identifier('bar') # a Composed >>> print(fields.join(', ').as_string(conn)) "foo", "bar"
-
Utility functions¶
-
psycopg3.sql.
quote
(obj: Any, context: Optional[psycopg3.proto.AdaptContext] = None) → str¶ Adapt a Python object to a quoted SQL string.
Use this function only if you absolutely want to convert a Python string to an SQL quoted literal to use e.g. to generate batch SQL and you won’t have a connection avaliable when you will need to use it.
This function is relatively inefficient, because it doesn’t cache the adaptation rules. If you pass a context you can adapt the adaptation rules used, otherwise only global rules are used.