30.Appendix C: The python-oracledb and cx_Oracle Drivers

The python-oracledb driver is the renamed, major version successor to cx_Oracle8.3. The python-oracledb driver has manynew features andsomeDeprecated and Desupported Features compared with cx_Oracle. The cx_Oracle driver isobsolete and should not be used for new development.

30.1.Upgrading from cx_Oracle 8.3 to python-oracledb

Below is a list of some useful things to know before upgrading from cx_Oracleto python-oracledb:

You can have both cx_Oracle and python-oracledb installed, and can use bothin the same application. Install python-oracledb like:
```
python-mpipinstalloracledb--upgrade
```
SeeInstalling python-oracledb for details.
By default, python-oracledb runs in a ‘Thin’ mode which connects directly toOracle Database. This mode does not need Oracle Client libraries. However,someadditional functionality is available whenpython-oracledb uses them. Python-oracledb is said to be in ‘Thick’ modewhen Oracle Client libraries are used. SeeEnabling python-oracledb Thick mode. Both modeshave comprehensive functionality supporting the Python Database API v2.0Specification. The Thick mode is equivalent to cx_Oracle.
cx_Oracle always runs in a Thick mode using Oracle Client libraries. Thefeatures in python-oracledb Thick mode and cx_Oracle 8.3 are the same, subjectto thenew features, somedeprecations, and to other changes noted in the documentation.
python-oracledb Thin and Thick modes have the same level of support for thePython Database API specification andcan be used to connect to on-premises databases and Oracle Clouddatabases. SeeAppendix B: Differences between python-oracledb Thin and Thick Modes.
Examples can be found in theGitHub samples directory. A basicexample is:
```
importoracledbimportgetpasspw=getpass.getpass(f"Enter password for hr@localhost/orclpdb: ")withoracledb.connect(user="hr",password=userpwd,dsn="localhost/orclpdb")asconnection:withconnection.cursor()ascursor:forrincursor.execute("select sysdate from dual"):print(r)
```
python-oracledb can be used in SQLAlchemy, Django, Pandas, Superset and otherframeworks and Object-relational Mappers (ORMs). SeeAppendix D: Python Frameworks, SQL Generators, and ORMs.
python-oracledb connection and pool creation calls require keyword argumentsto conform with the Python Database API specification. For example you mustuse:
```
connection=oracledb.connect(user="scott",password=pw,dsn="localhost/orclpdb")
```
This no longer works:
```
connection=oracledb.connect("scott",pw,"localhost/orclpdb")
```
New optional keyword arguments can be passed to connection and pool creationfunctions. For example you can pass the hostname, port and servicename asseparate parameters instead of using an Easy Connect connection string. Inpython-oracledb Thin mode, some of the new arguments replacesqlnet.ora settings.
Some previously deprecated features are no longer available. SeeDeprecated and Desupported Features.
There are many new features, see therelease notes.

30.1.1.Steps to Upgrade to python-oracledb

If you are creating new applications, followInstalling python-oracledb and refer toother sections of the documentation for usage information.

To upgrade existing code from cx_Oracle to python-oracledb, perform thefollowing steps:

