python - parameter unsupported when inserting int in sqlite

70
votes

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,)

2
pythondatetimesqlite
Possible duplicate of Python tuple trailing comma syntax rule - Ciro Santilli 新疆再教育营六四事件法轮功郝海东
@CiroSantilli六四事件法轮功包卓轩 Albeit this is the answer to the question, I fail to see the duplication. Same solution != same question. - Markus W Mahlberg
@MarkusWMahlberg I think in this kind of case it is better to dupe. Otherwise, we could ask infinitely many questions, one for each API that expects a tuple, and generate infinite question rep. No biggie though of course ;-) - Ciro Santilli 新疆再教育营六四事件法轮功郝海东

2 Answers

110
votes

Note the added comma after "temp" below:

cur.execute('insert into new_test (curent_dt) values (?)', (temp,))

The reason this happens is that (temp) is an integer but (temp,) is a tuple of length one containing temp.

-5
votes

changing that line with this

cur.execute('insert into new_test (curent_dt) values (?)',str(temp))