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

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy does Python's Sqlite3 module correctly parse the first instance of this parameter substitution and afterwards not? Caching?
    primarykey
    data
    text
    <p>I have a Sqlite3 table that has a <code>LastUpdated</code> column containing UTC datetimes formatted as <code>"2013-12-24 07:11:21"</code>, and all the rows int that table were updated 2 days ago. </p> <p>I want to write a <code>SELECT</code> statement to return only rows that haven't been updated in a while:</p> <pre><code>SELECT LastUpdated FROM UserToken WHERE DATETIME(LastUpdated) &lt; DATETIME('now', '-4 days'); </code></pre> <p>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 <a href="http://docs.python.org/2/library/sqlite3.html" rel="nofollow">sqlite3 standard lib documentation</a> to use a variable <code>stale_delta_parameter</code>:</p> <p><code>dbcursor.execute("SELECT LastUpdated FROM UserToken WHERE TokenValid = 1 AND DATETIME(LastUpdated) &lt; DATETIME('now', ?)", (stale_delta_parameter,))</code></p> <p>The first time I ran it, I set <code>stale_delta_parameter = '-4 days'</code> and it correctly returned zero rows. Then I changed the value of <code>stale_delta_parameter</code> to '-1 days' and ran the query. Instead of the expected result of all rows, the query continued to return 0 rows. </p> <p>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 <code>'-4 days'</code> which should return no rows and query2 at <code>'-1 days'</code> 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. </p> <p>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. </p> <p>Then I tried expanding the variable <code>stale_delta_parameter</code> from <code>'-n days'</code> to <code>DATETIME('now', '-n days')</code> just in case the parameter substitution wasn't working correctly inside Sqlite's special <code>DATETIME()</code> function. The weird behavior didn't change.</p> <p>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?</p> <p>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. </p> <p>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 <code>dbcursor.execute(...)</code>. </p> <p>Here is the actual code:</p> <pre><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) &lt; ? ''', (stale_delta_parameter,)) all_tokens = dbcursor.fetchall() print len(all_tokens) # For debugging, shows me how many rows are returned </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJeff Widman
    1. USJeff Widman
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COShow the actual code that updates the variable and then executes the query.
      singulars
      1. POWhy does Python's Sqlite3 module correctly parse the first instance of this parameter substitution and afterwards not? Caching?
      1. USCL.
    2. COAdded the actual code at the bottom @CL.
      singulars
      1. POWhy does Python's Sqlite3 module correctly parse the first instance of this parameter substitution and afterwards not? Caching?
      1. USJeff Widman
    3. COThis is not the same code quoted earlier, and the `-1 days` code is missing.
      singulars
      1. POWhy does Python's Sqlite3 module correctly parse the first instance of this parameter substitution and afterwards not? Caching?
      1. USCL.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload