To use the SQLite database, you simply import it like any other library. Once imported, you have to make a connection to it; this creates the database file. A cursor is the object within SQLite that performs most of the functions you will be doing with the DB.
The following code listing demonstrates the creation of a SQLite database:
# tools_db.py (part 1)
1 import sqlite3
2
3 connection = sqlite3.connect("Tools.db") # The .db extension is optional
4 cursor = connection.cursor() # Executes SQL queries
5
6 # Create the table to hold entries
7 cursor.execute("""
8 CREATE TABLE Tools
9 (id INTEGER PRIMARY KEY,
10 name TEXT,
11 size TEXT,
12 price INTEGER)
13 """)
The sqlite3 library is imported in line 1. Lines 2 and 3 create the connection to the DB file and the cursor object, respectively.
Lines 6-13 create the table that will hold all the DB entries. In this case, the name of the table is Tools, a primary key is provided to ensure each entry has a unique identifier, two text entries are created, and so is a numeric entry:
# tools_db.py (part 2)
1 # Populate table
2 for item in (
3 (None, "Box Knife", "Small", 15),
4 (None, "Drill", "Medium", 35),
5 (None, "Axe", "Large", 55),
6 (None, "Putty Knife", "Small", 25),
7 (None, "Hammer", "Small", 25),
8 (None, "Screwdriver", "Small", 10),
9 (None, "Crowbar", "Large", 60),
10 ):
11 cursor.execute("INSERT INTO Tools VALUES (?, ?, ?, ?)", item)
12
13 connection.commit() # Write data to database
14 cursor.close() # Close database
In part 2, lines 2-11 actually populate the DB with data. The entries are comma-separated to match the entries provided during table creation. The value None corresponds to the primary key; we don't have to provide a value as SQLite will automatically increment the key value for each new entry. The rest of the values in each entry apply to the item's name, size, and price.
Line 11 calls cursor.execute() to stage the data that will be added to the DB. The question marks are used to prevent a SQL injection attack, where a SQL command is passed to the DB as a legitimate value. The DB would process the command as a normal, legitimate command which could delete data, change data, or otherwise compromise your DB. The question marks act as a substitution value to prevent this from occurring.
Lines 13 and 14 write the data to the DB then close the connection. The commit() command is required to actually put the data into the DB; until this is done, the data is only staged for filling the DB. This allows multiple data staging to occur, with only a single commit being required.