Python is pretty great, and SQLite is pretty great too. Can they be pretty great together? Yes!

This will be a short tutorial on how to do it with sqlite3, which is part of the Python Standard Library. I’ll provide a lightning introduction to general usage, and how to create a trigger that will do automatic logging for you.

Let’s get started by importing sqlite3:

import sqlite3

One nice thing is that if you try to connect to a database that doesn’t exist, sqlite3 will create it.

If this is not a nice thing to you – perhaps because you want to rely on Python throwing an error if it doesn’t find the file – you could first check if the database file exists using os:

import os.path
if not os.path.isfile('mydb.sqlite'):
    print('Where is it?')

Let’s create a new database file by trying to connect to it:

connection = sqlite3.connect('mydb.sqlite')
c = connection.cursor()

I didn’t know what a cursor was until I started working with sqlite3. You can read a simple explanation at the Wikipedia page.

sqlite3 created the database file for us. Now let’s create two tables:

c.execute('CREATE TABLE bikes (id INTEGER PRIMARY KEY, brand TEXT, model TEXT, size INTEGER)')
c.execute('CREATE TABLE bike_logs (bike_id INTEGER, time_added TIMESTAMP)')
<sqlite3.Cursor at 0x10f948810>

We created two tables. bikes is a table that will store bike id, brand, model, and frame size, and bike_logs is a logging table that we will use to record timestamps of when each bike was added.

The second table is an excuse to demonstrate how to create a trigger with sqlite3. The trigger is executed whenever a row is added to the bikes table, and its job is to add a row of the bike id and timestamp to the bike_logs table.

c.execute('''CREATE TRIGGER bike_logger
             AFTER INSERT ON bikes
             BEGIN
                 INSERT INTO bike_logs (bike_id, time_added) VALUES (new.id, strftime('%s', 'now'));
             END
             ;
             ''')
<sqlite3.Cursor at 0x10f948810>

Learning how to create the trigger using sqlite3 took me a long time. I think SQLite is particular about the placement of the semicolons ;. Based on my trial and error, the ; at the end of INSERT INTO... as well as the ; after END is crucial.

Now let’s add a couple of bikes:

c.execute("INSERT INTO bikes (brand, model, size) VALUES ('State Bicycle', 'Contender', 52 )")
c.execute("INSERT INTO bikes (brand, model, size) VALUES ('Ritte', 'Vlaandren', 51)")
connection.commit()

… and check to see if everything worked:

c.execute('SELECT * FROM bikes')
for bike in c.fetchall():
    print(bike)
(1, 'State Bicycle', 'Contender', 52)
(2, 'Ritte', 'Vlaandren', 51)

Good. The Contender and Vlaandren are there in the bikes table, and they were automatically given ids 1 and 2.

What about the log?

c.execute('SELECT * FROM bike_logs')
for log in c.fetchall():
    print(log)
(1, 1468376023)
(2, 1468376023)

Nice! The bike ids are there, along with the unix epoch timestamp of when the rows were added.

Before we leave, we’ll close the connection.

connection.close()

Closing note

I’m pretty new to SQL. If you find any mistakes or bad practices in my code, please leave a comment.

See also: