.. currentmodule:: psycopg

.. index:: pair: Query; Parameters

.. _query-parameters:

Passing parameters to SQL queries

.. note::

This way of passing queries has been the "classic" way for many years. However, starting from Python 3.14, you might be interested in using the more expressive :ref:template string queries <template-strings>.

Most of the times, writing a program you will have to mix bits of SQL statements with values provided by the rest of the program:

.. code::

SELECT some, fields FROM some_table WHERE id = ...

:sql:id equals what? Probably you will have a Python value you are looking for.

`!execute()` arguments

Passing parameters to a SQL statement happens in functions such as Cursor.execute() by using %s placeholders in the SQL statement, and passing a sequence of values as the second argument of the function. For example the Python function call:

.. code:: python

cur.execute("""
    INSERT INTO some_table (id, created_at, last_name)
    VALUES (%s, %s, %s);
    """,
    (10, datetime.date(2020, 11, 18), "O'Reilly"))

is roughly equivalent to the SQL command:

.. code-block:: sql

INSERT INTO some_table (id, created_at, last_name)
VALUES (10, '2020-11-18', 'O''Reilly');

Note that the parameters will not be really merged to the query: query and the parameters are sent to the server separately: see :ref:server-side-binding for details.

Named arguments are supported too using :samp:%({name})s placeholders in the query and specifying the values into a mapping. Using named arguments allows to specify the values in any order and to repeat the same value in several places in the query::

cur.execute("""
    INSERT INTO some_table (id, created_at, updated_at, last_name)
    VALUES (%(id)s, %(created)s, %(created)s, %(name)s);
    """,
    {'id': 10, 'name': "O'Reilly", 'created': datetime.date(2020, 11, 18)})

Using characters %, (, ) in the argument names is not supported.

When parameters are used, in order to include a literal % in the query you can use the %% string::

cur.execute("SELECT (%s % 2) = 0 AS even", (10,))       # WRONG
cur.execute("SELECT (%s %% 2) = 0 AS even", (10,))      # correct

While the mechanism resembles regular Python strings manipulation, there are a few subtle differences you should care about when passing parameters to a query.

The Python string operator % must not be used: the ~cursor.execute() method accepts a tuple or dictionary of values as second parameter. |sql-warn|__:

.. |sql-warn| replace:: Never use % or + to merge values into queries

.. code:: python

cur.execute("INSERT INTO numbers VALUES (%s, %s)" % (10, 20)) # WRONG cur.execute("INSERT INTO numbers VALUES (%s, %s)", (10, 20)) # correct

.. _: sql-injection
For positional variables binding, the second argument must always be a sequence, even if it contains a single variable (remember that Python requires a comma to create a single element tuple)::

cur.execute("INSERT INTO foo VALUES (%s)", "bar") # WRONG cur.execute("INSERT INTO foo VALUES (%s)", ("bar")) # WRONG cur.execute("INSERT INTO foo VALUES (%s)", ("bar",)) # correct cur.execute("INSERT INTO foo VALUES (%s)", ["bar"]) # correct
The placeholder must not be quoted::

cur.execute("INSERT INTO numbers VALUES ('%s')", ("Hello",)) # WRONG cur.execute("INSERT INTO numbers VALUES (%s)", ("Hello",)) # correct
The variables placeholder must always be a %s, even if a different placeholder (such as a %d for integers or %f for floats) may look more appropriate for the type. You may find other placeholders used in Psycopg queries (%b and %t) but they are not related to the type of the argument: see :ref:binary-data if you want to read more::

cur.execute("INSERT INTO numbers VALUES (%d)", (10,)) # WRONG cur.execute("INSERT INTO numbers VALUES (%s)", (10,)) # correct
Only query values should be bound via this method: it shouldn't be used to merge table or field names to the query. If you need to generate SQL queries dynamically (for instance choosing a table name at runtime) you can use the functionalities provided in the psycopg.sql module::

cur.execute("INSERT INTO %s VALUES (%s)", ('numbers', 10)) # WRONG cur.execute( # correct SQL("INSERT INTO {} VALUES (%s)").format(Identifier('numbers')), (10,))

.. index:: Security, SQL injection

.. _sql-injection:

Danger: SQL injection

The SQL representation of many data types is often different from their Python string representation. The typical example is with single quotes in strings: in SQL single quotes are used as string literal delimiters, so the ones appearing inside the string itself must be escaped, whereas in Python single quotes can be left unescaped if the string is delimited by double quotes.

