2. API: Defaults Object

2.1. Defaults Class

class oracledb.Defaults

A singleton Defaults object contains attributes to adjust default behaviors of python-oracledb. It is accessed using the defaults attribute of the imported module.

See Changing python-oracledb Default Settings.

2.2. Defaults Attributes

property Defaults.arraysize: int

This read-write attribute specifies the default arraysize to use when cursors are created.

It is an attribute for tuning the performance of fetching rows from Oracle Database. It does not affect data insertion.

This value is the default for Cursor.arraysize and AsyncCursor.arraysize.

This attribute has an initial value of 100.

See Tuning Fetch Performance.

property Defaults.config_dir: str

This read-write attribute specifies the directory in which the optional configuration file tnsnames.ora will be read in python-oracledb Thin mode. It is also used in Thick mode if Defaults.thick_mode_dsn_passthrough is False.

At time of import oracledb the value of oracledb.defaults.config_dir will be set to (first one wins):

  • the value of $TNS_ADMIN, if TNS_ADMIN is set.

  • $ORACLE_HOME/network/admin, if $ORACLE_HOME is set.

Otherwise, oracledb.defaults.config_dir will not be set.

At completion of a call to oracledb.init_oracle_client() in python-oracledb Thick mode, the value of config_dir may get changed.

See Optional Oracle Net Configuration Files.

Changed in version 3.0.0: The directory $ORACLE_HOME/network/admin was added to the heuristic.

property Defaults.driver_name: str

This read-write attribute is a string recorded by Oracle Database as the name of the driver which originated the connection. This is the value used in the CLIENT_DRIVER column of the V$SESSION_CONNECT_INFO view.

This attribute has an initial value of None. It is used as required in python-oracledb Thick and Thin mode.

In python-oracledb Thick mode, this attribute is used if the driver_name parameter is not specified in oracledb.init_oracle_client(). In Thin mode, this attribute is used if the driver_name parameter is not specified in oracledb.connect(), oracledb.connect_async(), oracledb.create_pool(), or oracledb.create_pool_async(). If the value of this attribute is None, the value set when connecting in python-oracledb Thick mode is like “python-oracledb thk : <version>” and in Thin mode is like “python-oracledb thn : <version>”.

See Other python-oracledb Thick Mode Initialization, Database Views for Tracing python-oracledb, and V$SESSION_CONNECT_INFO.

Added in version 2.5.0.

property Defaults.fetch_decimals: bool

This read-write attribute specifies whether queries that contain numbers should be fetched as Python decimal.Decimal objects or floating point numbers. This can help avoid issues with converting numbers from Oracle Database’s decimal format to Python’s binary format.

The value of fetch_decimals can be overridden at statement execution by passing an equivalent parameter.

An output type handler such as previously required in the obsolete cx_Oracle driver can alternatively be used to adjust the returned type. If a type handler exists and returns a variable (that is, cursor.var(...)), then that return variable is used. If the type handler returns None, then the value of oracledb.defaults.fetch_decimals is used to determine whether to return decimal.Decimal values.

This attribute has an initial value of False.

See decimal.Decimal.

property Defaults.fetch_lobs: bool

This read-write attribute specifies whether queries that contain LOBs should return LOB objects or their contents instead.

When the value of this attribute is True, then queries to LOB columns return LOB locators. When the value of this attribute is False, then CLOBs and NCLOBs are fetched as strings, and BLOBs are fetched as bytes. If LOBs are larger than 1 GB, then this attribute should be set to True and the LOBs should be streamed.

The value of oracledb.defaults.fetch_lobs does not affect LOBs returned as OUT binds.

The value of fetch_lobs can be overridden at statement execution by passing an equivalent parameter.

An output type handler such as the one previously required in the obsolete cx_Oracle driver can alternatively be used to adjust the returned type. If a type handler exists and returns a variable (that is, cursor.var(…)), then that return variable is used. If the type handler returns None, then the value of oracledb.defaults.fetch_lobs is used.

This attribute has an initial value of True.

See Using CLOB, BLOB, NCLOB, and BFILE Data.

property Defaults.machine: str

This read-write attribute is a string recorded by Oracle Database as the name of machine from which the connection originates. This is the value used in the MACHINE column of the V$SESSION view.