Install the new python-oracledb module:
```
python-mpipinstalloracledb
```
SeeInstalling python-oracledb for more details.
Import the new interface module. This can be done in two ways. You can change:
```
importcx_Oracle
```
to:
```
importoracledbascx_Oracle
```
Alternatively, you can replace all references to the modulecx_Oraclewithoracledb. For example, change:
```
importcx_Oraclec=cx_Oracle.connect(...)
```
to:
```
importoracledbc=oracledb.connect(...)
```
Any new code being introduced during the upgrade should aim to use thelatter syntax.
Use keyword parameters in calls tooracledb.connect(),oracledb.Connection(), andoracledb.SessionPool().
Youmust replace positional parameters with keyword parameters, unlessonly one parameter is being passed. Python-oracledb uses keyword parametersexclusively unless a DSN containing the user, password, and connect stringcombined, for example"un/pw@cs", is used. This change makes the drivercompliant with the Python Database API specificationPEP 249.
For example, the following code will fail:
```
c=oracledb.connect("un","pw","cs")
```
and needs to be changed to:
```
c=oracledb.connect(user="un",password="pw",dsn="cs")
```
The following example will continue to work without change:
```
c=oracledb.connect("un/pw@cs")
```
Review obsolete encoding parameters in calls tooracledb.connect(),oracledb.Connection(), andoracledb.SessionPool():
- encoding andnencoding are desupported in python-oracledb and mustbe removed. The python-oracledb driver uses UTF-8 exclusively.
- threaded is desupported inoracledb.connect() andoracledb.Connection() by python-oracledb and must be removed. Thisparameter was already ignored inoracledb.SessionPool() from cx_Oracle8.2.
Remove all references toCursor.fetchraw() as this method was deprecatedin cx_Oracle 8.2 and has been removed in python-oracledb. Instead, use oneof the other fetch methods such asCursor.fetchmany().
The default value of theoracledb.SessionPool() parametergetmodenow waits for an available connection. That is, the default is nowPOOL_GETMODE_WAIT instead ofPOOL_GETMODE_NOWAIT. The new default value improves thebehavior for most applications. If the pool is in the middle of growing,the new value prevents transient connection creation errors from occurringwhen using python-oracledb Thin mode, or when using Thick mode with recentOracle Client libraries.
If the old default value is required, modify any pool creation code toexplicitly specifygetmode=oracledb.POOL_GETMODE_NOWAIT.
Note aConnectionPool class deprecates the equivalentSessionPool class. The methodoracledb.create_pool() deprecates theuse oforacledb.SessionPool(). New pool parameter constant names suchasPOOL_GETMODE_NOWAIT andPURITY_SELFare now preferred. The old namespaces still work.
A Pythontype()will show the class of a connection pool asoracledb.ConnectionPoolinstead ofcx_Oracle.SessionPool. Update code as needed.
Use the newAdvanced Queuing (AQ) API instead of theolder API which was deprecated in cx_Oracle 7.2 and is not available inpython-oracledb.
Replace:
- Connection.deq() withQueue.deqone() orQueue.deqmany()
- Connection.deqoptions() with attributeQueue.deqoptions
- Connection.enq() withQueue.enqone() orQueue.enqmany()
- Connection.enqoptions() with attributeQueue.enqoptions
SeeUsing Oracle Transactional Event Queues and Advanced Queuing.
Remove calls toCursor.executemanyprepared(). This method was previouslydeprecated in cx_Oracle 6.4 and has been removed inpython-oracledb. Instead, useCursor.executemany() by passingNonefor the statement argument and an integer for theparameters argument.
Remove the use of theCursor.bindarraysize. It is desupported and notneeded in the application code.
In python-oracledb, VARCHAR2 and LOB columns that have theISJSONconstraint enabled are fetched by default as Python objects. These columnsare fetched in the same way thatJSON type columns arefetched when using Oracle Database 21c (or later). The returned valuevaries depending on the JSON data. If the JSON data is an object, then adictionary is returned. If it is an array, then a list is returned. If itis a scalar value, then that particular scalar value is returned.
In cx_Oracle, VARCHAR2 and LOB columns that have theISJSON constraintenabled are fetched by default as strings and LOB objects respectively. Toenable this same fetch behavior in python-oracledb, you can use anoutput type handler as shown below.
```
deftype_handler(cursor,fetch_info):iffetch_info.is_json:returncursor.var(fetch_info.type_code,cursor.arraysize)
```
Review uses ofCursor.rowcount. AfterCursor.execute() orCursor.executemany() with PL/SQL statements,Cursor.rowcountwill return0. If the cursor or connection are not open, then the value-1 will be returned as required by the Python Database API.
In python-oracledb Thick mode, error messages generated by the OracleClient libraries and theODPI-C layerused by cx_Oracle and python-oracledb in Thick mode are mostly returnedunchanged from cx_Oracle 8.3. Some exceptions shown below.
Note that the python-oracledb driver error messages can also vary between Thinand Thick modes. SeeError Handling in Thin and Thick Modes.
ConnectionPool.acquire() Message Differences
ConnectionPool.acquire() ORA errors will be mapped to DPY errors. Forexample:
```
DPY-4005:timedoutwaitingfortheconnectionpooltoreturnaconnection
```
replaces the cx_Oracle 8.3 error:
```
ORA-24459:OCISessionGet()timedoutwaitingforpooltocreatenewconnections
```
Dead Connection Detection and Timeout Message Differences
Application code which detects connection failures or statement executiontimeouts will need to check for new errors,DPY-4011 andDPY-4024respectively. The errorDPY-1001 is returned if an already dead connectionis attempted to be used.
The new Error object attributefull_code may beuseful for checking the error code.
Example error messages are:
- Scenario 1: An already closed or dead connection was attempted to be used.
  python-oracledb Thin mode Error:
```
DPY-1001:notconnectedtodatabase
```
  python-oracledb Thick mode Error:
```
DPY-1001:notconnectedtodatabase
```
  cx_Oracle Error:
```
notconnected
```
- Scenario 2: The database side of the connection was terminated while theconnection was being used.
  python-oracledb Thin mode Error:
```
DPY-4011:thedatabaseornetworkclosedtheconnection
```
  python-oracledb Thick mode Error:
