From 2aeb1e4ad951f5dda351e87f71f7bba1bd2394bc Mon Sep 17 00:00:00 2001 From: Daniele Varrazzo Date: Fri, 13 May 2022 03:13:05 +0200 Subject: [PATCH] docs: add documentation for client-side binding cursors --- docs/advanced/cursors.rst | 54 +++++++++++++++++++++++++++++++- docs/api/cursors.rst | 45 +++++++++++++++++++++++--- docs/basic/from_pg2.rst | 48 +++++++++++++++++++--------- docs/news.rst | 1 + psycopg/psycopg/client_cursor.py | 6 +++- 5 files changed, 133 insertions(+), 21 deletions(-) diff --git a/docs/advanced/cursors.rst b/docs/advanced/cursors.rst index bfd530e20..938b6a935 100644 --- a/docs/advanced/cursors.rst +++ b/docs/advanced/cursors.rst @@ -38,6 +38,58 @@ and the client needs enough memory to hold it, so it is only suitable for reasonably small result sets. +.. index:: + double: Cursor; Client-binding + +.. _client-side-binding-cursors: + +Client-side-binding cursors +--------------------------- + +.. versionadded:: 3.1 + +The previously described :ref:`client-side cursors ` send +the query and the parameters separately to the server. This is the most +efficient way to process parametrised queries and allows to build several +features and optimizations. However, not all types of queries can be bound +server-side; in particular no Data Definition Language query can. See +:ref:`server-side-binding` for the description of these problems. + +The `ClientCursor` (and its `AsyncClientCursor` async counterpart) merge the +query on the client and send the query and the parameters merged together to +the server. This allows to parametrize any type of PostgreSQL statement, not +only queries (:sql:`SELECT`) and Data Manipulation statements (:sql:`INSERT`, +:sql:`UPDATE`, :sql:`DELETE`). + +Using `!ClientCursor`, Psycopg 3 behaviour will be more similar to `psycopg2` +(which only implements client-side binding) and could be useful to port +Psycopg 2 programs more easily to Psycopg 3. The objects in the `sql` module +allow for greater flexibility (for instance to parametrize a table name too, +not only values); however, for simple cases, a `!ClientCursor` could be the +right object. + +In order to obtain `!ClientCursor` from a connection, you can its +`~Connection.cursor_factory` (at init time or changing its attribute +afterwards): + +.. code:: python + + from psycopg import connect, ClientCursor + + conn = psycopg.connect(DSN, cursor_factory=ClientCursor) + cur = conn.cursor() + # + +If you need to create a one-off client-side-binding cursor out of a normal +connection, you can just use the `~ClientCursor` class passing the connection +as argument. + +.. code:: python + + conn = psycopg.connect(DSN) + cur = psycopg.ClientCursor(conn) + + .. index:: double: Cursor; Server-side single: Portal @@ -87,7 +139,7 @@ result is needed. .. _cursor-steal: "Stealing" an existing cursor ------------------------------ +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ A Psycopg `ServerCursor` can be also used to consume a cursor which was created in other ways than the :sql:`DECLARE` that `ServerCursor.execute()` diff --git a/docs/api/cursors.rst b/docs/api/cursors.rst index 57ffadbb8..a3be8b603 100644 --- a/docs/api/cursors.rst +++ b/docs/api/cursors.rst @@ -21,7 +21,7 @@ uncommitted data. The `!Cursor` class ------------------- -.. autoclass:: Cursor() +.. autoclass:: Cursor This class implements a `DBAPI-compliant interface`__. It is what the classic `Connection.cursor()` method returns. `AsyncConnection.cursor()` @@ -243,10 +243,35 @@ The `!Cursor` class is text or binary. +The `!ClientCursor` class +------------------------- + +.. seealso:: See :ref:`client-side-binding-cursors` for details. + +.. autoclass:: ClientCursor + + This `Cursor` subclass has exactly the same interface of its parent class, + but, instead of sending query and parameters separately to the server, it + merges them on the client and sends them as a non-parametric query on the + server. This allows, for instance, to execute parametrized data definition + statements and other :ref:`problematic queries `. + + .. versionadded:: 3.1 + + .. automethod:: mogrify + + :param query: The query to execute. + :type query: `!str`, `!bytes`, or `sql.Composable` + :param params: The parameters to pass to the query, if any. + :type params: Sequence or Mapping + + The `!ServerCursor` class -------------------------- -.. autoclass:: ServerCursor() +.. seealso:: See :ref:`server-side-cursors` for details. + +.. autoclass:: ServerCursor This class also implements a `DBAPI-compliant interface`__. It is created by `Connection.cursor()` specifying the ``name`` parameter. Using this @@ -351,7 +376,7 @@ The `!ServerCursor` class The `!AsyncCursor` class ------------------------ -.. autoclass:: AsyncCursor() +.. autoclass:: AsyncCursor This class implements a DBAPI-inspired interface, with all the blocking methods implemented as coroutines. Unless specified otherwise, @@ -409,10 +434,22 @@ The `!AsyncCursor` class to iterate on the async cursor results. +The `!AsyncClientCursor` class +------------------------------ + +.. autoclass:: AsyncClientCursor + + This class is the `!async` equivalent of the `ClientCursor`. The + difference are the same shown in `AsyncCursor`. + + .. versionadded:: 3.1 + + + The `!AsyncServerCursor` class ------------------------------ -.. autoclass:: AsyncServerCursor() +.. autoclass:: AsyncServerCursor This class implements a DBAPI-inspired interface as the `AsyncCursor` does, but wraps a server-side cursor like the `ServerCursor` class. It is diff --git a/docs/basic/from_pg2.rst b/docs/basic/from_pg2.rst index 6dbc28025..e2b22a307 100644 --- a/docs/basic/from_pg2.rst +++ b/docs/basic/from_pg2.rst @@ -65,15 +65,27 @@ function can be used instead of :sql:`NOTIFY`:: .. __: https://www.postgresql.org/docs/current/sql-notify.html #id-1.9.3.157.7.5 -If this is not possible, you can use client-side binding using the objects -from the `sql` module:: +If this is not possible, you must merge the query and the parameter on the +client side. You can do so using a :ref:`client-side binding cursor +` such as `ClientCursor`:: - >>> from psycopg import sql + >>> cur = ClientCursor(conn) + >>> cur.execute("CREATE TABLE foo (id int DEFAULT %s)", [42]) + +if you need `!ClientCursor` often you can set the `Connection.cursor_factory` +to have them created by default by `Connection.cursor()`. This way, Psycopg 3 +will behave largely the same way of Psycopg 2. - >>> conn.execute(sql.SQL("CREATE TABLE foo (id int DEFAULT {})").format(42)) +Note that, using parameters, you can only specify values. If you need to +parametrize different parts of a statement (aka *the ones that don't go in +single quotes*) you can use the objects from the `psycopg.sql` module:: - # This will correctly quote the password - >>> conn.execute(sql.SQL("ALTER USER john SET PASSWORD {}").format(password)) + >>> from psycopg import sql + + # This will quote the user and the password using the right quotes + >>> conn.execute( + ... sql.SQL("ALTER USER {} SET PASSWORD {}") + ... .format(sql.Identifier(username), password)) .. _multi-statements: @@ -86,7 +98,7 @@ when parameters are used, it is not possible to execute several statements in the same `!execute()` call, separating them with a semicolon:: >>> conn.execute( - ... "insert into foo values (%s); insert into foo values (%s)", + ... "INSERT INTO foo VALUES (%s); INSERT INTO foo VALUES (%s)", ... (10, 20)) Traceback (most recent call last): ... @@ -95,22 +107,28 @@ the same `!execute()` call, separating them with a semicolon:: One obvious way to work around the problem is to use several `!execute()` calls. -There is no such limitation if no parameters are used. This allows one to generate -batches of statements entirely on the client side (for instance using the -`psycopg.sql` objects) and to run them in the same `!execute()` call:: +There is no such limitation if no parameters are used. As a consequence, you +can use a :ref:`client-side binding cursor ` or +the `psycopg.sql` objects to compose a multiple query on the client side and +run them in the same `!execute()` call:: + + + >>> cur = psycopg.ClientCursor(conn) + >>> cur.execute( + ... "INSERT INTO foo VALUES (%s); INSERT INTO foo VALUES (%s)", + ... (10, 20)) >>> from psycopg import sql - >>> query = sql.SQL( - ... "insert into foo values ({}); insert into foo values ({})" - ... ).format(10, 20)) - >>> conn.execute(query) + >>> conn.execute( + ... sql.SQL("INSERT INTO foo VALUES ({}); INSERT INTO foo values ({})" + ... .format(10, 20)) Note that statements that must be run outside a transaction (such as :sql:`CREATE DATABASE`) can never be executed in batch with other statements, even if the connection is in autocommit mode:: >>> conn.autocommit = True - >>> conn.execute("create database foo; select 1") + >>> conn.execute("CREATE DATABASE foo; SELECT 1") Traceback (most recent call last): ... psycopg.errors.ActiveSqlTransaction: CREATE DATABASE cannot run inside a transaction block diff --git a/docs/news.rst b/docs/news.rst index 3076554e9..d13cc694c 100644 --- a/docs/news.rst +++ b/docs/news.rst @@ -14,6 +14,7 @@ Psycopg 3.1 (unreleased) ^^^^^^^^^^^^^^^^^^^^^^^^ - Add :ref:`Pipeline mode ` (:ticket:`#74`). +- Add :ref:`client-side-binding-cursors` (:ticket:`#101`). - Add :ref:`Two-Phase Commit ` support (:ticket:`#72`). - Add :ref:`adapt-enum` (:ticket:`#274`). - Add ``returning`` parameter to `~Cursor.executemany()` to retrieve query diff --git a/psycopg/psycopg/client_cursor.py b/psycopg/psycopg/client_cursor.py index 9c560bb3d..09c3430d2 100644 --- a/psycopg/psycopg/client_cursor.py +++ b/psycopg/psycopg/client_cursor.py @@ -26,7 +26,11 @@ if TYPE_CHECKING: class ClientCursorMixin(BaseCursor[ConnectionType, Row]): def mogrify(self, query: Query, params: Optional[Params] = None) -> str: """ - Return the query to be executed with parameters merged. + Return the query and parameters merged. + + Parameters are adapted and merged to the query the same way that + `!execute()` would do. + """ self._tx = adapt.Transformer(self) pgq = self._convert_query(query, params) -- 2.47.2