1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
|
import sqlite3
import os
def createTables():
cur.executescript(
'''CREATE TABLE class (
class_id INT(4) PRIMARY KEY,
name TEXT NOT NULL,
teacher VARCHAR(50));
CREATE TABLE pupil (
pupil_id INT(6) PRIMARY KEY,
firstName VARCHAR(50) NOT NULL,
lastName VARCHAR(50) NOT NULL,
age INT NOT NULL,
tutor VARCHAR(50));
CREATE TABLE enrol (
enrol_id INTEGER PRIMARY KEY AUTOINCREMENT,
pupil_id INT NOT NULL,
class_id INT NOT NULL,
FOREIGN KEY(pupil_id) REFERENCES pupil(pupil_id),
FOREIGN KEY(class_id) REFERENCES class(class_id));''')
def populateDB():
db_class = [("0001", "Maths", "N.Inskip"),
("0002", "Criminology", "G.Duffy"),
("0003", "English", "C.Reiley"),
("0004", "History(Modern)", "B.Batters"),
("0005", "Geography", "A.Podesta"),
("0006", "Computing", "N.Inskip")]
db_pupil = [("000001", "John", "Potato", 16, "G.Duffy"),
("000002", "Sammi", "Grange", 16, "G.Duffy"),
("000003", "Don", "Paisley", 16, "A.Podesta"),
("000004", "Barny", "Trex", 16, "B.Batters"),
("000005", "Lara", "Croft", 17, "B.Batters"),]
db_enrol = [(1, 1),
(2, 1),
(3, 1),
(2, 2),
(3, 3),
(5, 4),
(5, 2),
(5, 3),
(4, 1),
(1, 2)]
#add multiple list entries to the database
cur.executemany("INSERT INTO class (class_id, name, teacher) VALUES (?, ?, ?);", db_class)
cur.executemany("INSERT INTO pupil (pupil_id, firstName, lastName, age, tutor) VALUES (?, ?, ?, ?, ?);", db_pupil)
cur.executemany("INSERT INTO enrol (pupil_id, class_id) VALUES (?, ?);", db_enrol)
conn.commit()
def query(query):
#print out unformatted results
rows = cur.execute(query).fetchall()
for i in rows:
print(i)
#MAIN
if not os.path.exists('college_dbase.db'):
conn = sqlite3.connect('college_dbase.db')
cur = conn.cursor()
createTables()
populateDB()
else:
conn = sqlite3.connect('college_dbase.db')
cur = conn.cursor()
#query to be executed
q1 = '''SELECT *
FROM pupil
WHERE pupil.age = 17
ORDER BY pupil.lastName
;'''
query(q1)
conn.close()
|
