80

I try storing date and time in SQLite3 with the intention of retrieving the records using comparisons later e.g. SELECT * WHERE date1 < date2. I gave up trying to store datetime.datetime objects and decided to use a UNIX timestamp instead as they are just an int 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 error :

cur.execute('insert into new_test (curent_dt) values (? )', (temp)) ValueError: parameters are of unsupported type

user4157124
  • 2,809
  • 13
  • 27
  • 42
Tim McDonald
  • 1,212
  • 1
  • 10
  • 13
  • 3
    Possible duplicate of [Python tuple trailing comma syntax rule](http://stackoverflow.com/questions/7992559/python-tuple-trailing-comma-syntax-rule) – Ciro Santilli OurBigBook.com Nov 24 '15 at 09:31
  • 5
    @CiroSantilli六四事件法轮功包卓轩 Albeit this is the answer to the question, I fail to see the duplication. Same solution != same question. – Markus W Mahlberg Nov 24 '15 at 12:18
  • 1
    @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 OurBigBook.com Nov 24 '15 at 12:25

3 Answers3

136

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
  • 6,040
  • 8
  • 41
  • 80
  • 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
  • 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
0

your code with error :

temp = int(time.mktime(now.timetuple()))
cur.execute('insert into new_test (curent_dt) values (? )', temp)

your code without error :

temp = [(int(time.mktime(now.timetuple())))]
cur.execute('insert into new_test (curent_dt) values (? )', temp)

my code when it gave me the error :

all_stats_changed = change_health, change_damage, change_defense, change_cooldown

c.executemany("INSERT INTO player_stats VALUES (?, ?, ?, ?)", all_stats_changed)

my code when it works :

all_stats_changed = [(change_health, change_damage, change_defense, change_cooldown)]

c.executemany("INSERT INTO player_stats VALUES (?, ?, ?, ?)", all_stats_changed)

Basically, putting the code in a [()] resolves the issue.

AlexK
  • 2,855
  • 9
  • 16
  • 27
lukekrrish
  • 1
  • 1
-5

changing that line with this

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