29.Appendix B: Differences between python-oracledb Thin and Thick Modes

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’ mode whenOracle Client libraries are used. SeeEnabling python-oracledb Thick mode. Both modes havecomprehensive functionality supporting the Python Database API v2.0Specification.

This section details the differences between the python-oracledb Thin and Thickmodes. Also see the summary feature comparison table inAppendix A: Oracle Database Features Supported by python-oracledb.

29.1.Connection Handling Differences between Thin and Thick Modes

Python-oracledb can create connections in either a Thin mode or a Thickmode. However, only one of these modes can be used in each Python process:

  • By default, python-oracledb runs in a Thin mode which connects directly toOracle Database.

  • Iforacledb.init_oracle_client() loads Oracle Client libraries beforeany standalone connection or pool is created, then the python-oracledb modebecomes Thick. The client libraries handle communication with OracleDatabase. SeeEnabling python-oracledb Thick mode.

  • If an application opens a connection or creates a pool and then callsoracledb.init_oracle_client(), an error will occur.

  • Once a connection or pool has been opened, orinit_oracle_client() has been called, you cannot change themode.

Note

The parameters of connection and pool creation functionsoracledb.connect() andoracledb.create_pool() are keywordand not positional. This makes the python-oracledb driver compliant withthe Python Database API specification PEP 249. The old positional usagepossible in the obsolete cx_Oracle driver will cause an error, seeCommon Connection Errors.

29.1.1.Connections to a Local Database

In Thin mode, there is no concept of a local database. Bequeath connectionscannot be made since no Oracle Client libraries are used. The Thin mode doesnot de-reference environment variables such asORACLE_SID,TWO_TASK, orLOCAL (the latter is specific to Windows). A connection string, orequivalent, must always be used.

29.1.2.Oracle Net Services and Client Configuration Files

In the python-oracledb Thin mode:

  • The location of anytnsnames.ora files must explicitly be passed to theapplication.

  • Anysqlnet.ora file will not be read. Instead, pass an equivalentsetting when connecting.

  • There is no support fororaaccess.xml since there are no Oracle Clientlibraries.

SeeOptional Oracle Net Configuration Files andOptional Oracle Client Configuration File for more information.

29.1.3.Token Based Authentication

In the python-oracledb Thin mode:

29.1.4.Transport Layer Security (TLS) Support

When connecting with mutual TLS (mTLS) also known as two-way TLS, for example toOracle Autonomous Database in Oracle Cloud using a wallet, the certificate mustbe in the correct format.

For the python-oracledb Thin mode, the certificate must be in a PrivacyEnhanced Mail (PEM)ewallet.pem file. In python-oracledb Thick mode thecertificate must be in acwallet.sso file. SeeConnecting to Oracle Cloud Autonomous Databases formore information.

29.1.5.Native Network Encryption and Checksumming

The python-oracledb Thin mode does not support connections using OracleDatabase Native Network Encryption (NNE) or checksumming. You canenable TLS instead of using native network encryption. Ifnative network encryption or checksumming are required, then usepython-oracledb in Thick mode. SeeEnabling python-oracledb Thick mode.

For example, if you use python-oracledb Thin mode and try to connect to anOracle Cloud Infrastructure (OCI) Oracle Base Database (where Native NetworkEncryption is set toREQUIRED by default in the databasesqlnet.orafile), the connection will fail with an error like:

DPY-3001:NativeNetworkEncryptionandDataIntegrityisonlysupportedinpython-oracledbthickmode

or:

DPY-4011:thedatabaseornetworkclosedtheconnection

or:

DPY-6000:cannotconnecttodatabase.Listenerrefusedconnection.(SimilartoORA-12660)

SeeTroubleshooting DPY-3001 for more information.

29.2.Connection Pooling Differences between Thin and Thick Modes

Python-oracledb introduced theConnectionPool Object class toreplaceSessionPool. A neworacledb.create_pool() method is nowthe recommended way to create a connection pool. The use of the equivalentSessionPool() constructor isdeprecated.

