[Introduction to cx_Oracle] (Part 6) DB and Python data type mapping

Verification environment

--Using Oracle Cloud

• Oracle Linux 7.7 (VM.Standard2.1)
• Python 3.6
• cx_Oracle 7.3
• 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 summarized in the cx_Oracle manual in tabular form (https://cx-oracle.readthedocs.io/). en / latest / user_guide / sql_execution.html # fetch-data-types). As you can see from the linked table, the data obtained from Oracle Database is once converted to data of cx_Oracle data type and stored in Python data type via cx_Oracle data type. The flow is the reverse for updated data. The following is the correspondence of the main data types. See the above manual for a complete list.

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

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.

sample06b.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.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 output type handler")
                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 output type handler")
                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. By setting the num2Dec function as the outputtypehandler on the 5th line from the bottom, this function will 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 sample06b.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.