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/oracle/oracledb.py
2# Copyright (C) 2005-2025 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""".. 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/
15Description
16-----------
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.
23Python-oracle is the renamed, updated cx_Oracle driver. Oracle is no longer
24doing any releases in the cx_Oracle namespace.
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:
30* calling :func:`_sa.create_engine` with ``oracle+oracledb://...`` will
31 automatically select the sync version::
33 from sqlalchemy import create_engine
35 sync_engine = create_engine(
36 "oracle+oracledb://scott:tiger@localhost?service_name=FREEPDB1"
37 )
39* calling :func:`_asyncio.create_async_engine` with ``oracle+oracledb://...``
40 will automatically select the async version::
42 from sqlalchemy.ext.asyncio import create_async_engine
44 asyncio_engine = create_async_engine(
45 "oracle+oracledb://scott:tiger@localhost?service_name=FREEPDB1"
46 )
48 The asyncio version of the dialect may also be specified explicitly using the
49 ``oracledb_async`` suffix::
51 from sqlalchemy.ext.asyncio import create_async_engine
53 asyncio_engine = create_async_engine(
54 "oracle+oracledb_async://scott:tiger@localhost?service_name=FREEPDB1"
55 )
57.. versionadded:: 2.0.25 added support for the async version of oracledb.
59Thick mode support
60------------------
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.
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::
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 )
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.
86.. seealso::
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>`_
91Connecting to Oracle Database
92-----------------------------
94python-oracledb provides several methods of indicating the target database.
95The dialect translates from a series of different URL forms.
97Given the hostname, port and service name of the target database, you can
98connect in SQLAlchemy using the ``service_name`` query string parameter::
100 engine = create_engine(
101 "oracle+oracledb://scott:tiger@hostname:port?service_name=myservice"
102 )
104Connecting with Easy Connect strings
105^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
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>`_.
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 establishd in 30 seconds, and also setting
116a keep-alive time of 60 seconds to stop idle network connections from being
117terminated by a firewall::
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 )
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>`_.
133The general syntax is similar to:
135.. sourcecode:: text
137 [[protocol:]//]host[:port][/[service_name]][?parameter_name=value{¶meter_name=value}]
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::
143 engine = create_engine("oracle+oracledb://scott:tiger@hostname:port/sid")
145Easy Connect syntax does not support SIDs. It uses services names, which are
146the preferred choice for connecting to Oracle Database.
148Passing python-oracledb connect arguments
149^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
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::
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 )
166Connecting with tnsnames.ora TNS aliases
167^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
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:
176.. sourcecode:: text
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 )
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``::
191 engine = create_engine("oracle+oracledb://scott:tiger@myalias")
193Connecting to Oracle Autonomous Database
194^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
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.
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::
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 )
220Thin mode users of mTLS should pass the appropriate directories and PEM wallet
221password when creating the engine, similar to::
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 )
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.
239Connection Pooling
240------------------
242Applications with multiple concurrent users should use connection pooling. A
243minimal sized connection pool is also beneficial for long-running, single-user
244applications that do not frequently use a connection.
246The python-oracledb driver provides its own connection pool implementation that
247may be used in place of SQLAlchemy's pooling functionality. The driver pool
248gives support for high availability features such as dead connection detection,
249connection draining for planned database downtime, support for Oracle
250Application Continuity and Transparent Application Continuity, and gives
251support for `Database Resident Connection Pooling (DRCP)
252<https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html#database-resident-connection-pooling-drcp>`_.
254To take advantage of python-oracledb's pool, use the
255:paramref:`_sa.create_engine.creator` parameter to provide a function that
256returns a new connection, along with setting
257:paramref:`_sa.create_engine.pool_class` to ``NullPool`` to disable
258SQLAlchemy's pooling::
260 import oracledb
261 from sqlalchemy import create_engine
262 from sqlalchemy import text
263 from sqlalchemy.pool import NullPool
265 # Uncomment to use the optional python-oracledb Thick mode.
266 # Review the python-oracledb doc for the appropriate parameters
267 # oracledb.init_oracle_client(<your parameters>)
269 pool = oracledb.create_pool(
270 user="scott",
271 password="tiger",
272 dsn="localhost:1521/freepdb1",
273 min=1,
274 max=4,
275 increment=1,
276 )
277 engine = create_engine(
278 "oracle+oracledb://", creator=pool.acquire, poolclass=NullPool
279 )
281The above engine may then be used normally. Internally, python-oracledb handles
282connection pooling::
284 with engine.connect() as conn:
285 print(conn.scalar(text("select 1 from dual")))
287Refer to the python-oracledb documentation for `oracledb.create_pool()
288<https://python-oracledb.readthedocs.io/en/latest/api_manual/module.html#oracledb.create_pool>`_
289for the arguments that can be used when creating a connection pool.
291.. _drcp:
293Using Oracle Database Resident Connection Pooling (DRCP)
294--------------------------------------------------------
296When using Oracle Database's Database Resident Connection Pooling (DRCP), the
297best practice is to specify a connection class and "purity". Refer to the
298`python-oracledb documentation on DRCP
299<https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html#database-resident-connection-pooling-drcp>`_.
300For example::
302 import oracledb
303 from sqlalchemy import create_engine
304 from sqlalchemy import text
305 from sqlalchemy.pool import NullPool
307 # Uncomment to use the optional python-oracledb Thick mode.
308 # Review the python-oracledb doc for the appropriate parameters
309 # oracledb.init_oracle_client(<your parameters>)
311 pool = oracledb.create_pool(
312 user="scott",
313 password="tiger",
314 dsn="localhost:1521/freepdb1",
315 min=1,
316 max=4,
317 increment=1,
318 cclass="MYCLASS",
319 purity=oracledb.PURITY_SELF,
320 )
321 engine = create_engine(
322 "oracle+oracledb://", creator=pool.acquire, poolclass=NullPool
323 )
325The above engine may then be used normally where python-oracledb handles
326application connection pooling and Oracle Database additionally uses DRCP::
328 with engine.connect() as conn:
329 print(conn.scalar(text("select 1 from dual")))
331If you wish to use different connection classes or purities for different
332connections, then wrap ``pool.acquire()``::
334 import oracledb
335 from sqlalchemy import create_engine
336 from sqlalchemy import text
337 from sqlalchemy.pool import NullPool
339 # Uncomment to use python-oracledb Thick mode.
340 # Review the python-oracledb doc for the appropriate parameters
341 # oracledb.init_oracle_client(<your parameters>)
343 pool = oracledb.create_pool(
344 user="scott",
345 password="tiger",
346 dsn="localhost:1521/freepdb1",
347 min=1,
348 max=4,
349 increment=1,
350 cclass="MYCLASS",
351 purity=oracledb.PURITY_SELF,
352 )
355 def creator():
356 return pool.acquire(cclass="MYOTHERCLASS", purity=oracledb.PURITY_NEW)
359 engine = create_engine(
360 "oracle+oracledb://", creator=creator, poolclass=NullPool
361 )
363Engine Options consumed by the SQLAlchemy oracledb dialect outside of the driver
364--------------------------------------------------------------------------------
366There are also options that are consumed by the SQLAlchemy oracledb dialect
367itself. These options are always passed directly to :func:`_sa.create_engine`,
368such as::
370 e = create_engine("oracle+oracledb://user:pass@tnsalias", arraysize=500)
372The parameters accepted by the oracledb dialect are as follows:
374* ``arraysize`` - set the driver cursor.arraysize value. It defaults to
375 ``None``, indicating that the driver default value of 100 should be used.
376 This setting controls how many rows are buffered when fetching rows, and can
377 have a significant effect on performance if increased for queries that return
378 large numbers of rows.
380 .. versionchanged:: 2.0.26 - changed the default value from 50 to None,
381 to use the default value of the driver itself.
383* ``auto_convert_lobs`` - defaults to True; See :ref:`oracledb_lob`.
385* ``coerce_to_decimal`` - see :ref:`oracledb_numeric` for detail.
387* ``encoding_errors`` - see :ref:`oracledb_unicode_encoding_errors` for detail.
389.. _oracledb_unicode:
391Unicode
392-------
394As is the case for all DBAPIs under Python 3, all strings are inherently
395Unicode strings.
397Ensuring the Correct Client Encoding
398^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
400In python-oracledb, the encoding used for all character data is "UTF-8".
402Unicode-specific Column datatypes
403^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
405The Core expression language handles unicode data by use of the
406:class:`.Unicode` and :class:`.UnicodeText` datatypes. These types correspond
407to the VARCHAR2 and CLOB Oracle Database datatypes by default. When using
408these datatypes with Unicode data, it is expected that the database is
409configured with a Unicode-aware character set so that the VARCHAR2 and CLOB
410datatypes can accommodate the data.
412In the case that Oracle Database is not configured with a Unicode character
413set, the two options are to use the :class:`_types.NCHAR` and
414:class:`_oracle.NCLOB` datatypes explicitly, or to pass the flag
415``use_nchar_for_unicode=True`` to :func:`_sa.create_engine`, which will cause
416the SQLAlchemy dialect to use NCHAR/NCLOB for the :class:`.Unicode` /
417:class:`.UnicodeText` datatypes instead of VARCHAR/CLOB.
419.. versionchanged:: 1.3 The :class:`.Unicode` and :class:`.UnicodeText`
420 datatypes now correspond to the ``VARCHAR2`` and ``CLOB`` Oracle Database
421 datatypes unless the ``use_nchar_for_unicode=True`` is passed to the dialect
422 when :func:`_sa.create_engine` is called.
425.. _oracledb_unicode_encoding_errors:
427Encoding Errors
428^^^^^^^^^^^^^^^
430For the unusual case that data in Oracle Database is present with a broken
431encoding, the dialect accepts a parameter ``encoding_errors`` which will be
432passed to Unicode decoding functions in order to affect how decoding errors are
433handled. The value is ultimately consumed by the Python `decode
434<https://docs.python.org/3/library/stdtypes.html#bytes.decode>`_ function, and
435is passed both via python-oracledb's ``encodingErrors`` parameter consumed by
436``Cursor.var()``, as well as SQLAlchemy's own decoding function, as the
437python-oracledb dialect makes use of both under different circumstances.
439.. versionadded:: 1.3.11
442.. _oracledb_setinputsizes:
444Fine grained control over python-oracledb data binding with setinputsizes
445-------------------------------------------------------------------------
447The python-oracle DBAPI has a deep and fundamental reliance upon the usage of
448the DBAPI ``setinputsizes()`` call. The purpose of this call is to establish
449the datatypes that are bound to a SQL statement for Python values being passed
450as parameters. While virtually no other DBAPI assigns any use to the
451``setinputsizes()`` call, the python-oracledb DBAPI relies upon it heavily in
452its interactions with the Oracle Database, and in some scenarios it is not
453possible for SQLAlchemy to know exactly how data should be bound, as some
454settings can cause profoundly different performance characteristics, while
455altering the type coercion behavior at the same time.
457Users of the oracledb dialect are **strongly encouraged** to read through
458python-oracledb's list of built-in datatype symbols at `Database Types
459<https://python-oracledb.readthedocs.io/en/latest/api_manual/module.html#database-types>`_
460Note that in some cases, significant performance degradation can occur when
461using these types vs. not.
463On the SQLAlchemy side, the :meth:`.DialectEvents.do_setinputsizes` event can
464be used both for runtime visibility (e.g. logging) of the setinputsizes step as
465well as to fully control how ``setinputsizes()`` is used on a per-statement
466basis.
468.. versionadded:: 1.2.9 Added :meth:`.DialectEvents.setinputsizes`
471Example 1 - logging all setinputsizes calls
472^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
474The following example illustrates how to log the intermediary values from a
475SQLAlchemy perspective before they are converted to the raw ``setinputsizes()``
476parameter dictionary. The keys of the dictionary are :class:`.BindParameter`
477objects which have a ``.key`` and a ``.type`` attribute::
479 from sqlalchemy import create_engine, event
481 engine = create_engine(
482 "oracle+oracledb://scott:tiger@localhost:1521?service_name=freepdb1"
483 )
486 @event.listens_for(engine, "do_setinputsizes")
487 def _log_setinputsizes(inputsizes, cursor, statement, parameters, context):
488 for bindparam, dbapitype in inputsizes.items():
489 log.info(
490 "Bound parameter name: %s SQLAlchemy type: %r DBAPI object: %s",
491 bindparam.key,
492 bindparam.type,
493 dbapitype,
494 )
496Example 2 - remove all bindings to CLOB
497^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
499For performance, fetching LOB datatypes from Oracle Database is set by default
500for the ``Text`` type within SQLAlchemy. This setting can be modified as
501follows::
504 from sqlalchemy import create_engine, event
505 from oracledb import CLOB
507 engine = create_engine(
508 "oracle+oracledb://scott:tiger@localhost:1521?service_name=freepdb1"
509 )
512 @event.listens_for(engine, "do_setinputsizes")
513 def _remove_clob(inputsizes, cursor, statement, parameters, context):
514 for bindparam, dbapitype in list(inputsizes.items()):
515 if dbapitype is CLOB:
516 del inputsizes[bindparam]
518.. _oracledb_lob:
520LOB Datatypes
521--------------
523LOB datatypes refer to the "large object" datatypes such as CLOB, NCLOB and
524BLOB. Oracle Database can efficiently return these datatypes as a single
525buffer. SQLAlchemy makes use of type handlers to do this by default.
527To disable the use of the type handlers and deliver LOB objects as classic
528buffered objects with a ``read()`` method, the parameter
529``auto_convert_lobs=False`` may be passed to :func:`_sa.create_engine`.
531.. _oracledb_returning:
533RETURNING Support
534-----------------
536The oracledb dialect implements RETURNING using OUT parameters. The dialect
537supports RETURNING fully.
539Two Phase Transaction Support
540-----------------------------
542Two phase transactions are fully supported with python-oracledb. (Thin mode
543requires python-oracledb 2.3). APIs for two phase transactions are provided at
544the Core level via :meth:`_engine.Connection.begin_twophase` and
545:paramref:`_orm.Session.twophase` for transparent ORM use.
547.. versionchanged:: 2.0.32 added support for two phase transactions
549.. _oracledb_numeric:
551Precision Numerics
552------------------
554SQLAlchemy's numeric types can handle receiving and returning values as Python
555``Decimal`` objects or float objects. When a :class:`.Numeric` object, or a
556subclass such as :class:`.Float`, :class:`_oracle.DOUBLE_PRECISION` etc. is in
557use, the :paramref:`.Numeric.asdecimal` flag determines if values should be
558coerced to ``Decimal`` upon return, or returned as float objects. To make
559matters more complicated under Oracle Database, the ``NUMBER`` type can also
560represent integer values if the "scale" is zero, so the Oracle
561Database-specific :class:`_oracle.NUMBER` type takes this into account as well.
563The oracledb dialect makes extensive use of connection- and cursor-level
564"outputtypehandler" callables in order to coerce numeric values as requested.
565These callables are specific to the specific flavor of :class:`.Numeric` in
566use, as well as if no SQLAlchemy typing objects are present. There are
567observed scenarios where Oracle Database may send incomplete or ambiguous
568information about the numeric types being returned, such as a query where the
569numeric types are buried under multiple levels of subquery. The type handlers
570do their best to make the right decision in all cases, deferring to the
571underlying python-oracledb DBAPI for all those cases where the driver can make
572the best decision.
574When no typing objects are present, as when executing plain SQL strings, a
575default "outputtypehandler" is present which will generally return numeric
576values which specify precision and scale as Python ``Decimal`` objects. To
577disable this coercion to decimal for performance reasons, pass the flag
578``coerce_to_decimal=False`` to :func:`_sa.create_engine`::
580 engine = create_engine(
581 "oracle+oracledb://scott:tiger@tnsalias", coerce_to_decimal=False
582 )
584The ``coerce_to_decimal`` flag only impacts the results of plain string
585SQL statements that are not otherwise associated with a :class:`.Numeric`
586SQLAlchemy type (or a subclass of such).
588.. versionchanged:: 1.2 The numeric handling system for the oracle dialects has
589 been reworked to take advantage of newer driver features as well as better
590 integration of outputtypehandlers.
592.. versionadded:: 2.0.0 added support for the python-oracledb driver.
594""" # noqa
595from __future__ import annotations
597import collections
598import re
599from typing import Any
600from typing import TYPE_CHECKING
602from . import cx_oracle as _cx_oracle
603from ... import exc
604from ... import pool
605from ...connectors.asyncio import AsyncAdapt_dbapi_connection
606from ...connectors.asyncio import AsyncAdapt_dbapi_cursor
607from ...connectors.asyncio import AsyncAdapt_dbapi_ss_cursor
608from ...connectors.asyncio import AsyncAdaptFallback_dbapi_connection
609from ...engine import default
610from ...util import asbool
611from ...util import await_fallback
612from ...util import await_only
614if TYPE_CHECKING:
615 from oracledb import AsyncConnection
616 from oracledb import AsyncCursor
619class OracleExecutionContext_oracledb(
620 _cx_oracle.OracleExecutionContext_cx_oracle
621):
622 pass
625class OracleDialect_oracledb(_cx_oracle.OracleDialect_cx_oracle):
626 supports_statement_cache = True
627 execution_ctx_cls = OracleExecutionContext_oracledb
629 driver = "oracledb"
630 _min_version = (1,)
632 def __init__(
633 self,
634 auto_convert_lobs=True,
635 coerce_to_decimal=True,
636 arraysize=None,
637 encoding_errors=None,
638 thick_mode=None,
639 **kwargs,
640 ):
641 super().__init__(
642 auto_convert_lobs,
643 coerce_to_decimal,
644 arraysize,
645 encoding_errors,
646 **kwargs,
647 )
649 if self.dbapi is not None and (
650 thick_mode or isinstance(thick_mode, dict)
651 ):
652 kw = thick_mode if isinstance(thick_mode, dict) else {}
653 self.dbapi.init_oracle_client(**kw)
655 @classmethod
656 def import_dbapi(cls):
657 import oracledb
659 return oracledb
661 @classmethod
662 def is_thin_mode(cls, connection):
663 return connection.connection.dbapi_connection.thin
665 @classmethod
666 def get_async_dialect_cls(cls, url):
667 return OracleDialectAsync_oracledb
669 def _load_version(self, dbapi_module):
670 version = (0, 0, 0)
671 if dbapi_module is not None:
672 m = re.match(r"(\d+)\.(\d+)(?:\.(\d+))?", dbapi_module.version)
673 if m:
674 version = tuple(
675 int(x) for x in m.group(1, 2, 3) if x is not None
676 )
677 self.oracledb_ver = version
678 if (
679 self.oracledb_ver > (0, 0, 0)
680 and self.oracledb_ver < self._min_version
681 ):
682 raise exc.InvalidRequestError(
683 f"oracledb version {self._min_version} and above are supported"
684 )
686 def do_begin_twophase(self, connection, xid):
687 conn_xis = connection.connection.xid(*xid)
688 connection.connection.tpc_begin(conn_xis)
689 connection.connection.info["oracledb_xid"] = conn_xis
691 def do_prepare_twophase(self, connection, xid):
692 should_commit = connection.connection.tpc_prepare()
693 connection.info["oracledb_should_commit"] = should_commit
695 def do_rollback_twophase(
696 self, connection, xid, is_prepared=True, recover=False
697 ):
698 if recover:
699 conn_xid = connection.connection.xid(*xid)
700 else:
701 conn_xid = None
702 connection.connection.tpc_rollback(conn_xid)
704 def do_commit_twophase(
705 self, connection, xid, is_prepared=True, recover=False
706 ):
707 conn_xid = None
708 if not is_prepared:
709 should_commit = connection.connection.tpc_prepare()
710 elif recover:
711 conn_xid = connection.connection.xid(*xid)
712 should_commit = True
713 else:
714 should_commit = connection.info["oracledb_should_commit"]
715 if should_commit:
716 connection.connection.tpc_commit(conn_xid)
718 def do_recover_twophase(self, connection):
719 return [
720 # oracledb seems to return bytes
721 (
722 fi,
723 gti.decode() if isinstance(gti, bytes) else gti,
724 bq.decode() if isinstance(bq, bytes) else bq,
725 )
726 for fi, gti, bq in connection.connection.tpc_recover()
727 ]
729 def _check_max_identifier_length(self, connection):
730 if self.oracledb_ver >= (2, 5):
731 max_len = connection.connection.max_identifier_length
732 if max_len is not None:
733 return max_len
734 return super()._check_max_identifier_length(connection)
737class AsyncAdapt_oracledb_cursor(AsyncAdapt_dbapi_cursor):
738 _cursor: AsyncCursor
739 _awaitable_cursor_close: bool = False
741 __slots__ = ()
743 @property
744 def outputtypehandler(self):
745 return self._cursor.outputtypehandler
747 @outputtypehandler.setter
748 def outputtypehandler(self, value):
749 self._cursor.outputtypehandler = value
751 def var(self, *args, **kwargs):
752 return self._cursor.var(*args, **kwargs)
754 def setinputsizes(self, *args: Any, **kwargs: Any) -> Any:
755 return self._cursor.setinputsizes(*args, **kwargs)
757 def _aenter_cursor(self, cursor: AsyncCursor) -> AsyncCursor:
758 try:
759 return cursor.__enter__()
760 except Exception as error:
761 self._adapt_connection._handle_exception(error)
763 async def _execute_async(self, operation, parameters):
764 # override to not use mutex, oracledb already has a mutex
766 if parameters is None:
767 result = await self._cursor.execute(operation)
768 else:
769 result = await self._cursor.execute(operation, parameters)
771 if self._cursor.description and not self.server_side:
772 self._rows = collections.deque(await self._cursor.fetchall())
773 return result
775 async def _executemany_async(
776 self,
777 operation,
778 seq_of_parameters,
779 ):
780 # override to not use mutex, oracledb already has a mutex
781 return await self._cursor.executemany(operation, seq_of_parameters)
783 def __enter__(self):
784 return self
786 def __exit__(self, type_: Any, value: Any, traceback: Any) -> None:
787 self.close()
790class AsyncAdapt_oracledb_ss_cursor(
791 AsyncAdapt_dbapi_ss_cursor, AsyncAdapt_oracledb_cursor
792):
793 __slots__ = ()
795 def close(self) -> None:
796 if self._cursor is not None:
797 self._cursor.close()
798 self._cursor = None # type: ignore
801class AsyncAdapt_oracledb_connection(AsyncAdapt_dbapi_connection):
802 _connection: AsyncConnection
803 __slots__ = ()
805 thin = True
807 _cursor_cls = AsyncAdapt_oracledb_cursor
808 _ss_cursor_cls = None
810 @property
811 def autocommit(self):
812 return self._connection.autocommit
814 @autocommit.setter
815 def autocommit(self, value):
816 self._connection.autocommit = value
818 @property
819 def outputtypehandler(self):
820 return self._connection.outputtypehandler
822 @outputtypehandler.setter
823 def outputtypehandler(self, value):
824 self._connection.outputtypehandler = value
826 @property
827 def version(self):
828 return self._connection.version
830 @property
831 def stmtcachesize(self):
832 return self._connection.stmtcachesize
834 @stmtcachesize.setter
835 def stmtcachesize(self, value):
836 self._connection.stmtcachesize = value
838 @property
839 def max_identifier_length(self):
840 return self._connection.max_identifier_length
842 def cursor(self):
843 return AsyncAdapt_oracledb_cursor(self)
845 def ss_cursor(self):
846 return AsyncAdapt_oracledb_ss_cursor(self)
848 def xid(self, *args: Any, **kwargs: Any) -> Any:
849 return self._connection.xid(*args, **kwargs)
851 def tpc_begin(self, *args: Any, **kwargs: Any) -> Any:
852 return self.await_(self._connection.tpc_begin(*args, **kwargs))
854 def tpc_commit(self, *args: Any, **kwargs: Any) -> Any:
855 return self.await_(self._connection.tpc_commit(*args, **kwargs))
857 def tpc_prepare(self, *args: Any, **kwargs: Any) -> Any:
858 return self.await_(self._connection.tpc_prepare(*args, **kwargs))
860 def tpc_recover(self, *args: Any, **kwargs: Any) -> Any:
861 return self.await_(self._connection.tpc_recover(*args, **kwargs))
863 def tpc_rollback(self, *args: Any, **kwargs: Any) -> Any:
864 return self.await_(self._connection.tpc_rollback(*args, **kwargs))
867class AsyncAdaptFallback_oracledb_connection(
868 AsyncAdaptFallback_dbapi_connection, AsyncAdapt_oracledb_connection
869):
870 __slots__ = ()
873class OracledbAdaptDBAPI:
874 def __init__(self, oracledb) -> None:
875 self.oracledb = oracledb
877 for k, v in self.oracledb.__dict__.items():
878 if k != "connect":
879 self.__dict__[k] = v
881 def connect(self, *arg, **kw):
882 async_fallback = kw.pop("async_fallback", False)
883 creator_fn = kw.pop("async_creator_fn", self.oracledb.connect_async)
885 if asbool(async_fallback):
886 return AsyncAdaptFallback_oracledb_connection(
887 self, await_fallback(creator_fn(*arg, **kw))
888 )
890 else:
891 return AsyncAdapt_oracledb_connection(
892 self, await_only(creator_fn(*arg, **kw))
893 )
896class OracleExecutionContextAsync_oracledb(OracleExecutionContext_oracledb):
897 # restore default create cursor
898 create_cursor = default.DefaultExecutionContext.create_cursor
900 def create_default_cursor(self):
901 # copy of OracleExecutionContext_cx_oracle.create_cursor
902 c = self._dbapi_connection.cursor()
903 if self.dialect.arraysize:
904 c.arraysize = self.dialect.arraysize
906 return c
908 def create_server_side_cursor(self):
909 c = self._dbapi_connection.ss_cursor()
910 if self.dialect.arraysize:
911 c.arraysize = self.dialect.arraysize
913 return c
916class OracleDialectAsync_oracledb(OracleDialect_oracledb):
917 is_async = True
918 supports_server_side_cursors = True
919 supports_statement_cache = True
920 execution_ctx_cls = OracleExecutionContextAsync_oracledb
922 _min_version = (2,)
924 # thick_mode mode is not supported by asyncio, oracledb will raise
925 @classmethod
926 def import_dbapi(cls):
927 import oracledb
929 return OracledbAdaptDBAPI(oracledb)
931 @classmethod
932 def get_pool_class(cls, url):
933 async_fallback = url.query.get("async_fallback", False)
935 if asbool(async_fallback):
936 return pool.FallbackAsyncAdaptedQueuePool
937 else:
938 return pool.AsyncAdaptedQueuePool
940 def get_driver_connection(self, connection):
941 return connection._connection
944dialect = OracleDialect_oracledb
945dialect_async = OracleDialectAsync_oracledb