The final part of the TaskDB
class defines three methods, create()
that will create a completely new Task
instance, retrieve()
that will fetch a task from the task
table given a task_id
and return it as a Task
instance, and list()
that will return a list of task_ids
for a given user.
We separated retrieve()
and list()
because retrieving an object complete with all its attributes might be quite expensive and not always needed. For example, if we were to select a list with thousands of tasks, we would likely display them as a page of about twenty tasks each. If we were to retrieve complete information for all those tasks, we might have to wait a while, so we might choose to instantiate only a first page-full of them and fetch the rest on an as-needed basis as the users step through the pages. We will encounter this pattern a few more times in this book.
The create()
method itself simply passes on all parameters to the Task
constructor together with the thread local storage that holds the database connection. It returns the resulting Task
instance.
The retrieve()
method takes the username and the ID of the task to retrieve. The username is taken as a sanity check, but not strictly necessary. If a record is found that matches both the task_id
and the username
, a Task
instance is created and returned (highlighted). If no such record could be found, a KeyError
exception is raised.
The list()
method returns a list of task_ids
for a given user. It constructs this list from the list of tuples returned by taking the first (and only) item from each tuple (highlighted).
Chapter4/tasklistdb.py
def create (self, user=None, id=None, description='', duedate=None, completed=None): return Task(self.data, user=user, id=id, description=description, duedate=duedate, completed=completed) def retrieve(self, user,id): sql = """select * from task where task_id = ? and user_id = ?""" cursor = self.data.conn.cursor() cursor.execute(sql,(id,user)) tasks = cursor.fetchall() if len(tasks): return self.create(user, tasks[0]['task_id'], tasks[0] ['description'], tasks[0]['duedate'], tasks[0]['completed']) raise KeyError('no such task') def list(self,user): sql = '''select task_id from task where user_id = ?''' cursor = self.data.conn.cursor() cursor.execute(sql,(user,)) return [row[0] for row in cursor.fetchall()]
The constructor for Task
takes a number of optional parameters together with a mandatory username and a taskdb
parameter that point to the thread local data that holds the database connections. If the duedate
parameter is not given, it assigns it the date of today (highlighted).
The construction of Task instances in the previous code deserves a closer look. Based on the value of the id
parameter, the constructor can do two things.
If the id
is known, this Task
instance is constructed based on data just retrieved from a database query so there is nothing more to be done as all parameters are already stored as instance variables.
However, if id
is not given (or None)
, we apparently are creating a completely new Task
that is not already present in the database. Therefore, we have to insert it into the task table using an insert
statement (highlighted).
We do not pass a new task_id
as a value to this insert
statement, but one will be created for us because we defined the task_id
column as integer primary key autoincrement
. This generated number is available from the cursor's lastrowid
attribute and we store that for later reuse. All this is quite SQLite-specific, for more information, refer to the information box.
Only an integer primary key
column can be defined as autoincrement
and only an integer primary key autoincrement
column will mapped to the internal rowid
column (and that is not even a real column). All this is very useful, but also quite SQLite-specific. More information on this subject can be found on the SQLite FAQ at http://www.sqlite.org/faq.html and in the section on rowid in the SQL reference at http://www.sqlite.org/lang_createtable.html#rowid.
Chapter4/tasklistdb.py
class Task: def __init__(self,taskdb,user,id=None,description='',duedate=None, completed=None): self.taskdb=taskdb self.user=user self.id=id self.description=description self.completed=completed self.duedate=duedate if duedate != None else date.today(). isoformat() if id == None: cursor = self.taskdb.conn.cursor() sql = '''insert into task (description,duedate,completed,user_ id) values(?,?,?,?)''' cursor.execute(sql,(self.description,self.duedate,self. completed,self.user)) self.id = cursor.lastrowid self.taskdb.conn.commit()
3.144.30.62