Using Different versions of SQLite 3 in python

SQLite is a really impressive piece of software, and often sadly overlooked.

It's easily accessible in python, and you can work in memory, or with local files.

import sqlite3

con = sqlite3.connect('example.db')

And once you have a connection, you can create tables and query them easily

con.execute('''

            CREATE TABLE IF NOT EXISTS stocks

            (date text, trans text, symbol text, qty real, price real)

            ''')

# Insert a row of data

con.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Save (commit) the changes

con.commit()

You can then query it

con.execute("SELECT * from stocks").fetchall()

In the last couple of years, a bunch of extremely useful features have made it into sqlite3. But if you use python, it's likely the version you have installed is an old one.

sqlite3.version

sqlite3.version_info

The older versions have different options, and some features may not be available that you'd find handy. You can check by querying for compile_options like so:

con.execute('pragma compile_options;').fetchall()

A newer version

If you want the newer versions (and you're on linux) you can install a newer binary

! pip install pysqlite3-binary

This gives you access to new features

import pysqlite3

new_con = pysqlite3.connect('example.newer.sqlite.db')

pysqlite3.__file__

Gotchas

If you call pysqlite3.version you'll see a confusing version number.

pysqlite3.version

If you call pysqlite3.sqlite_version_info you'll see a more reassuring version number

pysqlite3.sqlite_version_info

Once again, you can check by looking for compile options

new_con.execute('pragma compile_options;').fetchall()