NFQtUFrF

1import sqlite3,json
2var_connection_db = sqlite3.connect('rosterdb.sqlite')
3var_cursor = var_connection_db.cursor()
4
5var_cursor.executescript("""
6drop table if exists User;
7drop table if exists Member;
8drop table if exists Course;
9
10create table User(
11id integer not null primary key autoincrement unique,
12name text unique);
13
14create table Course (
15id integer not null primary key autoincrement unique,
16title text unique);
17
18create table Member (
19user_id integer,
20course_id integer,
21role integer,
22primary key(user_id, course_id))""")
23var_connection_db.commit()
24fname = 'roster_data.json'
25
26str_data = open(fname).read()
27json_data = json.loads(str_data)
28var_person_details = {}
29for i in json_data:
30    name = i[0]
31    title = i[1]
32    role = i[2]
33    var_person_details[name] = [title,role]
34def _add_user(name,cours_title):
35        var_cursor.executescript("""
36        insert or ignore into User(name) values('{}');
37        
38        insert or ignore into Course(title) values('{}')
39        """.format(name,cours_title))
40
41def _read_ids_user_add_role(name,var_person):
42    user_id = var_cursor.execute("""
43    select User.id From User where name='{}'""".format(name))
44    user_id = user_id.fetchone()[0]
45
46    course_id = var_cursor.execute("""
47    select Course.id From Course where title='{}'""".format(var_person[name][0]))
48    course_id = course_id.fetchone()[0]
49
50    var_cursor.execute("""
51    insert or replace into Member(user_id,course_id,role) values('{}','{}','{}')""".format(user_id,course_id,var_person[name][1]))
52for i in var_person_details:
53    _add_user(i, var_person_details[i][0])
54    var_connection_db.commit()
55for i in var_person_details:
56    _read_ids_user_add_role(i,var_person_details)
57    var_connection_db.commit()