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.. versionadded:: 1.3.20 Support for multiple hosts in PostgreSQL connection
175 string.
177.. seealso::
179 `libpq connection strings <https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING>`_ - please refer
180 to this section in the libpq documentation for complete background on multiple host support.
183Empty DSN Connections / Environment Variable Connections
184---------------------------------------------------------
186The psycopg2 DBAPI can connect to PostgreSQL by passing an empty DSN to the
187libpq client library, which by default indicates to connect to a localhost
188PostgreSQL database that is open for "trust" connections. This behavior can be
189further tailored using a particular set of environment variables which are
190prefixed with ``PG_...``, which are consumed by ``libpq`` to take the place of
191any or all elements of the connection string.
193For this form, the URL can be passed without any elements other than the
194initial scheme::
196 engine = create_engine("postgresql+psycopg2://")
198In the above form, a blank "dsn" string is passed to the ``psycopg2.connect()``
199function which in turn represents an empty DSN passed to libpq.
201.. versionadded:: 1.3.2 support for parameter-less connections with psycopg2.
203.. seealso::
205 `Environment Variables\
206 <https://www.postgresql.org/docs/current/libpq-envars.html>`_ -
207 PostgreSQL documentation on how to use ``PG_...``
208 environment variables for connections.
210.. _psycopg2_execution_options:
212Per-Statement/Connection Execution Options
213-------------------------------------------
215The following DBAPI-specific options are respected when used with
216:meth:`_engine.Connection.execution_options`,
217:meth:`.Executable.execution_options`,
218:meth:`_query.Query.execution_options`,
219in addition to those not specific to DBAPIs:
221* ``isolation_level`` - Set the transaction isolation level for the lifespan
222 of a :class:`_engine.Connection` (can only be set on a connection,
223 not a statement
224 or query). See :ref:`psycopg2_isolation_level`.
226* ``stream_results`` - Enable or disable usage of psycopg2 server side
227 cursors - this feature makes use of "named" cursors in combination with
228 special result handling methods so that result rows are not fully buffered.
229 Defaults to False, meaning cursors are buffered by default.
231* ``max_row_buffer`` - when using ``stream_results``, an integer value that
232 specifies the maximum number of rows to buffer at a time. This is
233 interpreted by the :class:`.BufferedRowCursorResult`, and if omitted the
234 buffer will grow to ultimately store 1000 rows at a time.
236 .. versionchanged:: 1.4 The ``max_row_buffer`` size can now be greater than
237 1000, and the buffer will grow to that size.
239.. _psycopg2_batch_mode:
241.. _psycopg2_executemany_mode:
243Psycopg2 Fast Execution Helpers
244-------------------------------
246Modern versions of psycopg2 include a feature known as
247`Fast Execution Helpers \
248<https://www.psycopg.org/docs/extras.html#fast-execution-helpers>`_, which
249have been shown in benchmarking to improve psycopg2's executemany()
250performance, primarily with INSERT statements, by at least
251an order of magnitude.
253SQLAlchemy implements a native form of the "insert many values"
254handler that will rewrite a single-row INSERT statement to accommodate for
255many values at once within an extended VALUES clause; this handler is
256equivalent to psycopg2's ``execute_values()`` handler; an overview of this
257feature and its configuration are at :ref:`engine_insertmanyvalues`.
259.. versionadded:: 2.0 Replaced psycopg2's ``execute_values()`` fast execution
260 helper with a native SQLAlchemy mechanism known as
261 :ref:`insertmanyvalues <engine_insertmanyvalues>`.
263The psycopg2 dialect retains the ability to use the psycopg2-specific
264``execute_batch()`` feature, although it is not expected that this is a widely
265used feature. The use of this extension may be enabled using the
266``executemany_mode`` flag which may be passed to :func:`_sa.create_engine`::
268 engine = create_engine(
269 "postgresql+psycopg2://scott:tiger@host/dbname",
270 executemany_mode="values_plus_batch",
271 )
273Possible options for ``executemany_mode`` include:
275* ``values_only`` - this is the default value. SQLAlchemy's native
276 :ref:`insertmanyvalues <engine_insertmanyvalues>` handler is used for qualifying
277 INSERT statements, assuming
278 :paramref:`_sa.create_engine.use_insertmanyvalues` is left at
279 its default value of ``True``. This handler rewrites simple
280 INSERT statements to include multiple VALUES clauses so that many
281 parameter sets can be inserted with one statement.
283* ``'values_plus_batch'``- SQLAlchemy's native
284 :ref:`insertmanyvalues <engine_insertmanyvalues>` handler is used for qualifying
285 INSERT statements, assuming
286 :paramref:`_sa.create_engine.use_insertmanyvalues` is left at its default
287 value of ``True``. Then, psycopg2's ``execute_batch()`` handler is used for
288 qualifying UPDATE and DELETE statements when executed with multiple parameter
289 sets. When using this mode, the :attr:`_engine.CursorResult.rowcount`
290 attribute will not contain a value for executemany-style executions against
291 UPDATE and DELETE statements.
293.. versionchanged:: 2.0 Removed the ``'batch'`` and ``'None'`` options
294 from psycopg2 ``executemany_mode``. Control over batching for INSERT
295 statements is now configured via the
296 :paramref:`_sa.create_engine.use_insertmanyvalues` engine-level parameter.
298The term "qualifying statements" refers to the statement being executed
299being a Core :func:`_expression.insert`, :func:`_expression.update`
300or :func:`_expression.delete` construct, and **not** a plain textual SQL
301string or one constructed using :func:`_expression.text`. It also may **not** be
302a special "extension" statement such as an "ON CONFLICT" "upsert" statement.
303When using the ORM, all insert/update/delete statements used by the ORM flush process
304are qualifying.
306The "page size" for the psycopg2 "batch" strategy can be affected
307by using the ``executemany_batch_page_size`` parameter, which defaults to
308100.
310For the "insertmanyvalues" feature, the page size can be controlled using the
311:paramref:`_sa.create_engine.insertmanyvalues_page_size` parameter,
312which defaults to 1000. An example of modifying both parameters
313is below::
315 engine = create_engine(
316 "postgresql+psycopg2://scott:tiger@host/dbname",
317 executemany_mode="values_plus_batch",
318 insertmanyvalues_page_size=5000,
319 executemany_batch_page_size=500,
320 )
322.. seealso::
324 :ref:`engine_insertmanyvalues` - background on "insertmanyvalues"
326 :ref:`tutorial_multiple_parameters` - General information on using the
327 :class:`_engine.Connection`
328 object to execute statements in such a way as to make
329 use of the DBAPI ``.executemany()`` method.
332.. _psycopg2_unicode:
334Unicode with Psycopg2
335----------------------
337The psycopg2 DBAPI driver supports Unicode data transparently.
339The client character encoding can be controlled for the psycopg2 dialect
340in the following ways:
342* For PostgreSQL 9.1 and above, the ``client_encoding`` parameter may be
343 passed in the database URL; this parameter is consumed by the underlying
344 ``libpq`` PostgreSQL client library::
346 engine = create_engine(
347 "postgresql+psycopg2://user:pass@host/dbname?client_encoding=utf8"
348 )
350 Alternatively, the above ``client_encoding`` value may be passed using
351 :paramref:`_sa.create_engine.connect_args` for programmatic establishment with
352 ``libpq``::
354 engine = create_engine(
355 "postgresql+psycopg2://user:pass@host/dbname",
356 connect_args={"client_encoding": "utf8"},
357 )
359* For all PostgreSQL versions, psycopg2 supports a client-side encoding
360 value that will be passed to database connections when they are first
361 established. The SQLAlchemy psycopg2 dialect supports this using the
362 ``client_encoding`` parameter passed to :func:`_sa.create_engine`::
364 engine = create_engine(
365 "postgresql+psycopg2://user:pass@host/dbname", client_encoding="utf8"
366 )
368 .. tip:: The above ``client_encoding`` parameter admittedly is very similar
369 in appearance to usage of the parameter within the
370 :paramref:`_sa.create_engine.connect_args` dictionary; the difference
371 above is that the parameter is consumed by psycopg2 and is
372 passed to the database connection using ``SET client_encoding TO
373 'utf8'``; in the previously mentioned style, the parameter is instead
374 passed through psycopg2 and consumed by the ``libpq`` library.
376* A common way to set up client encoding with PostgreSQL databases is to
377 ensure it is configured within the server-side postgresql.conf file;
378 this is the recommended way to set encoding for a server that is
379 consistently of one encoding in all databases::
381 # postgresql.conf file
383 # client_encoding = sql_ascii # actually, defaults to database
384 # encoding
385 client_encoding = utf8
387Transactions
388------------
390The psycopg2 dialect fully supports SAVEPOINT and two-phase commit operations.
392.. _psycopg2_isolation_level:
394Psycopg2 Transaction Isolation Level
395-------------------------------------
397As discussed in :ref:`postgresql_isolation_level`,
398all PostgreSQL dialects support setting of transaction isolation level
399both via the ``isolation_level`` parameter passed to :func:`_sa.create_engine`
400,
401as well as the ``isolation_level`` argument used by
402:meth:`_engine.Connection.execution_options`. When using the psycopg2 dialect
403, these
404options make use of psycopg2's ``set_isolation_level()`` connection method,
405rather than emitting a PostgreSQL directive; this is because psycopg2's
406API-level setting is always emitted at the start of each transaction in any
407case.
409The psycopg2 dialect supports these constants for isolation level:
411* ``READ COMMITTED``
412* ``READ UNCOMMITTED``
413* ``REPEATABLE READ``
414* ``SERIALIZABLE``
415* ``AUTOCOMMIT``
417.. seealso::
419 :ref:`postgresql_isolation_level`
421 :ref:`pg8000_isolation_level`
424NOTICE logging
425---------------
427The psycopg2 dialect will log PostgreSQL NOTICE messages
428via the ``sqlalchemy.dialects.postgresql`` logger. When this logger
429is set to the ``logging.INFO`` level, notice messages will be logged::
431 import logging
433 logging.getLogger("sqlalchemy.dialects.postgresql").setLevel(logging.INFO)
435Above, it is assumed that logging is configured externally. If this is not
436the case, configuration such as ``logging.basicConfig()`` must be utilized::
438 import logging
440 logging.basicConfig() # log messages to stdout
441 logging.getLogger("sqlalchemy.dialects.postgresql").setLevel(logging.INFO)
443.. seealso::
445 `Logging HOWTO <https://docs.python.org/3/howto/logging.html>`_ - on the python.org website
447.. _psycopg2_hstore:
449HSTORE type
450------------
452The ``psycopg2`` DBAPI includes an extension to natively handle marshalling of
453the HSTORE type. The SQLAlchemy psycopg2 dialect will enable this extension
454by default when psycopg2 version 2.4 or greater is used, and
455it is detected that the target database has the HSTORE type set up for use.
456In other words, when the dialect makes the first
457connection, a sequence like the following is performed:
4591. Request the available HSTORE oids using
460 ``psycopg2.extras.HstoreAdapter.get_oids()``.
461 If this function returns a list of HSTORE identifiers, we then determine
462 that the ``HSTORE`` extension is present.
463 This function is **skipped** if the version of psycopg2 installed is
464 less than version 2.4.
4662. If the ``use_native_hstore`` flag is at its default of ``True``, and
467 we've detected that ``HSTORE`` oids are available, the
468 ``psycopg2.extensions.register_hstore()`` extension is invoked for all
469 connections.
471The ``register_hstore()`` extension has the effect of **all Python
472dictionaries being accepted as parameters regardless of the type of target
473column in SQL**. The dictionaries are converted by this extension into a
474textual HSTORE expression. If this behavior is not desired, disable the
475use of the hstore extension by setting ``use_native_hstore`` to ``False`` as
476follows::
478 engine = create_engine(
479 "postgresql+psycopg2://scott:tiger@localhost/test",
480 use_native_hstore=False,
481 )
483The ``HSTORE`` type is **still supported** when the
484``psycopg2.extensions.register_hstore()`` extension is not used. It merely
485means that the coercion between Python dictionaries and the HSTORE
486string format, on both the parameter side and the result side, will take
487place within SQLAlchemy's own marshalling logic, and not that of ``psycopg2``
488which may be more performant.
490""" # noqa
492from __future__ import annotations
494import collections.abc as collections_abc
495import logging
496import re
497from typing import cast
499from . import ranges
500from ._psycopg_common import _PGDialect_common_psycopg
501from ._psycopg_common import _PGExecutionContext_common_psycopg
502from .base import PGIdentifierPreparer
503from .json import JSON
504from .json import JSONB
505from ... import types as sqltypes
506from ... import util
507from ...util import FastIntFlag
508from ...util import parse_user_argument_for_enum
510logger = logging.getLogger("sqlalchemy.dialects.postgresql")
513class _PGJSON(JSON):
514 def result_processor(self, dialect, coltype):
515 return None
518class _PGJSONB(JSONB):
519 def result_processor(self, dialect, coltype):
520 return None
523class _Psycopg2Range(ranges.AbstractSingleRangeImpl):
524 _psycopg2_range_cls = "none"
526 def bind_processor(self, dialect):
527 psycopg2_Range = getattr(
528 cast(PGDialect_psycopg2, dialect)._psycopg2_extras,
529 self._psycopg2_range_cls,
530 )
532 def to_range(value):
533 if isinstance(value, ranges.Range):
534 value = psycopg2_Range(
535 value.lower, value.upper, value.bounds, value.empty
536 )
537 return value
539 return to_range
541 def result_processor(self, dialect, coltype):
542 def to_range(value):
543 if value is not None:
544 value = ranges.Range(
545 value._lower,
546 value._upper,
547 bounds=value._bounds if value._bounds else "[)",
548 empty=not value._bounds,
549 )
550 return value
552 return to_range
555class _Psycopg2NumericRange(_Psycopg2Range):
556 _psycopg2_range_cls = "NumericRange"
559class _Psycopg2DateRange(_Psycopg2Range):
560 _psycopg2_range_cls = "DateRange"
563class _Psycopg2DateTimeRange(_Psycopg2Range):
564 _psycopg2_range_cls = "DateTimeRange"
567class _Psycopg2DateTimeTZRange(_Psycopg2Range):
568 _psycopg2_range_cls = "DateTimeTZRange"
571class PGExecutionContext_psycopg2(_PGExecutionContext_common_psycopg):
572 _psycopg2_fetched_rows = None
574 def post_exec(self):
575 self._log_notices(self.cursor)
577 def _log_notices(self, cursor):
578 # check also that notices is an iterable, after it's already
579 # established that we will be iterating through it. This is to get
580 # around test suites such as SQLAlchemy's using a Mock object for
581 # cursor
582 if not cursor.connection.notices or not isinstance(
583 cursor.connection.notices, collections_abc.Iterable
584 ):
585 return
587 for notice in cursor.connection.notices:
588 # NOTICE messages have a
589 # newline character at the end
590 logger.info(notice.rstrip())
592 cursor.connection.notices[:] = []
595class PGIdentifierPreparer_psycopg2(PGIdentifierPreparer):
596 pass
599class ExecutemanyMode(FastIntFlag):
600 EXECUTEMANY_VALUES = 0
601 EXECUTEMANY_VALUES_PLUS_BATCH = 1
604(
605 EXECUTEMANY_VALUES,
606 EXECUTEMANY_VALUES_PLUS_BATCH,
607) = ExecutemanyMode.__members__.values()
610class PGDialect_psycopg2(_PGDialect_common_psycopg):
611 driver = "psycopg2"
613 supports_statement_cache = True
614 supports_server_side_cursors = True
616 default_paramstyle = "pyformat"
617 # set to true based on psycopg2 version
618 supports_sane_multi_rowcount = False
619 execution_ctx_cls = PGExecutionContext_psycopg2
620 preparer = PGIdentifierPreparer_psycopg2
621 psycopg2_version = (0, 0)
622 use_insertmanyvalues_wo_returning = True
624 returns_native_bytes = False
626 _has_native_hstore = True
628 colspecs = util.update_copy(
629 _PGDialect_common_psycopg.colspecs,
630 {
631 JSON: _PGJSON,
632 sqltypes.JSON: _PGJSON,
633 JSONB: _PGJSONB,
634 ranges.INT4RANGE: _Psycopg2NumericRange,
635 ranges.INT8RANGE: _Psycopg2NumericRange,
636 ranges.NUMRANGE: _Psycopg2NumericRange,
637 ranges.DATERANGE: _Psycopg2DateRange,
638 ranges.TSRANGE: _Psycopg2DateTimeRange,
639 ranges.TSTZRANGE: _Psycopg2DateTimeTZRange,
640 },
641 )
643 def __init__(
644 self,
645 executemany_mode="values_only",
646 executemany_batch_page_size=100,
647 **kwargs,
648 ):
649 _PGDialect_common_psycopg.__init__(self, **kwargs)
651 if self._native_inet_types:
652 raise NotImplementedError(
653 "The psycopg2 dialect does not implement "
654 "ipaddress type handling; native_inet_types cannot be set "
655 "to ``True`` when using this dialect."
656 )
658 # Parse executemany_mode argument, allowing it to be only one of the
659 # symbol names
660 self.executemany_mode = parse_user_argument_for_enum(
661 executemany_mode,
662 {
663 EXECUTEMANY_VALUES: ["values_only"],
664 EXECUTEMANY_VALUES_PLUS_BATCH: ["values_plus_batch"],
665 },
666 "executemany_mode",
667 )
669 self.executemany_batch_page_size = executemany_batch_page_size
671 if self.dbapi and hasattr(self.dbapi, "__version__"):
672 m = re.match(r"(\d+)\.(\d+)(?:\.(\d+))?", self.dbapi.__version__)
673 if m:
674 self.psycopg2_version = tuple(
675 int(x) for x in m.group(1, 2, 3) if x is not None
676 )
678 if self.psycopg2_version < (2, 7):
679 raise ImportError(
680 "psycopg2 version 2.7 or higher is required."
681 )
683 def initialize(self, connection):
684 super().initialize(connection)
685 self._has_native_hstore = (
686 self.use_native_hstore
687 and self._hstore_oids(connection.connection.dbapi_connection)
688 is not None
689 )
691 self.supports_sane_multi_rowcount = (
692 self.executemany_mode is not EXECUTEMANY_VALUES_PLUS_BATCH
693 )
695 @classmethod
696 def import_dbapi(cls):
697 import psycopg2
699 return psycopg2
701 @util.memoized_property
702 def _psycopg2_extensions(cls):
703 from psycopg2 import extensions
705 return extensions
707 @util.memoized_property
708 def _psycopg2_extras(cls):
709 from psycopg2 import extras
711 return extras
713 @util.memoized_property
714 def _isolation_lookup(self):
715 extensions = self._psycopg2_extensions
716 return {
717 "AUTOCOMMIT": extensions.ISOLATION_LEVEL_AUTOCOMMIT,
718 "READ COMMITTED": extensions.ISOLATION_LEVEL_READ_COMMITTED,
719 "READ UNCOMMITTED": extensions.ISOLATION_LEVEL_READ_UNCOMMITTED,
720 "REPEATABLE READ": extensions.ISOLATION_LEVEL_REPEATABLE_READ,
721 "SERIALIZABLE": extensions.ISOLATION_LEVEL_SERIALIZABLE,
722 }
724 def set_isolation_level(self, dbapi_connection, level):
725 dbapi_connection.set_isolation_level(self._isolation_lookup[level])
727 def set_readonly(self, connection, value):
728 connection.readonly = value
730 def get_readonly(self, connection):
731 return connection.readonly
733 def set_deferrable(self, connection, value):
734 connection.deferrable = value
736 def get_deferrable(self, connection):
737 return connection.deferrable
739 def on_connect(self):
740 extras = self._psycopg2_extras
742 fns = []
743 if self.client_encoding is not None:
745 def on_connect(dbapi_conn):
746 dbapi_conn.set_client_encoding(self.client_encoding)
748 fns.append(on_connect)
750 if self.dbapi:
752 def on_connect(dbapi_conn):
753 extras.register_uuid(None, dbapi_conn)
755 fns.append(on_connect)
757 if self.dbapi and self.use_native_hstore:
759 def on_connect(dbapi_conn):
760 hstore_oids = self._hstore_oids(dbapi_conn)
761 if hstore_oids is not None:
762 oid, array_oid = hstore_oids
763 kw = {"oid": oid}
764 kw["array_oid"] = array_oid
765 extras.register_hstore(dbapi_conn, **kw)
767 fns.append(on_connect)
769 if self.dbapi and self._json_deserializer:
771 def on_connect(dbapi_conn):
772 extras.register_default_json(
773 dbapi_conn, loads=self._json_deserializer
774 )
775 extras.register_default_jsonb(
776 dbapi_conn, loads=self._json_deserializer
777 )
779 fns.append(on_connect)
781 if fns:
783 def on_connect(dbapi_conn):
784 for fn in fns:
785 fn(dbapi_conn)
787 return on_connect
788 else:
789 return None
791 def do_executemany(self, cursor, statement, parameters, context=None):
792 if self.executemany_mode is EXECUTEMANY_VALUES_PLUS_BATCH:
793 if self.executemany_batch_page_size:
794 kwargs = {"page_size": self.executemany_batch_page_size}
795 else:
796 kwargs = {}
797 self._psycopg2_extras.execute_batch(
798 cursor, statement, parameters, **kwargs
799 )
800 else:
801 cursor.executemany(statement, parameters)
803 def _twophase_idle_check(self, dbapi_conn):
804 return dbapi_conn.status == self._psycopg2_extensions.STATUS_READY
806 @util.memoized_instancemethod
807 def _hstore_oids(self, dbapi_connection):
808 extras = self._psycopg2_extras
809 oids = extras.HstoreAdapter.get_oids(dbapi_connection)
810 if oids is not None and oids[0]:
811 return oids[0:2]
812 else:
813 return None
815 def is_disconnect(self, e, connection, cursor):
816 if isinstance(e, self.dbapi.Error):
817 # check the "closed" flag. this might not be
818 # present on old psycopg2 versions. Also,
819 # this flag doesn't actually help in a lot of disconnect
820 # situations, so don't rely on it.
821 if getattr(connection, "closed", False):
822 return True
824 # checks based on strings. in the case that .closed
825 # didn't cut it, fall back onto these.
826 str_e = str(e).partition("\n")[0]
827 for msg in self._is_disconnect_messages:
828 idx = str_e.find(msg)
829 if idx >= 0 and '"' not in str_e[:idx]:
830 return True
831 return False
833 @util.memoized_property
834 def _is_disconnect_messages(self):
835 return (
836 # these error messages from libpq: interfaces/libpq/fe-misc.c
837 # and interfaces/libpq/fe-secure.c.
838 "terminating connection",
839 "closed the connection",
840 "connection not open",
841 "could not receive data from server",
842 "could not send data to server",
843 # psycopg2 client errors, psycopg2/connection.h,
844 # psycopg2/cursor.h
845 "connection already closed",
846 "cursor already closed",
847 # not sure where this path is originally from, it may
848 # be obsolete. It really says "losed", not "closed".
849 "losed the connection unexpectedly",
850 # these can occur in newer SSL
851 "connection has been closed unexpectedly",
852 "SSL error: decryption failed or bad record mac",
853 "SSL SYSCALL error: Bad file descriptor",
854 "SSL SYSCALL error: EOF detected",
855 "SSL SYSCALL error: Operation timed out",
856 "SSL SYSCALL error: Bad address",
857 # This can occur in OpenSSL 1 when an unexpected EOF occurs.
858 # https://www.openssl.org/docs/man1.1.1/man3/SSL_get_error.html#BUGS
859 # It may also occur in newer OpenSSL for a non-recoverable I/O
860 # error as a result of a system call that does not set 'errno'
861 # in libc.
862 "SSL SYSCALL error: Success",
863 )
866dialect = PGDialect_psycopg2