Why does Python's Sqlite3 module correctly parse the first instance of this parameter substitution and afterwards not? Caching?

Question

I have a Sqlite3 table that has a LastUpdated column containing UTC datetimes formatted as "2013-12-24 07:11:21", and all the rows int that table were updated 2 days ago.

I want to write a SELECT statement to return only rows that haven't been updated in a while:

SELECT LastUpdated FROM UserToken WHERE DATETIME(LastUpdated) < DATETIME('now', '-4 days');

I'm trying to run this query from Python using the sqlite3 standard lib, and I want the outdated period to be variable. For security, I tried to use the parameter substitution mentioned in the sqlite3 standard lib documentation to use a variable stale_delta_parameter:

dbcursor.execute("SELECT LastUpdated FROM UserToken WHERE TokenValid = 1 AND DATETIME(LastUpdated) < DATETIME('now', ?)", (stale_delta_parameter,))

The first time I ran it, I set stale_delta_parameter = '-4 days' and it correctly returned zero rows. Then I changed the value of stale_delta_parameter to '-1 days' and ran the query. Instead of the expected result of all rows, the query continued to return 0 rows.

When I restart my computer, it seems the query works fine the first time, but again, if I run the python script with one delta value, as soon as I change the delta value, the results continue to be the value from the first instance of running the query. Furthermore, if I write two SELECT statements where query1 is set at '-4 days' which should return no rows and query2 at '-1 days' which should return all rows and then after running the script once, I switch the values on those queries, the output of the queries doesn't switch.

I thought it might be incorrect sql queries, so I tried hardcoding the delta and running the query in the Sqlite3 shell. Works just as I expect each time, so the query isn't wrong.

Then I tried expanding the variable stale_delta_parameter from '-n days' to DATETIME('now', '-n days') just in case the parameter substitution wasn't working correctly inside Sqlite's special DATETIME() function. The weird behavior didn't change.

Is there some kind of caching happening within the Sqlite3 standard lib or within the Python DB-API that might prevent updating the query passed to the underlying db?

There's nothing I can find in the docs, but it's the only theory that I can come up with that seems to fit this behavior.

I tried to find a way to print the assembled query that gets passed to the db from Python, so I can verify that the db isn't getting an updated version of the query, but I can't find any kind of method to print the assembled query from dbcursor.execute(...).

Here is the actual code:

til_user_tokens_go_stale = '4 days'
stale_delta_parameter = "DATETIME('now','-%s')" % til_user_tokens_go_stale  
dbcursor.execute('''
                 SELECT UserToken, UserID, AppID 
                 FROM UserToken 
                 WHERE TokenValid = 1 AND DATETIME(LastUpdated) < ?
                 ''', (stale_delta_parameter,))
all_tokens = dbcursor.fetchall()
print len(all_tokens) # For debugging, shows me how many rows are returned

Answer 1

From the SQLite documentation:

A "variable" or "parameter" token specifies a placeholder in the expression for a value that is filled in at runtime using the sqlite3_bind() family of C/C++ interfaces.

Those interfaces include basic types (strings, numbers, blobs) but not functions like datetime.

One way to accomplish what you're after is to provide an offset in unixepoch or juliandays, and reformat the query to do an arithmetic subtraction.

However, the first parameterized version you showed should work; it does in Lua:

> db = sqlite3.open(':memory:')
> db:execute'create table t (d);'
> db:execute"insert into t values ('2013-12-26 14:20:00');"
> st = db:prepare "select * from t where d < datetime('now',?);"
> st:bind(1,'+1 day');
> for row in st:urows() do print (row) end
2013-12-26 14:20:00
> st:bind(1,'-1 day');
> for row in st:urows() do print (row) end
>