How to Read Datetime Back from SQLite as a Datetime Instead of String in Python

How to read datetime back from sqlite as a datetime instead of string in Python?

If you declare your column with a type of timestamp, you're in clover:

>>> db = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES)
>>> c = db.cursor()
>>> c.execute('create table foo (bar integer, baz timestamp)')
<sqlite3.Cursor object at 0x40fc50>
>>> c.execute('insert into foo values(?, ?)', (23, datetime.datetime.now()))
<sqlite3.Cursor object at 0x40fc50>
>>> c.execute('select * from foo')
<sqlite3.Cursor object at 0x40fc50>
>>> c.fetchall()
[(23, datetime.datetime(2009, 12, 1, 19, 31, 1, 40113))]

See? both int (for a column declared integer) and datetime (for a column declared timestamp) survive the round-trip with the type intact.

sqlite3 returns byte string for datetime

There are two incorrectnesses in your snippet: The correct datatype for sqlite3 column is timestamp, not datetime. Sqlite3 is, well, peculiar, it will just accept anything and store everything internally as strings - it is the Python driver that will treat the column type "timestamp" differently, and able to accept and retrieve datetime objects directly.

That said, the "df.to_record" call does not help either, as it retrieves the pandas datetimes as strings. The correct thing is to retrieve each pandas cell value and call .to_pydatetime() on it.

So, if you need to convert from another, not given in the example, dataframe to sqlite, this sort of code works:

import sqlite3
import pandas as pd
from datetime import datetime

batch_size = 10

con = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
cur = con.cursor()

table = """CREATE TABLE my_table(DateCreated timestamp)"""
cur.execute(table)

DateCreated = [datetime(2020, 12, 1, 1, 1)] * batch_size
x = [(rec.to_pydatetime(),)  for rec in  pd.DataFrame({'DateCreated':DateCreated})["DateCreated"]]

query = """INSERT INTO my_table (DateCreated) values (?)"""
cur.executemany(query, x)

the way I wrote the x = expression above will allow you to select the desired columns from your dataframe, and call .to_pydatetime() only in the desired fields.

If your dataframe have 5 other colimns, and datetime is on the 3rd [index 2], this works:

x = [(*rec[0:2], rec[2].to_pydatetime(), *rec[3:])  for _, rec in df.iterrows()]

And, if you are not converting things from a pandas workflow at all, there is no need to meddle with it, just create datetime objects as tuples and insert them:

x = [(datetime.now(),) for i in range(10)] 

Reading back a datetime in sqlite3

You have to set detect_types to sqlite.PARSE_COLNAMES and use as "foo [timestamp]" like this:

import sqlite3
import datetime

db = sqlite3.connect(':memory:', detect_types = sqlite3.PARSE_COLNAMES)
c = db.cursor()
c.execute('create table foo (bar integer, baz timestamp)')
c.execute('insert into foo values(?, ?)', (23, datetime.datetime.now()))
c.execute('insert into foo values(?, ?)', (42, datetime.datetime.now() + datetime.timedelta(-1)))
c.execute('select bar, baz as "ts [timestamp]" from foo')
print c.fetchall()
c.execute('select max(baz) as "ts [timestamp]" from foo')
print c.fetchall()

Did a nice little Google search and found this message.

How to write date and time from string to Sqlite table?

date time format does not exist in sqlite, one work around is to use number sequence as date time for example:

def Strtime2Num(result):
    date_object = datetime.strptime(Strtime,"%Y-%m-%dT%H:%M%S+0800")
    Strtime = date_object.strftime('%Y%m%d%H%M')
    return int(result)

when select in sql command, you can use

