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
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
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
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,
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
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
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
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
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.
I’m pretty new to SQL. If you find any mistakes or bad practices in my code, please leave a comment.
- A thorough guide to SQLite database operations in Python. This is a great introductory post to using
sqlite3. It covers a lot of ground. The only missing point is creating triggers, which is why I wrote my post.