REDROOM

Edit File Size: 3.02 KB

/proc/self/root/opt/alt/python37/share/doc/alt-python37-psycopg2/doc/src/sql.rst

Text Base64

`psycopg2.sql` -- SQL string composition
========================================

.. sectionauthor:: Daniele Varrazzo <daniele.varrazzo@gmail.com>

.. module:: psycopg2.sql

.. versionadded:: 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 `~cursor.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 `~psycopg2.extensions.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 (`~psycopg2.extensions.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 `~Composable.as_string()` method) or passed directly
to cursor methods such as `~cursor.execute()`, `~cursor.executemany()`,
`~cursor.copy_expert()`.

.. autoclass:: Composable

.. automethod:: as_string

.. autoclass:: SQL

.. autoattribute:: string

.. automethod:: format

.. automethod:: join

.. autoclass:: Identifier

.. versionchanged:: 2.8
        added support for multiple strings.

.. autoattribute:: strings

.. versionadded:: 2.8
            previous verions only had a `!string` attribute. The attribute
            still exists but is deprecate and will only work if the
            `!Identifier` wraps a single string.

.. autoclass:: Literal

.. autoattribute:: wrapped

.. autoclass:: Placeholder

.. autoattribute:: name

.. autoclass:: Composed

.. autoattribute:: seq

.. automethod:: join

Exit & Reset Text mode: syntax highlighting auto-detects file type.

Name	Size	Perms	Modified	Actions
tools DIR	-	drwxr-xr-x	2023-05-06 02:18:13	Edit Download recursive recursive
_static DIR	-	drwxr-xr-x	2023-05-06 02:18:13	Edit Download recursive recursive
advanced.rst	21.90 KB	lrw-r--r--	2019-10-20 00:45:30	Edit Download recursive recursive
conf.py	8.27 KB	lrw-r--r--	2019-10-20 00:45:30	Edit Download recursive recursive
connection.rst	33.26 KB	lrw-r--r--	2019-10-20 00:45:30	Edit Download recursive recursive
cursor.rst	23.05 KB	lrw-r--r--	2019-10-20 00:45:30	Edit Download recursive recursive
errorcodes.rst	2.46 KB	lrw-r--r--	2019-10-20 00:45:30	Edit Download recursive recursive
errors.rst	2.90 KB	lrw-r--r--	2019-10-20 00:45:30	Edit Download recursive recursive
extensions.rst	32.81 KB	lrw-r--r--	2019-10-20 00:45:30	Edit Download recursive recursive
extras.rst	35.61 KB	lrw-r--r--	2019-10-20 00:45:30	Edit Download recursive recursive
faq.rst	14.59 KB	lrw-r--r--	2019-10-20 00:45:30	Edit Download recursive recursive
index.rst	1.83 KB	lrw-r--r--	2019-10-20 00:45:30	Edit Download recursive recursive
install.rst	10.31 KB	lrw-r--r--	2019-10-20 00:45:30	Edit Download recursive recursive
license.rst	76 B	lrw-r--r--	2019-10-20 00:45:30	Edit Download recursive recursive
Makefile	3.31 KB	lrw-r--r--	2019-10-20 00:45:30	Edit Download recursive recursive
module.rst	13.04 KB	lrw-r--r--	2019-10-20 00:45:30	Edit Download recursive recursive
news.rst	108 B	lrw-r--r--	2019-10-20 00:45:30	Edit Download recursive recursive
pool.rst	1.82 KB	lrw-r--r--	2019-10-20 00:45:30	Edit Download recursive recursive
sql.rst	3.02 KB	lrw-r--r--	2019-10-20 00:45:30	Edit Download recursive recursive
tz.rst	542 B	lrw-r--r--	2019-10-20 00:45:30	Edit Download recursive recursive
usage.rst	40.68 KB	lrw-r--r--	2019-10-20 00:45:30	Edit Download recursive recursive

Directory Contents