Many Students in Many Courses.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('4161697661736931303630',)\n"
     ]
    }
   ],
   "source": [
    "import json\n",
    "import sqlite3\n",
    "\n",
    "conn = sqlite3.connect('rosterdb.sqlite')\n",
    "cur = conn.cursor()\n",
    "\n",
    "# Do some setup\n",
    "cur.executescript('''\n",
    "DROP TABLE IF EXISTS User;\n",
    "DROP TABLE IF EXISTS Member;\n",
    "DROP TABLE IF EXISTS Course;\n",
    "\n",
    "CREATE TABLE User (\n",
    "    id     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,\n",
    "    name   TEXT UNIQUE\n",
    ");\n",
    "\n",
    "CREATE TABLE Course (\n",
    "    id     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,\n",
    "    title  TEXT UNIQUE\n",
    ");\n",
    "\n",
    "CREATE TABLE Member (\n",
    "    user_id     INTEGER,\n",
    "    course_id   INTEGER,\n",
    "    role        INTEGER,\n",
    "    PRIMARY KEY (user_id, course_id)\n",
    ")\n",
    "''')\n",
    "\n",
    "fname = \"roster_data.json\"\n",
    "if len(fname) < 1:\n",
    "    fname = 'roster_data_sample.json'\n",
    "\n",
    "# [\n",
    "#   [ \"Charley\", \"si110\", 1 ],\n",
    "#   [ \"Mea\", \"si110\", 0 ],\n",
    "\n",
    "str_data = open(fname).read()\n",
    "json_data = json.loads(str_data)\n",
    "\n",
    "for entry in json_data:\n",
    "\n",
    "    name = entry[0];\n",
    "    title = entry[1];\n",
    "    role=entry[2];\n",
    "\n",
    "#     print((name, title,role))\n",
    "\n",
    "    cur.execute('''INSERT OR IGNORE INTO User (name)\n",
    "        VALUES ( ? )''', ( name, ) )\n",
    "    cur.execute('SELECT id FROM User WHERE name = ? ', (name, ))\n",
    "    user_id = cur.fetchone()[0]\n",
    "\n",
    "    cur.execute('''INSERT OR IGNORE INTO Course (title)\n",
    "        VALUES ( ? )''', ( title, ) )\n",
    "    cur.execute('SELECT id FROM Course WHERE title = ? ', (title, ))\n",
    "    course_id = cur.fetchone()[0]\n",
    "\n",
    "    cur.execute('''INSERT OR REPLACE INTO Member\n",
    "        (user_id, course_id,role) VALUES ( ?, ?, ? )''',\n",
    "        ( user_id, course_id,role ) )\n",
    "\n",
    "    conn.commit()\n",
    "    \n",
    "test=\"\"\"SELECT hex(User.name || Course.title || Member.role ) AS X FROM \n",
    "    User JOIN Member JOIN Course \n",
    "    ON User.id = Member.user_id AND Member.course_id = Course.id\n",
    "    ORDER BY X\"\"\"\n",
    "cur.execute(test)\n",
    "result=cur.fetchone()\n",
    "print(result)\n",
    "cur.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}