Many to many relationships&Assignment

表与表之间多对多关联

import jsonimport sqlite3conn = sqlite3.connect('rosterdb.sqlite')cur = conn.cursor()# Do some setupcur.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 = raw_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];    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()

要点其实是需要再建立一张表用来存入course和users的primary key。
最后在SQL中输入

SELECT 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