The Python sqlite module currently uses some deprecated API [0] of SQLite. These are functions that have a counterpart with _v2 appended to their name.

The SQLite query planner will not use certain optimizations when using the old API. For example, as documented in [1], using a statement with a GLOB or LIKE operator where the pattern is parametrized will only use an appropriate index if sqlite3_prepare_v2() is used instead of sqlite3_prepare().

Following is an example of such a query. When executed, table 'test' is scanned row by row, which requires all data in the table to be loaded from disk.

cursor.execute('create table test(a text)')
cursor.execute('create index test_index on test(a)')
# insert some data here
cursor.execute('select * from test where a glob ?', ['prefix*'])

When the same query is executed in the sqlite3 command line utility, the index 'test_index' is used instead.

sqlite> create table test(a text);
sqlite> create index test_index on test(a);
sqlite> explain query plan select * from test where a glob 'prefix*';
order	from	detail
0	0	TABLE test WITH INDEX test_index

The query in this example is not parametrized as parameters can't be filled in when using the sqlite3 command line utility. This is just to show that the schema and the query allow the index to be used with certain pattern strings.

[0]: http://www.sqlite.org/c3ref/funclist.html
[1]: http://www.sqlite.org/optoverview.html#like_opt

Michael

Yes, the sqlite module uses the old API, and is written to work with older SQLite releases and their respective bugs as well.

Using the new API will mean requiring newer SQLite releases.

If we do this, then this is the chance to remove all the obscure backwards compatibility code with older SQLite releases.