con.execute(UPDATE YearUpdateCapa SET %s = %d WHERE DateTime >= %d AND DateTime <= %d' % (name, avail,start,end))

SQLite date storage and conversion

If you set detect_types=sqlite3.PARSE_DECLTYPES in sqlite3.connect,
then the connection will try to convert sqlite data types to Python data types
when you draw data out of the database.

This is a very good thing since its much nicer to work with datetime objects than
random date-like strings which you then have to parse with
datetime.datetime.strptime or dateutil.parser.parse.

Unfortunately, using detect_types does not stop sqlite from accepting
strings as DATE data, but you will get an error when you try to
draw the data out of the database (if it was inserted in some format other than YYYY-MM-DD)
because the connection will fail to convert it to a datetime.date object:

conn=sqlite3.connect(':memory:',detect_types=sqlite3.PARSE_DECLTYPES) 
cur=conn.cursor()
cur.execute('CREATE TABLE foo(bar DATE)')
# Unfortunately, this is still accepted by sqlite
cur.execute("INSERT INTO foo(bar) VALUES (?)",('25/06/2003',))

# But you won't be able to draw the data out later because parsing will fail
try:
    cur.execute("SELECT * FROM foo")
except ValueError as err:
    print(err)
    # invalid literal for int() with base 10: '25/06/2003'
    conn.rollback()

But at least the error will alert you to the fact that you've inserted
a string for a DATE when you really should be inserting datetime.date objects:

cur.execute("INSERT INTO foo(bar) VALUES (?)",(datetime.date(2003,6,25),))
cur.execute("SELECT ALL * FROM foo")
data=cur.fetchall()
data=zip(*data)[0]
print(data)
# (datetime.date(2003, 6, 25),)

You may also insert strings as DATE data as long as you use the YYYY-MM-DD format. Notice that although you inserted a string, it comes back out as a datetime.date object:

cur.execute("INSERT INTO foo(bar) VALUES (?)",('2003-06-25',))
cur.execute("SELECT ALL * FROM foo")
data=cur.fetchall()
data=zip(*data)[0]
print(data)
# (datetime.date(2003, 6, 25), datetime.date(2003, 6, 25))

So if you are disciplined about inserting only datetime.date objects into the DATE field, then you'll have no problems later when drawing the data out.

If your users are input-ing date data in various formats, check out dateutil.parser.parse. It may be able to help you convert those various strings into datetime.datetime objects.

How can I export to sqlite (or another format) and retain the date datatype?

As shown here (w/ sqlite writing), here (reading back from sqlite), the solution could be by creating the column type in sqlite as a datetime, so that when reading back, python will convert automatically to the datetime type.

Mind that, when you are connecting to the database, you need to give the parameter detect_types=sqlite3.PARSE_DECLTYPES

Appending Pandas dataframes with time to SQLite3 databases & back

Issue derives from pandas' to_records() which is converting your datetime field into an ISO timestamp with T separator:

print(df.to_records(index=False))
# [(3, 0.141, 'five-nine', '2018-03-07T20:40:39.808427000')
#  (1, 0.41 , 'four-two', '2018-03-07T20:40:39.808427000')]

Consider converting datetime column to string and then run cursor executemany():

df.D = df.D.astype('str')

print(df.to_records(index=False))
# [(3, 0.141, 'five-nine', '2018-03-07 20:40:39.808427')
#  (1, 0.41 , 'four-two', '2018-03-07 20:40:39.808427')]

Altogether:

db = sqlite3.connect(':memory:')
c = db.cursor()
c.execute('create table if not exists ABCD ( A integer, B real, C text, D timestamp );')
c.execute('insert into ABCD (A,B,C, D) values (?,?,?,?);',(1,2.2,'4',nowtime))

df['D'] = df['D'].astype('str')
c.executemany('insert into ABCD (A,B,C, D) values (?,?,?,?);',df.to_records(index=False))

db.commit()
print(pd.read_sql('select * from ABCD;',db))

#    A      B          C                           D
# 0  1  2.200          4  2018-03-07 20:47:15.031130
# 1  3  0.141  five-nine  2018-03-07 20:47:15.031130
# 2  1  0.410   four-two  2018-03-07 20:47:15.031130

Python's sqlite3 library sometimes returns naive datetimes and sometimes aware datetimes for the same column

Django ships with a built-in SQLite converter for TIMESTAMP columns that overrides the built-in Python converter, and, unlike the built-in one, return aware datetimes if the database column contains a time zone.

Since the converter is registered globally, it applies regardless of whether you are using the Django ORM or not.

To override Django's converter, you can call sqlite3.register_converter in the ready method of one of your apps, e.g.:

from django.apps import AppConfig

class MyAppConfig(AppConfig):
    name = "myapp"

    def ready(self):
        sqlite3.register_converter("TIMESTAMP", my_converter)

Python's sqlite3 doesn't expose its naive timestamp converter as a public method, but it is short enough that you can copy-paste it from the source.

Alternatively, you can register a time zone-aware converter in your non-Django code, e.g. using datetime.datetime.fromisoformat.

The converter is registered in the Django codebase here, and the original ticket with some discussion is available here.

---

Related Topics