Getting started with sqlite3 in Python

Intro

SQLite is a user friendly, file-based SQL database. It comes bundled with Python and can be used in any of your Python apps without having to install any additional software.

In this post we’ll walk through the sqlite3 module in Python. We’ll create a connection to a SQLite database, add a table, insert data into that table, and read data in that table.

1. Connecting to the database

We can connect to a SQLite database using the Python sqlite3 module. import sqlite3 gives our Python program access to the sqlite3 module. The sqlite3.connect() function returns a Connection object that we will use to interact with the SQLite database held in the file pets.db. The pets.db file is created automatically by sqlite3.connect() if pets.db does not already exist on our computer.

2. Adding data to the database

Now that we have connected to the pets.db SQLite database, we can start inserting and reading data from it. In a SQL database, data is stored in tables. Tables define a set of columns, and contain 0 or more rows with data for each of the defined columns. We will create a table named dogs that tracks a dog's name, breed, and age.

We can create this dogs table in SQLite using the connection we made in Step 1:

cursor = connection.cursor()
cursor.execute("CREATE TABLE dog (name TEXT, breed TEXT, age INTEGER)")

connection.cursor() returns a Cursor object. Cursor objects allow us to send SQL statements to a SQLite database using cursor.execute(). The "CREATE TABLE dog ..." string is a SQL statement that creates a table named dog with the three columns described earlier: name of type TEXT, breed of type TEXT, and age of type INTEGER.

Now that we have created a table, we can insert rows of data into it:

cursor.execute("INSERT INTO dog VALUES ('Buck', 'shepherd', 11)")
cursor.execute("INSERT INTO dog VALUES ('Evi', 'lab', 7)")

We call cursor.execute() two times: once to insert a row for the dog Buck, and once to insert a row for the lab Evi. "INSERT INTO dog VALUES ..." is a SQL statement that allows us to add rows to a table.

In the next section, we will use a SQL SELECT statement to inspect the rows we just inserted into our dog table.

3. Reading data from the database

In Step 2, we added two rows to a SQLite table named dog. We can retrieve those rows using a SELECT SQL statement:

rows = cursor.execute("SELECT name, breed, age FROM dog").fetchall()
print(rows)

The cursor.execute() function runs a SELECT statement to retrieve values for the name, breed, and age columns in the dog table. fetchall() retrieves all the results of the SELECT statement. When we print(rows) we see a list of two tuples. Each tuple has three entries; one entry for each column we selected from the dog table. The two tuples have the data we inserted in Step 2: one tuple for Buck the shepherd, and one tuple for Evi the lab.

If we wanted to retrieve rows in the dog table that match a specific set of criteria, we can use a WHERE clause:

target_dog_name = "Buck"
rows = cursor.execute(
    "SELECT name, breed, age FROM dog WHERE name = ?",
    (target_dog_name,),
).fetchall()
print(rows)

As with the previous example, cursor.execute(<SQL statement>).fetchall() allows us to fetch all the results of a SELECT statement. The WHERE clause in the SELECT statement filters for rows where the value of name is target_dog_name. Notice that we use ? to substitute our target_dog_name variable into the SELECT statement. We expect to only match one row, and indeed we only see the row for Buck the shepherd returned.

Conclusion

The sqlite3 module is a powerful part of the Python standard library. It lets us work with a full feature SQL database without installing any additional software.