I will show you how to handle datetime type (pseudo) in python standard library sqlite3.

There is no so-called date type (datetime type) in SQLite3, From the Python side, you can make SQLite behave as if it has a datetime type.

Code for the time being

With this kind of feeling

import sqlite3
import datetime

#Open the DB. Enable the conforming function / conversion function.
conn = sqlite3.connect(':memory:', 
detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)

# "TIMESTAMP"Use the converter function as it is for "DATETIME"
sqlite3.dbapi2.converters['DATETIME'] = sqlite3.dbapi2.converters['TIMESTAMP']

#Cursor generation
cur = conn.cursor()

#Create a table with a column named datetime
cur.execute("create table mytable(comment text, updated datetime);"

#Try to input the character string expression and datetime in the datetime column respectively.
cur.executemany("insert into mytable(comment, updated) value (?,?)",
        [["text_formated.", "2014-01-02 23:45:00"],
        ["datetime_class.", datetime.datetime(2014,3,4, 12,34,56)]])


ret = cur.execute("select * from mytable;")

for row in ret.fetchall():
  print "'%s'" % row[0], row[1], type(row[1])

##Like this ↓, the column declared as datetime returns the datetime type.
# text_formated. 2014-01-02 23:45:00 <type 'datetime.datetime'>
# datetime_class. 2014-03-04 12:34:56 <type 'datetime.datetime'>

The datetime type looks only when viewed from Python, and the actual data format stored in the SQLite database is "2014-01-02 23:45:00" or "2014-03-04 12:34:56" It's just ** string data **.

In fact, in the python standard library sqlite3 / dbapi2.py, a" converter function "that makes the SQL type name" timestamp "the Python datetime is predefined.

How it works

This automatic conversion behavior is enabled by specifying detect_types = sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES when doingsqlite3.connect ().

The list of converter functions is in sqlite3.dbapi2.converters, so Register the converter function for the already registered type name TIMESTAMP as it is for DATETIME, such as sqlite3.dbapi2.converters ['DATETIME'] = sqlite3.dbapi2.converters ['TIMESTAMP'] To do.

The above only describes datetime.datetime (YYYY, MM, DD, hh, mm, ss) [Python]-> "YYYY-MM-DD hh: mm: ss" [sqlite3]. Then where is the reverse pattern of the above "YYYY-MM-DD hh: mm: ss" [sqlite3]-> datetime.datetime (YYYY, MM, DD, hh, mm, ss) [Python]? I will omit the story. Roughly speaking, the "adapter function" that is input as a character string to SQLite when datetime is thrown is predefined in sqlite3 / dbapi2.py.

For more information, read 11.13.5.4. Default matching and conversion functions --SQLite in the Python Standard Library Reference. is.

How to handle datetime type in python sqlite3

Code for the time being

How it works