This book is not intended to be a database or SQL primer, but it will help to have an understanding of how traditional interaction with a database using SQL occurs.

First, consider a database to be one or more tables, just like a spreadsheet. The vertical columns comprise different fields or categories; they are analogous to the fields you fill out in a form. The horizontal rows are individual records; each row is one complete record entry. Here is a pictorial summary, representing a customer list. The table's name is Customers:

The only column that needs special explanation is the Index field. This field isn't required but is highly recommended. You can name it anything you want but the purpose is the same. It is a field that provides a unique value to every record; it's often called the primary key field. The primary key is a special object for most databases; simply identifying which field is the primary key will automatically increment that field as new entries are made, thereby ensuring a unique data object for easy identification. The other fields are simply created based on the information that you want to include in the database.

To make a true relational database, one table needs to refer to one or more different tables in some fashion.

If you wanted to make an order-entry database, you could make another table that tracks an order and relate that order to the preceding customer list, like so:

This table is called Orders. This table shows the various orders made by each person in the customer table. Each entry has a unique key and is related to Customers by the Customer_ID field, which is the Index value for each customer.

The code listing next shows how this database could be created:

# sqlite_db.py (part 1)
# import sqlite3
1​
2 connection = sqlite3.connect("Customers.db") # The .db extension is optional
3 cursor = connection.cursor() # Executes SQL queries
4​
5 # Alternative DB created only in memory
6 # mem_conn = sqlite3.connect(":memory:")
7 # cursor = mem_conn.cursor()
8​
9 # Create the table to hold entries
10 cursor.execute("""
11     CREATE TABLE Customers
12     (id INTEGER PRIMARY KEY,
13     LName TEXT,

Here, we import the sqlite3 database module in line 1, create the database in line 3, and establish a connection to the DB in line 4.

Lines 6-8 demonstrate how to create a database strictly within memory and not writing the data to disk. This is fine for testing, but shouldn't be used in production because, if the power fails on the computer, the data in memory is lost.

Starting with line 11, we create the DB table that will hold our customer information:

# sqlite_db.py (part 2)
1    FName TEXT,
2    Address TEXT,
3    City TEXT,
4    State TEXT)
5    """)
6​ 
7 cursor.execute("""
8     CREATE TABLE Orders
9     (id INTEGER PRIMARY KEY,
10     Item_title TEXT,
11    Price FLOAT,
12    Order_Number INTEGER,
13    customer_id INTEGER,
14    FOREIGN KEY (customer_id) REFERENCES Customers(id))
15    """)

Here, we complete the table declarations in lines 1-5. Then, in lines 7-15, we create the table to hold the customer orders:

# sqlite_db.py (part 3)
1 def customer_insert(last_name, first_name, address, city, state):
2     sql = "INSERT INTO Customers VALUES (?, ?, ?, ?, ?, ?)"
3     cursor.execute(sql, (None, last_name, first_name, address, city, state))
4     return cursor.lastrowid # Get ID of object
5​ 
6 def order_insert(item, price, order_num, customer_id):
7     sql = "INSERT INTO Orders VALUES (?, ?, ?, ?, ?)"
8     cursor.execute(sql, (None, item, price, order_num, customer_id))
9     return cursor.lastrowid
10​ 
11     johnson_id = customer_insert("Johnson", "Jack", "123 Easy St.", "Anywhere", "CA")
12     johnson_order1 = order_insert("Boots", 55.50, 4455, johnson_id)
13     johnson_order2 = order_insert("Shirt", 16.00, 4455, johnson_id)
14     johnson_order3 = order_insert("Pants", 33.00, 7690, johnson_id)

Here, lines 1-4 create a function that populates the customer table with supplied information, while lines 6-9 do the same thing for the order table.

Lines 11-14 provide the data that will be used to fill the customer and order table for the first customer:

# sqlite_db.py (part 4)
1 smith_id = customer_insert("Smith", "John", "312 Hard St.",           "Somewhere", "NY")
2 smith_order1 = order_insert("Shoes", 23.99, 3490, smith_id)
3 smith_order2 = order_insert("Shoes", 65.00, 5512, smith_id)
4​ 
5 connection.commit() # Write data to database
6 cursor.close() # Close database

Here, we populate the tables with information about the second customer and orders in lines 1-3. Lines 5 and 6 put the data into the DB and then close the connection to the DB.