· 10 years ago · Apr 04, 2016, 03:33 AM
1"""Practical Exam Week 6
2This template is for the practical exam on Mon 30 March, 12-2pm
3
4All tests are included in this file using the Python doctest
5module. Run this file to test your code.
6
7Author: Diego Molla-Aliod
8Email: diego.molla-aliod@mq.edu.au
9"""
10
11import sqlite3
12
13def create_db(filename):
14 """Create a database, populate it, and return a connection
15
16 The database will contain one table named users with the following fields:
17 - username: a text field
18 - email: a text field
19 The database MUST contain the following two records:
20 - Record 1:
21 username: Peter
22 email: peter@mail.com
23 - Record 2:
24 username: Mary
25 email: mary@mail.com
26 >>> c = create_db("db_test1.db")
27 >>> cur = c.cursor()
28 >>> list(cur.execute("SELECT email FROM users WHERE username='Peter'"))
29 [('peter@mail.com',)]
30 >>> list(cur.execute("SELECT username FROM users WHERE email='mary@mail.com'"))
31 [('Mary',)]
32 """
33 c = sqlite3.connect(filename)
34 cur = c.cursor()
35 c.execute("DROP TABLE IF EXISTS users")
36 c.execute("""CREATE TABLE users (
37 username text,
38 email text
39 )""")
40 c.execute("INSERT INTO users VALUES (?,?)",('Peter','peter@mail.com'))
41 c.execute("INSERT INTO users VALUES (?,?)",('Mary','mary@mail.com'))
42 c.commit()
43 return c
44
45def lookup_db(connection):
46 """Return the list of usernames from a database connection
47
48 The database has a table named data with the following fields:
49 - username: a text field
50 - password: a text field
51 >>> c = create_test_data('db_testa.db', [('John','Johnpass'), ('Mary','Marypass')])
52 >>> lookup_db(c)
53 ['John', 'Mary']
54 >>> c = create_test_data('db_testb.db', [('Peter','Peterpass')])
55 >>> lookup_db(c)
56 ['Peter']
57 """
58 sql = "SELECT username FROM data"
59 cur = connection.cursor()
60 cur.execute(sql)
61 return [r[0] for r in cur]
62
63def test_match(connection, username):
64 """Return the password of a database connection given a username, or None
65
66 The database has a table like in lookup_db. If the username does not
67 exist, return None
68 >>> c = create_test_data('db_testc.db', [('Peter','peterpass'), ('John','johnpass')])
69 >>> test_match(c,'Peter')
70 'peterpass'
71 >>> test_match(c,'Mary')
72 """
73 sql = "SELECT password FROM data WHERE username=?"
74 cur = connection.cursor()
75 rows = list(cur.execute(sql,(username,)))
76 if len(rows) == 0:
77 return None
78 return rows[0][0]
79
80def display_dict(d):
81 """Return HTML that represents an unordered list with the contents of the
82 dictionary. The keys are listed in alphabetical order.
83 >>> display_dict({'a':1,'b':2})
84 '<ul><li>a: 1</li><li>b: 2</li></ul>'
85 >>> display_dict({'z':5,'g':3,'x':8})
86 '<ul><li>g: 3</li><li>x: 8</li><li>z: 5</li></ul>'
87 >>> display_dict({})
88 '<ul></ul>'
89 """
90 result = ''
91 allkeys = list(d.keys())
92 allkeys.sort()
93 for k in allkeys:
94 result += '<li>%s: %s</li>' % (k,d[k])
95 return '<ul>'+result+'</ul>'
96
97################ DO NOT MODIFY THE CODE BELOW THIS LINE #####################
98
99def create_test_data(filename,data):
100 "Create a DB with test data and return a connection"
101 c = sqlite3.connect(filename)
102 cur = c.cursor()
103 cur.execute("DROP TABLE IF EXISTS data")
104 cur.execute("CREATE TABLE data (username text, password text)")
105 for d in data:
106 cur.execute("INSERT INTO data VALUES (?,?)",d)
107 c.commit()
108 return c
109
110
111if __name__ == "__main__":
112 import doctest
113 doctest.testmod()