Hi!
I wrote a programme that read roster data in JSON format, parse the file, and then produce an SQLite database that contains a User, Course, and Member table and populate the tables from the data file.
I’ve done the programme and it gives me the output:
(‘Katie’, ‘si430’, 0)
(‘Zuriel’, ‘si430’, 0)
(‘Youer’, ‘si430’, 0)
(‘Thara’, ‘si430’, 0)
But besides that then the programme has to give me something like: XYZZY53656C696E613333 and I have problems here. Any ideas? I copy my programme:
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.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 IGNORE INTO Member (role)
VALUES ( ? )''', ( role, ) )
cur.execute('''INSERT OR REPLACE INTO Member
(user_id, course_id, role) VALUES ( ?, ?, ?)''',
( user_id, course_id, role ) )
conn.commit()
cur.execute('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;’)