78

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

Sᴀᴍ Onᴇᴌᴀ
  • 7,890
  • 8
  • 30
  • 58
Tim McDonald
  • 1,182
  • 1
  • 9
  • 13

2 Answers2

126

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.

Alex Flint
  • 5,281
  • 7
  • 34
  • 73
  • 2
    **parameters are of unsupported type** does mean as example that you did pass an integer to sql query (wich is a string at all) as user2605884 said, doing the same thing than when you try to concatenate int and str. The comma omission on parameters simply fails silently. It's true that without the comma (,) in parameters will not work, but it's not the answer, really. Please anyone double check that because i think this answer is wrong and poeple simply vote it by reading, not testing. – m3nda Oct 29 '15 at 16:14
  • 2
    @erm3nda I just had this same issue, and that silly extra comma was what fixed it for me – jfox Jan 28 '16 at 01:25
  • 1
    @jfox thank you for verification :) silly commas are now part of my fixes list. – m3nda Jan 28 '16 at 10:36
  • you can just specify a list `cur.execute('insert into table (column) values (?)', [temp])`, that works too and is more readable IMO – wesinat0r Jul 06 '20 at 15:01
-5

changing that line with this

cur.execute('insert into new_test (curent_dt) values (?)',str(temp))
user2605884
  • 1
  • 2