```
DPY-4011:thedatabaseornetworkclosedtheconnectionDPI-1080:connectionwasclosedbyORA-%d
```
  cx_Oracle Error:
```
DPI-1080:connectionwasclosedbyORA-%d
```
- Scenario 3: Statement execution exceeded theconnection.call_timeoutvalue.
  python-oracledb Thin mode Error:
```
DPY-4024:calltimeoutof{timeout}msexceeded
```
  python-oracledb Thick mode Error:
```
DPY-4024:calltimeoutof{timeout}msexceededDPI-1067:calltimeoutof%umsexceededwithORA-%d
```
  cx_Oracle Error:
```
DPI-1067:calltimeoutof%umsexceededwithORA-%d
```
If your code base uses an older cx_Oracle version, reviewDeprecated and Desupported Features for additional changes that may be necessary.
Modernize code to take advantage of new features, if desired. See therelease notes.
Review the following sections to see if your application requirements aresatisfied by python-oracledb Thin mode:
- Appendix A: Oracle Database Features Supported by python-oracledb
- Appendix B: Differences between python-oracledb Thin and Thick Modes
If so, then followAdditional Upgrade Steps to use python-oracledb Thin Mode.
If your application requirements are not supported by python-oracledb Thinmode, then use Thick mode, seeAdditional Upgrade Steps to use python-oracledb Thick Mode.

30.1.1.1.Additional Upgrade Steps to use python-oracledb Thin Mode

To upgrade from cx_Oracle to python-oracledb Thin mode, the following changesneed to be made in addition to the commonSteps to Upgrade to python-oracledb:

Remove calls toinit_oracle_client() since this turns onpython-oracledb Thick mode.
If theconfig_dir parameter ofinit_oracle_client() hadbeen used, then set the newdefaults.config_dir attribute to thedesired value or set theconfig_dir parameter in your connection or poolcreation method call. For example:
```
oracledb.defaults.config_dir="/opt/oracle/config"
```
or
```
connection=oracledb.connect(user="hr",password=userpwd,dsn="orclpdb",config_dir="/opt/oracle/config")
```
Also, seeOracle Net Services and Client Configuration Files.
If thedriver_name parameter ofinit_oracle_client() hadbeen used, then set the newdefaults.driver_name attribute to thedesired value or set thedriver_name parameter when connecting. Theconvention for this parameter is to separate the product name from theproduct version by a colon and single blank characters. For example:
```
oracledb.defaults.driver_name="python-oracledb : 1.2.0"
```
SeeOther python-oracledb Thick Mode Initialization.
Remove calls tooracledb.clientversion().
Theoracledb.clientversion() function shows the version of theOracle Client libraries being used. Since Oracle Client libraries are notused in the python-oracledb Thin mode, this function cannot be called. If itis called before callingoracledb.init_oracle_client(), an exceptionis thrown.
To connect using aTNS Alias from atnsnames.orafile (seeOptional Oracle Net Configuration Files) in python-oracledb Thin mode, you shouldexplicitly set the environment variableTNS_ADMIN to the directorycontaining the file, or setdefaults.config_dir, or set theconfig_dir parameter when connecting.
Atnsnames.ora file in a “default” location such as the Instant Clientnetwork/admin/ subdirectory may not be automatically loaded in Thin modeon some platforms. Atnsnames.ora file identified by the Windowsregistry, or in$ORACLE_BASE/homes/XYZ/network/admin/ (in a read-onlyOracle Database home) will never be automatically located by python-oracledbThin mode.
Only python-oracledb Thick mode will readsqlnet.ora andoraaccess.xml files. The Thin mode lets equivalentproperties be set in the application when connecting.
To use python-oracledb Thin mode in anORACLE_HOME database installationenvironment, you must use an explicit connection string since theORACLE_SID,TWO_TASK, andLOCAL environment variables are notused. They are used in Thick mode.
Ensure that any assumptions about when connections are created in theconnection pool are eliminated. Python-oracledb Thin mode createsconnections in a daemon thread and so the attributeConnectionPool.opened will change over time and will not be equaltoConnectionPool.min immediately after the pool is created. Notethat this behavior is also similar in recent versions of the Oracle CallInterface (OCI) Session Pool used by the Thick mode. Unless theoracledb.SessionPool() function’s parametergetmode isoracledb.POOL_GETMODE_WAIT, then applications should not callConnectionPool.acquire() until sufficient time has passed forconnections in the pool to be created.
Review locale and globalization usage. Python-oracledb Thin mode ignoresall NLS environment variables. It also ignores theORA_TZFILEenvironment variable. Thick mode does use these variables. SeeCharacter Sets and Globalization.
If SQL statements contain repeated bind variable placeholder names, and youarebinding by position, then make sure that a valueis passed for each use of the placeholder, seeDuplicate Bind Variable Placeholders.

