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