Some bugs prove extremely hard to squash as this little tale shows.
Look a this bit of code:
import sqlite3 as sqlite
with sqlite.connect("/tmp/testdb.db") as conn:
conn.execute("create table if not exists test ( a default 1)")
conn.execute("insert into test default values")
c = conn.cursor()
c.execute("select count(*) from test")
print(c.fetchone()[0])
c.execute("select * from test where a = ?",(1,))
print(c.fetchone()[0])
c.execute("select * from test where a = ?",('1',))
print(c.fetchone()[0])
All it does is creating a table with a single column called a which has a numerical default of 1.
As expected the first print call produces a positive integer (incremented each time you run this snippet unless you delete /tmp/testdb.db in the mean time.) and the second call to print will select all records where a = 1, and printing the first column of the first record therefore will yield the number 1. Nothing special so far.
However, the final print call will raise an exception, stating that NoneType is not subscriptable because the select statement doesn't match any record. This seems logical as the a column contains numbers whereas in the final print call we select records matching the string '1' and a number is never equal to a string.
But what if we would change the table definition to
conn.execute("create table if not exists test ( a integer default 1)")
Now the final print call will succeed without a problem because sqlite will acknowledge the affinity of the a column and convert the string to an integer before comparing them.
This caused me some severe headaches because some of the queries in a CherryPy application I was working on returned different results although the queries where identical except from the location in the code, with identical arguments even.
But what might look identical to the eye proved to be different on close inspection: the column I checked was declared with an integer affinity and in the piece of code that did a successful query, the argument to compare against was an integer. The faulty code however was in a CherryPy method that received its arguments from the outside (a GET request from a browser) and all incoming arguments in CherryPy are strings!
Moral of the story: even though Sqlite allows you to assign any type of value to any column just like Python variables, be careful when comparing values!