[cx_Oracle入門](第17回) 日付型のハンドリング

連載目次

連載：cx_Oracle入門　目次

検証環境

• Oracle Cloud利用
• Oracle Linux 7.7 (VM.Standard2.1)
• Python 3.8
• cx_Oracle 8.0
• Oracle Database 19.5 (ATP, 1OCPU)
• Oracle Instant Client 18.5

事前準備

下記テーブルの作成を実施してください。

sample17.sql

create table sample17(
      col_date date
    , col_ts   timestamp(9)
    , col_tz   timestamp(9) with time zone
);

DATE型の基本ハンドリング

第6回や第9回でも解説していますが、DATE型に対応するPythonの型はdatetime.datetimeになります。
DATE型に関しては、datetime.datetimeと受け渡しさせることで、普通にDBとのやり取りが可能です。
また、datetime.datetimeはマイクロ秒までの値をハンドリングできますが、DATE型は秒までしかハンドリングできないため、秒未満の値は切り捨てられます。
以下サンプルと実行結果です。
SELECT時に1日進めた値を取得して、異なる値を参照していることがわかるようにしています。

sample17a.py

import cx_Oracle
import datetime

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL1 = "insert into sample17(col_date) values(:now)"
SQL2 = "select col_date + 1 from sample17"

sys_date = datetime.datetime.now()
print("APの値 :", sys_date)
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as conn:
    with conn.cursor() as cur:
        cur.execute("truncate table sample17")
        cur.execute(SQL1, [sys_date])
        conn.commit()
        val = cur.execute(SQL2).fetchone()[0]
        print("DBの値 :", val.strftime("%Y-%m-%d %H:%M:%S.%f"))

$ python sample17a.py
APの値 : 2020-09-26 10:48:02.605423
DBの値 : 2020-09-27 10:48:02.000000

TIMESTAMP型の基本ハンドリング

DATE型と同様、TIMESTAMP型も対応するPythonの型はdatetime.datetimeになります。
だたし、INSERTに関してはDATE型に比べて追加コーディングが必要です。
DATE型と同じコーディングだと、DATE型と同様に扱われてしまい、秒未満の値が切り捨てられてしまいます。
現時点では、

1. Prepared Statementを使用する
2. Cursor.setinputsizes()で該当するバインド変数がcx_Oracle.DB_TYPE_TIMESTAMPのサイズであることを指定する
3. SQLを実行する

という記述が必要です。
以下サンプルと実行結果です。
なお、1日進めるSQL文がDATE型と異なりINTERVALを使用しているのは、「+ 1」だとDATE型にキャストされてしまい、秒未満の値が切り捨てられるためです。

sample17b.py

import cx_Oracle
import datetime

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL1 = "insert into sample17(col_ts) values(:now)"
SQL2 = "select col_ts + interval '1' day from sample17"

sys_date = datetime.datetime.now()
print("APの値 :", sys_date)
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as conn:
    with conn.cursor() as cur:
        cur.execute("truncate table sample17")
        cur.prepare(SQL1)
        cur.setinputsizes(now=cx_Oracle.DB_TYPE_TIMESTAMP)
        cur.execute(None, {"now":sys_date})
        conn.commit()
        val = cur.execute(SQL2).fetchone()[0]
        print("DBの値 :", val.strftime("%Y-%m-%d %H:%M:%S.%f"))

$ python sample17b.py
APの値 : 2020-09-26 23:51:27.832640
DBの値 : 2020-09-27 23:51:27.832640

TIMESTAMP WITH TIME ZONE型、TIMESTAMP WITH LOCAL TIME ZONE型の基本ハンドリング

これらのデータ型は、TIMESTAMP型と同様のコーディングでハンドリングできます。
Cursor.setinputsizes()で指定する型を、それぞれcx_Oracle.DB_TYPE_TIMESTAMP_TZ、cx_Oracle.DB_TYPE_TIMESTAMP_LTZにしてください。
ただし、執筆時点ではnativeではなくawareなdatetimeを定義することで更新は行えますが、SELECTの場合、nativeなdatetimeで受けてしまうためにタイムゾーンの情報が落ちてしまいます。マニュアルを確認する限りでは、残念ながらCurosr.var()を使用してもawareなdatetimeで受ける設定はない模様です。
ですのでこれらのデータ型を使用する場合、cx_Oracleによるタイムゾーンの差異の吸収に期待せず、PythonもしくはSQLにてタイムゾーンの差異を意識したコーディング(自分でタイムゾーンの差異を計算する)を行うか、文字列(例えばISO 8601形式)を介してPythonとSQLの間を受け渡すようにしてください。
以下はISO 8601形式の日付文字列を使用した場合のサンプルです。

sample17c.py

import cx_Oracle
import datetime

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL1 = "insert into sample17(col_tz) values(to_utc_timestamp_tz(:now))"
SQL2 = f"select to_char(col_tz + interval '1' day, 'YYYY-MM-DD\"T\"HH24:MI:SS.ff6\"Z\"') from sample17"

sys_date = datetime.datetime.utcnow().replace(tzinfo=datetime.timezone.utc).isoformat()
print("APの値 :", sys_date)
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as conn:
    with conn.cursor() as cur:
        cur.execute("truncate table sample17")
        cur.execute(SQL1, [sys_date])
        conn.commit()
        val = cur.execute(SQL2).fetchone()[0]
        print("DBの値 :", val)

$ python sample17c.py
APの値 : 2020-09-27T07:05:42.948348+00:00
DBの値 : 2020-09-28T07:05:42.948348Z