Introduction
This tutorial will cover using SQLite in combination with Python's sqlite3 interface. SQLite is a single file relational database bundled with most standard Python installs. SQLite is often the technology of choice for small applications, particularly those of embedded systems and devices like phones and tablets, smart appliances, and instruments. However, it is not uncommon to hear it being used for small to medium web and desktop applications.
Creating a Database and Making a Connection
Creating a new SQLite database is as simple as creating a connection using the sqlite3 module in the Python standard library. To establish a connection all you need to do is pass a file path to the connect(...) method in the sqlite3 module, and if the database represented by the file does not exist one will be created at that path.
import sqlite3
con = sqlite3.connect('/path/to/file/db.sqlite3')
You will find that in everyday database programming you will be constantly creating connections to your database, so it is a good idea to wrap this simple connection statement into a reusable generalized function.
# db_utils.py
import os
import sqlite3
# create a default path to connect to and create (if necessary) a database
# called 'database.sqlite3' in the same directory as this script
DEFAULT_PATH = os.path.join(os.path.dirname(__file__), 'database.sqlite3')
def db_connect(db_path=DEFAULT_PATH):
con = sqlite3.connect(db_path)
return con
Creating Tables
In order to create database tables you need to have an idea of the structure of the data you are interested in storing. There are many design considerations that go into defining the tables of a relational database, which entire books have been written about. I will not be going into the details of this practice and will instead leave it up to the reader to further investigate.
However, to aid in our discussion of SQLite database programming with Python I will be working off the premise that a database needs to be created for a fictitious book store that has the below data already collected on book sales.
| customer | date | product | price |
|---|---|---|---|
| Alan Turing | 2/22/1944 | Introduction to Combinatorics | 7.99 |
| Donald Knuth | 7/3/1967 | A Guide to Writing Short Stories | 17.99 |
| Donald Knuth | 7/3/1967 | Data Structures and Algorithms | 11.99 |
| Edgar Codd | 1/12/1969 | Advanced Set Theory | 16.99 |
Upon inspecting this data it is evident that it contains information about customers, products, and orders. A common pattern in database design for transactional systems of this type are to break the orders into two additional tables, orders and line items (sometimes referred to as order details) to achieve greater normalization.
In a Python interpreter, in the same directory as the db_utils.py module defined previously, enter the SQL for creating the customers and products tables follows:
>>> from db_utils import db_connect
>>> con = db_connect() # connect to the database
>>> cur = con.cursor() # instantiate a cursor obj
>>> customers_sql = """
... CREATE TABLE customers (
... id integer PRIMARY KEY,
... first_name text NOT NULL,
... last_name text NOT NULL)"""
>>> cur.execute(customers_sql)
>>> products_sql = """
... CREATE TABLE products (
... id integer PRIMARY KEY,
... name text NOT NULL,
... price real NOT NULL)"""
>>> cur.execute(products_sql)
The above code creates a connection object then uses it to instantiate a cursor object. The cursor object is used to execute SQL statements on the SQLite database.
With the cursor created I then wrote the SQL to create the customers table, giving it a primary key along with a first and last name text field and assigning it to a variable called customers_sql. I then call the execute(...) method of the cursor object passing it the customers_sql variable. I then create a products table in a similar way.
You can query the sqlite_master table, a built-in SQLite metadata table, to verify that the above commands were successful.
To see all the tables in the currently connected database, query the name column of the sqlite_master table where the type is equal to "table".
>>> cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
<sqlite3.Cursor object at 0x104ff7ce0>
>>> print(cur.fetchall())
[('customers',), ('products',)]
To get a look at the schema of the tables, query the sql column of the same table where the type is still "table" and the name is equal to "customers" and/or "products".
>>> cur.execute("""SELECT sql FROM sqlite_master WHERE type='table'
… AND name='customers'""")
<sqlite3.Cursor object at 0x104ff7ce0>
>>> print(cur.fetchone()[0])
CREATE TABLE customers (
id integer PRIMARY KEY,
first_name text NOT NULL,
last_name text NOT NULL)
The next table to define will be the orders table which associates customers to orders via a foreign key and the date of their purchase. Since SQLite does not support an actual date/time data type (or data class to be consistent with the SQLite vernacular) all dates will be represented as text values.
>>> orders_sql = """
... CREATE TABLE orders (
... id integer PRIMARY KEY,
... date text NOT NULL,
... customer_id integer,
... FOREIGN KEY (customer_id) REFERENCES customers (id))"""
>>> cur.execute(orders_sql)
The final table to define will be the line items table which gives a detailed accounting of the products in each order.
lineitems_sql = """
... CREATE TABLE lineitems (
... id integer PRIMARY KEY,
... quantity integer NOT NULL,
... total real NOT NULL,
... product_id integer,
... order_id integer,
...