1`psycopg2.sql` -- SQL string composition
2========================================
3
4.. sectionauthor:: Daniele Varrazzo <daniele.varrazzo@gmail.com>
5
6.. module:: psycopg2.sql
7
8.. versionadded:: 2.7
9
10The module contains objects and functions useful to generate SQL dynamically,
11in a convenient and safe way. SQL identifiers (e.g. names of tables and
12fields) cannot be passed to the `~cursor.execute()` method like query
13arguments::
14
15    # This will not work
16    table_name = 'my_table'
17    cur.execute("insert into %s values (%s, %s)", [table_name, 10, 20])
18
19The SQL query should be composed before the arguments are merged, for
20instance::
21
22    # This works, but it is not optimal
23    table_name = 'my_table'
24    cur.execute(
25        "insert into %s values (%%s, %%s)" % table_name,
26        [10, 20])
27
28This sort of works, but it is an accident waiting to happen: the table name
29may be an invalid SQL literal and need quoting; even more serious is the
30security problem in case the table name comes from an untrusted source. The
31name should be escaped using `~psycopg2.extensions.quote_ident()`::
32
33    # This works, but it is not optimal
34    table_name = 'my_table'
35    cur.execute(
36        "insert into %s values (%%s, %%s)" % ext.quote_ident(table_name),
37        [10, 20])
38
39This is now safe, but it somewhat ad-hoc. In case, for some reason, it is
40necessary to include a value in the query string (as opposite as in a value)
41the merging rule is still different (`~psycopg2.extensions.adapt()` should be
42used...). It is also still relatively dangerous: if `!quote_ident()` is
43forgotten somewhere, the program will usually work, but will eventually crash
44in the presence of a table or field name with containing characters to escape,
45or will present a potentially exploitable weakness.
46
47The objects exposed by the `!psycopg2.sql` module allow generating SQL
48statements on the fly, separating clearly the variable parts of the statement
49from the query parameters::
50
51    from psycopg2 import sql
52
53    cur.execute(
54        sql.SQL("insert into {} values (%s, %s)")
55            .format(sql.Identifier('my_table')),
56        [10, 20])
57
58
59Module usage
60------------
61
62Usually you should express the template of your query as an `SQL` instance
63with `{}`\-style placeholders and use `~SQL.format()` to merge the variable
64parts into them, all of which must be `Composable` subclasses. You can still
65have `%s`\ -style placeholders in your query and pass values to
66`~cursor.execute()`: such value placeholders will be untouched by
67`!format()`::
68
69    query = sql.SQL("select {field} from {table} where {pkey} = %s").format(
70        field=sql.Identifier('my_name'),
71        table=sql.Identifier('some_table'),
72        pkey=sql.Identifier('id'))
73
74The resulting object is meant to be passed directly to cursor methods such as
75`~cursor.execute()`, `~cursor.executemany()`, `~cursor.copy_expert()`, but can
76also be used to compose a query as a Python string, using the
77`~Composable.as_string()` method::
78
79    cur.execute(query, (42,))
80
81If part of your query is a variable sequence of arguments, such as a
82comma-separated list of field names, you can use the `SQL.join()` method to
83pass them to the query::
84
85    query = sql.SQL("select {fields} from {table}").format(
86        fields=sql.SQL(',').join([
87            sql.Identifier('field1'),
88            sql.Identifier('field2'),
89            sql.Identifier('field3'),
90        ]),
91        table=sql.Identifier('some_table'))
92
93
94`!sql` objects
95--------------
96
97The `!sql` objects are in the following inheritance hierarchy:
98
99|   `Composable`: the base class exposing the common interface
100|   ``|__`` `SQL`: a literal snippet of an SQL query
101|   ``|__`` `Identifier`: a PostgreSQL identifier or dot-separated sequence of identifiers
102|   ``|__`` `Literal`: a value hardcoded into a query
103|   ``|__`` `Placeholder`: a `%s`\ -style placeholder whose value will be added later e.g. by `~cursor.execute()`
104|   ``|__`` `Composed`: a sequence of `!Composable` instances.
105
106
107.. autoclass:: Composable
108
109    .. automethod:: as_string
110
111
112.. autoclass:: SQL
113
114    .. autoattribute:: string
115
116    .. automethod:: format
117
118    .. automethod:: join
119
120
121.. autoclass:: Identifier
122
123    .. versionchanged:: 2.8
124        added support for multiple strings.
125
126    .. autoattribute:: strings
127
128        .. versionadded:: 2.8
129            previous verions only had a `!string` attribute. The attribute
130            still exists but is deprecate and will only work if the
131            `!Identifier` wraps a single string.
132
133.. autoclass:: Literal
134
135    .. autoattribute:: wrapped
136
137
138.. autoclass:: Placeholder
139
140    .. autoattribute:: name
141
142
143.. autoclass:: Composed
144
145    .. autoattribute:: seq
146
147    .. automethod:: join
148