Inserting datetime into a SQLite Database

Question

I am trying to insert the time into my database but when I print what I inserted it isn't correct. I printed the time variable before inserting it into the database and it is "12:01:09.149059". It was working fine when I was using strftime but I switched because the time was off.

from datetime import datetime
time = str(datetime.now().time())
cur.execute("insert into apple values (?,?,?)",(price,time,date))
con.commit()

#prints:
>>>('132.75', "<method 'time' of 'datetime.datetime' objects>", 'Apr 27, 2015')

Answer 1

sqlite3 module already provides adapters for datetime.datetime objects. You could pass datetime.datetime, datetime.date objects as is:

>>> import sqlite3
>>> from datetime import datetime, timezone
>>> db = sqlite3.connect(':memory:')
>>> db.execute('select ?', (datetime.now(timezone.utc),)).fetchone()
('2015-04-27 20:22:26.951841+00:00',)

You could define your own adapters if necessary e.g., to pass datetime.time objects:

>>> from datetime import time as datetime_time
>>> def adapt_time(t):
...     return str(t)
... 
>>> sqlite3.register_adapter(datetime_time, adapt_time)
>>> db = sqlite3.connect(':memory:')
>>> db.execute('select ?', (datetime.now(timezone.utc).time(),)).fetchone()
('20:24:00.867221',)

Store date and time together (just pass datetime object without any conversions). Use UTC time or aware datetime objects.

Answer 2

You are trying to convert a Python method object into a string, which is defaulting to its representation, which you don't want. Try this instead:

from datetime import datetime
time = datetime.now().strftime("%B %d, %Y %I:%M%p")
cur.execute("insert into apple values (?,?,?)",(price,time,date))
con.commit()

#prints:
>>>('132.75', "Apr 27, 2015 09:38AM", 'Apr 27, 2015')