Every1 application makes use of some form of (persistent) storage. We have looked at plain text files already. In this chapter we consider how a database, in particular a relational database, can be accessed and used by Python programs.
Python provides standard functions to access all of the popular databases. There are many open source and commercial database products and each one has its own adapter that allows Python to connect to and use data held in it. For our purposes, we use the SQLite database because it requires no other installed software.
SQLite
SQLite
is a very lightweight serverless tool. The core Python product includes the SQLite adapter, allowing us to demonstrate the most important database features. SQLite behaves in the same way as bigger systems, but has low (near-zero) administrative overhead. A consequence of this is that SQLite can be used for development or prototyping and migrating to a more sophisticated database can be done later. For our purposes, SQLite provides all the features we require.
Database Functions
These are the key SQLite database functions we will be using:
# open (or create) a database file and return
# the connection
conn = sqlite3
.connect(filename)
# executes a SQL statement
conn.executescript(sql)
# return a cursor
cursor = conn.cursor()
# execute the SQL query that returns rows of data
cursor.execute(sql)
# returns the data as a list of rows
rows = cursor.fetchall()
Connecting and Loading Data into SQLite
Here is an example program that creates a new database, a single table, inserts some data, performs a query, and attempts to insert a duplicate row (dbcreate.py).
1 import os
2 import sqlite3
3
4 db_filename='mydatabase.db'
5 #
6 # if DB exists - delete it
7 #
8 exists = os.path.exists(db_filename)
9 if exists:
10 os.unlink(db_filename)
11 #
12 # connect to DB (create it if it doesn't exist)
13 #
14 conn = sqlite3.connect(db_filename)
15 #
16 # create a table
17 #
18 schema="""create table person (
19 id integer primary key autoincrement not null,
20 name text not null,
21 dob date,
22 nationality text,
23 gender text)
24 """
25 conn.executescript(schema)
26 #
27 # create some data
28 #
29 people="""insert into person (name, dob,nationality,gender)
30 values ('Fred Bloggs', '1965-12-25','British','Male');
31 insert into person (name, dob,nationality,gender)
32 values ('Santa Claus', '968-01-01','Lap','Male');
33 insert into person (name, dob,nationality,gender)
34 values ('Tooth Fairy', '1931-03-31','American','Female');
35 """
36 conn.executescript(people)
37 #
38 # execute a query
39 #
40 cursor = conn.cursor()
41 cursor.execute("select id, name, dob,nationality,gender from person")
42 for row in cursor.fetchall():
43 id, name, dob,nationality,gender = row
44 print("%3d %15s %12s %10s %6s" % (id, name, dob,nationality,gender))
45 #
46 # attempt to insert a person with no name
47 #
48 try:
49 dupe="insert into person (id, dob,nationality,gender)
50 values (1,'1931-03-31','American','Female');"
51 conn.executescript(dupe)
52 except Exception as e:
53 print('Cannot insert record',e.__class__.__name__)
- Lines 1 and 2 import the modules we need.
- Lines 4 through 10 delete an old database file if one exists (be careful not to use the database created in this program for anything useful!).
- Line 14 creates the database file.
- In lines 18 through 25, the schema is a set of commands (a SQL script) that will create a new table.
- Line 26 executes the SQL script to create the new table.
- In lines 29 through 36 a new script is defined that includes the SQL commands to insert three records in the new table.
- Line 37 executes the script.
- In lines 40 through 44, to execute a query, you need to create a cursor, then execute the query using that cursor. This establishes the query content but doesn’t fetch the data. The cursor.fetchall() provides an iterable list of rows that are assigned to named variables, which are then printed.
- Lines 48 through 53 set up an insert of a row and the try…except clauses catch errors on the insert. The insert SQL omits the name field deliberately to trigger an exception.
The output from this program is shown hee.
D:LeanPythonprograms>python dbcreate.py
1 Fred Bloggs 1965-12-25 British Male
2 Santa Claus 968-01-01 Lap Male
3 Tooth Fairy 1931-03-31 American Female
Cannot insert record IntegrityError
The exception caused by the insert statement on line 52 is triggered because the name field is not supplied (and must be not null).
In the following listing (dbupdate.py), we are passing two arguments to the program and using those in a SQL update command to change the nationality of a person.
1 import sqlite3
2 import sys
3 #
4 # arguments from command line
5 # use: python dbupdate.py 1 Chinese
6 #
7 db_filename = 'mydatabase.db'
8 inid = sys.argv[1]
9 innat = sys.argv[2]
10 #
11 # execute update using command-line arguments
12 #
13 conn = sqlite3.connect(db_filename)
14 cursor = conn.cursor()
15 query = "update person set nationality = :nat where id = :id"
16 cursor.execute(query, {'id':inid, 'nat':innat})
17 #
18 # list the persons to see changes
19 #
20 cursor.execute("select id, name, dob,nationality,gender from person")
21 for row in cursor.fetchall():
22 id, name, dob,nationality,gender = row
23 print("%3d %15s %12s %10s %6s" % (id, name, dob,nationality,gender))
- Lines 8 and 9 get the data from the command line: inid and innat.
- Lines 13 through 16 do most of the work. Lines 13 and 14 set up the cursor. Line 15 is SQL as before, but the values to be used for the fields in the SQL (id and nat) are parameterized using the colon notation (:id and :nat). Line 16 executes the query and provides the actual values of the parameters using a dictionary as the second argument to the call {'id':inid, 'nat':innat}.
The output is shown here.
D:LeanPythonprograms>python dbupdate.py 1 Chinese
1 Fred Bloggs 1965-12-25 Chinese Male
2 Santa Claus 968-01-01 Lap Male
3 Tooth Fairy 1931-03-31 American Female
The colon notation and dictionary can be used to parameterize any SQL call.
Footnotes
1
This chapter presumes a knowledge of the relational database model and simple Structured Query Language (SQL) commands.