[Introduction to cx_Oracle] (Part 9) DB and Python data type mapping (version 8 or later)

Verification environment

--Using Oracle Cloud

• Oracle Linux 7.7 (VM.Standard2.1)
• Python 3.6
• cx_Oracle 8.0.0
• Oracle Database 19.5 (ATP, 1OCPU)
• Oracle Instant Client 18.5

Introduction

Of course, Oracle Database and Python have different data types. In this article, I will explain how cx_Oracle mediates between the two data types.

Data type mapping overview

Which data type in the Oracle Database is ultimately mapped to which Python data type is in the cx_Oracle Manual (https://cx-oracle.readthedocs.io/en/latest/user_guide/sql_execution.html). It is summarized in # fetch-data-types). Data type exchange between Oracle Database and Python Oracle Database data type ⇔ cx_Oracle data type ⇔ Python data type It will be converted through the flow. Basically, there is a cx_Oracle data type with a name starting with "DB_TYPE_" that corresponds to a certain Oracle Database data type. The cx_Oracle data type used up to cx_Oracle 7.3 (before 8) can still be used as a synonym, but since it will be deprecated in the future, when creating a new application with version 8 or using the cx_Oracle data type. If there are any revisions that need to be made, make sure to use the cx_Oracle datatype that starts with "DB_TYPE_". In addition, data types defined in the DB API will continue to be supported.

■ Data type mapping

■ Data type mapping that conforms to DB API

■ Manual reference DB API compliant cx_Oracle data types (https://cx-oracle.readthedocs.io/en/latest/api_manual/module.html#db-api-types) cx_Oracle-specific data type

It should be noted here that there are two types of Python data types that support the NUMBER type: float and int. This depends on the definition of the NUMBER type and the stored value. Check the execution result of the sample application below.

sample06a.py(Repost)

import cx_Oracle

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL1 = """
        create table sample06a (col1 number, col2 number, col3 number,
          col4 number(5, 0), col5 number(5, 0), col6 number(5, 2),
          col7 number(5, 2), col8 number(5, 2))
"""
SQL2 = "insert into sample06a values(7, 7.0, 7.1, 7, 7.0, 7, 7.0, 7.1)"
SQL3 = "commit"
SQL4 = "select * from sample06a"

with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
        with connection.cursor() as cursor:
                cursor.execute(SQL1)
                cursor.execute(SQL2)
                cursor.execute(SQL3)
                row = cursor.execute(SQL4).fetchone()
                print(f""7" for NUMBER: {type(row[0])}")
                print(f""7" in NUMBER.0」 : {type(row[1])}")
                print(f""7" in NUMBER.1」 : {type(row[2])}")
                print(f"NUMBER(5, 0)To "7": {type(row[3])}")
                print(f"NUMBER(5, 0)To "7.0」 : {type(row[4])}")
                print(f"NUMBER(5, 2)To "7": {type(row[5])}")
                print(f"NUMBER(5, 2)To "7.0」 : {type(row[6])}")
                print(f"NUMBER(5, 2)To "7.1」 : {type(row[7])}")

Execution result

$ python sample06a.py
"7" for NUMBER: <class 'int'>
"7" in NUMBER.0」 : <class 'int'>
"7" in NUMBER.1」 : <class 'float'>
NUMBER(5, 0)To "7": <class 'int'>
NUMBER(5, 0)To "7.0」 : <class 'int'>
NUMBER(5, 2)To "7": <class 'float'>
NUMBER(5, 2)To "7.0」 : <class 'float'>
NUMBER(5, 2)To "7.1」 : <class 'float'>

From the execution result, the following rules can be seen.

--In the case of inaccurate NUMBER type, float type if there is a decimal number other than 0, int type otherwise --Int type is always used when the decimal part is 0 NUMBER type. --Always a float type if the decimal part is a non-zero NUMBER type

There is no particular problem with the int type, but the problem is the float type. In the case of business applications where Oracle Database is often used, there is a concern that rounding errors may occur and cause problems, especially when it comes to information about money and floating point numbers. In such cases, Python uses the decimal module to handle it, but as shown in the table above, cx_Oracle itself does not convert to decimal. However, cx_Oracle provides for such cases.

outputtypehandler If you don't want to use cx_Oracle's default data type conversion specification for the reasons mentioned above, you can specify your own data conversion function in the outputtypehandler attribute of the Connection object to convert that function instead of the original conversion rule. You will be using it. In the case of Python → Oracle direction, it will be the inputtypehandler attribute.

sample09a.py

import cx_Oracle
import decimal

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = "select * from sample06a"

def num2Dec(cursor, name, defaultType, size, precision, scale):
        if defaultType == cx_Oracle.DB_TYPE_NUMBER:
                return cursor.var(decimal.Decimal, arraysize=cursor.arraysize)

with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
        with connection.cursor() as cursor:
                row = cursor.execute(SQL).fetchone()
                print(f"No OutputTypeHandler")
                print(f"Set "7" to NUMBER and triple: {row[0] * 3}")
                print(f""7" in NUMBER.1 ”is set and tripled: {row[2] * 3}")
        with connection.cursor() as cursor:
                cursor.outputtypehandler = num2Dec
                row = cursor.execute(SQL).fetchone()
                print(f"With OutputTypeHandler")
                print(f"Set "7" to NUMBER and triple: {row[0] * 3}")
                print(f""7" in NUMBER.1 ”is set and tripled: {row[2] * 3}")

Please note that this script SELECTs the table and data created in the previous script. The num2Dec function around the middle of the script is the actual new data conversion routine. On the 5th line from the bottom, set the num2Dec function as the outputtypehandler to make this function work. You can specify any function name and argument name of outputtypehandler, but the argument specifications are defined as follows, and all six are required as arguments even if they are not used in the function.

The var method of the Cursor object in the sample is a method that updates the variable information in the form specified by the argument for the variable in the corresponding column. In the first argument, specify the data type to be changed. Must be specified. The var method itself is a method that is widely used for purposes other than outputtypehandler, and the second and subsequent arguments are optional as a method specification, but for outputtypehandler use, a parameter called arraysize is required, and the arraysize of the Cursor object is set. Must be set.

Execution result

$ python sample09a.py
No output type handler
Set "7" to NUMBER and triple: 21
"7" in NUMBER.1 ”is set and tripled: 21.299999999999997
With output type handler
Set "7" to NUMBER and triple: 21
"7" in NUMBER.1 ”is set and tripled: 21.3

If you go through the outputtypehandler like the execution result, the calculation result will be more expected. Of course, instead of using the outputtypehandler, you can receive it as a Python float type variable and then convert it to decimal, but if you have a large number of supported columns, you can easily code by using the outputtypehandler.