From 0da8ff2d13c0d0c448444bc8f4ad13459a97aa23 Mon Sep 17 00:00:00 2001
From: standenboy <standenboy@StandenboyLAP.lan>
Date: Tue, 23 Jan 2024 14:48:49 +0000
Subject: added stuff

---
 comp/work/19/sql.py | 89 +++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 89 insertions(+)
 create mode 100644 comp/work/19/sql.py

(limited to 'comp/work/19')

diff --git a/comp/work/19/sql.py b/comp/work/19/sql.py
new file mode 100644
index 0000000..03b8953
--- /dev/null
+++ b/comp/work/19/sql.py
@@ -0,0 +1,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()
+
-- 
cgit v1.2.3