Date: March 12th 2016
Last updated: March 12th 2016
After normalising the database, the data are separated across tables and linked by foreign keys. Getting at this data requires a select statement by joining tables.
This example follows on from the code in foreign keys. That is, the tables and foreign keys are already created. The objective here is to print out the patients name and animal type for all records that are dogs (e.g. 'rover', 'dog').
import sqlite3 # connect con = sqlite3.connect('gitbook.db') # cursor cur = con.cursor() # execute: 3 parts # 1. select the columns to print out (e.g. patient name # 2. join on 2 columns that match (type INTEGER) # 3. return only records matching "dog" cur.execute('SELECT patient.patientname, animals.animaltype FROM patient \ JOIN animals ON animals.animalid == patient.patientanimal \ WHERE animals.animaltype = "dog";') # print out records rows = cur.fetchall() for row in rows: print(row) # commit and close con.commit() con.close()
('rover', 'dog') ('peanut', 'dog') ('snoopy', 'dog')