Unable to match python datetime.date object with sqlite DATE

Question

I have a sqlite database with DATE columns which i am unable to match via my SQL in python. I read the documentation from https://docs.python.org/3/library/sqlite3.html#module-functions-and-constants and tried below:

import sqlite3
import datetime

con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("create table test(d date, ts timestamp)")

today = datetime.date.today()
now = datetime.datetime.now()

cur.execute("insert into test(d, ts) values (?, ?)", (today, now))

cur.execute('select current_date as "d [date]" from test') #No errors
cur.execute('select current_date as "d [date]" from test where current_date = ?', today ) #ValueError: parameters are of unsupported type

r = cur.fetchall()
print(r)

con.close()

Can someone explain why i get "ValueError: parameters are of unsupported type" on the second SQL statement?

John Kugelman · Accepted Answer · 2019-11-04 21:34:33Z

1

If you read the documentation you'll see that execute expects a tuple containing one element for every question mark in the statement.

If you change it to

cur.execute('select current_date as "d [date]" from test where current_date = ?', (today,) )

It will work. Note the , after the item, necessary to avoid it being "optimized" as a single element. Alternative you could use a list:

cur.execute('select current_date as "d [date]" from test where current_date = ?', [today] )

edited Nov 4, 2019 at 21:34

John Kugelman

365k70 gold badges555 silver badges600 bronze badges

answered Nov 4, 2019 at 21:30

ChatterOne

3,5611 gold badge22 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PExplorer Over a year ago

Thanks a lot @ChatterOne. It works like a charm now.

Collectives™ on Stack Overflow

Unable to match python datetime.date object with sqlite DATE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related