Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/sqlalchemy/dialects/oracle/oracledb.py: 51%

1# dialects/oracle/oracledb.py

3# <see AUTHORS file>

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 )

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 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::

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{&parameter_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