検証環境
- 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
はじめに
Oracle DatabaseとPythonでは当然ながらデータ型が異なります。本記事では、cx_Oracleがどのように両者のデータ型の仲立ちを行うのかを解説します。
データ型のマッピングの概要
Oracle Databaseのどのデータ型が、最終的にどのPythonのデータ型にマッピングされるのかは、cx_Oracleのマニュアルにまとめられています。Oracle DatabaseとPythonの間のデータ型のやり取りは、
Oracle Databaseのデータ型 ⇔ cx_Oracleのデータ型 ⇔ Pythonのデータ型
という流れを経て変換されます。基本、あるOracle Databaseのデータ型に対応する、「DB_TYPE_」で始まる名称のcx_Oracleのデータ型が存在します。なお、cx_Oracle 7.3(8の前)まで使用されていたcx_Oracleデータ型はシノニムとして引き続き利用できますが、将来廃止予定のため、バージョン8で新規のアプリケーションを作成する場合や、cx_Oracleデータ型を利用しなければならない改定が入った場合は、「DB_TYPE_」で始まるcx_Oracleデータ型を使用するようにしましょう。また、DB APIで定義されているデータ型も引き続きサポートされます。
■ データ型のマッピング
| Oracle Databaseのデータ型 | cx_Oracleのデータ型 | Pythonのデータ型 |
|---|---|---|
| CHA | cx_Oracle.DB_TYPE_CHAR | str |
| VARCHAR2 | cx_Oracle.DB_TYPE_VARCHAR | str |
| NUMBER | cx_Oracle.DB_TYPE_NUMBER | float もしくは int |
| DATE | cx_Oracle.DB_TYPE_DATE | datetime.datetime |
| TIMESTAMP | cx_Oracle.DB_TYPE_TIMESTAMP | datetime.datetime |
| RAW | cx_Oracle.DB_TYPE_RAW | bytes |
■ DB APIに準拠しているデータ型のマッピング
| Oracle Databaseのデータ型 | cx_Oracleのデータ型 | Pythonのデータ型 |
|---|---|---|
| CHAR, VARCHAR2 | cx_Oracle.STRING | str |
| NUMBER | cx_Oracle.NUMBER | float もしくは int |
| DATE | cx_Oracle.DATETIME | datetime.datetime |
| TIMESTAMP | cx_Oracle.TIMESTAMP | datetime.datetime |
| RAW | cx_Oracle.BINARY | bytes |
■マニュアル参照先
DB APIに準拠しているcx_Oracleデータ型
cx_Oracle独自のデータ型
ここで注意しないといけないのは、NUMBER型に対応したPythonのデータ型に、floatとintの2種類が存在する点です。これは、NUMBER型の定義や格納されている値に依存します。以下のサンプルアプリケーションの実行結果を確認してください。
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"NUMBERに「7」 : {type(row[0])}")
print(f"NUMBERに「7.0」 : {type(row[1])}")
print(f"NUMBERに「7.1」 : {type(row[2])}")
print(f"NUMBER(5, 0)に「7」 : {type(row[3])}")
print(f"NUMBER(5, 0)に「7.0」 : {type(row[4])}")
print(f"NUMBER(5, 2)に「7」 : {type(row[5])}")
print(f"NUMBER(5, 2)に「7.0」 : {type(row[6])}")
print(f"NUMBER(5, 2)に「7.1」 : {type(row[7])}")
$ python sample06a.py
NUMBERに「7」 : <class 'int'>
NUMBERに「7.0」 : <class 'int'>
NUMBERに「7.1」 : <class 'float'>
NUMBER(5, 0)に「7」 : <class 'int'>
NUMBER(5, 0)に「7.0」 : <class 'int'>
NUMBER(5, 2)に「7」 : <class 'float'>
NUMBER(5, 2)に「7.0」 : <class 'float'>
NUMBER(5, 2)に「7.1」 : <class 'float'>
実行結果から、以下の法則が見受けられます。
- 精度なしのNUMBER型の場合、0以外の小数がある場合はfloat型、そうではない場合はint型
- 小数部が0のNUMBER型の場合、常にint型
- 小数部が0ではないNUMBER型の場合、常にfloat型
int型の場合は特段問題ありませんが、問題はfloat型です。Oracle Databaseがよく利用されるビジネスアプリケーションの場合、特にお金に関する情報で浮動小数だと丸め誤差が発生して問題となる可能性が懸念されます。そのような場合、Pythonではdecimalモジュールを使用して処理しますが、前述の表のとおり、cx_Oracle自身はdecimalへの変換を行いません。ただし、cx_Oracleはこのような場合の備えを用意しています。
outputtypehandler
先述のような理由で、cx_Oracleのデフォルトのデータ型の変換仕様を利用したくない場合、Connectionオブジェクトのoutputtypehandler属性に自作のデータ変換関数を指定すると、オリジナルの変換ルールではなく、その関数を変換に使用するようになります。Python→Oracleの方向の場合はinputtypehandler属性となります。
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"OutputTypeHandlerなし")
print(f"NUMBERに「7」をセットして3倍 : {row[0] * 3}")
print(f"NUMBERに「7.1」をセットして3倍 : {row[2] * 3}")
with connection.cursor() as cursor:
cursor.outputtypehandler = num2Dec
row = cursor.execute(SQL).fetchone()
print(f"OutputTypeHandlerあり")
print(f"NUMBERに「7」をセットして3倍 : {row[0] * 3}")
print(f"NUMBERに「7.1」をセットして3倍 : {row[2] * 3}")
このスクリプトは先のスクリプトで作成したテーブルとデータをSELECTしている点、ご注意ください。
スクリプトの真ん中あたりのnum2Dec関数が実際の新しいデータ変換ルーチンとなります。下から5行目にて、outputtypehandlerとしてnum2Dec関数をセットすることで、この関数が機能するようになります。
outputtypehandlerの関数名や引数名は任意のものを指定できますが、引数の仕様は以下のとおりに定められており、関数内で使用しなくとも、6つとも引数として必要です。
| 引数の順番 | 意味 |
|---|---|
| 1 | 操作対象のCursorオブジェクト |
| 2 | 列名 |
| 3 | 列のcx_Oracleのデータ型 |
| 4 | 列のサイズ |
| 5 | 列の小数桁数(NUMBER(p,s)のs) |
| 6 | 列の全体桁数(NUMBER(p,s)のp) |
サンプル中のCursorオブジェクトのvarメソッドは、該当する列の変数に関して、変数の情報を引数で指定した形に更新するメソッドとなります。1個目の引数には変更先のデータ型を指定します。指定必須です。varメソッド自体はoutputtypehandler以外の用途にも広く使われるメソッドで、メソッドの仕様としては2個目以降の引数は任意指定ですが、outputtypehandler用途の場合、arraysizeというパラメータが必須となり、Cursorオブジェクトのarraysizeを設定する必要があります。
$ python sample09a.py
outputtypehandlerなし
NUMBERに「7」をセットして3倍 : 21
NUMBERに「7.1」をセットして3倍 : 21.299999999999997
outputtypehandlerあり
NUMBERに「7」をセットして3倍 : 21
NUMBERに「7.1」をセットして3倍 : 21.3
実行結果のように、outputtypehandlerを経由させると、より期待する計算結果になっています。outputtypehandlerを使用せず、一旦Pythonのfloat型の変数で受けてからdecimalに変換する形でももちろん構いませんが、対応列数が多い場合などはoutputtypehandlerを使用すると楽にコーディングできます。