Series: Introduction to cx_Oracle Table of Contents
--Using Oracle Cloud
Please create the following table.
sample17.sql
create table sample17(
col_date date
, col_ts timestamp(9)
, col_tz timestamp(9) with time zone
);
As explained in 6th and 9th, DATE type The corresponding Python type for is datetime.datetime. For DATE type, you can exchange with DB normally by passing it to datetime.datetime. Also, datetime.datetime can handle values up to microseconds, but the DATE type can only handle values up to seconds, so values less than seconds are truncated. Below are samples and execution results. I get the value advanced by one day at SELECT so that I can see that it refers to a different value.
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 value:", 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 value:", val.strftime("%Y-%m-%d %H:%M:%S.%f"))
$ python sample17a.py
AP value: 2020-09-26 10:48:02.605423
DB value: 2020-09-27 10:48:02.000000
Like the DATE type, the TIMESTAMP type also supports the Python type datetime.datetime. However, INSERT requires additional coding compared to DATE type. If the coding is the same as the DATE type, it will be treated in the same way as the DATE type, and values less than seconds will be truncated. At the moment,
Is required. Below are samples and execution results. Note that the SQL statement that advances one day uses INTERVAL unlike the DATE type because if it is "+1", it will be cast to the DATE type and values less than seconds will be truncated.
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 value:", 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 value:", val.strftime("%Y-%m-%d %H:%M:%S.%f"))
$ python sample17b.py
AP value: 2020-09-26 23:51:27.832640
DB value: 2020-09-27 23:51:27.832640
These data types can be handled with the same coding as the TIMESTAMP type. Set the types specified by Cursor.setinputsizes () to cx_Oracle.DB_TYPE_TIMESTAMP_TZ and cx_Oracle.DB_TYPE_TIMESTAMP_LTZ, respectively. However, at the time of writing, it can be updated by defining an acquire datetime instead of native, but in the case of SELECT, the time zone information will be lost because it will be received at the native datetime. As far as I check the manual, unfortunately it seems that there is no setting to receive with aware datetime even if Curosr.var () is used. Therefore, when using these data types, do not expect cx_Oracle to absorb the time zone difference, and code with Python or SQL conscious of the time zone difference (calculate the time zone difference by yourself). Or try to pass between Python and SQL via a string (eg ISO 8601 format). The following is a sample when using a date string in ISO 8601 format.
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 value:", 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 value:", val)
$ python sample17c.py
AP value: 2020-09-27T07:05:42.948348+00:00
DB value: 2020-09-28T07:05:42.948348Z
Recommended Posts