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-2026 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7# mypy: ignore-errors
9r""".. 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 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::
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.
239Using python-oracledb Connection Pooling
240----------------------------------------
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>`_.
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::
256 import oracledb
257 from sqlalchemy import create_engine
258 from sqlalchemy import text
259 from sqlalchemy.pool import NullPool
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>)
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 )
277The above engine may then be used normally. Internally, python-oracledb handles
278connection pooling::
280 with engine.connect() as conn:
281 print(conn.scalar(text("select 1 from dual")))
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.
287.. _drcp:
289Using Oracle Database Resident Connection Pooling (DRCP)
290--------------------------------------------------------
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::
298 import oracledb
299 from sqlalchemy import create_engine
300 from sqlalchemy import text
301 from sqlalchemy.pool import NullPool
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>)
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 )
321The above engine may then be used normally where python-oracledb handles
322application connection pooling and Oracle Database additionally uses DRCP::
324 with engine.connect() as conn:
325 print(conn.scalar(text("select 1 from dual")))
327If you wish to use different connection classes or purities for different
328connections, then wrap ``pool.acquire()``::
330 import oracledb
331 from sqlalchemy import create_engine
332 from sqlalchemy import text
333 from sqlalchemy.pool import NullPool
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>)
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 )
351 def creator():
352 return pool.acquire(cclass="MYOTHERCLASS", purity=oracledb.PURITY_NEW)
355 engine = create_engine(
356 "oracle+oracledb://", creator=creator, poolclass=NullPool
357 )
359Engine Options consumed by the SQLAlchemy oracledb dialect outside of the driver
360--------------------------------------------------------------------------------
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::
366 e = create_engine("oracle+oracledb://user:pass@tnsalias", arraysize=500)
368The parameters accepted by the oracledb dialect are as follows:
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.
376 .. versionchanged:: 2.0.26 - changed the default value from 50 to None,
377 to use the default value of the driver itself.
379* ``auto_convert_lobs`` - defaults to True; See :ref:`oracledb_lob`.
381* ``coerce_to_decimal`` - see :ref:`oracledb_numeric` for detail.
383* ``encoding_errors`` - see :ref:`oracledb_unicode_encoding_errors` for detail.
385.. _oracledb_unicode:
387Unicode
388-------
390As is the case for all DBAPIs under Python 3, all strings are inherently
391Unicode strings.
393Ensuring the Correct Client Encoding
394^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
396In python-oracledb, the encoding used for all character data is "UTF-8".
398Unicode-specific Column datatypes
399^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
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.
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.
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.
421.. _oracledb_unicode_encoding_errors:
423Encoding Errors
424^^^^^^^^^^^^^^^
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.
435.. versionadded:: 1.3.11
438.. _oracledb_setinputsizes:
440Fine grained control over python-oracledb data binding with setinputsizes
441-------------------------------------------------------------------------
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.
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.
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.
464.. versionadded:: 1.2.9 Added :meth:`.DialectEvents.setinputsizes`
467Example 1 - logging all setinputsizes calls
468^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
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::
475 from sqlalchemy import create_engine, event
477 engine = create_engine(
478 "oracle+oracledb://scott:tiger@localhost:1521?service_name=freepdb1"
479 )
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 )
492Example 2 - remove all bindings to CLOB
493^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
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::
500 from sqlalchemy import create_engine, event
501 from oracledb import CLOB
503 engine = create_engine(
504 "oracle+oracledb://scott:tiger@localhost:1521?service_name=freepdb1"
505 )
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]
514.. _oracledb_lob:
516LOB Datatypes
517--------------
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.
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`.
527.. _oracledb_returning:
529RETURNING Support
530-----------------
532The oracledb dialect implements RETURNING using OUT parameters. The dialect
533supports RETURNING fully.
535Two Phase Transaction Support
536-----------------------------
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.
543.. versionchanged:: 2.0.32 added support for two phase transactions
545.. _oracledb_numeric:
547Precision Numerics
548------------------
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.
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.
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`::
576 engine = create_engine(
577 "oracle+oracledb://scott:tiger@tnsalias", coerce_to_decimal=False
578 )
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).
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.
588.. versionadded:: 2.0.0 added support for the python-oracledb driver.
590""" # noqa
592from __future__ import annotations
594import collections
595import re
596from typing import Any
597from typing import TYPE_CHECKING
599from . import cx_oracle as _cx_oracle
600from ... import exc
601from ... import pool
602from ...connectors.asyncio import AsyncAdapt_dbapi_connection
603from ...connectors.asyncio import AsyncAdapt_dbapi_cursor
604from ...connectors.asyncio import AsyncAdapt_dbapi_ss_cursor
605from ...connectors.asyncio import AsyncAdaptFallback_dbapi_connection
606from ...engine import default
607from ...util import asbool
608from ...util import await_fallback
609from ...util import await_only
611if TYPE_CHECKING:
612 from oracledb import AsyncConnection
613 from oracledb import AsyncCursor
616class OracleExecutionContext_oracledb(
617 _cx_oracle.OracleExecutionContext_cx_oracle
618):
619 pass
622class OracleDialect_oracledb(_cx_oracle.OracleDialect_cx_oracle):
623 supports_statement_cache = True
624 execution_ctx_cls = OracleExecutionContext_oracledb
626 driver = "oracledb"
627 _min_version = (1,)
629 def __init__(
630 self,
631 auto_convert_lobs=True,
632 coerce_to_decimal=True,
633 arraysize=None,
634 encoding_errors=None,
635 thick_mode=None,
636 **kwargs,
637 ):
638 super().__init__(
639 auto_convert_lobs,
640 coerce_to_decimal,
641 arraysize,
642 encoding_errors,
643 **kwargs,
644 )
646 if self.dbapi is not None and (
647 thick_mode or isinstance(thick_mode, dict)
648 ):
649 kw = thick_mode if isinstance(thick_mode, dict) else {}
650 self.dbapi.init_oracle_client(**kw)
652 @classmethod
653 def import_dbapi(cls):
654 import oracledb
656 return oracledb
658 @classmethod
659 def is_thin_mode(cls, connection):
660 return connection.connection.dbapi_connection.thin
662 @classmethod
663 def get_async_dialect_cls(cls, url):
664 return OracleDialectAsync_oracledb
666 def _load_version(self, dbapi_module):
667 version = (0, 0, 0)
668 if dbapi_module is not None:
669 m = re.match(r"(\d+)\.(\d+)(?:\.(\d+))?", dbapi_module.version)
670 if m:
671 version = tuple(
672 int(x) for x in m.group(1, 2, 3) if x is not None
673 )
674 self.oracledb_ver = version
675 if (
676 self.oracledb_ver > (0, 0, 0)
677 and self.oracledb_ver < self._min_version
678 ):
679 raise exc.InvalidRequestError(
680 f"oracledb version {self._min_version} and above are supported"
681 )
683 def do_begin_twophase(self, connection, xid):
684 conn_xis = connection.connection.xid(*xid)
685 connection.connection.tpc_begin(conn_xis)
686 connection.connection.info["oracledb_xid"] = conn_xis
688 def do_prepare_twophase(self, connection, xid):
689 should_commit = connection.connection.tpc_prepare()
690 connection.info["oracledb_should_commit"] = should_commit
692 def do_rollback_twophase(
693 self, connection, xid, is_prepared=True, recover=False
694 ):
695 if recover:
696 conn_xid = connection.connection.xid(*xid)
697 else:
698 conn_xid = None
699 connection.connection.tpc_rollback(conn_xid)
701 def do_commit_twophase(
702 self, connection, xid, is_prepared=True, recover=False
703 ):
704 conn_xid = None
705 if not is_prepared:
706 should_commit = connection.connection.tpc_prepare()
707 elif recover:
708 conn_xid = connection.connection.xid(*xid)
709 should_commit = True
710 else:
711 should_commit = connection.info["oracledb_should_commit"]
712 if should_commit:
713 connection.connection.tpc_commit(conn_xid)
715 def do_recover_twophase(self, connection):
716 return [
717 # oracledb seems to return bytes
718 (
719 fi,
720 gti.decode() if isinstance(gti, bytes) else gti,
721 bq.decode() if isinstance(bq, bytes) else bq,
722 )
723 for fi, gti, bq in connection.connection.tpc_recover()
724 ]
726 def _check_max_identifier_length(self, connection):
727 if self.oracledb_ver >= (2, 5):
728 max_len = connection.connection.max_identifier_length
729 if max_len is not None:
730 return max_len
731 return super()._check_max_identifier_length(connection)
734class AsyncAdapt_oracledb_cursor(AsyncAdapt_dbapi_cursor):
735 _cursor: AsyncCursor
736 _awaitable_cursor_close: bool = False
738 __slots__ = ()
740 @property
741 def outputtypehandler(self):
742 return self._cursor.outputtypehandler
744 @outputtypehandler.setter
745 def outputtypehandler(self, value):
746 self._cursor.outputtypehandler = value
748 def var(self, *args, **kwargs):
749 return self._cursor.var(*args, **kwargs)
751 def setinputsizes(self, *args: Any, **kwargs: Any) -> Any:
752 return self._cursor.setinputsizes(*args, **kwargs)
754 def _aenter_cursor(self, cursor: AsyncCursor) -> AsyncCursor:
755 try:
756 return cursor.__enter__()
757 except Exception as error:
758 self._adapt_connection._handle_exception(error)
760 async def _execute_async(self, operation, parameters):
761 # override to not use mutex, oracledb already has a mutex
763 if parameters is None:
764 result = await self._cursor.execute(operation)
765 else:
766 result = await self._cursor.execute(operation, parameters)
768 if self._cursor.description and not self.server_side:
769 self._rows = collections.deque(await self._cursor.fetchall())
770 return result
772 async def _executemany_async(
773 self,
774 operation,
775 seq_of_parameters,
776 ):
777 # override to not use mutex, oracledb already has a mutex
778 return await self._cursor.executemany(operation, seq_of_parameters)
780 def __enter__(self):
781 return self
783 def __exit__(self, type_: Any, value: Any, traceback: Any) -> None:
784 self.close()
787class AsyncAdapt_oracledb_ss_cursor(
788 AsyncAdapt_dbapi_ss_cursor, AsyncAdapt_oracledb_cursor
789):
790 __slots__ = ()
792 def close(self) -> None:
793 if self._cursor is not None:
794 self._cursor.close()
795 self._cursor = None # type: ignore
798class AsyncAdapt_oracledb_connection(AsyncAdapt_dbapi_connection):
799 _connection: AsyncConnection
800 __slots__ = ()
802 thin = True
804 _cursor_cls = AsyncAdapt_oracledb_cursor
805 _ss_cursor_cls = None
807 @property
808 def autocommit(self):
809 return self._connection.autocommit
811 @autocommit.setter
812 def autocommit(self, value):
813 self._connection.autocommit = value
815 @property
816 def outputtypehandler(self):
817 return self._connection.outputtypehandler
819 @outputtypehandler.setter
820 def outputtypehandler(self, value):
821 self._connection.outputtypehandler = value
823 @property
824 def version(self):
825 return self._connection.version
827 @property
828 def stmtcachesize(self):
829 return self._connection.stmtcachesize
831 @stmtcachesize.setter
832 def stmtcachesize(self, value):
833 self._connection.stmtcachesize = value
835 @property
836 def max_identifier_length(self):
837 return self._connection.max_identifier_length
839 def cursor(self):
840 return AsyncAdapt_oracledb_cursor(self)
842 def ss_cursor(self):
843 return AsyncAdapt_oracledb_ss_cursor(self)
845 def xid(self, *args: Any, **kwargs: Any) -> Any:
846 return self._connection.xid(*args, **kwargs)
848 def tpc_begin(self, *args: Any, **kwargs: Any) -> Any:
849 return self.await_(self._connection.tpc_begin(*args, **kwargs))
851 def tpc_commit(self, *args: Any, **kwargs: Any) -> Any:
852 return self.await_(self._connection.tpc_commit(*args, **kwargs))
854 def tpc_prepare(self, *args: Any, **kwargs: Any) -> Any:
855 return self.await_(self._connection.tpc_prepare(*args, **kwargs))
857 def tpc_recover(self, *args: Any, **kwargs: Any) -> Any:
858 return self.await_(self._connection.tpc_recover(*args, **kwargs))
860 def tpc_rollback(self, *args: Any, **kwargs: Any) -> Any:
861 return self.await_(self._connection.tpc_rollback(*args, **kwargs))
864class AsyncAdaptFallback_oracledb_connection(
865 AsyncAdaptFallback_dbapi_connection, AsyncAdapt_oracledb_connection
866):
867 __slots__ = ()
870class OracledbAdaptDBAPI:
871 def __init__(self, oracledb) -> None:
872 self.oracledb = oracledb
874 for k, v in self.oracledb.__dict__.items():
875 if k != "connect":
876 self.__dict__[k] = v
878 def connect(self, *arg, **kw):
879 async_fallback = kw.pop("async_fallback", False)
880 creator_fn = kw.pop("async_creator_fn", self.oracledb.connect_async)
882 if asbool(async_fallback):
883 return AsyncAdaptFallback_oracledb_connection(
884 self, await_fallback(creator_fn(*arg, **kw))
885 )
887 else:
888 return AsyncAdapt_oracledb_connection(
889 self, await_only(creator_fn(*arg, **kw))
890 )
893class OracleExecutionContextAsync_oracledb(OracleExecutionContext_oracledb):
894 # restore default create cursor
895 create_cursor = default.DefaultExecutionContext.create_cursor
897 def create_default_cursor(self):
898 # copy of OracleExecutionContext_cx_oracle.create_cursor
899 c = self._dbapi_connection.cursor()
900 if self.dialect.arraysize:
901 c.arraysize = self.dialect.arraysize
903 return c
905 def create_server_side_cursor(self):
906 c = self._dbapi_connection.ss_cursor()
907 if self.dialect.arraysize:
908 c.arraysize = self.dialect.arraysize
910 return c
913class OracleDialectAsync_oracledb(OracleDialect_oracledb):
914 is_async = True
915 supports_server_side_cursors = True
916 supports_statement_cache = True
917 execution_ctx_cls = OracleExecutionContextAsync_oracledb
919 _min_version = (2,)
921 # thick_mode mode is not supported by asyncio, oracledb will raise
922 @classmethod
923 def import_dbapi(cls):
924 import oracledb
926 return OracledbAdaptDBAPI(oracledb)
928 @classmethod
929 def get_pool_class(cls, url):
930 async_fallback = url.query.get("async_fallback", False)
932 if asbool(async_fallback):
933 return pool.FallbackAsyncAdaptedQueuePool
934 else:
935 return pool.AsyncAdaptedQueuePool
937 def get_driver_connection(self, connection):
938 return connection._connection
941dialect = OracleDialect_oracledb
942dialect_async = OracleDialectAsync_oracledb