[Introduction to cx_Oracle] (17th) Date type handling

Serial table of contents

Series: Introduction to cx_Oracle Table of Contents

Verification environment

--Using Oracle Cloud

Advance preparation

Please create the following table.

sample17.sql


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

DATE type basic handling

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

TIMESTAMP type basic handling

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,

  1. Use Prepared Statement
  2. Cursor.setinputsizes () specifies that the corresponding bind variable is the size of cx_Oracle.DB_TYPE_TIMESTAMP
  3. Execute SQL

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

Basic handling of TIMESTAMP WITH TIME ZONE type and TIMESTAMP WITH LOCAL TIME ZONE type

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

[Introduction to cx_Oracle] (17th) Date type handling
[Introduction to cx_Oracle] (5th) Handling of Japanese data
[Introduction to cx_Oracle] (8th) cx_Oracle 8.0 release
[Introduction to cx_Oracle] (Part 7) Handling of bind variables
[Introduction to cx_Oracle] Overview of cx_Oracle
Series: Introduction to cx_Oracle Contents
[Introduction to cx_Oracle] (Part 6) DB and Python data type mapping
Practice! !! Introduction to Python (Type Hints)
[Introduction to Udemy Python3 + Application] 28. Collective type
[Introduction to Udemy Python3 + Application] 21. Tuple type
[Introduction to Udemy Python3 + Application] 65. Exception handling
[Introduction to Udemy Python3 + Application] 24. Dictionary type
[Introduction to Udemy Python3 + Application] 16. List type
Introduction to MQTT (Introduction)
Introduction to Scrapy (1)
Introduction to Scrapy (3)
Introduction to Supervisor
Introduction to Tkinter 1: Introduction
[Introduction to cx_Oracle] (Part 3) Basics of Table Reference
Introduction to PyQt
Introduction to Scrapy (2)
[Linux] Introduction to Linux
[Introduction to cx_Oracle] (15th) Creating a result set in a format other than tuples
Introduction to Scrapy (4)
Introduction to discord.py (2)
Introduction to discord.py
0 Convert unfilled date to datetime type with regular expression
Set the form DateField to type = date in Django
[Introduction to cx_Oracle] (Part 11) Basics of PL / SQL Execution
python> datetime> From date string (ISO format: 2015-12-09 12:40:08) to datetime type
[Introduction to cx_Oracle] (Part 13) Connection using connection pool (client side)
Introduction to Web Scraping
Introduction to Nonparametric Bayes
Introduction to Python language
Introduction to TensorFlow-Image Recognition
Introduction to OpenCV (python)-(2)
Introduction to PyQt4 Part 1
Introduction to Dependency Injection
Introduction to Private Chainer
Introduction to machine learning
[Introduction to cx_Oracle] (Part 4) Fetch and scroll of result set