SHARE
TWEET

Untitled

a guest Dec 10th, 2019 114 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. import sqlite3
  2.  
  3. conn = sqlite3.connect('coursesdb.sqlite')
  4. cur = conn.cursor()
  5.  
  6. cur.executescript('''
  7. CREATE TABLE IF NOT EXISTS User (
  8.   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
  9.   name TEXT,
  10.   email TEXT
  11. );
  12.  
  13. CREATE TABLE IF NOT EXISTS Course (
  14.   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
  15.   title TEXT
  16. );
  17.  
  18. CREATE TABLE IF NOT EXISTS Member (
  19.   user_id INTEGER,
  20.   course_id INTEGER,
  21.   role INTEGER,
  22.   PRIMARY KEY (user_id, course_id)
  23. )
  24.  
  25. ''')
  26.  
  27. cur.execute("INSERT INTO User (name, email) VALUES ('Jane', 'jane@tsugi.org')")
  28. cur.execute("INSERT INTO User (name, email) VALUES ('Ed', 'ed@tsugi.org')")
  29. cur.execute("INSERT INTO User (name, email) VALUES ('Sue', 'sue@tsugi.org')")
  30.  
  31. cur.execute("INSERT INTO Course (title) VALUES ('Python')")
  32. cur.execute("INSERT INTO Course (title) VALUES ('SQL')")
  33. cur.execute("INSERT INTO Course (title) VALUES ('PHP')")
  34.  
  35. cur.execute("INSERT INTO Member (user_id, course_id, role) VALUES (1, 1, 1)")
  36. cur.execute("INSERT INTO Member (user_id, course_id, role) VALUES (2, 1, 0)")
  37. cur.execute("INSERT INTO Member (user_id, course_id, role) VALUES (3, 1, 0)")
  38.  
  39. cur.execute("INSERT INTO Member (user_id, course_id, role) VALUES (1, 2, 0)")
  40. cur.execute("INSERT INTO Member (user_id, course_id, role) VALUES (2, 2, 1)")
  41.  
  42. cur.execute("INSERT INTO Member (user_id, course_id, role) VALUES (2, 3, 1)")
  43. cur.execute("INSERT INTO Member (user_id, course_id, role) VALUES (3, 3, 0)")
  44.  
  45. conn.commit()
  46.  
  47. print(list(cur.execute('''
  48. SELECT User.name, Member.role, Course.title
  49. FROM User JOIN Member JOIN Course
  50. ON Member.user_id = User.id AND Member.course_id = Course.id
  51. ORDER BY Course.title, Member.role DESC, User.name
  52. ''')))
  53.  
  54. conn.close()
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top