codekingpro/portable-devtools
115k
1# dialects/oracle/oracledb.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
8
9r""".. dialect:: oracle+oracledb
10 :name: python-oracledb
11 :dbapi: oracledb
12 :connectstring: oracle+oracledb://user:pass@hostname:port[/dbname][?service_name=<service>[&key=value&key=value...]]
13 :url: https://oracle.github.io/python-oracledb/
14
15Description
16-----------
17
18Python-oracledb is the Oracle Database driver for Python. It features a default
19"thin" client mode that requires no dependencies, and an optional "thick" mode
20that uses Oracle Client libraries. It supports SQLAlchemy features including
21two phase transactions and Asyncio.
22
23Python-oracle is the renamed, updated cx_Oracle driver. Oracle is no longer
24doing any releases in the cx_Oracle namespace.
25
26The SQLAlchemy ``oracledb`` dialect provides both a sync and an async
27implementation under the same dialect name. The proper version is
28selected depending on how the engine is created:
29
30* calling :func:`_sa.create_engine` with ``oracle+oracledb://...`` will
31 automatically select the sync version::
32
33 from sqlalchemy import create_engine
34
35 sync_engine = create_engine(
36 "oracle+oracledb://scott:tiger@localhost?service_name=FREEPDB1"
37 )
38
39* calling :func:`_asyncio.create_async_engine` with ``oracle+oracledb://...``
40 will automatically select the async version::
41
42 from sqlalchemy.ext.asyncio import create_async_engine
43
44 asyncio_engine = create_async_engine(
45 "oracle+oracledb://scott:tiger@localhost?service_name=FREEPDB1"
46 )
47
48 The asyncio version of the dialect may also be specified explicitly using the
49 ``oracledb_async`` suffix::
50
51 from sqlalchemy.ext.asyncio import create_async_engine
52
53 asyncio_engine = create_async_engine(
54 "oracle+oracledb_async://scott:tiger@localhost?service_name=FREEPDB1"
55 )
56
57.. versionadded:: 2.0.25 added support for the async version of oracledb.
58
59Thick mode support
60------------------
61
62By default, the python-oracledb driver runs in a "thin" mode that does not
63require Oracle Client libraries to be installed. The driver also supports a
64"thick" mode that uses Oracle Client libraries to get functionality such as
65Oracle Application Continuity.
66
67To enable thick mode, call `oracledb.init_oracle_client()
68<https://python-oracledb.readthedocs.io/en/latest/api_manual/module.html#oracledb.init_oracle_client>`_
69explicitly, or pass the parameter ``thick_mode=True`` to
70:func:`_sa.create_engine`. To pass custom arguments to
71``init_oracle_client()``, like the ``lib_dir`` path, a dict may be passed, for
72example::
73
74 engine = sa.create_engine(
75 "oracle+oracledb://...",
76 thick_mode={
77 "lib_dir": "/path/to/oracle/client/lib",
78 "config_dir": "/path/to/network_config_file_directory",
79 "driver_name": "my-app : 1.0.0",
80 },
81 )
82
83Note that passing a ``lib_dir`` path should only be done on macOS or
84Windows. On Linux it does not behave as you might expect.
85
86.. seealso::
87
88 python-oracledb documentation `Enabling python-oracledb Thick mode
89 <https://python-oracledb.readthedocs.io/en/latest/user_guide/initialization.html#enabling-python-oracledb-thick-mode>`_
90
91Connecting to Oracle Database
92-----------------------------
93
94python-oracledb provides several methods of indicating the target database.
95The dialect translates from a series of different URL forms.
96
97Given the hostname, port and service name of the target database, you can
98connect in SQLAlchemy using the ``service_name`` query string parameter::
99
100 engine = create_engine(
101 "oracle+oracledb://scott:tiger@hostname:port?service_name=myservice"
102 )
103
104Connecting with Easy Connect strings
105^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
106
107You can pass any valid python-oracledb connection string as the ``dsn`` key
108value in a :paramref:`_sa.create_engine.connect_args` dictionary. See
109python-oracledb documentation `Oracle Net Services Connection Strings
110<https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html#oracle-net-services-connection-strings>`_.
111
112For example to use an `Easy Connect string
113<https://download.oracle.com/ocomdocs/global/Oracle-Net-Easy-Connect-Plus.pdf>`_
114with a timeout to prevent connection establishment from hanging if the network
115transport to the database cannot be established in 30 seconds, and also setting
116a keep-alive time of 60 seconds to stop idle network connections from being
117terminated by a firewall::
118
119 e = create_engine(
120 "oracle+oracledb://@",
121 connect_args={
122 "user": "scott",
123 "password": "tiger",
124 "dsn": "hostname:port/myservice?transport_connect_timeout=30&expire_time=60",
125 },
126 )
127
128The Easy Connect syntax has been enhanced during the life of Oracle Database.
129Review the documentation for your database version. The current documentation
130is at `Understanding the Easy Connect Naming Method
131<https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=GUID-B0437826-43C1-49EC-A94D-B650B6A4A6EE>`_.
132
133The general syntax is similar to:
134
135.. sourcecode:: text
136
137 [[protocol:]//]host[:port][/[service_name]][?parameter_name=value{¶meter_name=value}]
138
139Note that although the SQLAlchemy URL syntax ``hostname:port/dbname`` looks
140like Oracle's Easy Connect syntax, it is different. SQLAlchemy's URL requires a
141system identifier (SID) for the ``dbname`` component::
142
143 engine = create_engine("oracle+oracledb://scott:tiger@hostname:port/sid")
144
145Easy Connect syntax does not support SIDs. It uses services names, which are
146the preferred choice for connecting to Oracle Database.
147
148Passing python-oracledb connect arguments
149^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
150
151Other python-oracledb driver `connection options
152<https://python-oracledb.readthedocs.io/en/latest/api_manual/module.html#oracledb.connect>`_
153can be passed in ``connect_args``. For example::
154
155 e = create_engine(
156 "oracle+oracledb://@",
157 connect_args={
158 "user": "scott",
159 "password": "tiger",
160 "dsn": "hostname:port/myservice",
161 "events": True,
162 "mode": oracledb.AUTH_MODE_SYSDBA,
163 },
164 )
165
166Connecting with tnsnames.ora TNS aliases
167^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
168
169If no port, database name, or service name is provided, the dialect will use an
170Oracle Database DSN "connection string". This takes the "hostname" portion of
171the URL as the data source name. For example, if the ``tnsnames.ora`` file
172contains a `TNS Alias
173<https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html#tns-aliases-for-connection-strings>`_
174of ``myalias`` as below:
175
176.. sourcecode:: text
177
178 myalias =
179 (DESCRIPTION =
180 (ADDRESS = (PROTOCOL = TCP)(HOST = mymachine.example.com)(PORT = 1521))
181 (CONNECT_DATA =
182 (SERVER = DEDICATED)
183 (SERVICE_NAME = orclpdb1)
184 )
185 )
186
187The python-oracledb dialect connects to this database service when ``myalias`` is the
188hostname portion of the URL, without specifying a port, database name or
189``service_name``::
190
191 engine = create_engine("oracle+oracledb://scott:tiger@myalias")
192
193Connecting to Oracle Autonomous Database
194^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
195
196Users of Oracle Autonomous Database should use either use the TNS Alias URL
197shown above, or pass the TNS Alias as the ``dsn`` key value in a
198:paramref:`_sa.create_engine.connect_args` dictionary.
199
200If Oracle Autonomous Database is configured for mutual TLS ("mTLS")
201connections, then additional configuration is required as shown in `Connecting
202to Oracle Cloud Autonomous Databases
203<https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html#connecting-to-oracle-cloud-autonomous-databases>`_. In
204summary, Thick mode users should configure file locations and set the wallet
205path in ``sqlnet.ora`` appropriately::
206
207 e = create_engine(
208 "oracle+oracledb://@",
209 thick_mode={
210 # directory containing tnsnames.ora and cwallet.so
211 "config_dir": "/opt/oracle/wallet_dir",
212 },
213 connect_args={
214 "user": "scott",
215 "password": "tiger",
216 "dsn": "mydb_high",
217 },
218 )
219
220Thin mode users of mTLS should pass the appropriate directories and PEM wallet
221password when creating the engine, similar to::
222
223 e = create_engine(
224 "oracle+oracledb://@",
225 connect_args={
226 "user": "scott",
227 "password": "tiger",
228 "dsn": "mydb_high",
229 "config_dir": "/opt/oracle/wallet_dir", # directory containing tnsnames.ora
230 "wallet_location": "/opt/oracle/wallet_dir", # directory containing ewallet.pem
231 "wallet_password": "top secret", # password for the PEM file
232 },
233 )
234
235Typically ``config_dir`` and ``wallet_location`` are the same directory, which
236is where the Oracle Autonomous Database wallet zip file was extracted. Note
237this directory should be protected.
238
239Using python-oracledb Connection Pooling
240----------------------------------------
241
242The python-oracledb driver provides its own connection pool implementation that
243may be used in place of SQLAlchemy's pooling functionality. The driver pool
244gives support for high availability features such as dead connection detection,
245connection draining for planned database downtime, support for Oracle
246Application Continuity and Transparent Application Continuity, and gives
247support for `Database Resident Connection Pooling (DRCP)
248<https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html#database-resident-connection-pooling-drcp>`_.
249
250To take advantage of python-oracledb's pool, use the
251:paramref:`_sa.create_engine.creator` parameter to provide a function that
252returns a new connection, along with setting
253:paramref:`_sa.create_engine.pool_class` to ``NullPool`` to disable
254SQLAlchemy's pooling::
255
256 import oracledb
257 from sqlalchemy import create_engine
258 from sqlalchemy import text
259 from sqlalchemy.pool import NullPool
260
261 # Uncomment to use the optional python-oracledb Thick mode.
262 # Review the python-oracledb doc for the appropriate parameters
263 # oracledb.init_oracle_client(<your parameters>)
264
265 pool = oracledb.create_pool(
266 user="scott",
267 password="tiger",
268 dsn="localhost:1521/freepdb1",
269 min=1,
270 max=4,
271 increment=1,
272 )
273 engine = create_engine(
274 "oracle+oracledb://", creator=pool.acquire, poolclass=NullPool
275 )
276
277The above engine may then be used normally. Internally, python-oracledb handles
278connection pooling::
279
280 with engine.connect() as conn:
281 print(conn.scalar(text("select 1 from dual")))
282
283Refer to the python-oracledb documentation for `oracledb.create_pool()
284<https://python-oracledb.readthedocs.io/en/latest/api_manual/module.html#oracledb.create_pool>`_
285for the arguments that can be used when creating a connection pool.
286
287.. _drcp:
288
289Using Oracle Database Resident Connection Pooling (DRCP)
290--------------------------------------------------------
291
292When using Oracle Database's Database Resident Connection Pooling (DRCP), the
293best practice is to specify a connection class and "purity". Refer to the
294`python-oracledb documentation on DRCP
295<https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html#database-resident-connection-pooling-drcp>`_.
296For example::
297
298 import oracledb
299 from sqlalchemy import create_engine
300 from sqlalchemy import text
301 from sqlalchemy.pool import NullPool
302
303 # Uncomment to use the optional python-oracledb Thick mode.
304 # Review the python-oracledb doc for the appropriate parameters
305 # oracledb.init_oracle_client(<your parameters>)
306
307 pool = oracledb.create_pool(
308 user="scott",
309 password="tiger",
310 dsn="localhost:1521/freepdb1",
311 min=1,
312 max=4,
313 increment=1,
314 cclass="MYCLASS",
315 purity=oracledb.PURITY_SELF,
316 )
317 engine = create_engine(
318 "oracle+oracledb://", creator=pool.acquire, poolclass=NullPool
319 )
320
321The above engine may then be used normally where python-oracledb handles
322application connection pooling and Oracle Database additionally uses DRCP::
323
324 with engine.connect() as conn:
325 print(conn.scalar(text("select 1 from dual")))
326
327If you wish to use different connection classes or purities for different
328connections, then wrap ``pool.acquire()``::
329
330 import oracledb
331 from sqlalchemy import create_engine
332 from sqlalchemy import text
333 from sqlalchemy.pool import NullPool
334
335 # Uncomment to use python-oracledb Thick mode.
336 # Review the python-oracledb doc for the appropriate parameters
337 # oracledb.init_oracle_client(<your parameters>)
338
339 pool = oracledb.create_pool(
340 user="scott",
341 password="tiger",
342 dsn="localhost:1521/freepdb1",
343 min=1,
344 max=4,
345 increment=1,
346 cclass="MYCLASS",
347 purity=oracledb.PURITY_SELF,
348 )
349
350
351 def creator():
352 return pool.acquire(cclass="MYOTHERCLASS", purity=oracledb.PURITY_NEW)
353
354
355 engine = create_engine(
356 "oracle+oracledb://", creator=creator, poolclass=NullPool
357 )
358
359Engine Options consumed by the SQLAlchemy oracledb dialect outside of the driver
360--------------------------------------------------------------------------------
361
362There are also options that are consumed by the SQLAlchemy oracledb dialect
363itself. These options are always passed directly to :func:`_sa.create_engine`,
364such as::
365
366 e = create_engine("oracle+oracledb://user:pass@tnsalias", arraysize=500)
367
368The parameters accepted by the oracledb dialect are as follows:
369
370* ``arraysize`` - set the driver cursor.arraysize value. It defaults to
371 ``None``, indicating that the driver default value of 100 should be used.
372 This setting controls how many rows are buffered when fetching rows, and can
373 have a significant effect on performance if increased for queries that return
374 large numbers of rows.
375
376 .. versionchanged:: 2.0.26 - changed the default value from 50 to None,
377 to use the default value of the driver itself.
378
379* ``auto_convert_lobs`` - defaults to True; See :ref:`oracledb_lob`.
380
381* ``coerce_to_decimal`` - see :ref:`oracledb_numeric` for detail.
382
383* ``encoding_errors`` - see :ref:`oracledb_unicode_encoding_errors` for detail.
384
385.. _oracledb_unicode:
386
387Unicode
388-------
389
390As is the case for all DBAPIs under Python 3, all strings are inherently
391Unicode strings.
392
393Ensuring the Correct Client Encoding
394^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
395
396In python-oracledb, the encoding used for all character data is "UTF-8".
397
398Unicode-specific Column datatypes
399^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
400
401The Core expression language handles unicode data by use of the
402:class:`.Unicode` and :class:`.UnicodeText` datatypes. These types correspond
403to the VARCHAR2 and CLOB Oracle Database datatypes by default. When using
404these datatypes with Unicode data, it is expected that the database is
405configured with a Unicode-aware character set so that the VARCHAR2 and CLOB
406datatypes can accommodate the data.
407
408In the case that Oracle Database is not configured with a Unicode character
409set, the two options are to use the :class:`_types.NCHAR` and
410:class:`_oracle.NCLOB` datatypes explicitly, or to pass the flag
411``use_nchar_for_unicode=True`` to :func:`_sa.create_engine`, which will cause
412the SQLAlchemy dialect to use NCHAR/NCLOB for the :class:`.Unicode` /
413:class:`.UnicodeText` datatypes instead of VARCHAR/CLOB.
414
415.. versionchanged:: 1.3 The :class:`.Unicode` and :class:`.UnicodeText`
416 datatypes now correspond to the ``VARCHAR2`` and ``CLOB`` Oracle Database
417 datatypes unless the ``use_nchar_for_unicode=True`` is passed to the dialect
418 when :func:`_sa.create_engine` is called.
419
420
421.. _oracledb_unicode_encoding_errors:
422
423Encoding Errors
424^^^^^^^^^^^^^^^
425
426For the unusual case that data in Oracle Database is present with a broken
427encoding, the dialect accepts a parameter ``encoding_errors`` which will be
428passed to Unicode decoding functions in order to affect how decoding errors are
429handled. The value is ultimately consumed by the Python `decode
430<https://docs.python.org/3/library/stdtypes.html#bytes.decode>`_ function, and
431is passed both via python-oracledb's ``encodingErrors`` parameter consumed by
432``Cursor.var()``, as well as SQLAlchemy's own decoding function, as the
433python-oracledb dialect makes use of both under different circumstances.
434
435.. versionadded:: 1.3.11
436
437
438.. _oracledb_setinputsizes:
439
440Fine grained control over python-oracledb data binding with setinputsizes
441-------------------------------------------------------------------------
442
443The python-oracle DBAPI has a deep and fundamental reliance upon the usage of
444the DBAPI ``setinputsizes()`` call. The purpose of this call is to establish
445the datatypes that are bound to a SQL statement for Python values being passed
446as parameters. While virtually no other DBAPI assigns any use to the
447``setinputsizes()`` call, the python-oracledb DBAPI relies upon it heavily in
448its interactions with the Oracle Database, and in some scenarios it is not
449possible for SQLAlchemy to know exactly how data should be bound, as some
450settings can cause profoundly different performance characteristics, while
451altering the type coercion behavior at the same time.
452
453Users of the oracledb dialect are **strongly encouraged** to read through
454python-oracledb's list of built-in datatype symbols at `Database Types
455<https://python-oracledb.readthedocs.io/en/latest/api_manual/module.html#database-types>`_
456Note that in some cases, significant performance degradation can occur when
457using these types vs. not.
458
459On the SQLAlchemy side, the :meth:`.DialectEvents.do_setinputsizes` event can
460be used both for runtime visibility (e.g. logging) of the setinputsizes step as
461well as to fully control how ``setinputsizes()`` is used on a per-statement
462basis.
463
464.. versionadded:: 1.2.9 Added :meth:`.DialectEvents.setinputsizes`
465
466
467Example 1 - logging all setinputsizes calls
468^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
469
470The following example illustrates how to log the intermediary values from a
471SQLAlchemy perspective before they are converted to the raw ``setinputsizes()``
472parameter dictionary. The keys of the dictionary are :class:`.BindParameter`
473objects which have a ``.key`` and a ``.type`` attribute::
474
475 from sqlalchemy import create_engine, event
476
477 engine = create_engine(
478 "oracle+oracledb://scott:tiger@localhost:1521?service_name=freepdb1"
479 )
480
481
482 @event.listens_for(engine, "do_setinputsizes")
483 def _log_setinputsizes(inputsizes, cursor, statement, parameters, context):
484 for bindparam, dbapitype in inputsizes.items():
485 log.info(
486 "Bound parameter name: %s SQLAlchemy type: %r DBAPI object: %s",
487 bindparam.key,
488 bindparam.type,
489 dbapitype,
490 )
491
492Example 2 - remove all bindings to CLOB
493^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
494
495For performance, fetching LOB datatypes from Oracle Database is set by default
496for the ``Text`` type within SQLAlchemy. This setting can be modified as
497follows::
498
499
500 from sqlalchemy import create_engine, event
501 from oracledb import CLOB
502
503 engine = create_engine(
504 "oracle+oracledb://scott:tiger@localhost:1521?service_name=freepdb1"
505 )
506
507
508 @event.listens_for(engine, "do_setinputsizes")
509 def _remove_clob(inputsizes, cursor, statement, parameters, context):
510 for bindparam, dbapitype in list(inputsizes.items()):
511 if dbapitype is CLOB:
512 del inputsizes[bindparam]
513
514.. _oracledb_lob:
515
516LOB Datatypes
517--------------
518
519LOB datatypes refer to the "large object" datatypes such as CLOB, NCLOB and
520BLOB. Oracle Database can efficiently return these datatypes as a single
521buffer. SQLAlchemy makes use of type handlers to do this by default.
522
523To disable the use of the type handlers and deliver LOB objects as classic
524buffered objects with a ``read()`` method, the parameter
525``auto_convert_lobs=False`` may be passed to :func:`_sa.create_engine`.
526
527.. _oracledb_returning:
528
529RETURNING Support
530-----------------
531
532The oracledb dialect implements RETURNING using OUT parameters. The dialect
533supports RETURNING fully.
534
535Two Phase Transaction Support
536-----------------------------
537
538Two phase transactions are fully supported with python-oracledb. (Thin mode
539requires python-oracledb 2.3). APIs for two phase transactions are provided at
540the Core level via :meth:`_engine.Connection.begin_twophase` and
541:paramref:`_orm.Session.twophase` for transparent ORM use.
542
543.. versionchanged:: 2.0.32 added support for two phase transactions
544
545.. _oracledb_numeric:
546
547Precision Numerics
548------------------
549
550SQLAlchemy's numeric types can handle receiving and returning values as Python
551``Decimal`` objects or float objects. When a :class:`.Numeric` object, or a
552subclass such as :class:`.Float`, :class:`_oracle.DOUBLE_PRECISION` etc. is in
553use, the :paramref:`.Numeric.asdecimal` flag determines if values should be
554coerced to ``Decimal`` upon return, or returned as float objects. To make
555matters more complicated under Oracle Database, the ``NUMBER`` type can also
556represent integer values if the "scale" is zero, so the Oracle
557Database-specific :class:`_oracle.NUMBER` type takes this into account as well.
558
559The oracledb dialect makes extensive use of connection- and cursor-level
560"outputtypehandler" callables in order to coerce numeric values as requested.
561These callables are specific to the specific flavor of :class:`.Numeric` in
562use, as well as if no SQLAlchemy typing objects are present. There are
563observed scenarios where Oracle Database may send incomplete or ambiguous
564information about the numeric types being returned, such as a query where the
565numeric types are buried under multiple levels of subquery. The type handlers
566do their best to make the right decision in all cases, deferring to the
567underlying python-oracledb DBAPI for all those cases where the driver can make
568the best decision.
569
570When no typing objects are present, as when executing plain SQL strings, a
571default "outputtypehandler" is present which will generally return numeric
572values which specify precision and scale as Python ``Decimal`` objects. To
573disable this coercion to decimal for performance reasons, pass the flag
574``coerce_to_decimal=False`` to :func:`_sa.create_engine`::
575
576 engine = create_engine(
577 "oracle+oracledb://scott:tiger@tnsalias", coerce_to_decimal=False
578 )
579
580The ``coerce_to_decimal`` flag only impacts the results of plain string
581SQL statements that are not otherwise associated with a :class:`.Numeric`
582SQLAlchemy type (or a subclass of such).
583
584.. versionchanged:: 1.2 The numeric handling system for the oracle dialects has
585 been reworked to take advantage of newer driver features as well as better
586 integration of outputtypehandlers.
587
588.. versionadded:: 2.0.0 added support for the python-oracledb driver.
589
590""" # noqa
591from __future__ import annotations
592
593import collections
594import re
595from typing import Any
596from typing import TYPE_CHECKING
597
598from . import cx_oracle as _cx_oracle
599from ... import exc
600from ... import pool
601from ...connectors.asyncio import AsyncAdapt_dbapi_connection
602from ...connectors.asyncio import AsyncAdapt_dbapi_cursor
603from ...connectors.asyncio import AsyncAdapt_dbapi_ss_cursor
604from ...connectors.asyncio import AsyncAdaptFallback_dbapi_connection
605from ...engine import default
606from ...util import asbool
607from ...util import await_fallback
608from ...util import await_only
609
610if TYPE_CHECKING:
611 from oracledb import AsyncConnection
612 from oracledb import AsyncCursor
613
614
615class OracleExecutionContext_oracledb(
616 _cx_oracle.OracleExecutionContext_cx_oracle
617):
618 pass
619
620
621class OracleDialect_oracledb(_cx_oracle.OracleDialect_cx_oracle):
622 supports_statement_cache = True
623 execution_ctx_cls = OracleExecutionContext_oracledb
624
625 driver = "oracledb"
626 _min_version = (1,)
627
628 def __init__(
629 self,
630 auto_convert_lobs=True,
631 coerce_to_decimal=True,
632 arraysize=None,
633 encoding_errors=None,
634 thick_mode=None,
635 **kwargs,
636 ):
637 super().__init__(
638 auto_convert_lobs,
639 coerce_to_decimal,
640 arraysize,
641 encoding_errors,
642 **kwargs,
643 )
644
645 if self.dbapi is not None and (
646 thick_mode or isinstance(thick_mode, dict)
647 ):
648 kw = thick_mode if isinstance(thick_mode, dict) else {}
649 self.dbapi.init_oracle_client(**kw)
650
651 @classmethod
652 def import_dbapi(cls):
653 import oracledb
654
655 return oracledb
656
657 @classmethod
658 def is_thin_mode(cls, connection):
659 return connection.connection.dbapi_connection.thin
660
661 @classmethod
662 def get_async_dialect_cls(cls, url):
663 return OracleDialectAsync_oracledb
664
665 def _load_version(self, dbapi_module):
666 version = (0, 0, 0)
667 if dbapi_module is not None:
668 m = re.match(r"(\d+)\.(\d+)(?:\.(\d+))?", dbapi_module.version)
669 if m:
670 version = tuple(
671 int(x) for x in m.group(1, 2, 3) if x is not None
672 )
673 self.oracledb_ver = version
674 if (
675 self.oracledb_ver > (0, 0, 0)
676 and self.oracledb_ver < self._min_version
677 ):
678 raise exc.InvalidRequestError(
679 f"oracledb version {self._min_version} and above are supported"
680 )
681
682 def do_begin_twophase(self, connection, xid):
683 conn_xis = connection.connection.xid(*xid)
684 connection.connection.tpc_begin(conn_xis)
685 connection.connection.info["oracledb_xid"] = conn_xis
686
687 def do_prepare_twophase(self, connection, xid):
688 should_commit = connection.connection.tpc_prepare()
689 connection.info["oracledb_should_commit"] = should_commit
690
691 def do_rollback_twophase(
692 self, connection, xid, is_prepared=True, recover=False
693 ):
694 if recover:
695 conn_xid = connection.connection.xid(*xid)
696 else:
697 conn_xid = None
698 connection.connection.tpc_rollback(conn_xid)
699
700 def do_commit_twophase(
701 self, connection, xid, is_prepared=True, recover=False
702 ):
703 conn_xid = None
704 if not is_prepared:
705 should_commit = connection.connection.tpc_prepare()
706 elif recover:
707 conn_xid = connection.connection.xid(*xid)
708 should_commit = True
709 else:
710 should_commit = connection.info["oracledb_should_commit"]
711 if should_commit:
712 connection.connection.tpc_commit(conn_xid)
713
714 def do_recover_twophase(self, connection):
715 return [
716 # oracledb seems to return bytes
717 (
718 fi,
719 gti.decode() if isinstance(gti, bytes) else gti,
720 bq.decode() if isinstance(bq, bytes) else bq,
721 )
722 for fi, gti, bq in connection.connection.tpc_recover()
723 ]
724
725 def _check_max_identifier_length(self, connection):
726 if self.oracledb_ver >= (2, 5):
727 max_len = connection.connection.max_identifier_length
728 if max_len is not None:
729 return max_len
730 return super()._check_max_identifier_length(connection)
731
732
733class AsyncAdapt_oracledb_cursor(AsyncAdapt_dbapi_cursor):
734 _cursor: AsyncCursor
735 _awaitable_cursor_close: bool = False
736
737 __slots__ = ()
738
739 @property
740 def outputtypehandler(self):
741 return self._cursor.outputtypehandler
742
743 @outputtypehandler.setter
744 def outputtypehandler(self, value):
745 self._cursor.outputtypehandler = value
746
747 def var(self, *args, **kwargs):
748 return self._cursor.var(*args, **kwargs)
749
750 def setinputsizes(self, *args: Any, **kwargs: Any) -> Any:
751 return self._cursor.setinputsizes(*args, **kwargs)
752
753 def _aenter_cursor(self, cursor: AsyncCursor) -> AsyncCursor:
754 try:
755 return cursor.__enter__()
756 except Exception as error:
757 self._adapt_connection._handle_exception(error)
758
759 async def _execute_async(self, operation, parameters):
760 # override to not use mutex, oracledb already has a mutex
761
762 if parameters is None:
763 result = await self._cursor.execute(operation)
764 else:
765 result = await self._cursor.execute(operation, parameters)
766
767 if self._cursor.description and not self.server_side:
768 self._rows = collections.deque(await self._cursor.fetchall())
769 return result
770
771 async def _executemany_async(
772 self,
773 operation,
774 seq_of_parameters,
775 ):
776 # override to not use mutex, oracledb already has a mutex
777 return await self._cursor.executemany(operation, seq_of_parameters)
778
779 def __enter__(self):
780 return self
781
782 def __exit__(self, type_: Any, value: Any, traceback: Any) -> None:
783 self.close()
784
785
786class AsyncAdapt_oracledb_ss_cursor(
787 AsyncAdapt_dbapi_ss_cursor, AsyncAdapt_oracledb_cursor
788):
789 __slots__ = ()
790
791 def close(self) -> None:
792 if self._cursor is not None:
793 self._cursor.close()
794 self._cursor = None # type: ignore
795
796
797class AsyncAdapt_oracledb_connection(AsyncAdapt_dbapi_connection):
798 _connection: AsyncConnection
799 __slots__ = ()
800
801 thin = True
802
803 _cursor_cls = AsyncAdapt_oracledb_cursor
804 _ss_cursor_cls = None
805
806 @property
807 def autocommit(self):
808 return self._connection.autocommit
809
810 @autocommit.setter
811 def autocommit(self, value):
812 self._connection.autocommit = value
813
814 @property
815 def outputtypehandler(self):
816 return self._connection.outputtypehandler
817
818 @outputtypehandler.setter
819 def outputtypehandler(self, value):
820 self._connection.outputtypehandler = value
821
822 @property
823 def version(self):
824 return self._connection.version
825
826 @property
827 def stmtcachesize(self):
828 return self._connection.stmtcachesize
829
830 @stmtcachesize.setter
831 def stmtcachesize(self, value):
832 self._connection.stmtcachesize = value
833
834 @property
835 def max_identifier_length(self):
836 return self._connection.max_identifier_length
837
838 def cursor(self):
839 return AsyncAdapt_oracledb_cursor(self)
840
841 def ss_cursor(self):
842 return AsyncAdapt_oracledb_ss_cursor(self)
843
844 def xid(self, *args: Any, **kwargs: Any) -> Any:
845 return self._connection.xid(*args, **kwargs)
846
847 def tpc_begin(self, *args: Any, **kwargs: Any) -> Any:
848 return self.await_(self._connection.tpc_begin(*args, **kwargs))
849
850 def tpc_commit(self, *args: Any, **kwargs: Any) -> Any:
851 return self.await_(self._connection.tpc_commit(*args, **kwargs))
852
853 def tpc_prepare(self, *args: Any, **kwargs: Any) -> Any:
854 return self.await_(self._connection.tpc_prepare(*args, **kwargs))
855
856 def tpc_recover(self, *args: Any, **kwargs: Any) -> Any:
857 return self.await_(self._connection.tpc_recover(*args, **kwargs))
858
859 def tpc_rollback(self, *args: Any, **kwargs: Any) -> Any:
860 return self.await_(self._connection.tpc_rollback(*args, **kwargs))
861
862
863class AsyncAdaptFallback_oracledb_connection(
864 AsyncAdaptFallback_dbapi_connection, AsyncAdapt_oracledb_connection
865):
866 __slots__ = ()
867
868
869class OracledbAdaptDBAPI:
870 def __init__(self, oracledb) -> None:
871 self.oracledb = oracledb
872
873 for k, v in self.oracledb.__dict__.items():
874 if k != "connect":
875 self.__dict__[k] = v
876
877 def connect(self, *arg, **kw):
878 async_fallback = kw.pop("async_fallback", False)
879 creator_fn = kw.pop("async_creator_fn", self.oracledb.connect_async)
880
881 if asbool(async_fallback):
882 return AsyncAdaptFallback_oracledb_connection(
883 self, await_fallback(creator_fn(*arg, **kw))
884 )
885
886 else:
887 return AsyncAdapt_oracledb_connection(
888 self, await_only(creator_fn(*arg, **kw))
889 )
890
891
892class OracleExecutionContextAsync_oracledb(OracleExecutionContext_oracledb):
893 # restore default create cursor
894 create_cursor = default.DefaultExecutionContext.create_cursor
895
896 def create_default_cursor(self):
897 # copy of OracleExecutionContext_cx_oracle.create_cursor
898 c = self._dbapi_connection.cursor()
899 if self.dialect.arraysize:
900 c.arraysize = self.dialect.arraysize
901
902 return c
903
904 def create_server_side_cursor(self):
905 c = self._dbapi_connection.ss_cursor()
906 if self.dialect.arraysize:
907 c.arraysize = self.dialect.arraysize
908
909 return c
910
911
912class OracleDialectAsync_oracledb(OracleDialect_oracledb):
913 is_async = True
914 supports_server_side_cursors = True
915 supports_statement_cache = True
916 execution_ctx_cls = OracleExecutionContextAsync_oracledb
917
918 _min_version = (2,)
919
920 # thick_mode mode is not supported by asyncio, oracledb will raise
921 @classmethod
922 def import_dbapi(cls):
923 import oracledb
924
925 return OracledbAdaptDBAPI(oracledb)
926
927 @classmethod
928 def get_pool_class(cls, url):
929 async_fallback = url.query.get("async_fallback", False)
930
931 if asbool(async_fallback):
932 return pool.FallbackAsyncAdaptedQueuePool
933 else:
934 return pool.AsyncAdaptedQueuePool
935
936 def get_driver_connection(self, connection):
937 return connection._connection
938
939
940dialect = OracleDialect_oracledb
941dialect_async = OracleDialectAsync_oracledb
942