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

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 established in 30 seconds, and also setting

116a keep-alive time of 60 seconds to stop idle network connections from being

117terminated by a firewall::

118

119 e = create_engine(

120 "oracle+oracledb://@",

121 connect_args={

122 "user": "scott",

123 "password": "tiger",

124 "dsn": "hostname:port/myservice?transport_connect_timeout=30&expire_time=60",

125 },

126 )

127

128The Easy Connect syntax has been enhanced during the life of Oracle Database.

129Review the documentation for your database version. The current documentation

130is at `Understanding the Easy Connect Naming Method

131<https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=GUID-B0437826-43C1-49EC-A94D-B650B6A4A6EE>`_.

132

133The general syntax is similar to:

134

135.. sourcecode:: text

136

137 [[protocol:]//]host[:port][/[service_name]][?parameter_name=value{&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.. _oracledb_unicode_encoding_errors:

416

417Encoding Errors

418^^^^^^^^^^^^^^^

419

420For the unusual case that data in Oracle Database is present with a broken

421encoding, the dialect accepts a parameter ``encoding_errors`` which will be

422passed to Unicode decoding functions in order to affect how decoding errors are

423handled. The value is ultimately consumed by the Python `decode

424<https://docs.python.org/3/library/stdtypes.html#bytes.decode>`_ function, and

425is passed both via python-oracledb's ``encodingErrors`` parameter consumed by

426``Cursor.var()``, as well as SQLAlchemy's own decoding function, as the

427python-oracledb dialect makes use of both under different circumstances.

428

429.. _oracledb_setinputsizes:

430

431Fine grained control over python-oracledb data binding with setinputsizes

432-------------------------------------------------------------------------

433

434The python-oracle DBAPI has a deep and fundamental reliance upon the usage of

435the DBAPI ``setinputsizes()`` call. The purpose of this call is to establish

436the datatypes that are bound to a SQL statement for Python values being passed

437as parameters. While virtually no other DBAPI assigns any use to the

438``setinputsizes()`` call, the python-oracledb DBAPI relies upon it heavily in

439its interactions with the Oracle Database, and in some scenarios it is not

440possible for SQLAlchemy to know exactly how data should be bound, as some

441settings can cause profoundly different performance characteristics, while

442altering the type coercion behavior at the same time.

443

444Users of the oracledb dialect are **strongly encouraged** to read through

445python-oracledb's list of built-in datatype symbols at `Database Types

446<https://python-oracledb.readthedocs.io/en/latest/api_manual/module.html#database-types>`_

447Note that in some cases, significant performance degradation can occur when

448using these types vs. not.

449

450On the SQLAlchemy side, the :meth:`.DialectEvents.do_setinputsizes` event can

451be used both for runtime visibility (e.g. logging) of the setinputsizes step as

452well as to fully control how ``setinputsizes()`` is used on a per-statement

453basis.

454

455Example 1 - logging all setinputsizes calls

456^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

457

458The following example illustrates how to log the intermediary values from a

459SQLAlchemy perspective before they are converted to the raw ``setinputsizes()``

460parameter dictionary. The keys of the dictionary are :class:`.BindParameter`

461objects which have a ``.key`` and a ``.type`` attribute::

462

463 from sqlalchemy import create_engine, event

464

465 engine = create_engine(

466 "oracle+oracledb://scott:tiger@localhost:1521?service_name=freepdb1"

467 )

468

469

470 @event.listens_for(engine, "do_setinputsizes")

471 def _log_setinputsizes(inputsizes, cursor, statement, parameters, context):

472 for bindparam, dbapitype in inputsizes.items():

473 log.info(

474 "Bound parameter name: %s SQLAlchemy type: %r DBAPI object: %s",

475 bindparam.key,

476 bindparam.type,

477 dbapitype,

478 )

479

480Example 2 - remove all bindings to CLOB

481^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

482

483For performance, fetching LOB datatypes from Oracle Database is set by default

484for the ``Text`` type within SQLAlchemy. This setting can be modified as

485follows::

486

487

488 from sqlalchemy import create_engine, event

489 from oracledb import CLOB

490

491 engine = create_engine(

492 "oracle+oracledb://scott:tiger@localhost:1521?service_name=freepdb1"

493 )

494

495

496 @event.listens_for(engine, "do_setinputsizes")

497 def _remove_clob(inputsizes, cursor, statement, parameters, context):

498 for bindparam, dbapitype in list(inputsizes.items()):

499 if dbapitype is CLOB:

500 del inputsizes[bindparam]

501

502.. _oracledb_lob:

503

504LOB Datatypes

505--------------

506

507LOB datatypes refer to the "large object" datatypes such as CLOB, NCLOB and

508BLOB. Oracle Database can efficiently return these datatypes as a single

509buffer. SQLAlchemy makes use of type handlers to do this by default.

510

511To disable the use of the type handlers and deliver LOB objects as classic

512buffered objects with a ``read()`` method, the parameter

513``auto_convert_lobs=False`` may be passed to :func:`_sa.create_engine`.

514

515.. _oracledb_returning:

516

517RETURNING Support

518-----------------

519

520The oracledb dialect implements RETURNING using OUT parameters. The dialect

521supports RETURNING fully.

522

523Two Phase Transaction Support

524-----------------------------

525

526Two phase transactions are fully supported with python-oracledb. (Thin mode

527requires python-oracledb 2.3). APIs for two phase transactions are provided at

528the Core level via :meth:`_engine.Connection.begin_twophase` and

529:paramref:`_orm.Session.twophase` for transparent ORM use.

530

531.. versionchanged:: 2.0.32 added support for two phase transactions

532

533.. _oracledb_numeric:

534

535Precision Numerics

536------------------

537

538SQLAlchemy's numeric types can handle receiving and returning values as Python

539``Decimal`` objects or float objects. When a :class:`.Numeric` object, or a

540subclass such as :class:`.Float`, :class:`_oracle.DOUBLE_PRECISION` etc. is in

541use, the :paramref:`.Numeric.asdecimal` flag determines if values should be

542coerced to ``Decimal`` upon return, or returned as float objects. To make

543matters more complicated under Oracle Database, the ``NUMBER`` type can also

544represent integer values if the "scale" is zero, so the Oracle

545Database-specific :class:`_oracle.NUMBER` type takes this into account as well.

546

547The oracledb dialect makes extensive use of connection- and cursor-level

548"outputtypehandler" callables in order to coerce numeric values as requested.

549These callables are specific to the specific flavor of :class:`.Numeric` in

550use, as well as if no SQLAlchemy typing objects are present. There are

551observed scenarios where Oracle Database may send incomplete or ambiguous

552information about the numeric types being returned, such as a query where the

553numeric types are buried under multiple levels of subquery. The type handlers

554do their best to make the right decision in all cases, deferring to the

555underlying python-oracledb DBAPI for all those cases where the driver can make

556the best decision.

557

558When no typing objects are present, as when executing plain SQL strings, a

559default "outputtypehandler" is present which will generally return numeric

560values which specify precision and scale as Python ``Decimal`` objects. To

561disable this coercion to decimal for performance reasons, pass the flag

562``coerce_to_decimal=False`` to :func:`_sa.create_engine`::

563

564 engine = create_engine(

565 "oracle+oracledb://scott:tiger@tnsalias", coerce_to_decimal=False

566 )

567

568The ``coerce_to_decimal`` flag only impacts the results of plain string

569SQL statements that are not otherwise associated with a :class:`.Numeric`

570SQLAlchemy type (or a subclass of such).

571

572.. versionadded:: 2.0.0 added support for the python-oracledb driver.

573

574.. _oracledb_json:

575

576JSON Support

577------------

578

579Oracle Database supports a native JSON datatype as of version 21c, as well as

580support for JSON functions on character and BLOB columns as of version 12c. The

581SQLAlchemy :class:`_sqltypes.JSON` datatype may be used with the oracledb

582backend in the same way it works with any other backend, with some slight

583behavioral changes particularly when using the native JSON datatype. See

584:class:`_oracle.JSON` for platform-specific notes.

585

586.. versionadded:: 2.1 added JSON support for the Oracle backend.

587

588

589""" # noqa

590

591from __future__ import annotations

592

593import collections

594from typing import Any

595from typing import TYPE_CHECKING

596

597from . import cx_oracle as _cx_oracle

598from ... import util

599from ...connectors.asyncio import AsyncAdapt_dbapi_connection

600from ...connectors.asyncio import AsyncAdapt_dbapi_cursor

601from ...connectors.asyncio import AsyncAdapt_dbapi_module

602from ...connectors.asyncio import AsyncAdapt_dbapi_ss_cursor

603from ...connectors.asyncio import AsyncAdapt_terminate

604from ...engine import default

605from ...util import await_

606

607if TYPE_CHECKING:

608 from oracledb import AsyncConnection

609 from oracledb import AsyncCursor

610

611

612class OracleExecutionContext_oracledb(

613 _cx_oracle.OracleExecutionContext_cx_oracle

614):

615 pass

616

617

618class OracleDialect_oracledb(_cx_oracle.OracleDialect_cx_oracle):

619 supports_statement_cache = True

620 execution_ctx_cls = OracleExecutionContext_oracledb

621

622 driver = "oracledb"

623

624 minimum_dbapi_version = util.VersionInfo((1,))

625

626 def __init__(

627 self,

628 auto_convert_lobs=True,

629 coerce_to_decimal=True,

630 arraysize=None,

631 encoding_errors=None,

632 thick_mode=None,

633 **kwargs,

634 ):

635 super().__init__(

636 auto_convert_lobs,

637 coerce_to_decimal,

638 arraysize,

639 encoding_errors,

640 **kwargs,

641 )

642

643 if self.dbapi is not None and (

644 thick_mode or isinstance(thick_mode, dict)

645 ):

646 kw = thick_mode if isinstance(thick_mode, dict) else {}

647 self.dbapi.init_oracle_client(**kw)

648

649 @classmethod

650 def import_dbapi(cls):

651 import oracledb

652

653 return oracledb

654

655 @classmethod

656 def is_thin_mode(cls, connection):

657 return connection.connection.dbapi_connection.thin

658

659 @classmethod

660 def get_async_dialect_cls(cls, url):

661 return OracleDialectAsync_oracledb

662

663 @property

664 def oracledb_ver(self):

665 """Legacy accessor for :attr:`.Dialect.dbapi_version`.

666

667 Retained for backwards compatibility; ``(0, 0, 0)`` is returned

668 when no version can be determined.

669

670 """

671 version = self._dbapi_version_or_none

672 return version if version is not None else util.VersionInfo((0, 0, 0))

673

674 def do_begin_twophase(self, connection, xid):

675 conn_xis = connection.connection.xid(*xid)

676 connection.connection.tpc_begin(conn_xis)

677 connection.connection.info["oracledb_xid"] = conn_xis

678

679 def do_prepare_twophase(self, connection, xid):

680 should_commit = connection.connection.tpc_prepare()

681 connection.info["oracledb_should_commit"] = should_commit

682

683 def do_rollback_twophase(

684 self, connection, xid, is_prepared=True, recover=False

685 ):

686 if recover:

687 conn_xid = connection.connection.xid(*xid)

688 else:

689 conn_xid = None

690 connection.connection.tpc_rollback(conn_xid)

691

692 def do_commit_twophase(

693 self, connection, xid, is_prepared=True, recover=False

694 ):

695 conn_xid = None

696 if not is_prepared:

697 should_commit = connection.connection.tpc_prepare()

698 elif recover:

699 conn_xid = connection.connection.xid(*xid)

700 should_commit = True

701 else:

702 should_commit = connection.info["oracledb_should_commit"]

703 if should_commit:

704 connection.connection.tpc_commit(conn_xid)

705

706 def do_recover_twophase(self, connection):

707 return [

708 # oracledb seems to return bytes

709 (

710 fi,

711 gti.decode() if isinstance(gti, bytes) else gti,

712 bq.decode() if isinstance(bq, bytes) else bq,

713 )

714 for fi, gti, bq in connection.connection.tpc_recover()

715 ]

716

717 def _check_max_identifier_length(self, connection):

718 if self.dbapi_version >= (2, 5):

719 max_len = connection.connection.max_identifier_length

720 if max_len is not None:

721 return max_len

722 return super()._check_max_identifier_length(connection)

723

724

725class AsyncAdapt_oracledb_cursor(AsyncAdapt_dbapi_cursor):

726 _cursor: AsyncCursor

727 _awaitable_cursor_close: bool = False

728

729 __slots__ = ()

730

731 @property

732 def outputtypehandler(self):

733 return self._cursor.outputtypehandler

734

735 @outputtypehandler.setter

736 def outputtypehandler(self, value):

737 self._cursor.outputtypehandler = value

738

739 def var(self, *args, **kwargs):

740 return self._cursor.var(*args, **kwargs)

741

742 def setinputsizes(self, *args: Any, **kwargs: Any) -> Any:

743 return self._cursor.setinputsizes(*args, **kwargs)

744

745 async def _execute_async(self, operation, parameters):

746 # override to not use mutex, oracledb already has a mutex

747

748 if parameters is None:

749 result = await self._cursor.execute(operation)

750 else:

751 result = await self._cursor.execute(operation, parameters)

752

753 if self._cursor.description and not self.server_side:

754 self._rows = collections.deque(await self._cursor.fetchall())

755 return result

756

757 async def _executemany_async(

758 self,

759 operation,

760 seq_of_parameters,

761 ):

762 # override to not use mutex, oracledb already has a mutex

763 return await self._cursor.executemany(operation, seq_of_parameters)

764

765

766class AsyncAdapt_oracledb_ss_cursor(

767 AsyncAdapt_dbapi_ss_cursor, AsyncAdapt_oracledb_cursor

768):

769 __slots__ = ()

770

771 def close(self) -> None:

772 if self._cursor is not None:

773 self._cursor.close()

774 self._cursor = None # type: ignore

775

776

777class AsyncAdapt_oracledb_connection(

778 AsyncAdapt_terminate, AsyncAdapt_dbapi_connection

779):

780 _connection: AsyncConnection

781 __slots__ = ()

782

783 thin = True

784

785 _cursor_cls = AsyncAdapt_oracledb_cursor

786 _ss_cursor_cls = None

787

788 @property

789 def autocommit(self):

790 return self._connection.autocommit

791

792 @autocommit.setter

793 def autocommit(self, value):

794 self._connection.autocommit = value

795

796 @property

797 def outputtypehandler(self):

798 return self._connection.outputtypehandler

799

800 @outputtypehandler.setter

801 def outputtypehandler(self, value):

802 self._connection.outputtypehandler = value

803

804 @property

805 def version(self):

806 return self._connection.version

807

808 @property

809 def stmtcachesize(self):

810 return self._connection.stmtcachesize

811

812 @stmtcachesize.setter

813 def stmtcachesize(self, value):

814 self._connection.stmtcachesize = value

815

816 @property

817 def max_identifier_length(self):

818 return self._connection.max_identifier_length

819

820 def cursor(self):

821 return AsyncAdapt_oracledb_cursor(self)

822

823 def ss_cursor(self):

824 return AsyncAdapt_oracledb_ss_cursor(self)

825

826 def xid(self, *args: Any, **kwargs: Any) -> Any:

827 return self._connection.xid(*args, **kwargs)

828

829 def tpc_begin(self, *args: Any, **kwargs: Any) -> Any:

830 return await_(self._connection.tpc_begin(*args, **kwargs))

831

832 def tpc_commit(self, *args: Any, **kwargs: Any) -> Any:

833 return await_(self._connection.tpc_commit(*args, **kwargs))

834

835 def tpc_prepare(self, *args: Any, **kwargs: Any) -> Any:

836 return await_(self._connection.tpc_prepare(*args, **kwargs))

837

838 def tpc_recover(self, *args: Any, **kwargs: Any) -> Any:

839 return await_(self._connection.tpc_recover(*args, **kwargs))

840

841 def tpc_rollback(self, *args: Any, **kwargs: Any) -> Any:

842 return await_(self._connection.tpc_rollback(*args, **kwargs))

843

844 async def _terminate_graceful_close(self) -> None:

845 await self._connection.close()

846

847 def _terminate_force_close(self) -> None:

848 self._connection.terminate()

849

850

851class OracledbAdaptDBAPI(AsyncAdapt_dbapi_module):

852 def __init__(self, oracledb) -> None:

853 super().__init__(oracledb)

854 self.oracledb = oracledb

855

856 for k, v in self.oracledb.__dict__.items():

857 if k != "connect":

858 self.__dict__[k] = v

859

860 def connect(self, *arg, **kw):

861 creator_fn = kw.pop("async_creator_fn", self.oracledb.connect_async)

862 return await_(

863 AsyncAdapt_oracledb_connection.create(self, creator_fn(*arg, **kw))

864 )

865

866

867class OracleExecutionContextAsync_oracledb(OracleExecutionContext_oracledb):

868 # restore default create cursor

869 create_cursor = default.DefaultExecutionContext.create_cursor

870

871 def create_default_cursor(self):

872 # copy of OracleExecutionContext_cx_oracle.create_cursor

873 c = self._dbapi_connection.cursor()

874 if self.dialect.arraysize:

875 c.arraysize = self.dialect.arraysize

876

877 return c

878

879 def create_server_side_cursor(self):

880 c = self._dbapi_connection.ss_cursor()

881 if self.dialect.arraysize:

882 c.arraysize = self.dialect.arraysize

883

884 return c

885

886

887class OracleDialectAsync_oracledb(OracleDialect_oracledb):

888 is_async = True

889 supports_server_side_cursors = True

890 supports_statement_cache = True

891 execution_ctx_cls = OracleExecutionContextAsync_oracledb

892

893 minimum_dbapi_version = util.VersionInfo((2, 0, 1))

894

895 def __init__(self, **kwargs):

896 super().__init__(**kwargs)

897

898 self.has_terminate = (

899 self._dbapi_version_or_none is not None

900 and self._dbapi_version_or_none >= (26, 0, 0)

901 )

902

903 # thick_mode mode is not supported by asyncio, oracledb will raise

904 @classmethod

905 def import_dbapi(cls):

906 import oracledb

907

908 return OracledbAdaptDBAPI(oracledb)

909

910 def get_driver_connection(self, connection):

911 return connection._connection

912

913 def do_terminate(self, dbapi_connection) -> None:

914 if self.has_terminate:

915 dbapi_connection.terminate()

916 else:

917 super().do_terminate(dbapi_connection)

918

919

920dialect = OracleDialect_oracledb

921dialect_async = OracleDialectAsync_oracledb