Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# dialects/postgresql/psycopg2.py
2# Copyright (C) 2005-2026 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7# mypy: ignore-errors
9r"""
10.. dialect:: postgresql+psycopg2
11 :name: psycopg2
12 :dbapi: psycopg2
13 :connectstring: postgresql+psycopg2://user:password@host:port/dbname[?key=value&key=value...]
14 :url: https://pypi.org/project/psycopg2/
16.. _psycopg2_toplevel:
18psycopg2 Connect Arguments
19--------------------------
21Keyword arguments that are specific to the SQLAlchemy psycopg2 dialect
22may be passed to :func:`_sa.create_engine()`, and include the following:
25* ``isolation_level``: This option, available for all PostgreSQL dialects,
26 includes the ``AUTOCOMMIT`` isolation level when using the psycopg2
27 dialect. This option sets the **default** isolation level for the
28 connection that is set immediately upon connection to the database before
29 the connection is pooled. This option is generally superseded by the more
30 modern :paramref:`_engine.Connection.execution_options.isolation_level`
31 execution option, detailed at :ref:`dbapi_autocommit`.
33 .. seealso::
35 :ref:`psycopg2_isolation_level`
37 :ref:`dbapi_autocommit`
40* ``client_encoding``: sets the client encoding in a libpq-agnostic way,
41 using psycopg2's ``set_client_encoding()`` method.
43 .. seealso::
45 :ref:`psycopg2_unicode`
48* ``executemany_mode``, ``executemany_batch_page_size``,
49 ``executemany_values_page_size``: Allows use of psycopg2
50 extensions for optimizing "executemany"-style queries. See the referenced
51 section below for details.
53 .. seealso::
55 :ref:`psycopg2_executemany_mode`
57.. tip::
59 The above keyword arguments are **dialect** keyword arguments, meaning
60 that they are passed as explicit keyword arguments to :func:`_sa.create_engine()`::
62 engine = create_engine(
63 "postgresql+psycopg2://scott:tiger@localhost/test",
64 isolation_level="SERIALIZABLE",
65 )
67 These should not be confused with **DBAPI** connect arguments, which
68 are passed as part of the :paramref:`_sa.create_engine.connect_args`
69 dictionary and/or are passed in the URL query string, as detailed in
70 the section :ref:`custom_dbapi_args`.
72.. _psycopg2_ssl:
74SSL Connections
75---------------
77The psycopg2 module has a connection argument named ``sslmode`` for
78controlling its behavior regarding secure (SSL) connections. The default is
79``sslmode=prefer``; it will attempt an SSL connection and if that fails it
80will fall back to an unencrypted connection. ``sslmode=require`` may be used
81to ensure that only secure connections are established. Consult the
82psycopg2 / libpq documentation for further options that are available.
84Note that ``sslmode`` is specific to psycopg2 so it is included in the
85connection URI::
87 engine = sa.create_engine(
88 "postgresql+psycopg2://scott:tiger@192.168.0.199:5432/test?sslmode=require"
89 )
91Unix Domain Connections
92------------------------
94psycopg2 supports connecting via Unix domain connections. When the ``host``
95portion of the URL is omitted, SQLAlchemy passes ``None`` to psycopg2,
96which specifies Unix-domain communication rather than TCP/IP communication::
98 create_engine("postgresql+psycopg2://user:password@/dbname")
100By default, the socket file used is to connect to a Unix-domain socket
101in ``/tmp``, or whatever socket directory was specified when PostgreSQL
102was built. This value can be overridden by passing a pathname to psycopg2,
103using ``host`` as an additional keyword argument::
105 create_engine(
106 "postgresql+psycopg2://user:password@/dbname?host=/var/lib/postgresql"
107 )
109.. warning:: The format accepted here allows for a hostname in the main URL
110 in addition to the "host" query string argument. **When using this URL
111 format, the initial host is silently ignored**. That is, this URL::
113 engine = create_engine(
114 "postgresql+psycopg2://user:password@myhost1/dbname?host=myhost2"
115 )
117 Above, the hostname ``myhost1`` is **silently ignored and discarded.** The
118 host which is connected is the ``myhost2`` host.
120 This is to maintain some degree of compatibility with PostgreSQL's own URL
121 format which has been tested to behave the same way and for which tools like
122 PifPaf hardcode two hostnames.
124.. seealso::
126 `PQconnectdbParams \
127 <https://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-PQCONNECTDBPARAMS>`_
129.. _psycopg2_multi_host:
131Specifying multiple fallback hosts
132-----------------------------------
134psycopg2 supports multiple connection points in the connection string.
135When the ``host`` parameter is used multiple times in the query section of
136the URL, SQLAlchemy will create a single string of the host and port
137information provided to make the connections. Tokens may consist of
138``host::port`` or just ``host``; in the latter case, the default port
139is selected by libpq. In the example below, three host connections
140are specified, for ``HostA::PortA``, ``HostB`` connecting to the default port,
141and ``HostC::PortC``::
143 create_engine(
144 "postgresql+psycopg2://user:password@/dbname?host=HostA:PortA&host=HostB&host=HostC:PortC"
145 )
147As an alternative, libpq query string format also may be used; this specifies
148``host`` and ``port`` as single query string arguments with comma-separated
149lists - the default port can be chosen by indicating an empty value
150in the comma separated list::
152 create_engine(
153 "postgresql+psycopg2://user:password@/dbname?host=HostA,HostB,HostC&port=PortA,,PortC"
154 )
156With either URL style, connections to each host is attempted based on a
157configurable strategy, which may be configured using the libpq
158``target_session_attrs`` parameter. Per libpq this defaults to ``any``
159which indicates a connection to each host is then attempted until a connection is successful.
160Other strategies include ``primary``, ``prefer-standby``, etc. The complete
161list is documented by PostgreSQL at
162`libpq connection strings <https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING>`_.
164For example, to indicate two hosts using the ``primary`` strategy::
166 create_engine(
167 "postgresql+psycopg2://user:password@/dbname?host=HostA:PortA&host=HostB&host=HostC:PortC&target_session_attrs=primary"
168 )
170.. versionchanged:: 1.4.40 Port specification in psycopg2 multiple host format
171 is repaired, previously ports were not correctly interpreted in this context.
172 libpq comma-separated format is also now supported.
174.. seealso::
176 `libpq connection strings <https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING>`_ - please refer
177 to this section in the libpq documentation for complete background on multiple host support.
180Empty DSN Connections / Environment Variable Connections
181---------------------------------------------------------
183The psycopg2 DBAPI can connect to PostgreSQL by passing an empty DSN to the
184libpq client library, which by default indicates to connect to a localhost
185PostgreSQL database that is open for "trust" connections. This behavior can be
186further tailored using a particular set of environment variables which are
187prefixed with ``PG_...``, which are consumed by ``libpq`` to take the place of
188any or all elements of the connection string.
190For this form, the URL can be passed without any elements other than the
191initial scheme::
193 engine = create_engine("postgresql+psycopg2://")
195In the above form, a blank "dsn" string is passed to the ``psycopg2.connect()``
196function which in turn represents an empty DSN passed to libpq.
198.. seealso::
200 `Environment Variables\
201 <https://www.postgresql.org/docs/current/libpq-envars.html>`_ -
202 PostgreSQL documentation on how to use ``PG_...``
203 environment variables for connections.
205.. _psycopg2_execution_options:
207Per-Statement/Connection Execution Options
208-------------------------------------------
210The following DBAPI-specific options are respected when used with
211:meth:`_engine.Connection.execution_options`,
212:meth:`.Executable.execution_options`,
213:meth:`_query.Query.execution_options`,
214in addition to those not specific to DBAPIs:
216* ``isolation_level`` - Set the transaction isolation level for the lifespan
217 of a :class:`_engine.Connection` (can only be set on a connection,
218 not a statement
219 or query). See :ref:`psycopg2_isolation_level`.
221* ``stream_results`` - Enable or disable usage of psycopg2 server side
222 cursors - this feature makes use of "named" cursors in combination with
223 special result handling methods so that result rows are not fully buffered.
224 Defaults to False, meaning cursors are buffered by default.
226* ``max_row_buffer`` - when using ``stream_results``, an integer value that
227 specifies the maximum number of rows to buffer at a time. This is
228 interpreted by the :class:`.BufferedRowCursorResult`, and if omitted the
229 buffer will grow to ultimately store 1000 rows at a time.
231 .. versionchanged:: 1.4 The ``max_row_buffer`` size can now be greater than
232 1000, and the buffer will grow to that size.
234.. _psycopg2_batch_mode:
236.. _psycopg2_executemany_mode:
238Psycopg2 Fast Execution Helpers
239-------------------------------
241Modern versions of psycopg2 include a feature known as
242`Fast Execution Helpers \
243<https://www.psycopg.org/docs/extras.html#fast-execution-helpers>`_, which
244have been shown in benchmarking to improve psycopg2's executemany()
245performance, primarily with INSERT statements, by at least
246an order of magnitude.
248SQLAlchemy implements a native form of the "insert many values"
249handler that will rewrite a single-row INSERT statement to accommodate for
250many values at once within an extended VALUES clause; this handler is
251equivalent to psycopg2's ``execute_values()`` handler; an overview of this
252feature and its configuration are at :ref:`engine_insertmanyvalues`.
254.. versionadded:: 2.0 Replaced psycopg2's ``execute_values()`` fast execution
255 helper with a native SQLAlchemy mechanism known as
256 :ref:`insertmanyvalues <engine_insertmanyvalues>`.
258The psycopg2 dialect retains the ability to use the psycopg2-specific
259``execute_batch()`` feature, although it is not expected that this is a widely
260used feature. The use of this extension may be enabled using the
261``executemany_mode`` flag which may be passed to :func:`_sa.create_engine`::
263 engine = create_engine(
264 "postgresql+psycopg2://scott:tiger@host/dbname",
265 executemany_mode="values_plus_batch",
266 )
268Possible options for ``executemany_mode`` include:
270* ``values_only`` - this is the default value. SQLAlchemy's native
271 :ref:`insertmanyvalues <engine_insertmanyvalues>` handler is used for qualifying
272 INSERT statements, assuming
273 :paramref:`_sa.create_engine.use_insertmanyvalues` is left at
274 its default value of ``True``. This handler rewrites simple
275 INSERT statements to include multiple VALUES clauses so that many
276 parameter sets can be inserted with one statement.
278* ``'values_plus_batch'``- SQLAlchemy's native
279 :ref:`insertmanyvalues <engine_insertmanyvalues>` handler is used for qualifying
280 INSERT statements, assuming
281 :paramref:`_sa.create_engine.use_insertmanyvalues` is left at its default
282 value of ``True``. Then, psycopg2's ``execute_batch()`` handler is used for
283 qualifying UPDATE and DELETE statements when executed with multiple parameter
284 sets. When using this mode, the :attr:`_engine.CursorResult.rowcount`
285 attribute will not contain a value for executemany-style executions against
286 UPDATE and DELETE statements.
288.. versionchanged:: 2.0 Removed the ``'batch'`` and ``'None'`` options
289 from psycopg2 ``executemany_mode``. Control over batching for INSERT
290 statements is now configured via the
291 :paramref:`_sa.create_engine.use_insertmanyvalues` engine-level parameter.
293The term "qualifying statements" refers to the statement being executed
294being a Core :func:`_expression.insert`, :func:`_expression.update`
295or :func:`_expression.delete` construct, and **not** a plain textual SQL
296string or one constructed using :func:`_expression.text`. It also may **not** be
297a special "extension" statement such as an "ON CONFLICT" "upsert" statement.
298When using the ORM, all insert/update/delete statements used by the ORM flush process
299are qualifying.
301The "page size" for the psycopg2 "batch" strategy can be affected
302by using the ``executemany_batch_page_size`` parameter, which defaults to
303100.
305For the "insertmanyvalues" feature, the page size can be controlled using the
306:paramref:`_sa.create_engine.insertmanyvalues_page_size` parameter,
307which defaults to 1000. An example of modifying both parameters
308is below::
310 engine = create_engine(
311 "postgresql+psycopg2://scott:tiger@host/dbname",
312 executemany_mode="values_plus_batch",
313 insertmanyvalues_page_size=5000,
314 executemany_batch_page_size=500,
315 )
317.. seealso::
319 :ref:`engine_insertmanyvalues` - background on "insertmanyvalues"
321 :ref:`tutorial_multiple_parameters` - General information on using the
322 :class:`_engine.Connection`
323 object to execute statements in such a way as to make
324 use of the DBAPI ``.executemany()`` method.
327.. _psycopg2_unicode:
329Unicode with Psycopg2
330----------------------
332The psycopg2 DBAPI driver supports Unicode data transparently.
334The client character encoding can be controlled for the psycopg2 dialect
335in the following ways:
337* For PostgreSQL 9.1 and above, the ``client_encoding`` parameter may be
338 passed in the database URL; this parameter is consumed by the underlying
339 ``libpq`` PostgreSQL client library::
341 engine = create_engine(
342 "postgresql+psycopg2://user:pass@host/dbname?client_encoding=utf8"
343 )
345 Alternatively, the above ``client_encoding`` value may be passed using
346 :paramref:`_sa.create_engine.connect_args` for programmatic establishment with
347 ``libpq``::
349 engine = create_engine(
350 "postgresql+psycopg2://user:pass@host/dbname",
351 connect_args={"client_encoding": "utf8"},
352 )
354* For all PostgreSQL versions, psycopg2 supports a client-side encoding
355 value that will be passed to database connections when they are first
356 established. The SQLAlchemy psycopg2 dialect supports this using the
357 ``client_encoding`` parameter passed to :func:`_sa.create_engine`::
359 engine = create_engine(
360 "postgresql+psycopg2://user:pass@host/dbname", client_encoding="utf8"
361 )
363 .. tip:: The above ``client_encoding`` parameter admittedly is very similar
364 in appearance to usage of the parameter within the
365 :paramref:`_sa.create_engine.connect_args` dictionary; the difference
366 above is that the parameter is consumed by psycopg2 and is
367 passed to the database connection using ``SET client_encoding TO
368 'utf8'``; in the previously mentioned style, the parameter is instead
369 passed through psycopg2 and consumed by the ``libpq`` library.
371* A common way to set up client encoding with PostgreSQL databases is to
372 ensure it is configured within the server-side postgresql.conf file;
373 this is the recommended way to set encoding for a server that is
374 consistently of one encoding in all databases::
376 # postgresql.conf file
378 # client_encoding = sql_ascii # actually, defaults to database
379 # encoding
380 client_encoding = utf8
382Transactions
383------------
385The psycopg2 dialect fully supports SAVEPOINT and two-phase commit operations.
387.. _psycopg2_isolation_level:
389Psycopg2 Transaction Isolation Level
390-------------------------------------
392As discussed in :ref:`postgresql_isolation_level`,
393all PostgreSQL dialects support setting of transaction isolation level
394both via the ``isolation_level`` parameter passed to :func:`_sa.create_engine`
395,
396as well as the ``isolation_level`` argument used by
397:meth:`_engine.Connection.execution_options`. When using the psycopg2 dialect
398, these
399options make use of psycopg2's ``set_isolation_level()`` connection method,
400rather than emitting a PostgreSQL directive; this is because psycopg2's
401API-level setting is always emitted at the start of each transaction in any
402case.
404The psycopg2 dialect supports these constants for isolation level:
406* ``READ COMMITTED``
407* ``READ UNCOMMITTED``
408* ``REPEATABLE READ``
409* ``SERIALIZABLE``
410* ``AUTOCOMMIT``
412.. seealso::
414 :ref:`postgresql_isolation_level`
416 :ref:`pg8000_isolation_level`
419NOTICE logging
420---------------
422The psycopg2 dialect will log PostgreSQL NOTICE messages
423via the ``sqlalchemy.dialects.postgresql`` logger. When this logger
424is set to the ``logging.INFO`` level, notice messages will be logged::
426 import logging
428 logging.getLogger("sqlalchemy.dialects.postgresql").setLevel(logging.INFO)
430Above, it is assumed that logging is configured externally. If this is not
431the case, configuration such as ``logging.basicConfig()`` must be utilized::
433 import logging
435 logging.basicConfig() # log messages to stdout
436 logging.getLogger("sqlalchemy.dialects.postgresql").setLevel(logging.INFO)
438.. seealso::
440 `Logging HOWTO <https://docs.python.org/3/howto/logging.html>`_ - on the python.org website
442.. _psycopg2_hstore:
444HSTORE type
445------------
447The ``psycopg2`` DBAPI includes an extension to natively handle marshalling of
448the HSTORE type. The SQLAlchemy psycopg2 dialect will enable this extension
449by default when psycopg2 version 2.4 or greater is used, and
450it is detected that the target database has the HSTORE type set up for use.
451In other words, when the dialect makes the first
452connection, a sequence like the following is performed:
4541. Request the available HSTORE oids using
455 ``psycopg2.extras.HstoreAdapter.get_oids()``.
456 If this function returns a list of HSTORE identifiers, we then determine
457 that the ``HSTORE`` extension is present.
458 This function is **skipped** if the version of psycopg2 installed is
459 less than version 2.4.
4612. If the ``use_native_hstore`` flag is at its default of ``True``, and
462 we've detected that ``HSTORE`` oids are available, the
463 ``psycopg2.extensions.register_hstore()`` extension is invoked for all
464 connections.
466The ``register_hstore()`` extension has the effect of **all Python
467dictionaries being accepted as parameters regardless of the type of target
468column in SQL**. The dictionaries are converted by this extension into a
469textual HSTORE expression. If this behavior is not desired, disable the
470use of the hstore extension by setting ``use_native_hstore`` to ``False`` as
471follows::
473 engine = create_engine(
474 "postgresql+psycopg2://scott:tiger@localhost/test",
475 use_native_hstore=False,
476 )
478The ``HSTORE`` type is **still supported** when the
479``psycopg2.extensions.register_hstore()`` extension is not used. It merely
480means that the coercion between Python dictionaries and the HSTORE
481string format, on both the parameter side and the result side, will take
482place within SQLAlchemy's own marshalling logic, and not that of ``psycopg2``
483which may be more performant.
485""" # noqa
487from __future__ import annotations
489import collections.abc as collections_abc
490import logging
491from typing import cast
493from . import ranges
494from ._psycopg_common import _PGDialect_common_psycopg
495from ._psycopg_common import _PGExecutionContext_common_psycopg
496from .base import PGIdentifierPreparer
497from .json import JSON
498from .json import JSONB
499from ... import types as sqltypes
500from ... import util
501from ...util import FastIntFlag
502from ...util import parse_user_argument_for_enum
504logger = logging.getLogger("sqlalchemy.dialects.postgresql")
507class _PGJSON(JSON):
508 def result_processor(self, dialect, coltype):
509 return None
512class _PGJSONB(JSONB):
513 def result_processor(self, dialect, coltype):
514 return None
517class _Psycopg2Range(ranges.AbstractSingleRangeImpl):
518 _psycopg2_range_cls = "none"
520 def bind_processor(self, dialect):
521 psycopg2_Range = getattr(
522 cast(PGDialect_psycopg2, dialect)._psycopg2_extras,
523 self._psycopg2_range_cls,
524 )
526 def to_range(value):
527 if isinstance(value, ranges.Range):
528 value = psycopg2_Range(
529 value.lower, value.upper, value.bounds, value.empty
530 )
531 return value
533 return to_range
535 def result_processor(self, dialect, coltype):
536 def to_range(value):
537 if value is not None:
538 value = ranges.Range(
539 value._lower,
540 value._upper,
541 bounds=value._bounds if value._bounds else "[)",
542 empty=not value._bounds,
543 )
544 return value
546 return to_range
549class _Psycopg2NumericRange(_Psycopg2Range):
550 _psycopg2_range_cls = "NumericRange"
553class _Psycopg2DateRange(_Psycopg2Range):
554 _psycopg2_range_cls = "DateRange"
557class _Psycopg2DateTimeRange(_Psycopg2Range):
558 _psycopg2_range_cls = "DateTimeRange"
561class _Psycopg2DateTimeTZRange(_Psycopg2Range):
562 _psycopg2_range_cls = "DateTimeTZRange"
565class PGExecutionContext_psycopg2(_PGExecutionContext_common_psycopg):
566 _psycopg2_fetched_rows = None
568 def post_exec(self):
569 self._log_notices(self.cursor)
571 def _log_notices(self, cursor):
572 # check also that notices is an iterable, after it's already
573 # established that we will be iterating through it. This is to get
574 # around test suites such as SQLAlchemy's using a Mock object for
575 # cursor
576 if not cursor.connection.notices or not isinstance(
577 cursor.connection.notices, collections_abc.Iterable
578 ):
579 return
581 for notice in cursor.connection.notices:
582 # NOTICE messages have a
583 # newline character at the end
584 logger.info(notice.rstrip())
586 cursor.connection.notices[:] = []
589class PGIdentifierPreparer_psycopg2(PGIdentifierPreparer):
590 pass
593class ExecutemanyMode(FastIntFlag):
594 EXECUTEMANY_VALUES = 0
595 EXECUTEMANY_VALUES_PLUS_BATCH = 1
598(
599 EXECUTEMANY_VALUES,
600 EXECUTEMANY_VALUES_PLUS_BATCH,
601) = ExecutemanyMode.__members__.values()
604class PGDialect_psycopg2(_PGDialect_common_psycopg):
605 driver = "psycopg2"
607 minimum_dbapi_version = util.VersionInfo((2, 7))
609 supports_statement_cache = True
610 supports_server_side_cursors = True
612 default_paramstyle = "pyformat"
613 # set to true based on psycopg2 version
614 supports_sane_multi_rowcount = False
615 execution_ctx_cls = PGExecutionContext_psycopg2
616 preparer = PGIdentifierPreparer_psycopg2
617 use_insertmanyvalues_wo_returning = True
619 returns_native_bytes = False
621 _has_native_hstore = True
623 colspecs = util.update_copy(
624 _PGDialect_common_psycopg.colspecs,
625 {
626 JSON: _PGJSON,
627 sqltypes.JSON: _PGJSON,
628 JSONB: _PGJSONB,
629 ranges.INT4RANGE: _Psycopg2NumericRange,
630 ranges.INT8RANGE: _Psycopg2NumericRange,
631 ranges.NUMRANGE: _Psycopg2NumericRange,
632 ranges.DATERANGE: _Psycopg2DateRange,
633 ranges.TSRANGE: _Psycopg2DateTimeRange,
634 ranges.TSTZRANGE: _Psycopg2DateTimeTZRange,
635 },
636 )
638 def __init__(
639 self,
640 executemany_mode="values_only",
641 executemany_batch_page_size=100,
642 **kwargs,
643 ):
644 _PGDialect_common_psycopg.__init__(self, **kwargs)
646 if self._native_inet_types:
647 raise NotImplementedError(
648 "The psycopg2 dialect does not implement "
649 "ipaddress type handling; native_inet_types cannot be set "
650 "to ``True`` when using this dialect."
651 )
653 # Parse executemany_mode argument, allowing it to be only one of the
654 # symbol names
655 self.executemany_mode = parse_user_argument_for_enum(
656 executemany_mode,
657 {
658 EXECUTEMANY_VALUES: ["values_only"],
659 EXECUTEMANY_VALUES_PLUS_BATCH: ["values_plus_batch"],
660 },
661 "executemany_mode",
662 )
664 self.executemany_batch_page_size = executemany_batch_page_size
666 @property
667 def psycopg2_version(self):
668 """Legacy accessor for :attr:`.Dialect.dbapi_version`.
670 Retained for backwards compatibility; ``(0, 0)`` is returned when
671 no version can be determined.
673 """
674 version = self._dbapi_version_or_none
675 return version if version is not None else (0, 0)
677 def initialize(self, connection):
678 super().initialize(connection)
679 self._has_native_hstore = (
680 self.use_native_hstore
681 and self._hstore_oids(connection.connection.dbapi_connection)
682 is not None
683 )
685 self.supports_sane_multi_rowcount = (
686 self.executemany_mode is not EXECUTEMANY_VALUES_PLUS_BATCH
687 )
689 @classmethod
690 def import_dbapi(cls):
691 import psycopg2
693 return psycopg2
695 @util.memoized_property
696 def _psycopg2_extensions(cls):
697 from psycopg2 import extensions
699 return extensions
701 @util.memoized_property
702 def _psycopg2_extras(cls):
703 from psycopg2 import extras
705 return extras
707 @util.memoized_property
708 def _isolation_lookup(self):
709 extensions = self._psycopg2_extensions
710 return {
711 "AUTOCOMMIT": extensions.ISOLATION_LEVEL_AUTOCOMMIT,
712 "READ COMMITTED": extensions.ISOLATION_LEVEL_READ_COMMITTED,
713 "READ UNCOMMITTED": extensions.ISOLATION_LEVEL_READ_UNCOMMITTED,
714 "REPEATABLE READ": extensions.ISOLATION_LEVEL_REPEATABLE_READ,
715 "SERIALIZABLE": extensions.ISOLATION_LEVEL_SERIALIZABLE,
716 }
718 def set_isolation_level(self, dbapi_connection, level):
719 dbapi_connection.set_isolation_level(self._isolation_lookup[level])
721 def set_readonly(self, connection, value):
722 connection.readonly = value
724 def get_readonly(self, connection):
725 return connection.readonly
727 def set_deferrable(self, connection, value):
728 connection.deferrable = value
730 def get_deferrable(self, connection):
731 return connection.deferrable
733 def on_connect(self):
734 extras = self._psycopg2_extras
736 fns = []
737 if self.client_encoding is not None:
739 def on_connect(dbapi_conn):
740 dbapi_conn.set_client_encoding(self.client_encoding)
742 fns.append(on_connect)
744 if self.dbapi:
746 def on_connect(dbapi_conn):
747 extras.register_uuid(None, dbapi_conn)
749 fns.append(on_connect)
751 if self.dbapi and self.use_native_hstore:
753 def on_connect(dbapi_conn):
754 hstore_oids = self._hstore_oids(dbapi_conn)
755 if hstore_oids is not None:
756 oid, array_oid = hstore_oids
757 kw = {"oid": oid}
758 kw["array_oid"] = array_oid
759 extras.register_hstore(dbapi_conn, **kw)
761 fns.append(on_connect)
763 if self.dbapi and self._json_deserializer:
765 def on_connect(dbapi_conn):
766 extras.register_default_json(
767 dbapi_conn, loads=self._json_deserializer
768 )
769 extras.register_default_jsonb(
770 dbapi_conn, loads=self._json_deserializer
771 )
773 fns.append(on_connect)
775 if fns:
777 def on_connect(dbapi_conn):
778 for fn in fns:
779 fn(dbapi_conn)
781 return on_connect
782 else:
783 return None
785 def do_executemany(self, cursor, statement, parameters, context=None):
786 if self.executemany_mode is EXECUTEMANY_VALUES_PLUS_BATCH:
787 if self.executemany_batch_page_size:
788 kwargs = {"page_size": self.executemany_batch_page_size}
789 else:
790 kwargs = {}
791 self._psycopg2_extras.execute_batch(
792 cursor, statement, parameters, **kwargs
793 )
794 else:
795 cursor.executemany(statement, parameters)
797 def _twophase_idle_check(self, dbapi_conn):
798 return dbapi_conn.status == self._psycopg2_extensions.STATUS_READY
800 @util.memoized_instancemethod
801 def _hstore_oids(self, dbapi_connection):
802 extras = self._psycopg2_extras
803 oids = extras.HstoreAdapter.get_oids(dbapi_connection)
804 if oids is not None and oids[0]:
805 return oids[0:2]
806 else:
807 return None
809 def is_disconnect(self, e, connection, cursor):
810 if isinstance(e, self.dbapi.Error):
811 # check the "closed" flag. this might not be
812 # present on old psycopg2 versions. Also,
813 # this flag doesn't actually help in a lot of disconnect
814 # situations, so don't rely on it.
815 if getattr(connection, "closed", False):
816 return True
818 # checks based on strings. in the case that .closed
819 # didn't cut it, fall back onto these.
820 str_e = str(e).partition("\n")[0]
821 for msg in self._is_disconnect_messages:
822 idx = str_e.find(msg)
823 if idx >= 0 and '"' not in str_e[:idx]:
824 return True
825 return False
827 @util.memoized_property
828 def _is_disconnect_messages(self):
829 return (
830 # these error messages from libpq: interfaces/libpq/fe-misc.c
831 # and interfaces/libpq/fe-secure.c.
832 "terminating connection",
833 "closed the connection",
834 "connection not open",
835 "could not receive data from server",
836 "could not send data to server",
837 # psycopg2 client errors, psycopg2/connection.h,
838 # psycopg2/cursor.h
839 "connection already closed",
840 "cursor already closed",
841 # not sure where this path is originally from, it may
842 # be obsolete. It really says "losed", not "closed".
843 "losed the connection unexpectedly",
844 # these can occur in newer SSL
845 "connection has been closed unexpectedly",
846 "SSL error: decryption failed or bad record mac",
847 "SSL SYSCALL error: Bad file descriptor",
848 "SSL SYSCALL error: EOF detected",
849 "SSL SYSCALL error: Operation timed out",
850 "SSL SYSCALL error: Bad address",
851 # This can occur in OpenSSL 1 when an unexpected EOF occurs.
852 # https://www.openssl.org/docs/man1.1.1/man3/SSL_get_error.html#BUGS
853 # It may also occur in newer OpenSSL for a non-recoverable I/O
854 # error as a result of a system call that does not set 'errno'
855 # in libc.
856 "SSL SYSCALL error: Success",
857 )
860dialect = PGDialect_psycopg2