In SQL, the select statement can be used to retrieve records from a database. How would you express a query to retrieve all tasks belonging to the user john?
Answer: select * from task where user_id = 'john'
We may implement this in Python as follows (only relevant lines shown, complete implementation is available as taskdb2.py):
Chapter4/tasktdb2.py
connection.row_factory = sqlite3.Row
sql = """select * from task where user_id = 'john'"""
cursor.execute(sql)
tasks = cursor.fetchall()
for t in tasks:
print(t['duedate'],t['description'])
The first line in the code is normally placed just after establishing a connection to the database and ensures that any row returned from a fetchone() or fetchall() method are not plain tuples, but sqlite3.Row objects. These objects behave just like tuples, but their fields can be indexed by the name of the column they represent as well.
The query is executed by passing it to the execute() method of the cursor attribute (highlighted) and the results are then retrieved with the fetchall() method that will return a list of tuples, each tuple representing a matching record, its elements equal to the columns. We print some of those elements by indexing the tuples with the column names we are interested in.
When taskdb2.py is run, the output will show a list of task records, each with a date and a description:
C:Tasklist II>python taskdb2.py
2010-03-01 work
2010-04-01 even more work
Most of the time we would like to pass the user_id to match as a variable. As we saw in the insert statements used in taskdb1.py, it is possible to construct a query using ? as placeholders. This way, we could pass a variable containing a user_id to the execute method. How would you refactor the code to select all records for a user whose user_id is contained in the variable username?
Now we are ready to take on the real implementation of the database interface needed for the tasklist application.
The database interface layer will have to provide functionality to initialize a database and to provide thread-safe ways to create, retrieve, update, and delete tasks (collectively, often called CRUD) as well as list all tasks for a given user. The code to do this is contained in two classes, Task and TaskDB (both available in tasklistdb.py). TaskDB encapsulates the connection to the database and contains code to initialize the database as well as methods to retrieve a selection of tasks and to create new tasks. These tasks are implemented as instances of the Task class and a Task instance may be updated or deleted.