Exercise 22: Accessing SQLite3 from Python

You can easily access SQLite3 from Python using the DB-API 2.0 interface. Here is a very simple example using the queries from Exercise 16:

import sqlite3
conn = sqlite3.connect('thw.db')

cursor = conn.cursor()

cursor.execute("""
select * from person, person_pet, pet 
    where person.id = person_pet.person_id and pet.id = person_pet.pet_id;
""")

for row in cursor:
    print row

conn.close()

The general pattern for using the Python library is to:

• Create a connection to the database using its file name.

• Get a cursor from that connection to do work.

• Do the work with the cursor using any of the CRUD operations.

• Call conn.commit() when you are ready to commit that work. This is only necessary if you change the database.

• Close with conn.close() when you are done with the database.

Most other languages follow this pattern, but these days most people interact with databases using some kind of Object-Relational Mapping (ORM), but that is outside the scope of this little book.