{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction to the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(0, 'Year', 'INTEGER', 0, None, 0)\n",
      "(1, 'Category', 'TEXT', 0, None, 0)\n",
      "(2, 'Nominee', 'TEXT', 0, None, 0)\n",
      "(3, 'Won', 'INTEGER', 0, None, 0)\n",
      "(4, 'Movie', 'TEXT', 0, None, 0)\n",
      "(5, 'Character', 'TEXT', 0, None, 0)\n",
      "(2010, 'Actor -- Leading Role', 'Javier Bardem', 0, 'Biutiful', 'Uxbal')\n",
      "(2010, 'Actor -- Leading Role', 'Jeff Bridges', 0, 'True Grit', 'Rooster Cogburn')\n",
      "(2010, 'Actor -- Leading Role', 'Jesse Eisenberg', 0, 'The Social Network', 'Mark Zuckerberg')\n",
      "(2010, 'Actor -- Leading Role', 'Colin Firth', 1, \"The King's Speech\", 'King George VI')\n",
      "(2010, 'Actor -- Leading Role', 'James Franco', 0, '127 Hours', 'Aron Ralston')\n",
      "(2010, 'Actor -- Supporting Role', 'Christian Bale', 1, 'The Fighter', 'Dicky Eklund')\n",
      "(2010, 'Actor -- Supporting Role', 'John Hawkes', 0, \"Winter's Bone\", 'Teardrop')\n",
      "(2010, 'Actor -- Supporting Role', 'Jeremy Renner', 0, 'The Town', 'James Coughlin')\n",
      "(2010, 'Actor -- Supporting Role', 'Mark Ruffalo', 0, 'The Kids Are All Right', 'Paul')\n",
      "(2010, 'Actor -- Supporting Role', 'Geoffrey Rush', 0, \"The King's Speech\", 'Lionel Logue')\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "conn = sqlite3.connect(\"nominations.db\")\n",
    "schema = conn.execute(\"pragma table_info(nominations);\").fetchall()\n",
    "first_ten = conn.execute(\"select * from nominations limit 10;\").fetchall()\n",
    "\n",
    "for r in schema:\n",
    "    print(r)\n",
    "    \n",
    "for r in first_ten:\n",
    "    print(r)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating the ceremonies table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(1, 2010, 'Steve Martin'), (2, 2009, 'Hugh Jackman'), (3, 2008, 'Jon Stewart'), (4, 2007, 'Ellen DeGeneres'), (5, 2006, 'Jon Stewart'), (6, 2005, 'Chris Rock'), (7, 2004, 'Billy Crystal'), (8, 2003, 'Steve Martin'), (9, 2002, 'Whoopi Goldberg'), (10, 2001, 'Steve Martin')]\n",
      "[(0, 'id', 'integer', 0, None, 1), (1, 'year', 'integer', 0, None, 0), (2, 'host', 'text', 0, None, 0)]\n"
     ]
    }
   ],
   "source": [
    "years_hosts = [(2010, \"Steve Martin\"),\n",
    "               (2009, \"Hugh Jackman\"),\n",
    "               (2008, \"Jon Stewart\"),\n",
    "               (2007, \"Ellen DeGeneres\"),\n",
    "               (2006, \"Jon Stewart\"),\n",
    "               (2005, \"Chris Rock\"),\n",
    "               (2004, \"Billy Crystal\"),\n",
    "               (2003, \"Steve Martin\"),\n",
    "               (2002, \"Whoopi Goldberg\"),\n",
    "               (2001, \"Steve Martin\"),\n",
    "               (2000, \"Billy Crystal\"),\n",
    "            ]\n",
    "create_ceremonies = \"create table ceremonies (id integer primary key, year integer, host text);\"\n",
    "conn.execute(create_ceremonies)\n",
    "insert_query = \"insert into ceremonies (Year, Host) values (?,?);\"\n",
    "conn.executemany(insert_query, years_hosts)\n",
    "\n",
    "print(conn.execute(\"select * from ceremonies limit 10;\").fetchall())\n",
    "print(conn.execute(\"pragma table_info(ceremonies);\").fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Foreign key constraints"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x10675e3b0>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.execute(\"PRAGMA foreign_keys = ON;\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Setting up one-to-many"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(1, 'Actor -- Leading Role', 'Javier Bardem', 'Biutiful', 'Uxbal', '0', 1), (2, 'Actor -- Leading Role', 'Jeff Bridges', 'True Grit', 'Rooster Cogburn', '0', 1), (3, 'Actor -- Leading Role', 'Jesse Eisenberg', 'The Social Network', 'Mark Zuckerberg', '0', 1), (4, 'Actor -- Leading Role', 'Colin Firth', \"The King's Speech\", 'King George VI', '1', 1), (5, 'Actor -- Leading Role', 'James Franco', '127 Hours', 'Aron Ralston', '0', 1)]\n"
     ]
    }
   ],
   "source": [
    "create_nominations_two = '''create table nominations_two \n",
    "(id integer primary key, \n",
    "category text, \n",
    "nominee text, \n",
    "movie text, \n",
    "character text, \n",
    "won integer,\n",
    "ceremony_id integer,\n",
    "foreign key(ceremony_id) references ceremonies(id));\n",
    "'''\n",
    "\n",
    "nom_query = '''\n",
    "select ceremonies.id as ceremony_id, nominations.category as category, \n",
    "nominations.nominee as nominee, nominations.movie as movie, \n",
    "nominations.character as character, nominations.won as won\n",
    "from nominations\n",
    "inner join ceremonies \n",
    "on nominations.year == ceremonies.year\n",
    ";\n",
    "'''\n",
    "joined_nominations = conn.execute(nom_query).fetchall()\n",
    "\n",
    "conn.execute(create_nominations_two)\n",
    "\n",
    "insert_nominations_two = '''insert into nominations_two (ceremony_id, category, nominee, movie, character, won) \n",
    "values (?,?,?,?,?,?);\n",
    "'''\n",
    "\n",
    "conn.executemany(insert_nominations_two, joined_nominations)\n",
    "print(conn.execute(\"select * from nominations_two limit 5;\").fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Deleting and renaming tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x10675e6c0>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drop_nominations = \"drop table nominations;\"\n",
    "conn.execute(drop_nominations)\n",
    "\n",
    "rename_nominations_two = \"alter table nominations_two rename to nominations;\"\n",
    "conn.execute(rename_nominations_two)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating a join table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x10675e960>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "create_movies = \"create table movies (id integer primary key,movie text);\"\n",
    "create_actors = \"create table actors (id integer primary key,actor text);\"\n",
    "create_movies_actors = '''create table movies_actors (id INTEGER PRIMARY KEY,\n",
    "movie_id INTEGER references movies(id), actor_id INTEGER references actors(id));\n",
    "'''\n",
    "conn.execute(create_movies)\n",
    "conn.execute(create_actors)\n",
    "conn.execute(create_movies_actors)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Populating the movies and actors tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(1, 'Biutiful'), (2, 'True Grit'), (3, 'The Social Network'), (4, \"The King's Speech\"), (5, '127 Hours')]\n",
      "[(1, 'Javier Bardem'), (2, 'Jeff Bridges'), (3, 'Jesse Eisenberg'), (4, 'Colin Firth'), (5, 'James Franco')]\n"
     ]
    }
   ],
   "source": [
    "insert_movies = \"insert into movies (movie) select distinct movie from nominations;\"\n",
    "insert_actors = \"insert into actors (actor) select distinct nominee from nominations;\"\n",
    "conn.execute(insert_movies)\n",
    "conn.execute(insert_actors)\n",
    "\n",
    "print(conn.execute(\"select * from movies limit 5;\").fetchall())\n",
    "print(conn.execute(\"select * from actors limit 5;\").fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Populating a join table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4), (5, 5, 5)]\n"
     ]
    }
   ],
   "source": [
    "pairs_query = \"select movie,nominee from nominations;\"\n",
    "movie_actor_pairs = conn.execute(pairs_query).fetchall()\n",
    "\n",
    "join_table_insert = \"insert into movies_actors (movie_id, actor_id) values ((select id from movies where movie == ?),(select id from actors where actor == ?));\"\n",
    "conn.executemany(join_table_insert,movie_actor_pairs)\n",
    "\n",
    "print(conn.execute(\"select * from movies_actors limit 5;\").fetchall())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}