7.300
SQLite with Python: A Primer

library(reticulate)

Prerequisites

Prior to this lesson, consider working through:

• 70.801 ┆ The SQLite Database

Introduction

This lesson on Python and SQLite is structured to guide you through the process of integrating Python applications with the SQLite database. You will acquire expertise in executing SQLite database operations via Python as well as through embedded SQL code chunks in Posit and Quarto Notebooks.

SQLite, a comprehensive C-language library, is renowned for its SQL database engine that offers impressive speed, serverless architecture, self-sufficiency, and exceptional reliability. It holds the distinction of being the most prevalent database engine in testing environments, as well as on mobile apps. Note that SQLite is not a multi-user database and is file-based rather than server-based like MySQL or SQL Server.

The ubiquity of SQLite extends to its pre-installation on a majority of computers, mobile devices, and web browsers. Python’s built-in sqlite3 module is a critical tool in interacting with SQLite databases.

Significantly, the Python sqlite3 module is in compliance with the Python Database API Specification v2.0, as defined in PEP 249. This specification outlines a standardized SQL interface, aiming to promote uniformity and consistency across Python modules that facilitate database access.

At-a-Glance

Connecting to and accessing data in a SQLite database in Python generally involves these steps:

1. Import sqlite3 module:

The import sqlite3 statement imports the sqlite3 module which is required to access SQLite databases. It contains the classes and methods necessary for communicating with the SQLite database. Note that the sqlite3 module actually contains the code for SQLite and therefore no further installation of SQLite is necessary. This emphasizes the lighweight nature of SQLite and the reason why it is often built into apps and simple single-user applications.

2. Connect to database with connect() method:

Use the connect() method of the connector class with the database file name to connect to the database. If a database with that file name does not exist, it will be created. This method returns the SQLite Connection Object if the connection is successful and that object is needed to access the database.

3. Define access with cursor() method:

Use the cursor() method of a Connection object to create a cursor object to execute SQLite statements including queries.

4. Run SQL with the execute() method:

The execute() methods runs the SQL statement and returns a result (result set; a table).

5. Get result set with fetchall():

Use cursor.fetchall() or fetchone() or fetchmany() to read the query result.

6. Close cursor and connection objects:

Use cursor.close() and connection.close() methods to close the cursor and disconnect from the SQLite database. This is necessary to release all allocated resources and free memory. The database cannot be accessed after this.

Of course, be mindful to catch any database exceptions that may occur during this process.

The code below demonstrates accessing an existing database and retrieving all rows from a single table using a SQL SELECT statement.

import sqlite3

try:
    dbcon = sqlite3.connect('committeeDB.sqlite')
    cursor = dbcon.cursor()
    print("connection successful")

    sql = "SELECT * FROM faculty;"
    cursor.execute(sql)
    
    rs = cursor.fetchall()
    
    print(rs)
    
    cursor.close()

except sqlite3.Error as error:
    print("can't connect", error)
    
finally:
    if sqliteConnection:
        dbcon.close()
        print("connection closed")

Caution: The connection object is not thread-safe; the sqlite3 module does not allow sharing connections between threads. Any synchronization must be done by the application programmer.

Tutorial

Before proceeding with the remainder of the lesson, watch the video by Dr. Martin Schedlbauer and follow along.

FIle used in Tutorial: create-tables.py | sqlite-from-python.py

Create Database

A new SQLite database is created by connecting to a database that does not exist. This forces creation of a new database file. Note that SQLite is not a server-based database management system like MySQL or Oracle, but is a file-based relational database akin to Microsoft Access.

In the example below, the database will be created in the current folder – if the code is in an R Project then the database file will be created in the project folder. If the database is to be created in a different folder, then a path name must be provided.

By convention, SQLite database files generally have an extension of .db, .sqlite, or .sqlitedb.

Start your Python script by importing the sqlite3 module. This module provides an interface for connecting to a SQLite database. The sqlite3 module comes pre-installed with Python, so there’s no need for additional packages.

import sqlite3

To create a new SQLite database, you use the connect method from the sqlite3 module. If the database file specified does not exist, it will be created automatically.

# Connect to a database (or create if it doesn't exist)
conn = sqlite3.connect('newDB.db')

# Create a cursor object using the cursor method
cursor = conn.cursor()

# Close the connection when done
conn.close()

In the above code:

