codekingpro/portable-devtools
114k
1# dialects/postgresql/psycopg2.py
2# Copyright (C) 2005-2024 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
8
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/
15
16.. _psycopg2_toplevel:
17
18psycopg2 Connect Arguments
19--------------------------
20
21Keyword arguments that are specific to the SQLAlchemy psycopg2 dialect
22may be passed to :func:`_sa.create_engine()`, and include the following:
23
24
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`.
32
33 .. seealso::
34
35 :ref:`psycopg2_isolation_level`
36
37 :ref:`dbapi_autocommit`
38
39
40* ``client_encoding``: sets the client encoding in a libpq-agnostic way,
41 using psycopg2's ``set_client_encoding()`` method.
42
43 .. seealso::
44
45 :ref:`psycopg2_unicode`
46
47
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.
52
53 .. seealso::
54
55 :ref:`psycopg2_executemany_mode`
56
57.. tip::
58
59 The above keyword arguments are **dialect** keyword arguments, meaning
60 that they are passed as explicit keyword arguments to :func:`_sa.create_engine()`::
61
62 engine = create_engine(
63 "postgresql+psycopg2://scott:tiger@localhost/test",
64 isolation_level="SERIALIZABLE",
65 )
66
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`.
71
72.. _psycopg2_ssl:
73
74SSL Connections
75---------------
76
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.
83
84Note that ``sslmode`` is specific to psycopg2 so it is included in the
85connection URI::
86
87 engine = sa.create_engine(
88 "postgresql+psycopg2://scott:tiger@192.168.0.199:5432/test?sslmode=require"
89 )
90
91
92Unix Domain Connections
93------------------------
94
95psycopg2 supports connecting via Unix domain connections. When the ``host``
96portion of the URL is omitted, SQLAlchemy passes ``None`` to psycopg2,
97which specifies Unix-domain communication rather than TCP/IP communication::
98
99 create_engine("postgresql+psycopg2://user:password@/dbname")
100
101By default, the socket file used is to connect to a Unix-domain socket
102in ``/tmp``, or whatever socket directory was specified when PostgreSQL
103was built. This value can be overridden by passing a pathname to psycopg2,
104using ``host`` as an additional keyword argument::
105
106 create_engine("postgresql+psycopg2://user:password@/dbname?host=/var/lib/postgresql")
107
108.. warning:: The format accepted here allows for a hostname in the main URL
109 in addition to the "host" query string argument. **When using this URL
110 format, the initial host is silently ignored**. That is, this URL::
111
112 engine = create_engine("postgresql+psycopg2://user:password@myhost1/dbname?host=myhost2")
113
114 Above, the hostname ``myhost1`` is **silently ignored and discarded.** The
115 host which is connected is the ``myhost2`` host.
116
117 This is to maintain some degree of compatibility with PostgreSQL's own URL
118 format which has been tested to behave the same way and for which tools like
119 PifPaf hardcode two hostnames.
120
121.. seealso::
122
123 `PQconnectdbParams \
124 <https://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-PQCONNECTDBPARAMS>`_
125
126.. _psycopg2_multi_host:
127
128Specifying multiple fallback hosts
129-----------------------------------
130
131psycopg2 supports multiple connection points in the connection string.
132When the ``host`` parameter is used multiple times in the query section of
133the URL, SQLAlchemy will create a single string of the host and port
134information provided to make the connections. Tokens may consist of
135``host::port`` or just ``host``; in the latter case, the default port
136is selected by libpq. In the example below, three host connections
137are specified, for ``HostA::PortA``, ``HostB`` connecting to the default port,
138and ``HostC::PortC``::
139
140 create_engine(
141 "postgresql+psycopg2://user:password@/dbname?host=HostA:PortA&host=HostB&host=HostC:PortC"
142 )
143
144As an alternative, libpq query string format also may be used; this specifies
145``host`` and ``port`` as single query string arguments with comma-separated
146lists - the default port can be chosen by indicating an empty value
147in the comma separated list::
148
149 create_engine(
150 "postgresql+psycopg2://user:password@/dbname?host=HostA,HostB,HostC&port=PortA,,PortC"
151 )
152
153With either URL style, connections to each host is attempted based on a
154configurable strategy, which may be configured using the libpq
155``target_session_attrs`` parameter. Per libpq this defaults to ``any``
156which indicates a connection to each host is then attempted until a connection is successful.
157Other strategies include ``primary``, ``prefer-standby``, etc. The complete
158list is documented by PostgreSQL at
159`libpq connection strings <https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING>`_.
160
161For example, to indicate two hosts using the ``primary`` strategy::
162
163 create_engine(
164 "postgresql+psycopg2://user:password@/dbname?host=HostA:PortA&host=HostB&host=HostC:PortC&target_session_attrs=primary"
165 )
166
167.. versionchanged:: 1.4.40 Port specification in psycopg2 multiple host format
168 is repaired, previously ports were not correctly interpreted in this context.
169 libpq comma-separated format is also now supported.
170
171.. versionadded:: 1.3.20 Support for multiple hosts in PostgreSQL connection
172 string.
173
174.. seealso::
175
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.
178
179
180Empty DSN Connections / Environment Variable Connections
181---------------------------------------------------------
182
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.
189
190For this form, the URL can be passed without any elements other than the
191initial scheme::
192
193 engine = create_engine('postgresql+psycopg2://')
194
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.
197
198.. versionadded:: 1.3.2 support for parameter-less connections with psycopg2.
199
200.. seealso::
201
202 `Environment Variables\
203 <https://www.postgresql.org/docs/current/libpq-envars.html>`_ -
204 PostgreSQL documentation on how to use ``PG_...``
205 environment variables for connections.
206
207.. _psycopg2_execution_options:
208
209Per-Statement/Connection Execution Options
210-------------------------------------------
211
212The following DBAPI-specific options are respected when used with
213:meth:`_engine.Connection.execution_options`,
214:meth:`.Executable.execution_options`,
215:meth:`_query.Query.execution_options`,
216in addition to those not specific to DBAPIs:
217
218* ``isolation_level`` - Set the transaction isolation level for the lifespan
219 of a :class:`_engine.Connection` (can only be set on a connection,
220 not a statement
221 or query). See :ref:`psycopg2_isolation_level`.
222
223* ``stream_results`` - Enable or disable usage of psycopg2 server side
224 cursors - this feature makes use of "named" cursors in combination with
225 special result handling methods so that result rows are not fully buffered.
226 Defaults to False, meaning cursors are buffered by default.
227
228* ``max_row_buffer`` - when using ``stream_results``, an integer value that
229 specifies the maximum number of rows to buffer at a time. This is
230 interpreted by the :class:`.BufferedRowCursorResult`, and if omitted the
231 buffer will grow to ultimately store 1000 rows at a time.
232
233 .. versionchanged:: 1.4 The ``max_row_buffer`` size can now be greater than
234 1000, and the buffer will grow to that size.
235
236.. _psycopg2_batch_mode:
237
238.. _psycopg2_executemany_mode:
239
240Psycopg2 Fast Execution Helpers
241-------------------------------
242
243Modern versions of psycopg2 include a feature known as
244`Fast Execution Helpers \
245<https://www.psycopg.org/docs/extras.html#fast-execution-helpers>`_, which
246have been shown in benchmarking to improve psycopg2's executemany()
247performance, primarily with INSERT statements, by at least
248an order of magnitude.
249
250SQLAlchemy implements a native form of the "insert many values"
251handler that will rewrite a single-row INSERT statement to accommodate for
252many values at once within an extended VALUES clause; this handler is
253equivalent to psycopg2's ``execute_values()`` handler; an overview of this
254feature and its configuration are at :ref:`engine_insertmanyvalues`.
255
256.. versionadded:: 2.0 Replaced psycopg2's ``execute_values()`` fast execution
257 helper with a native SQLAlchemy mechanism known as
258 :ref:`insertmanyvalues <engine_insertmanyvalues>`.
259
260The psycopg2 dialect retains the ability to use the psycopg2-specific
261``execute_batch()`` feature, although it is not expected that this is a widely
262used feature. The use of this extension may be enabled using the
263``executemany_mode`` flag which may be passed to :func:`_sa.create_engine`::
264
265 engine = create_engine(
266 "postgresql+psycopg2://scott:tiger@host/dbname",
267 executemany_mode='values_plus_batch')
268
269
270Possible options for ``executemany_mode`` include:
271
272* ``values_only`` - this is the default value. SQLAlchemy's native
273 :ref:`insertmanyvalues <engine_insertmanyvalues>` handler is used for qualifying
274 INSERT statements, assuming
275 :paramref:`_sa.create_engine.use_insertmanyvalues` is left at
276 its default value of ``True``. This handler rewrites simple
277 INSERT statements to include multiple VALUES clauses so that many
278 parameter sets can be inserted with one statement.
279
280* ``'values_plus_batch'``- SQLAlchemy's native
281 :ref:`insertmanyvalues <engine_insertmanyvalues>` handler is used for qualifying
282 INSERT statements, assuming
283 :paramref:`_sa.create_engine.use_insertmanyvalues` is left at its default
284 value of ``True``. Then, psycopg2's ``execute_batch()`` handler is used for
285 qualifying UPDATE and DELETE statements when executed with multiple parameter
286 sets. When using this mode, the :attr:`_engine.CursorResult.rowcount`
287 attribute will not contain a value for executemany-style executions against
288 UPDATE and DELETE statements.
289
290.. versionchanged:: 2.0 Removed the ``'batch'`` and ``'None'`` options
291 from psycopg2 ``executemany_mode``. Control over batching for INSERT
292 statements is now configured via the
293 :paramref:`_sa.create_engine.use_insertmanyvalues` engine-level parameter.
294
295The term "qualifying statements" refers to the statement being executed
296being a Core :func:`_expression.insert`, :func:`_expression.update`
297or :func:`_expression.delete` construct, and **not** a plain textual SQL
298string or one constructed using :func:`_expression.text`. It also may **not** be
299a special "extension" statement such as an "ON CONFLICT" "upsert" statement.
300When using the ORM, all insert/update/delete statements used by the ORM flush process
301are qualifying.
302
303The "page size" for the psycopg2 "batch" strategy can be affected
304by using the ``executemany_batch_page_size`` parameter, which defaults to
305100.
306
307For the "insertmanyvalues" feature, the page size can be controlled using the
308:paramref:`_sa.create_engine.insertmanyvalues_page_size` parameter,
309which defaults to 1000. An example of modifying both parameters
310is below::
311
312 engine = create_engine(
313 "postgresql+psycopg2://scott:tiger@host/dbname",
314 executemany_mode='values_plus_batch',
315 insertmanyvalues_page_size=5000, executemany_batch_page_size=500)
316
317.. seealso::
318
319 :ref:`engine_insertmanyvalues` - background on "insertmanyvalues"
320
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.
325
326
327.. _psycopg2_unicode:
328
329Unicode with Psycopg2
330----------------------
331
332The psycopg2 DBAPI driver supports Unicode data transparently.
333
334The client character encoding can be controlled for the psycopg2 dialect
335in the following ways:
336
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::
340
341 engine = create_engine("postgresql+psycopg2://user:pass@host/dbname?client_encoding=utf8")
342
343 Alternatively, the above ``client_encoding`` value may be passed using
344 :paramref:`_sa.create_engine.connect_args` for programmatic establishment with
345 ``libpq``::
346
347 engine = create_engine(
348 "postgresql+psycopg2://user:pass@host/dbname",
349 connect_args={'client_encoding': 'utf8'}
350 )
351
352* For all PostgreSQL versions, psycopg2 supports a client-side encoding
353 value that will be passed to database connections when they are first
354 established. The SQLAlchemy psycopg2 dialect supports this using the
355 ``client_encoding`` parameter passed to :func:`_sa.create_engine`::
356
357 engine = create_engine(
358 "postgresql+psycopg2://user:pass@host/dbname",
359 client_encoding="utf8"
360 )
361
362 .. tip:: The above ``client_encoding`` parameter admittedly is very similar
363 in appearance to usage of the parameter within the
364 :paramref:`_sa.create_engine.connect_args` dictionary; the difference
365 above is that the parameter is consumed by psycopg2 and is
366 passed to the database connection using ``SET client_encoding TO
367 'utf8'``; in the previously mentioned style, the parameter is instead
368 passed through psycopg2 and consumed by the ``libpq`` library.
369
370* A common way to set up client encoding with PostgreSQL databases is to
371 ensure it is configured within the server-side postgresql.conf file;
372 this is the recommended way to set encoding for a server that is
373 consistently of one encoding in all databases::
374
375 # postgresql.conf file
376
377 # client_encoding = sql_ascii # actually, defaults to database
378 # encoding
379 client_encoding = utf8
380
381
382
383Transactions
384------------
385
386The psycopg2 dialect fully supports SAVEPOINT and two-phase commit operations.
387
388.. _psycopg2_isolation_level:
389
390Psycopg2 Transaction Isolation Level
391-------------------------------------
392
393As discussed in :ref:`postgresql_isolation_level`,
394all PostgreSQL dialects support setting of transaction isolation level
395both via the ``isolation_level`` parameter passed to :func:`_sa.create_engine`
396,
397as well as the ``isolation_level`` argument used by
398:meth:`_engine.Connection.execution_options`. When using the psycopg2 dialect
399, these
400options make use of psycopg2's ``set_isolation_level()`` connection method,
401rather than emitting a PostgreSQL directive; this is because psycopg2's
402API-level setting is always emitted at the start of each transaction in any
403case.
404
405The psycopg2 dialect supports these constants for isolation level:
406
407* ``READ COMMITTED``
408* ``READ UNCOMMITTED``
409* ``REPEATABLE READ``
410* ``SERIALIZABLE``
411* ``AUTOCOMMIT``
412
413.. seealso::
414
415 :ref:`postgresql_isolation_level`
416
417 :ref:`pg8000_isolation_level`
418
419
420NOTICE logging
421---------------
422
423The psycopg2 dialect will log PostgreSQL NOTICE messages
424via the ``sqlalchemy.dialects.postgresql`` logger. When this logger
425is set to the ``logging.INFO`` level, notice messages will be logged::
426
427 import logging
428
429 logging.getLogger('sqlalchemy.dialects.postgresql').setLevel(logging.INFO)
430
431Above, it is assumed that logging is configured externally. If this is not
432the case, configuration such as ``logging.basicConfig()`` must be utilized::
433
434 import logging
435
436 logging.basicConfig() # log messages to stdout
437 logging.getLogger('sqlalchemy.dialects.postgresql').setLevel(logging.INFO)
438
439.. seealso::
440
441 `Logging HOWTO <https://docs.python.org/3/howto/logging.html>`_ - on the python.org website
442
443.. _psycopg2_hstore:
444
445HSTORE type
446------------
447
448The ``psycopg2`` DBAPI includes an extension to natively handle marshalling of
449the HSTORE type. The SQLAlchemy psycopg2 dialect will enable this extension
450by default when psycopg2 version 2.4 or greater is used, and
451it is detected that the target database has the HSTORE type set up for use.
452In other words, when the dialect makes the first
453connection, a sequence like the following is performed:
454
4551. Request the available HSTORE oids using
456 ``psycopg2.extras.HstoreAdapter.get_oids()``.
457 If this function returns a list of HSTORE identifiers, we then determine
458 that the ``HSTORE`` extension is present.
459 This function is **skipped** if the version of psycopg2 installed is
460 less than version 2.4.
461
4622. If the ``use_native_hstore`` flag is at its default of ``True``, and
463 we've detected that ``HSTORE`` oids are available, the
464 ``psycopg2.extensions.register_hstore()`` extension is invoked for all
465 connections.
466
467The ``register_hstore()`` extension has the effect of **all Python
468dictionaries being accepted as parameters regardless of the type of target
469column in SQL**. The dictionaries are converted by this extension into a
470textual HSTORE expression. If this behavior is not desired, disable the
471use of the hstore extension by setting ``use_native_hstore`` to ``False`` as
472follows::
473
474 engine = create_engine("postgresql+psycopg2://scott:tiger@localhost/test",
475 use_native_hstore=False)
476
477The ``HSTORE`` type is **still supported** when the
478``psycopg2.extensions.register_hstore()`` extension is not used. It merely
479means that the coercion between Python dictionaries and the HSTORE
480string format, on both the parameter side and the result side, will take
481place within SQLAlchemy's own marshalling logic, and not that of ``psycopg2``
482which may be more performant.
483
484""" # noqa
485from __future__ import annotations
486
487import collections.abc as collections_abc
488import logging
489import re
490from typing import cast
491
492from . import ranges
493from ._psycopg_common import _PGDialect_common_psycopg
494from ._psycopg_common import _PGExecutionContext_common_psycopg
495from .base import PGIdentifierPreparer
496from .json import JSON
497from .json import JSONB
498from ... import types as sqltypes
499from ... import util
500from ...util import FastIntFlag
501from ...util import parse_user_argument_for_enum
502
503logger = logging.getLogger("sqlalchemy.dialects.postgresql")
504
505
506class _PGJSON(JSON):
507 def result_processor(self, dialect, coltype):
508 return None
509
510
511class _PGJSONB(JSONB):
512 def result_processor(self, dialect, coltype):
513 return None
514
515
516class _Psycopg2Range(ranges.AbstractSingleRangeImpl):
517 _psycopg2_range_cls = "none"
518
519 def bind_processor(self, dialect):
520 psycopg2_Range = getattr(
521 cast(PGDialect_psycopg2, dialect)._psycopg2_extras,
522 self._psycopg2_range_cls,
523 )
524
525 def to_range(value):
526 if isinstance(value, ranges.Range):
527 value = psycopg2_Range(
528 value.lower, value.upper, value.bounds, value.empty
529 )
530 return value
531
532 return to_range
533
534 def result_processor(self, dialect, coltype):
535 def to_range(value):
536 if value is not None:
537 value = ranges.Range(
538 value._lower,
539 value._upper,
540 bounds=value._bounds if value._bounds else "[)",
541 empty=not value._bounds,
542 )
543 return value
544
545 return to_range
546
547
548class _Psycopg2NumericRange(_Psycopg2Range):
549 _psycopg2_range_cls = "NumericRange"
550
551
552class _Psycopg2DateRange(_Psycopg2Range):
553 _psycopg2_range_cls = "DateRange"
554
555
556class _Psycopg2DateTimeRange(_Psycopg2Range):
557 _psycopg2_range_cls = "DateTimeRange"
558
559
560class _Psycopg2DateTimeTZRange(_Psycopg2Range):
561 _psycopg2_range_cls = "DateTimeTZRange"
562
563
564class PGExecutionContext_psycopg2(_PGExecutionContext_common_psycopg):
565 _psycopg2_fetched_rows = None
566
567 def post_exec(self):
568 self._log_notices(self.cursor)
569
570 def _log_notices(self, cursor):
571 # check also that notices is an iterable, after it's already
572 # established that we will be iterating through it. This is to get
573 # around test suites such as SQLAlchemy's using a Mock object for
574 # cursor
575 if not cursor.connection.notices or not isinstance(
576 cursor.connection.notices, collections_abc.Iterable
577 ):
578 return
579
580 for notice in cursor.connection.notices:
581 # NOTICE messages have a
582 # newline character at the end
583 logger.info(notice.rstrip())
584
585 cursor.connection.notices[:] = []
586
587
588class PGIdentifierPreparer_psycopg2(PGIdentifierPreparer):
589 pass
590
591
592class ExecutemanyMode(FastIntFlag):
593 EXECUTEMANY_VALUES = 0
594 EXECUTEMANY_VALUES_PLUS_BATCH = 1
595
596
597(
598 EXECUTEMANY_VALUES,
599 EXECUTEMANY_VALUES_PLUS_BATCH,
600) = ExecutemanyMode.__members__.values()
601
602
603class PGDialect_psycopg2(_PGDialect_common_psycopg):
604 driver = "psycopg2"
605
606 supports_statement_cache = True
607 supports_server_side_cursors = True
608
609 default_paramstyle = "pyformat"
610 # set to true based on psycopg2 version
611 supports_sane_multi_rowcount = False
612 execution_ctx_cls = PGExecutionContext_psycopg2
613 preparer = PGIdentifierPreparer_psycopg2
614 psycopg2_version = (0, 0)
615 use_insertmanyvalues_wo_returning = True
616
617 returns_native_bytes = False
618
619 _has_native_hstore = True
620
621 colspecs = util.update_copy(
622 _PGDialect_common_psycopg.colspecs,
623 {
624 JSON: _PGJSON,
625 sqltypes.JSON: _PGJSON,
626 JSONB: _PGJSONB,
627 ranges.INT4RANGE: _Psycopg2NumericRange,
628 ranges.INT8RANGE: _Psycopg2NumericRange,
629 ranges.NUMRANGE: _Psycopg2NumericRange,
630 ranges.DATERANGE: _Psycopg2DateRange,
631 ranges.TSRANGE: _Psycopg2DateTimeRange,
632 ranges.TSTZRANGE: _Psycopg2DateTimeTZRange,
633 },
634 )
635
636 def __init__(
637 self,
638 executemany_mode="values_only",
639 executemany_batch_page_size=100,
640 **kwargs,
641 ):
642 _PGDialect_common_psycopg.__init__(self, **kwargs)
643
644 if self._native_inet_types:
645 raise NotImplementedError(
646 "The psycopg2 dialect does not implement "
647 "ipaddress type handling; native_inet_types cannot be set "
648 "to ``True`` when using this dialect."
649 )
650
651 # Parse executemany_mode argument, allowing it to be only one of the
652 # symbol names
653 self.executemany_mode = parse_user_argument_for_enum(
654 executemany_mode,
655 {
656 EXECUTEMANY_VALUES: ["values_only"],
657 EXECUTEMANY_VALUES_PLUS_BATCH: ["values_plus_batch"],
658 },
659 "executemany_mode",
660 )
661
662 self.executemany_batch_page_size = executemany_batch_page_size
663
664 if self.dbapi and hasattr(self.dbapi, "__version__"):
665 m = re.match(r"(\d+)\.(\d+)(?:\.(\d+))?", self.dbapi.__version__)
666 if m:
667 self.psycopg2_version = tuple(
668 int(x) for x in m.group(1, 2, 3) if x is not None
669 )
670
671 if self.psycopg2_version < (2, 7):
672 raise ImportError(
673 "psycopg2 version 2.7 or higher is required."
674 )
675
676 def initialize(self, connection):
677 super().initialize(connection)
678 self._has_native_hstore = (
679 self.use_native_hstore
680 and self._hstore_oids(connection.connection.dbapi_connection)
681 is not None
682 )
683
684 self.supports_sane_multi_rowcount = (
685 self.executemany_mode is not EXECUTEMANY_VALUES_PLUS_BATCH
686 )
687
688 @classmethod
689 def import_dbapi(cls):
690 import psycopg2
691
692 return psycopg2
693
694 @util.memoized_property
695 def _psycopg2_extensions(cls):
696 from psycopg2 import extensions
697
698 return extensions
699
700 @util.memoized_property
701 def _psycopg2_extras(cls):
702 from psycopg2 import extras
703
704 return extras
705
706 @util.memoized_property
707 def _isolation_lookup(self):
708 extensions = self._psycopg2_extensions
709 return {
710 "AUTOCOMMIT": extensions.ISOLATION_LEVEL_AUTOCOMMIT,
711 "READ COMMITTED": extensions.ISOLATION_LEVEL_READ_COMMITTED,
712 "READ UNCOMMITTED": extensions.ISOLATION_LEVEL_READ_UNCOMMITTED,
713 "REPEATABLE READ": extensions.ISOLATION_LEVEL_REPEATABLE_READ,
714 "SERIALIZABLE": extensions.ISOLATION_LEVEL_SERIALIZABLE,
715 }
716
717 def set_isolation_level(self, dbapi_connection, level):
718 dbapi_connection.set_isolation_level(self._isolation_lookup[level])
719
720 def set_readonly(self, connection, value):
721 connection.readonly = value
722
723 def get_readonly(self, connection):
724 return connection.readonly
725
726 def set_deferrable(self, connection, value):
727 connection.deferrable = value
728
729 def get_deferrable(self, connection):
730 return connection.deferrable
731
732 def on_connect(self):
733 extras = self._psycopg2_extras
734
735 fns = []
736 if self.client_encoding is not None:
737
738 def on_connect(dbapi_conn):
739 dbapi_conn.set_client_encoding(self.client_encoding)
740
741 fns.append(on_connect)
742
743 if self.dbapi:
744
745 def on_connect(dbapi_conn):
746 extras.register_uuid(None, dbapi_conn)
747
748 fns.append(on_connect)
749
750 if self.dbapi and self.use_native_hstore:
751
752 def on_connect(dbapi_conn):
753 hstore_oids = self._hstore_oids(dbapi_conn)
754 if hstore_oids is not None:
755 oid, array_oid = hstore_oids
756 kw = {"oid": oid}
757 kw["array_oid"] = array_oid
758 extras.register_hstore(dbapi_conn, **kw)
759
760 fns.append(on_connect)
761
762 if self.dbapi and self._json_deserializer:
763
764 def on_connect(dbapi_conn):
765 extras.register_default_json(
766 dbapi_conn, loads=self._json_deserializer
767 )
768 extras.register_default_jsonb(
769 dbapi_conn, loads=self._json_deserializer
770 )
771
772 fns.append(on_connect)
773
774 if fns:
775
776 def on_connect(dbapi_conn):
777 for fn in fns:
778 fn(dbapi_conn)
779
780 return on_connect
781 else:
782 return None
783
784 def do_executemany(self, cursor, statement, parameters, context=None):
785 if self.executemany_mode is EXECUTEMANY_VALUES_PLUS_BATCH:
786 if self.executemany_batch_page_size:
787 kwargs = {"page_size": self.executemany_batch_page_size}
788 else:
789 kwargs = {}
790 self._psycopg2_extras.execute_batch(
791 cursor, statement, parameters, **kwargs
792 )
793 else:
794 cursor.executemany(statement, parameters)
795
796 def do_begin_twophase(self, connection, xid):
797 connection.connection.tpc_begin(xid)
798
799 def do_prepare_twophase(self, connection, xid):
800 connection.connection.tpc_prepare()
801
802 def _do_twophase(self, dbapi_conn, operation, xid, recover=False):
803 if recover:
804 if dbapi_conn.status != self._psycopg2_extensions.STATUS_READY:
805 dbapi_conn.rollback()
806 operation(xid)
807 else:
808 operation()
809
810 def do_rollback_twophase(
811 self, connection, xid, is_prepared=True, recover=False
812 ):
813 dbapi_conn = connection.connection.dbapi_connection
814 self._do_twophase(
815 dbapi_conn, dbapi_conn.tpc_rollback, xid, recover=recover
816 )
817
818 def do_commit_twophase(
819 self, connection, xid, is_prepared=True, recover=False
820 ):
821 dbapi_conn = connection.connection.dbapi_connection
822 self._do_twophase(
823 dbapi_conn, dbapi_conn.tpc_commit, xid, recover=recover
824 )
825
826 @util.memoized_instancemethod
827 def _hstore_oids(self, dbapi_connection):
828 extras = self._psycopg2_extras
829 oids = extras.HstoreAdapter.get_oids(dbapi_connection)
830 if oids is not None and oids[0]:
831 return oids[0:2]
832 else:
833 return None
834
835 def is_disconnect(self, e, connection, cursor):
836 if isinstance(e, self.dbapi.Error):
837 # check the "closed" flag. this might not be
838 # present on old psycopg2 versions. Also,
839 # this flag doesn't actually help in a lot of disconnect
840 # situations, so don't rely on it.
841 if getattr(connection, "closed", False):
842 return True
843
844 # checks based on strings. in the case that .closed
845 # didn't cut it, fall back onto these.
846 str_e = str(e).partition("\n")[0]
847 for msg in [
848 # these error messages from libpq: interfaces/libpq/fe-misc.c
849 # and interfaces/libpq/fe-secure.c.
850 "terminating connection",
851 "closed the connection",
852 "connection not open",
853 "could not receive data from server",
854 "could not send data to server",
855 # psycopg2 client errors, psycopg2/connection.h,
856 # psycopg2/cursor.h
857 "connection already closed",
858 "cursor already closed",
859 # not sure where this path is originally from, it may
860 # be obsolete. It really says "losed", not "closed".
861 "losed the connection unexpectedly",
862 # these can occur in newer SSL
863 "connection has been closed unexpectedly",
864 "SSL error: decryption failed or bad record mac",
865 "SSL SYSCALL error: Bad file descriptor",
866 "SSL SYSCALL error: EOF detected",
867 "SSL SYSCALL error: Operation timed out",
868 "SSL SYSCALL error: Bad address",
869 ]:
870 idx = str_e.find(msg)
871 if idx >= 0 and '"' not in str_e[:idx]:
872 return True
873 return False
874
875
876dialect = PGDialect_psycopg2
877