I have been going around and around with storing date and time in SQLite3 with the intention of retrieving the records using comparisons later e.g. SELECT * WHERE date1 < date2
I finally gave up trying to store datetime.datetime objects and decided to use a UNIX timestamp instead as they are just an int and easy to manipulate but I am still getting errors.
import sqlite3 as lite
import datetime
import time
conn = lite.connect('dispatcher.db')
cur = conn.cursor()
query = "create table if not exists new_test (curent_dt)"
cur.execute(query)
conn.commit()
now = datetime.datetime.now() - datetime.timedelta(minutes=60)
temp = int(time.mktime(now.timetuple()))
cur.execute('insert into new_test (curent_dt) values (? )', (temp))
conn.commit()
conn.close()
returns the following error:
cur.execute('insert into new_test (curent_dt) values (? )', (temp)) ValueError: parameters are of unsupported type
After investigating the problem a little further I found that you have to use a trailing comma to create a single element tuple e.g. (temp,)
changing that line with this
Note the added comma after "temp" below:
The reason this happens is that
(temp)
is an integer but(temp,)
is a tuple of length one containingtemp
.Replace this code with
In Python, (temp) and (temp,) are two different things. (temp) is just a normal variable like integer but (temp,) is a tuple. Tuple is only taken by providing commas after each element and the execute() method takes only tuples in its second parameter.