Created
January 24, 2021 23:13
-
-
Save ayubmetah/e0698a135ca41a1bea9b23d9e93e3873 to your computer and use it in GitHub Desktop.
This assignment will be a Python program to build a set of tables using the Many-to-Many approach to store enrollment and role data.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import json | |
import sqlite3 | |
conn = sqlite3.connect('rosterdb.sqlite') | |
cur = conn.cursor() | |
# Do some setup | |
cur.executescript(''' | |
DROP TABLE IF EXISTS User; | |
DROP TABLE IF EXISTS Member; | |
DROP TABLE IF EXISTS Course; | |
CREATE TABLE User ( | |
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, | |
name TEXT UNIQUE | |
); | |
CREATE TABLE Course ( | |
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, | |
title TEXT UNIQUE | |
); | |
CREATE TABLE Member ( | |
user_id INTEGER, | |
course_id INTEGER, | |
role INTEGER, | |
PRIMARY KEY (user_id, course_id) | |
) | |
''') | |
fname = input('Enter file name: ') | |
if len(fname) < 1: | |
fname = 'roster_data_sample.json' | |
# [ | |
# [ "Charley", "si110", 1 ], | |
# [ "Mea", "si110", 0 ], | |
str_data = open(fname).read() | |
json_data = json.loads(str_data) | |
for entry in json_data: | |
name = entry[0]; | |
title = entry[1]; | |
role = entry[2]; | |
print((name, title, role)) | |
cur.execute('''INSERT OR IGNORE INTO User (name) | |
VALUES ( ? )''', ( name, ) ) | |
cur.execute('SELECT id FROM User WHERE name = ? ', (name, )) | |
user_id = cur.fetchone()[0] | |
cur.execute('''INSERT OR IGNORE INTO Course (title) | |
VALUES ( ? )''', ( title, ) ) | |
cur.execute('SELECT id FROM Course WHERE title = ? ', (title, )) | |
course_id = cur.fetchone()[0] | |
cur.execute('''INSERT OR REPLACE INTO Member | |
(user_id, course_id, role) VALUES ( ?, ?, ? )''', | |
( user_id, course_id, role ) ) | |
conn.commit() | |
# SQL COMMAND 1 | |
SELECT User.name,Course.title, Member.role FROM | |
User JOIN Member JOIN Course | |
ON User.id = Member.user_id AND Member.course_id = Course.id | |
ORDER BY User.name DESC, Course.title DESC, Member.role DESC LIMIT 2; | |
#SQL COMMAND 2 | |
SELECT 'XYZZY' || hex(User.name || Course.title || Member.role ) AS X FROM | |
User JOIN Member JOIN Course | |
ON User.id = Member.user_id AND Member.course_id = Course.id | |
ORDER BY X LIMIT 1; #OUTPUT: XYZZY416172726F6E736933333430 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment