school

thing1's amazing school repo
Log | Files | Refs | Submodules | README

sql.py (2320B)

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