Thecreate_pool() method in the python-oracledb Thin modediffers from the python-oracledb Thick mode in the following ways:

  • Not all the parameters of theoracledb.create_pool() method areapplicable to both python-oracledb modes. Each mode ignores unrecognizedparameters. The parameters that are ignored in Thin mode includeevents,tag,matchanytag,shardingkey,supershardingkey, andhandle parameters. The parameters that are ignored in the Thick modeincludewallet_password,disable_oob, anddebug_jdwp parameters.

  • The python-oracledb Thin mode only supportshomogeneous pools.

  • The python-oracledb Thin mode creates connections in a daemon thread and sooracledb.create_pool() returns before any or all minimum number ofconnections are created. As soon as the pool is created, theConnectionPool.opened attribute will not be equal toConnectionPool.min. Theopened attribute willincrease to the minimum value over a short time as the connections areestablished. Note that this behavior may also be true of recent versions ofthe Oracle Call Interface (OCI) Session Pool used in the Thin mode.

    If the newgetmode default value ofPOOL_GETMODE_WAITis used, then this behavior will not be an issue. With this new defaultvalue, any immediateConnectionPool.acquire() calls will wait for theconnections to be created by the daemon thread. This improves applicationstart up time when compared to the python-oracledb Thick mode, whereoracledb.create_pool() will not return control to the applicationuntil allpool.min connections have been created.

    If the old default valuePOOL_GETMODE_NOWAIT is required, then the applicationcould check ifConnectionPool.opened has reachedConnectionPool.minand then continue with application start up.

  • In python-oracledb Thick mode, when you close a connection pool with theparameterforce=True, the underlying Oracle Client libraries wait for thecurrent SQL executions to complete before closing the connections. All of theconnections are then dropped from the pool and the pool is closed. Closingthe pool in python-oracledb Thick mode could stop responding indefinitely,depending on the network and Oracle Net Services timeout parameters. Inpython-oracledb Thin mode, the parameterforce=True disconnects eachconnection’s socket using a background thread, leaving the database to cleanup its end of the connections.

  • In python-oracledb Thin mode, thecclass parameter value is not used totag connections in the application connection pool. It is only used forDatabase Resident Connection Pooling (DRCP).

  • In python-oracledb Thin mode, the connection pool supports all theconnectionmode privileges.

29.3.Supported Database Data Types in Thin and Thick Modes

The python-oracledb Thin and Thick mode support for the UROWID, REF, andXMLType database data types has some small differences. SeeSupported Oracle Database Data Types.

29.4.Query Metadata in Thin and Thick Modes

In python-oracledb Thin mode,Cursor.description metadata can distinguishthe ROWID and UROWID database types. The UROWID database type shows the new valueDB_TYPE_UROWID and the database type ROWID uses the existing valueDB_TYPE_ROWID.

In python-oracledb Thick mode, the valueDB_TYPE_ROWID is shown for both ROWIDand UROWID database types. In python-oracledb Thick and Thin modes, comparison withthe typeoracledb.ROWID (defined in the Python DB API) will match both ROWID andUROWID database types.

29.5.Implicit Results in Thin and Thick Modes

In python-oracledb Thick mode, the parent cursor that is used to get theimplicit results must remain open until all of theimplicit result sets have been fetched or until the application no longerrequires them. Closing the parent cursor before all the implicit result setshave been fetched will result in the automatic closure of the implicit resultset cursors.

In python-oracledb Thin mode, there is no requirement to leave the parentcursor open when fetching implicit result sets. The parent cursor and implicitcursors are independently handled in Thin mode.

29.6.Statement Caching in Thin and Thick Modes

Thestatement cache implemented in the python-oracledb Thinmode is capable of determining when different database data types are used forthe same bind variables when a statement is re-executed. This capability isnot supported in the Oracle Client libraries that are used in python-oracledbThick mode. Note changing the type of bind variables for the same SQL text isinappropriate and gives indeterminate results in both modes.

29.7.Duplicate SQL Bind Variable Placeholders in Thin and Thick Modes

To use python-oracledb Thin mode when you have duplicate bind variableplaceholder names in a SQL statement and arebinding by position, then supply a value for each use of the placeholders, seeDuplicate Bind Variable Placeholders.

This does not apply to PL/SQL blocks.

29.8.Error Handling in Thin and Thick Modes

The python-oracledb Thin and Thick modes handle some errors differently. SeeError Handling in Thin and Thick Modes.

29.9.Globalization in Thin and Thick Modes

All NLS environment variables, and theORA_TZFILE environment variable, areignored by the python-oracledb Thin mode. Use Python’s capabilities instead.

The python-oracledb Thin mode can only use NCHAR, NVARCHAR2, and NCLOB datawhen Oracle Database’s secondary character set is AL16UTF16.

SeeCharacter Sets and Globalization.

29.10.Tracing in Thin and Thick Modes

In the python-oracledb Thin mode, low level tracing is different because thereare no Oracle Client libraries. SeeTracing python-oracledb.