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

Verification environment

--Using Oracle Cloud

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

Oracle Database data type cx_Oracle data type Python data type
CHA cx_Oracle.DB_TYPE_CHAR str
VARCHAR2 cx_Oracle.DB_TYPE_VARCHAR str
NUMBER cx_Oracle.DB_TYPE_NUMBER float or int
DATE cx_Oracle.DB_TYPE_DATE datetime.datetime
TIMESTAMP cx_Oracle.DB_TYPE_TIMESTAMP datetime.datetime
RAW cx_Oracle.DB_TYPE_RAW bytes

■ Data type mapping that conforms to DB API

Oracle Database data type cx_Oracle data type Python data type
CHAR, VARCHAR2 cx_Oracle.STRING str
NUMBER cx_Oracle.NUMBER float or int
DATE cx_Oracle.DATETIME datetime.datetime
TIMESTAMP cx_Oracle.TIMESTAMP datetime.datetime
RAW cx_Oracle.BINARY bytes

■ 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.

Argument order meaning
1 Cursor object to be operated
2 Column name
3 Column cx_Oracle data type
4 Column size
5 Number of decimal places in a column(NUMBER(p,s)S)
6 Total number of digits in the column(NUMBER(p,s)P)

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.

Recommended Posts

[Introduction to cx_Oracle] (Part 9) DB and Python data type mapping (version 8 or later)
[Introduction to cx_Oracle] (Part 6) DB and Python data type mapping
Solving AOJ's Algorithm and Introduction to Data Structures in Python -Part2-
Solving AOJ's Algorithm and Introduction to Data Structures in Python -Part4-
Solving AOJ's Algorithm and Introduction to Data Structures in Python -Part3-
Introduction to Python (Python version APG4b)
[Introduction to cx_Oracle] (Part 4) Fetch and scroll of result set
[Introduction to Data Scientists] Basics of Python ♬ Functions and classes
[Introduction to Python] Combine Nikkei 225 and NY Dow csv data
Practice! !! Introduction to Python (Type Hints)
[Introduction to Python3 Day 1] Programming and Python
Introduction to Python Hands On Part 1
[AWS SAM] Introduction to Python version
[Introduction to cx_Oracle] (Part 2) Basics of connecting and disconnecting to Oracle Database
[Introduction to Data Scientists] Basics of Python ♬ Conditional branching and loops
[Introduction to Python] How to use the Boolean operator (and ・ or ・ not)
[Introduction to Data Scientists] Basics of Python ♬ Functions and anonymous functions, etc.
[Introduction to Udemy Python3 + Application] 28. Collective type
[Introduction to Python3, Day 17] Chapter 8 Data Destinations (8.1-8.2.5)
[Introduction to Python3, Day 17] Chapter 8 Data Destinations (8.3-8.3.6.1)
Web-WF Python Tornado Part 3 (Introduction to Openpyexcel)
[Introduction to Udemy Python3 + Application] 21. Tuple type
[Introduction to Python3 Day 19] Chapter 8 Data Destinations (8.4-8.5)
[Introduction to cx_Oracle] (12th) DB Exception Handling
[Introduction to cx_Oracle] (17th) Date type handling
[Introduction to Python3 Day 18] Chapter 8 Data Destinations (8.3.6.2 to 8.3.6.3)
[Introduction to Udemy Python3 + Application] 24. Dictionary type
Compress python data and write to sqlite
[Introduction to Udemy Python3 + Application] 16. List type
[Introduction to Data Scientists] Basics of Python ♬
[Introduction to Python3 Day 12] Chapter 6 Objects and Classes (6.3-6.15)
[Introduction to cx_Oracle] (Part 3) Basics of Table Reference
[Python] How to read data from CIFAR-10 and CIFAR-100
[Introduction to Python3 Day 22] Chapter 11 Concurrency and Networking (11.1 to 11.3)
[Introduction to Python] How to handle JSON format data
[Introduction to Udemy Python3 + Application] 64. Namespace and Scope
[Introduction to Python3 Day 11] Chapter 6 Objects and Classes (6.1-6.2)
[Introduction to cx_Oracle] (5th) Handling of Japanese data
[Introduction to cx_Oracle] (Part 7) Handling of bind variables
[Python3] Connection to Oracle Database and SQL execution [cx_Oracle]
Try to operate DB with Python and visualize with d3
Reading Note: An Introduction to Data Analysis with Python
[Introduction to cx_Oracle] (Part 11) Basics of PL / SQL Execution
[Introduction to Udemy Python3 + Application] 68. Import statement and AS
Python # How to check type and type for super beginners
[Introduction to cx_Oracle] (Part 13) Connection using connection pool (client side)
Introduction to Python numpy pandas matplotlib (~ towards B3 ~ part2)
[Technical book] Introduction to data analysis using Python -1 Chapter Introduction-
How to change python version of Notebook in Watson Studio (or Cloud Pak for Data)
An introduction to statistical modeling for data analysis (Midorimoto) reading notes (in Python and Stan)
Introduction to Python language
Introduction to OpenCV (python)-(2)
Introduction to PyQt4 Part 1
I want to be able to analyze data with Python (Part 3)
Update Python for Raspberry Pi to 3.7 or later with pyenv
[Introduction to Data Scientists] Descriptive Statistics and Simple Regression Analysis ♬
I want to be able to analyze data with Python (Part 1)
[Introduction to Udemy Python3 + Application] 42. for statement, break statement, and continue statement
[Introduction to Udemy Python3 + Application] 39. while statement, continue statement and break statement
[Introduction to cx_Oracle] (10th) Update DML and DDL / DCL statements
[Introduction to Udemy Python 3 + Application] 36. How to use In and Not