• sqlite3.connect('newDB.db') creates a new SQLite database file named my_database.db in the current working directory. You can specify a different path if you wish to create the database elsewhere.
• The cursor object is essential for executing SQL statements in Python. Think of the cursor as a pointer to the database, allowing you to interact with it.
• Finally, conn.close() closes the connection to the database. It’s important to close the connection when you’re done to free up resources.

Creating an In-Memory Database

SQLite also allows you to create a database in RAM (main memory), which can be faster but is temporary (it’s lost when the program ends). This is useful for testing and scenarios where you need a temporary database such as when you want to run SQL queries agains tabular data loaded from a CSV.

Here’s how you can create an in-memory SQLite database:

# Create a database in RAM
conn = sqlite3.connect(':memory:')
# ... perform operations ...
conn.close()

When ':memory:' is used in place of a database filename, SQLite creates a temporary database in memory.

This section explained how to set up a Python script to work with SQLite databases. We covered how to create a SQLite database file, connect to it, create a cursor object for executing SQL commands, and close the connection. In the following sections, we will explore how to create tables and manipulate data within the SQLite database using this setup.

Creating Tables

After establishing a connection to a SQLite database, the next step – for a new database – is creating tables to store data. Of course, if the database already exists, then there is no need to create new tables. In this section, we’ll learn how to define and create tables within a SQLite database using Python’s sqlite3 module.

Creating a table in SQLite involves using the CREATE TABLE SQL statement. This statement allows you to define the table’s structure, including its column names, data types, and any constraints like primary keys.

CREATE TABLE

The basic syntax for creating a table in SQL is as follows:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    [PRIMARY KEY (column)],
    [FOREIGN KEY REFERENCES]
   ....
);

Primary Keys

A primary key is a unique identifier for each record in a database table. It ensures that no two rows have the same key, enabling efficient data retrieval and management.

Defining a Primary Key

When creating a table, you designate one or more columns as the primary key. In SQLite, a primary key can be a single column or a combination of multiple columns (composite key). Designating a primary key can be done in two ways:

• adding the PRIMARY KEY constraint to a column’s definition
• using the PRIMARY KEY constraint to define which column or combination of columns are the primary key

Example: Single Column Primary Key

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);

Here, id is an integer that serves as the primary key for the users table.

Example: Composite Primary Key

CREATE TABLE user_roles (
    user_id INTEGER,
    role_id INTEGER,
    PRIMARY KEY (user_id, role_id)
);

This creates a composite primary key consisting of user_id and role_id.

Foreign Keys

A foreign key is a column or a group of columns in one table that references the primary key column(s) in another table. It is used to link the data between two tables. SQLite enforces referential integrity which ensures that every foreign key is an actual primary key value in the linked table.

Defining a Foreign Key

The FOREIGN KEY constraint is used to define a foreign key. It establishes a relationship between the foreign key in one table and the primary key in another.

Example: Foreign Key

Imagine we have another table named roles:

CREATE TABLE roles (
    id INTEGER PRIMARY KEY,
    role_name TEXT
);

We can create a foreign key in the user_roles table from above that references the id column in the roles table:

CREATE TABLE user_roles (
    user_id INTEGER,
    role_id INTEGER,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

Here, role_id in the user_roles table is a foreign key that references the id in the roles table.

Enforcing Foreign Key Constraints

SQLite has foreign key constraints disabled by default. To enforce them, you need to enable it in your SQLite session setting the PRAGMA below:

conn = sqlite3.connect('my_database.db')
conn.execute("PRAGMA foreign_keys = ON")

This command ensures that all foreign key constraints are enforced in your database session. Note that the enforcement only applies to subsequent insertions of data and is not checked retroactively, so it is still possible to have referential integrity issues and “dangling references” if insertions were done prior to turning on enforcement.

To summarize, a primary key uniquely identifies each record in a table and can be a single column or a combination of columns, while a foreign key is used to link two tables together, ensuring that the data in one table relates correctly to data in another. Defining these keys correctly is essential for data integrity and efficient, and is a key element of the relational model and relational databases. These links are necessary when executing SQL “joins”.

Data Types in SQLite

SQLite uses a dynamic typing system. Some of the common data types include:

• INTEGER: for integers
• REAL: for floating-point numbers
• TEXT: for text strings
• BLOB: for binary data
• NUMERIC: for any numeric data
• DATE: for dates
• DATETIME: for dates and times

Example: Creating a Table

Let’s create a simple table in Python named users with three columns: id, name, and email with id as the primary key.

# connect to the SQLite database
conn = sqlite3.connect('userDB.sqlitedb')
cursor = conn.cursor()

# create table with 3 columns
cursor.execute('''
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT NOT NULL UNIQUE
    )
''')

# commit the changes
conn.commit()

# close database connection
conn.close()

Ensuring Table Creation

Sometimes, you might run the script multiple times, and attempting to create an already existing table will result in an error. To prevent this, you can use IF NOT EXISTS in your CREATE TABLE statement:

cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT NOT NULL UNIQUE
    )
