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:

class psycopg.sql. Composable ( obj ) #

Abstract base class for objects that can be used to compose an SQL string.

Composable objects can be passed directly to execute() , executemany() , copy() 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.

abstract as_bytes ( context ) #

Return the value of the object as bytes.

Parameters :

context ( connection or cursor ) - the context to evaluate the object into.

Return type :

bytes

The method is automatically invoked by ~psycopg.Cursor.execute() , ~psycopg.Cursor.executemany() , ~psycopg.Cursor.copy() if a !Composable is passed instead of the query string.

as_string ( context ) #

Return the value of the object as string.

Parameters :

context ( connection or cursor ) - the context to evaluate the string into.

Return type :

str

class psycopg.sql. SQL ( obj ) #

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 !obj 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"

Changed in version 3.1: The input object should be a ~typing.LiteralString . See PEP 675 for details.

format ( * args , ** kwargs ) #

Merge Composable objects into a template.

Parameters :

• args ( Any ) - parameters to replace to numbered ( {0} , {1} ) or auto-numbered ( {} ) placeholders

• kwargs ( Any ) - 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.

If a !Composable objects is passed to the template it will be merged according to its as_string() method. If any other Python object is passed, it will be wrapped in a Literal 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 ) #

Join a sequence of Composable .

Parameters :

seq (iterable of !Composable ) - the elements to join.

Return type :

Composed

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 psycopg.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"

class psycopg.sql. Literal ( obj ) #

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 ~cursor.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("fo'o")
>>> s2 = sql.Literal(42)
>>> s3 = sql.Literal(date(2000, 1, 1))
>>> print(sql.SQL(', ').join([s1, s2, s3]).as_string(conn))
'fo''o', 42, '2000-01-01'::date

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

class psycopg.sql. Placeholder ( name = '' , format = PyFormat.AUTO ) #

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 my_table ({}) VALUES ({})").format(
...     sql.SQL(', ').join(map(sql.Identifier, names)),
...     sql.SQL(', ').join(sql.Placeholder() * len(names)))
>>> print(q1.as_string(conn))
INSERT INTO my_table ("foo", "bar", "baz") VALUES (%s, %s, %s)

>>> q2 = sql.SQL("INSERT INTO my_table ({}) VALUES ({})").format(
...     sql.SQL(', ').join(map(sql.Identifier, names)),
...     sql.SQL(', ').join(map(sql.Placeholder, names)))
>>> print(q2.as_string(conn))
INSERT INTO my_table ("foo", "bar", "baz") VALUES (%(foo)s, %(bar)s, %(baz)s)

class psycopg.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 objects as arguments: if they are not !Composable they will be wrapped in a Literal .

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).

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"

Return type :

Composed

Utility functions #

psycopg.sql. quote ( obj , context = None ) #

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 available 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.

Return type :

str

psycopg.sql. NULL #

psycopg.sql. DEFAULT #

sql.SQL objects often useful in queries.