CODEWITHSUNDEEP
pythonsqlite
PExplorer
PExplorer

Reputation: 71

Unable to match python datetime.date object with sqlite DATE

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?

Upvotes: 1

Views: 190

Answers (1)

ChatterOne
ChatterOne

Reputation: 3541

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

Upvotes: 1

Related Questions