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

Show the actual code that updates the variable and then executes the query. — CL., Dec 26 '13 at 16:27
This is not the same code quoted earlier, and the `-1 days` code is missing. — CL., Dec 28 '13 at 12:22
@CL. This is the exact code that I'm using--previously in the example I'd simplified it down to highlight the most relevant pieces, but you asked for the actual code so I left it unsimplified. As I mentioned in my explanation, I switch the value of the `til_user_tokens_go_stale` parameter between `-4 days` and `-1 days` to check whether the query works as expected, so the `-1 days` code isn't missing, just change the param. — Jeff Widman, Dec 29 '13 at 00:16
What exactly do you mean with "switched"? The code does not do any such thing. — CL., Dec 29 '13 at 22:13

Doug Currie · Answer 1 · 2013-12-26T19:41:28.980

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
>

Thanks Doug for looking into this, but I'm pretty sure the problem lies somewhere within Python's Sqlite3 module, not within sqlite core. I'm also unsure whether the datetime parameterizing for the sqlite3 python module happens within python or within sqlite core itself--the python module docs mention that the parameterizing is compliant with Python's DB-API, so the query might be fully 'built' within python before it ever gets passed to sqlite. — Jeff Widman, Dec 27 '13 at 01:58

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

1 Answers1