30.1.1.2.Additional Upgrade Steps to use python-oracledb Thick Mode

To upgrade from cx_Oracle to python-oracledb Thick mode, in addition to thecommonSteps to Upgrade to python-oracledb, the functionoracledb.init_oracle_client()must be called to enable the Thick mode. It can be called anywhere beforethe first call tooracledb.connect(),oracledb.Connection(), ororacledb.SessionPool(). If a connection or pool is created first in thedefault Thin mode, then Thick mode cannot be enabled. SeeEnabling python-oracledb Thick mode for more details.

The requirement to callinit_oracle_client() means thatOracle Client library loading is not automatically deferred until the driveris first used, such as when a connection is opened. To emulate the cx_Oraclebehavior of deferring library loading until the creation of the firstconnection (in the case wheninit_oracle_client() is notcalled), your application will need to explicitly defer callinginit_oracle_client() as appropriate.

In python-oracledb,init_oracle_client() can be calledmultiple times in a Python process as long as the arguments are the same.

Note that on Linux and related operating systems, theinit_oracle_client() parameterlib_dir should not bepassed. Instead, set the system library search path withldconfig orLD_LIBRARY_PATH prior to running Python.

30.1.2.Modernizing Code

Many significant new features have been added to python-oracledb. You may wantto take advantage of them when upgrading from cx_Oracle. See the rest of thedocumentation, therelease notes, andAppendix A: Oracle Database Features Supported by python-oracledb.

The following points summarize some of the smaller changes that you may findinteresting:

Theoracledb.makedsn() method for creating thedsn value has beendeprecated. New code should use keyword arguments when creating connectionsor connection pools, or make use of aparams object described below.
A new optional parameterparams of typeConnectParamscan be used to encapsulate connection properties. Similarly a new optionalparameterparams of typePoolParams can be used toencapsulate pool creation properties. SeeUsing the ConnectParams Builder Class for moreinformation.
The use of the class constructor methodoracledb.Connection() to createconnections is no longer recommended for creating connections. Useconnect() instead.
The new method signature ofConnection.outputtypehandler ishandler(cursor,metadata). The old signaturehandler(cursor,name,default_type,length,precision,scale) was deprecated in python-oracledb1.4 but will still work and will be removed in a future version.
The attributeConnection.maxBytesPerCharacter is deprecated. Thiswill return a constant value of4 since encodings are always UTF-8.
In python-oracledb, the name of the size argument ofCursor.fetchmany() issize. This change was done to comply withPEP 249. The previous keywordargument name,numRows is deprecated.

30.1.3.Code to Aid the Upgrade to python-oracledb

30.1.3.1.Toggling between Drivers

The sampleoracledb_upgrade.py shows a way to toggle applicationsbetween cx_Oracle and the two python-oracledb modes. Note this script cannotmap some functionality such as obsolete cx_Oracle features or error messagechanges.

An example application showing this module in use is:

# test.pyimportoracledb_upgradeascx_Oracleimportosun=os.environ.get("PYTHON_USERNAME")pw=os.environ.get("PYTHON_PASSWORD")cs=os.environ.get("PYTHON_CONNECTSTRING")connection=cx_Oracle.connect(user=un,password=pw,dsn=cs)withconnection.cursor()ascursor:sql="""select unique client_driver             from v$session_connect_info             where sid = sys_context('userenv', 'sid')"""forr,incursor.execute(sql):print(r)

You can then choose what mode is in use by setting the environment variableORA_PYTHON_DRIVER_TYPE to one of “cx”, “thin”, or “thick”:

exportORA_PYTHON_DRIVER_TYPE=thinpythontest.py

Output shows that python-oracledb Thin mode was used:

python-oracledbthn:3.0.0

You can customizeoracledb_upgrade.py to your needs. For example, if yourconnection and pool creation calls always use keyword parameters, you canremove the shims that map from positional arguments to keyword arguments.

The simplest form is shown inAppendix D: Python Frameworks, SQL Generators, and ORMs.

30.1.3.2.Testing Which Driver is in Use

To know whether the driver is cx_Oracle or python-oracledb, you can use codesimilar to:

importoracledbascx_Oracle# or:# import cx_Oracleifcx_Oracle.__name__=='cx_Oracle':print('cx_Oracle')else:print('oracledb')

Another method that can be used to check which driver is in use is to query theview V$SESSION_CONNECT_INFO, seeFinding the python-oracledb Mode.

Movatterモバイル変換