This attribute takes the host name where the application is running as its initial value.

This attribute is only used in python-oracledb Thin mode.

See Database Views for Tracing python-oracledb and V$SESSION.

Added in version 2.5.0.

property Defaults.osuser: str

This read-write attribute is a string recorded by Oracle Database as the operating system user who originated the connection. This is the value used in the OSUSER column of the V$SESSION view.

This attribute takes the login name of the user as its initial value.

This attribute is only used in python-oracledb Thin mode.

See Database Views for Tracing python-oracledb V$SESSION.

Added in version 2.5.0.

property Defaults.prefetchrows: int

This read-write attribute specifies the default number of rows to prefetch when cursors are executed.

This is an attribute for tuning the performance of fetching rows from Oracle Database. It does not affect data insertion.

This value is the default for Cursor.prefetchrows and AsyncCursor.prefetchrows.

This attribute is ignored when using Connection.fetch_df_all() or Connection.fetch_df_batches() since these methods always set the internal prefetch size to their relevant arraysize or size parameter value.

This attribute has an initial value of 2.

See Tuning Fetch Performance.

property Defaults.program: str

This read-write attribute is a string recorded by Oracle Database as the program from which the connection originates. This is the value used in the PROGRAM column of the V$SESSION view.

This attribute has an initial value that is populated by sys.executable.

This attribute is only used in python-oracledb Thin mode.

See Database Views for Tracing python-oracledb and V$SESSION.

Added in version 2.5.0.

property Defaults.stmtcachesize: int

This read-write attribute specifies the default size of the statement cache.

This is an attribute for tuning statement execution performance when a statement is executed more than once.

This value is the default for Connection.stmtcachesize, ConnectionPool.stmtcachesize, AsyncConnection.stmtcachesize, and AsyncConnectionPool.stmtcachesize.

This attribute has an initial value of 20.

See Statement Caching.

property Defaults.terminal: str

This read-write attribute specifies the terminal identifier from which the connection originates. This is the value used in the TERMINAL column of the V$SESSION view.

This attribute has an initial value of “unknown”.

This attribute is only used in python-oracledb Thin mode.

See Database Views for Tracing python-oracledb and V$SESSION.

Added in version 2.5.0.

property Defaults.thick_mode_dsn_passthrough: bool

This read-write attribute determines whether connection strings passed as the dsn parameter to oracledb.connect(), oracledb.create_pool(), oracledb.connect_async(), and oracledb.create_pool_async() in python-oracledb Thick mode will be parsed by Oracle Client libraries or by python-oracledb itself.

The value of thick_mode_dsn_passthrough is ignored in python-oracledb Thin mode, which always parses all connect strings (including reading a tnsnames.ora file, if required).

This attribute has an initial value of True.

When thick_mode_dsn_passthrough is the default value True, the behavior of python-oracledb 2.5 and earlier versions occurs: python-oracledb Thick mode passes connect strings unchanged to the Oracle Client libraries to handle. Those libraries have their own heuristics for locating the optional tnsnames.ora, if used.

When thick_mode_dsn_passthrough is False, python-oracledb Thick mode behaves similarly to Thin mode, which can be helpful for applications that may be run in either mode:

  • The search path used to locate and read any optional tnsnames.ora file is handled in the python-oracledb driver. Different tnsnames.ora files can be used by each connection. Note loading of optional Thick mode files such as sqlnet.ora and oraaccess.xml is always handled by Oracle Client libraries regardless of the value of thick_mode_dsn_passthrough because it is those libraries that use these files.

  • All connect strings will be parsed by the python-oracledb driver and a generated connect descriptor is sent to the database. Parameters unrecognized by python-oracledb in Easy Connect strings are discarded. In full connect descriptors passed explicitly as the dsn parameter value or stored in a tnsnames.ora file, any parameters that are unrecognized by python-oracledb in the DESCRIPTION, CONNECT_DATA and SECURITY sections will be passed through to the database unchanged, while unrecognized parameters in other sections are discarded.

  • If a Centralized Configuration Provider is used for connection configuration, any python-oracledb parameter values in the configuration will be used.

Added in version 3.0.0.