Because of the difference, sometimes subtle, between the data types representations, a naïve approach to query strings composition, such as using Python strings concatenation, is a recipe for terrible problems::

SQL = "INSERT INTO authors (name) VALUES ('%s')" # NEVER DO THIS
data = ("O'Reilly", )
cur.execute(SQL % data) # THIS WILL FAIL MISERABLY
# SyntaxError: syntax error at or near "Reilly"

If the variables containing the data to send to the database come from an untrusted source (such as data coming from a form on a web site) an attacker could easily craft a malformed string, either gaining access to unauthorized data or performing destructive operations on the database. This form of attack is called SQL injection_ and is known to be one of the most widespread forms of attack on database systems. Before continuing, please print this page__ as a memo and hang it onto your desk.

.. _SQL injection: https://en.wikipedia.org/wiki/SQL_injection .. __: https://xkcd.com/327/

Psycopg can :ref:automatically convert Python objects to SQL values<types-adaptation>: using this feature your code will be more robust and reliable. We must stress this point:

.. warning::

- Don't manually merge values to a query: hackers from a foreign country
  will break into your computer and steal not only your disks, but also
  your cds, leaving you only with the three most embarrassing records you
  ever bought. On cassette tapes.

- If you use the ``%`` operator to merge values to a query, con artists
  will seduce your cat, who will run away taking your credit card
  and your sunglasses with them.

- If you use ``+`` to merge a textual value to a string, bad guys in
  balaclava will find their way to your fridge, drink all your beer, and
  leave your toilet seat up and your toilet paper in the wrong orientation.

- You don't want to manually merge values to a query: :ref:`use the
  provided methods <query-parameters>` instead.

The correct way to pass variables in a SQL command is using the second argument of the Cursor.execute() method::

SQL = "INSERT INTO authors (name) VALUES (%s)"  # Note: no quotes
data = ("O'Reilly", )
cur.execute(SQL, data)  # Note: no % operator

.. note::

Python static code checkers are not quite there yet, but, in the future,
it will be possible to check your code for improper use of string
expressions in queries. See :ref:`literal-string` for details.

.. seealso::

Now that you know how to pass parameters to queries, you can take a look
at :ref:`how Psycopg converts data types <types-adaptation>`.

.. index:: pair: Binary; Parameters

.. _binary-data:

Binary parameters and results

PostgreSQL has two different ways to transmit data between client and server: ~psycopg.pq.Format.TEXT, always available, and ~psycopg.pq.Format.BINARY, available most of the times but not always. Usually the binary format is more efficient to use.

Psycopg can support both formats for each data type. Whenever a value is passed to a query using the normal %s placeholder, the best format available is chosen (often, but not always, the binary format is picked as the best choice).

If you have a reason to select explicitly the binary format or the text format for a value you can use respectively a %b placeholder or a %t placeholder instead of the normal %s. ~Cursor.execute() will fail if a ~psycopg.adapt.Dumper for the right data type and format is not available.

The same two formats, text or binary, are used by PostgreSQL to return data from a query to the client. Unlike with parameters, where you can choose the format value-by-value, all the columns returned by a query will have the same format. Every type returned by the query should have a ~psycopg.adapt.Loader configured, otherwise the data will be returned as unparsed !str (for text results) or buffer (for binary results).

.. note:: The pg_type_ table defines which format is supported for each PostgreSQL data type. Text input/output is managed by the functions declared in the typinput and typoutput fields (always present), binary input/output is managed by the typsend and typreceive (which are optional).

.. _pg_type: https://www.postgresql.org/docs/current/catalog-pg-type.html

Because not every PostgreSQL type supports binary output, by default, the data will be returned in text format. In order to return data in binary format you can create the cursor using Connection.cursor\ !(binary=True) or execute the query using Cursor.execute\ !(binary=True). A case in which requesting binary results is a clear winner is when you have large binary data in the database, such as images::

cur.execute(
    "SELECT image_data FROM images WHERE id = %s", [image_id], binary=True)
data = cur.fetchone()[0]

.. warning::

You cannot execute :ref:`multiple statements in the same query
<multi-statements>` when requesting a binary result.

Passing parameters to SQL queries

Passing parameters to SQL queries

!execute() arguments

Danger: SQL injection

Binary parameters and results

`!execute()` arguments