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...