''')

This will only create the table if the table does not exist. If you wish to alter the table definition it is necessary to first delete the table from the database (which will also delete any data in the table, so caution is advised). Deleting a table is done with the DROP statement as shown below.

cursor.execute('''
    DROP TABLE IF EXISTS users
''')

In this section we learned how to create tables in a SQLite database using Python. We covered the SQL syntax for creating tables, demonstrated how to create primary and foreign keys, discussed SQLite data types, and walked through an example of creating a simple users table. The ability to create tables is foundational for storing and organizing data effectively in a database. In the next sections, we will explore how to insert, retrieve, update, and delete data in these tables.

Inserting Data into Tables

Having created our tables, we now turn to populating them with data. Inserting data into SQLite tables using Python involves the INSERT INTO SQL statement. Let’s explore how to insert data into tables, handle exceptions, and ensure data integrity through Python’s sqlite3 module.

Basic Insert Statement

The INSERT INTO statement is used to add new rows of data to a table.

Syntax

INSERT INTO table_name (column1, column2, column3, ...)
     VALUES (value1, value2, value3, ...);

Example: Inserting Data into Users Table

Let’s insert a new user into the users table we created earlier.

# Assuming connection (conn) and cursor (cursor) are already set up

# Insert a new user
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("John Doe", "john@example.com"))

# Commit the changes
conn.commit()

In this example, we use ? as placeholders for our values in the INSERT INTO statement. This is a parameterized query that helps prevent SQL injection attacks, a common web security vulnerability. The values for the placeholders are provided as a tuple (“John Doe”, “john@example.com”).

Handling Exceptions

When working with databases, it’s important to handle exceptions that may occur during data insertion, such as inserting data that violates primary key or foreign key constraints.

try:
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Jane Doe", "jane@example.com"))
    conn.commit()
except sqlite3.IntegrityError:
    print("Couldn't insert data due to an integrity error.")

In this code snippet, we catch sqlite3.IntegrityError exceptions, which may occur due to constraint violations.

Bulk Insertion

If you have multiple records to insert, you can use executemany to perform bulk insertions efficiently.

users_to_insert = [("Alice", "alice@example.com"), ("Bob", "bob@example.com")]
cursor.executemany("INSERT INTO users (name, email) VALUES (?, ?)", users_to_insert)
conn.commit()

This method is more efficient than inserting each row in a separate transaction, especially when dealing with a large volume of data.

Auto-Incrementing Primary Keys

If a column in a table is defined as INTEGER PRIMARY KEY, SQLite treats it as an “auto-increment” field. This means you don’t need to specify a value for this column; SQLite will automatically assign a unique value by incrementing some counter. Do not assume that the primary key values are sequential.

Retrieving Data with SELECT

Once data is stored in a SQLite database, the next critical operation is retrieving it. The SELECT SQL statement is used for this purpose. In this section, we will explore how to use SELECT to retrieve data from a table in SQLite, filter it with conditions, and handle the results in Python. This is only an overview to demonstrate how to execute queries from Python. Consult additional lessons on SQL for more details on SQL SELECT.

Basic SELECT Statement

The SELECT statement is used to select data from a database table.

Syntax

SELECT column1, column2, ... FROM table_name;

To select all columns from a table, use *.

Example: Selecting Data

Here’s how to select all data from the users table:

cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

• cursor.fetchall() retrieves all rows of the query result, returning them as a list of tuples
• the loop then prints each row

Selecting Specific Columns

To select only specific columns:

cursor.execute("SELECT name, email FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

Filtering Results with WHERE

You can filter query results using the WHERE clause.

Example: Filtering Data

To find users with a specific name:

cursor.execute("SELECT * FROM users WHERE name = ?", ("John Doe",))
rows = cursor.fetchall()
for row in rows:
    print(row)

• this query retrieves rows from users where the name column matches “John Doe”.

Sorting Results

Use the ORDER BY clause to sort the results.

Example: Sorting Data

To sort users by name in ascending order:

cursor.execute("SELECT * FROM users ORDER BY name ASC")
rows = cursor.fetchall()
for row in rows:
    print(row)

Limiting Results

The LIMIT clause can be used to limit the number of results returned.

Example: Limiting Data

To get the first 5 users:

cursor.execute("SELECT * FROM users LIMIT 5")
rows = cursor.fetchall()
for row in rows:
    print(row)

In this section, we discussed how to retrieve data from a SQLite database using the SELECT statement. We covered how to select all or specific columns, filter results using WHERE, sort results with ORDER BY, and limit the number of results returned using LIMIT. These tools are fundamental for efficiently querying and handling data in a SQLite database. The next section will focus on processing the results of these queries in Python.

After retrieving data from a SQLite database using the SELECT statement, the next step is to process these results in Python. SQLite returns query results as a list of tuples, where each tuple corresponds to a row in the database. Let’s explore how to handle and utilize these query results effectively.

Processing Query Results

When you execute a SELECT statement using the sqlite3 module in Python, the results are typically returned as a list of tuples. Each tuple represents a database record, and each item within the tuple corresponds to a column in the table.

Fetch Methods

There are several methods to retrieve query results:

• fetchone(): Retrieves the next row of a query result set, returning a single tuple, or None when no more data is available.
• fetchall(): Fetches all (remaining) rows of a query result, returning a list of tuples.
• fetchmany(size): Fetches the next set of rows of a query result, returning a list. You can specify the number of rows to retrieve.

Example: Processing Results with fetchall()

Here’s a basic example of executing a SELECT statement and using fetchall():

cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(f"ID: {row[0]}, Name: {row[1]}, Email: {row[2]}")

In this example, we iterate over each row in a loop and access individual column values using indexing (row[0], row[1], row[2], etc.).

Working with Named Tuples

For better code readability, you can use named tuples. This makes your code more readable by accessing column values by name.

import sqlite3
from collections import namedtuple

# Connect to SQLite database
conn = sqlite3.connect('my_database.db')
conn.row_factory = sqlite3.Row  # use sqlite3.Row for named tuples
cursor = conn.cursor()

cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    user = namedtuple('User', row.keys())(*row)
    print(f"ID: {user.id}, Name: {user.name}, Email: {user.email}")

• sqlite3.Row provides both index-based and case-insensitive name-based access to columns with almost no memory overhead.
• We then create a named tuple User for each row, making the code more expressive.

Mapping Results to a Dictionary

Alternatively, you can map results to a dictionary for ease of use.

cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
users = [dict(row) for row in rows]
for user in users:
    print(user)

This approach converts each row into a dictionary where column names are keys, allowing you to access data by column names.

In this section, we discussed how to process and handle the results obtained from SQLite queries in Python. We looked at different fetch methods (fetchone, fetchall, fetchmany), and explored how to make query results more readable and manageable using named tuples and dictionaries. Understanding these methods is crucial for effective data manipulation and retrieval in Python-based database applications. Next, we will move on to updating and deleting data in tables.

Updating and Deleting Data

After inserting and retrieving data in SQLite using Python, you may need to update or delete records. This section covers how to modify existing data with the UPDATE statement and remove records with the DELETE statement.

Updating Data

The UPDATE Statement

The UPDATE statement is used to modify existing records in a table.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example: Updating User Data

Suppose we want to update the email of a user with a specific id.

# Assuming connection (conn) and cursor (cursor) are already set up

# Update user's email
cursor.execute("UPDATE users SET email = ? WHERE id = ?", ("newemail@example.com", 1))

# Commit changes
conn.commit()

In this example:

• we use UPDATE to change the email of the user where id equals 1
• it’s important to use the WHERE clause to specify which records to update. Without it, all records in the table would be updated

Deleting Data

The DELETE Statement

The DELETE statement is used to delete records from a table.

Syntax

DELETE FROM table_name WHERE condition;

Example: Deleting a User

To delete a user with a specific id:

# Delete user with id 1
cursor.execute("DELETE FROM users WHERE id = ?", (1,))

# Commit changes
conn.commit()

• This statement deletes the record from users where id equals 1.
• As with UPDATE, it’s crucial to use the WHERE clause to specify which records to delete to avoid removing all records in the table.

Handling Exceptions

When updating or deleting data, exceptions can occur, particularly relating to constraints like foreign keys. It’s a good practice to handle these exceptions.

try:
    cursor.execute("DELETE FROM users WHERE id = ?", (1,))
    conn.commit()
except sqlite3.IntegrityError as e:
    print(f"An error occurred: {e}")

In this section, we covered how to update and delete data in a SQLite database using Python. We discussed the importance of using the WHERE clause in both UPDATE and DELETE statements to target specific records and avoid unintended data modifications. We also touched on the importance of exception handling to manage constraint violations. These operations are fundamental for maintaining the integrity and relevance of data within your database. Next, we will look into finalizing transactions and properly closing the database connection.

Finalizing Transactions and Closing the Connection

After performing database operations such as creating tables, inserting, updating, or deleting data, it’s crucial to finalize transactions and properly close the connection to the SQLite database. This section covers the significance of committing transactions and safely closing the database connection using Python’s sqlite3 module.

Committing Transactions

In database systems, a transaction is a sequence of database operations that are treated as a single unit. In SQLite, these transactions are used to ensure data integrity. When you modify data (insert, update, delete), these changes are not immediately saved to the database. They are instead held in a temporary state until you commit the transaction.

Using commit()

The commit() method of the connection object finalizes the transaction and writes all changes to the database.

# Perform some data modification
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("John Doe", "john@example.com"))

# Commit the transaction
conn.commit()

Failing to call commit() will result in changes not being saved when the connection is closed.

Auto-Commit Mode

SQLite also supports an auto-commit mode, which can be activated by setting isolation_level to None. In auto-commit mode, each statement is treated as a transaction and is automatically committed.

conn.isolation_level = None

However, manual control of transactions using commit() is recommended for better error handling and performance, especially in applications with multiple operations.

Rolling Back Transactions

If an error occurs during a transaction, you may need to undo all the operations in that transaction. This is known as rolling back a transaction.

try:
    # Perform database operations
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Jane Doe", "jane@example.com"))
    # Commit if successful
    conn.commit()
except sqlite3.Error as error:
    # Rollback in case of an error
    print(f"Error: {error}")
    conn.rollback()

• rollback() reverts the database to the state before the transaction began.

Closing the Connection

Properly closing the database connection is vital to free up resources. Use the close() method on the connection object to close the connection.

# Close the connection
conn.close()

Always ensure that the connection is closed after all database operations have completed.

Summary

In this lesson, we have covered the fundamentals of using SQLite with Python. Starting from setting up a SQLite database to performing various database operations such as creating tables, inserting, updating, deleting, and retrieving data, we have navigated through the essential elements of Python’s sqlite3 module.

Let’s briefly recap the key points:

1. SQLite and Python Setup: We began by exploring how to set up a SQLite database using Python’s sqlite3 module. We discussed creating a new database and establishing a connection to it.

2. Creating Tables: We learned how to create tables in SQLite, defining primary keys and structuring data with various data types.

3. Inserting Data: The tutorial covered the insertion of data into tables, including handling exceptions and performing bulk insertions for efficiency.

4. Retrieving Data: We demonstrated how to retrieve data using the SELECT statement, applying filters with the WHERE clause, and sorting and limiting results.

5. Processing Query Results: The handling and processing of query results returned as tuples, and the use of named tuples and dictionaries for better data management, were explored.

6. Updating and Deleting Data: We discussed updating existing records and deleting data from tables, emphasizing the importance of the WHERE clause to target specific records.

7. Transactions and Connection Management: The tutorial highlighted the significance of committing transactions, rolling back in case of errors, and the importance of properly closing the database connection to ensure data integrity and resource management.

To solidify your understanding, it’s beneficial to engage in hands-on practice:

• Experiment with different data types and table structures.
• Try more complex SQL queries, joins, and aggregate functions.
• Implement error handling and transaction management in more complex scenarios.

SQLite, with its simplicity and lightweight nature, combined with Python’s versatility, offers a powerful set of tools for managing and manipulating database-driven applications. The skills you’ve acquired here should serve as a robust foundation for your journey in database programming with Python and SQLite.

---

Files & Resources

All Files for Lesson 7.300

---

See Also

• 70.801 - The SQLite Database

References

None yet.

Errata

Let us know.