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

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()