test.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "I create this file to read error and test tables.:) you didn't need this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import configparser\n",
    "import boto3\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Param</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ACCESS_KEY</td>\n",
       "      <td>***********</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SECRET_KEY</td>\n",
       "      <td>***********</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LOG_DATA</td>\n",
       "      <td>s3://udacity-dend/log_data</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LOG_JSONPATH</td>\n",
       "      <td>s3://udacity-dend/log_json_path.json</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SONG_DATA</td>\n",
       "      <td>s3://udacity-dend/song_data</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>HOST</td>\n",
       "      <td>khanomgol.cpovghv6fcxd.us-west-2.redshift.amaz...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>DB_NAME</td>\n",
       "      <td>dev</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>DB_USER</td>\n",
       "      <td>khanomgol</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>DB_PASSWORD</td>\n",
       "      <td>***********</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>DB_PORT</td>\n",
       "      <td>5439</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Param                                              Value\n",
       "0    ACCESS_KEY                                        ***********\n",
       "1    SECRET_KEY                                        ***********\n",
       "2      LOG_DATA                         s3://udacity-dend/log_data\n",
       "3  LOG_JSONPATH               s3://udacity-dend/log_json_path.json\n",
       "4     SONG_DATA                        s3://udacity-dend/song_data\n",
       "5          HOST  khanomgol.cpovghv6fcxd.us-west-2.redshift.amaz...\n",
       "6       DB_NAME                                                dev\n",
       "7       DB_USER                                          khanomgol\n",
       "8   DB_PASSWORD                                        ***********\n",
       "9       DB_PORT                                               5439"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import configparser\n",
    "config = configparser.ConfigParser()\n",
    "config.read_file(open('dwh.cfg'))\n",
    "\n",
    "ACCESS_KEY              = config.get('AWS','ACCESS_KEY')\n",
    "SECRET_KEY              = config.get('AWS','SECRET_KEY')\n",
    "\n",
    "LOG_DATA                = config.get(\"S3\",\"LOG_DATA\")\n",
    "LOG_JSONPATH            = config.get(\"S3\",\"LOG_JSONPATH\")\n",
    "SONG_DATA               = config.get(\"S3\",\"SONG_DATA\")\n",
    "\n",
    "HOST                    = config.get(\"CLUSTER\",\"HOST\")\n",
    "DB_NAME                 = config.get(\"CLUSTER\",\"DB_NAME\")\n",
    "DB_USER                 = config.get(\"CLUSTER\",\"DB_USER\")\n",
    "DB_PASSWORD             = config.get(\"CLUSTER\",\"DB_PASSWORD\")\n",
    "DB_PORT                 = config.get(\"CLUSTER\",\"DB_PORT\")\n",
    "\n",
    "ARN= config.get(\"IAM_ROLE\", \"ARN\")\n",
    "\n",
    "(DB_USER, DB_PASSWORD, DB_NAME)\n",
    "\n",
    "pd.DataFrame({\"Param\":\n",
    "                  [\"ACCESS_KEY\",\" SECRET_KEY\", \"LOG_DATA\", \"LOG_JSONPATH\", \"SONG_DATA\", \"HOST\", \"DB_NAME\", \"DB_USER\", \"DB_PASSWORD\", \"DB_PORT\"],\n",
    "              \"Value\":\n",
    "                  [ACCESS_KEY, SECRET_KEY, LOG_DATA, LOG_JSONPATH, SONG_DATA, HOST, DB_NAME, DB_USER, DB_PASSWORD, DB_PORT]\n",
    "             })"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "import boto3\n",
    "\n",
    "ec2 = boto3.resource('ec2',\n",
    "                       region_name=\"us-west-2\",\n",
    "                       aws_access_key_id=ACCESS_KEY,\n",
    "                       aws_secret_access_key=SECRET_KEY\n",
    "                    )\n",
    "\n",
    "s3 = boto3.resource('s3',\n",
    "                       region_name=\"us-west-2\",\n",
    "                       aws_access_key_id=ACCESS_KEY,\n",
    "                       aws_secret_access_key=SECRET_KEY\n",
    "                   )\n",
    "\n",
    "iam = boto3.client('iam',aws_access_key_id=ACCESS_KEY,\n",
    "                     aws_secret_access_key=SECRET_KEY,\n",
    "                     region_name='us-west-2'\n",
    "                  )\n",
    "\n",
    "redshift = boto3.client('redshift',\n",
    "                       region_name=\"us-west-2\",\n",
    "                       aws_access_key_id=ACCESS_KEY,\n",
    "                       aws_secret_access_key=SECRET_KEY\n",
    "                       )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "s3.ObjectSummary(bucket_name='udacity-dend', key='song_data/')\n",
      "s3.ObjectSummary(bucket_name='udacity-dend', key='song_data/A/A/A/TRAAAAK128F9318786.json')\n",
      "s3.ObjectSummary(bucket_name='udacity-dend', key='song_data/A/A/A/TRAAAAV128F421A322.json')\n",
      "s3.ObjectSummary(bucket_name='udacity-dend', key='song_data/A/A/A/TRAAABD128F429CF47.json')\n",
      "s3.ObjectSummary(bucket_name='udacity-dend', key='song_data/A/A/A/TRAAACN128F9355673.json')\n"
     ]
    }
   ],
   "source": [
    "song_data_objects =  s3.Bucket(\"udacity-dend\").objects.filter(Prefix=\"song_data\").limit(5)\n",
    "for x in song_data_objects:\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "s3.ObjectSummary(bucket_name='udacity-dend', key='log_data/')\n",
      "s3.ObjectSummary(bucket_name='udacity-dend', key='log_data/2018/11/2018-11-01-events.json')\n",
      "s3.ObjectSummary(bucket_name='udacity-dend', key='log_data/2018/11/2018-11-02-events.json')\n",
      "s3.ObjectSummary(bucket_name='udacity-dend', key='log_data/2018/11/2018-11-03-events.json')\n",
      "s3.ObjectSummary(bucket_name='udacity-dend', key='log_data/2018/11/2018-11-04-events.json')\n"
     ]
    }
   ],
   "source": [
    "log_data_objects =  s3.Bucket(\"udacity-dend\").objects.filter(Prefix=\"log_data\").limit(5)\n",
    "for x in log_data_objects:\n",
    "    print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "postgresql://khanomgol:CHoPiTFunGeHENTO1@khanomgol.cpovghv6fcxd.us-west-2.redshift.amazonaws.com:5439/dev\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Connected: khanomgol@dev'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn_string=\"postgresql://{}:{}@{}:{}/{}\".format(DB_USER, DB_PASSWORD, HOST, DB_PORT, DB_NAME)\n",
    "print(conn_string)\n",
    "%sql $conn_string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "#%%sql \n",
    "#Select * From stl_load_errors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "staging_events_table_drop = \"DROP TABLE IF EXISTS staging_events;\"\n",
    "staging_songs_table_drop = \"DROP TABLE IF EXISTS staging_songs;\"\n",
    "songplay_table_drop = \"DROP TABLE IF EXISTS songplay;\"\n",
    "user_table_drop = \"DROP TABLE IF EXISTS users;\"\n",
    "song_table_drop = \"DROP TABLE IF EXISTS song;\"\n",
    "artist_table_drop = \"DROP TABLE IF EXISTS artist;\"\n",
    "time_table_drop = \"DROP TABLE IF EXISTS time;\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "staging_events_table_create= (\"\"\"\n",
    "CREATE TABLE IF NOT EXISTS staging_events\n",
    "(\n",
    "    artist           varchar,\n",
    "    auth             varchar,\n",
    "    first_name       varchar,\n",
    "    gender           varchar,\n",
    "    item_in_session  varchar,\n",
    "    last_name        varchar,\n",
    "    length           numeric(18,0),\n",
    "    level            varchar,\n",
    "    location         varchar,\n",
    "    method           varchar,\n",
    "    page             varchar,\n",
    "    registration     numeric(18,0),\n",
    "    session_id       integer,\n",
    "    song             varchar,\n",
    "    status           integer,\n",
    "    ts               bigint,\n",
    "    user_agent       varchar,\n",
    "    user_id          integer\n",
    ");\n",
    "\"\"\")\n",
    "\n",
    "staging_songs_table_create = (\"\"\"\n",
    "CREATE TABLE IF NOT EXISTS staging_songs\n",
    "(\n",
    "    num_songs        integer,\n",
    "    artist_id        varchar,\n",
    "    artist_latitude  numeric(18,0),\n",
    "    artist_longitude numeric(18,0),\n",
    "    artist_location  varchar,    \n",
    "    artist_name      varchar,\n",
    "    song_id          varchar,\n",
    "    title            varchar,\n",
    "    duration         numeric(18,0),\n",
    "    year             integer\n",
    ");\n",
    "\"\"\")\n",
    "\n",
    "songplay_table_create = (\"\"\"\n",
    "CREATE TABLE IF NOT EXISTS songplay\n",
    "(\n",
    "    songplay_id      int          IDENTITY(0,1),\n",
    "    ts               bigint       NOT NULL,\n",
    "    user_id          integer      NOT NULL,\n",
    "    level            varchar,\n",
    "    song_id          varchar      NOT NULL,\n",
    "    artist_id        varchar      NOT NULL,\n",
    "    session_id       integer,\n",
    "    location         varchar,\n",
    "    user_agent       varchar,\n",
    "    PRIMARY KEY(songplay_id)\n",
    ") DISTKEY(user_id) SORTKEY(user_id);\n",
    "\"\"\")\n",
    "\n",
    "user_table_create = (\"\"\"\n",
    "CREATE TABLE IF NOT EXISTS users\n",
    "(\n",
    "    user_id         integer,\n",
    "    first_name      varchar       NOT NULL,\n",
    "    last_name       varchar,\n",
    "    gender          varchar,\n",
    "    level           varchar,\n",
    "    PRIMARY KEY(user_id)\n",
    ") DISTKEY(user_id) SORTKEY(user_id);\n",
    "\"\"\")\n",
    "\n",
    "song_table_create = (\"\"\"\n",
    "CREATE TABLE IF NOT EXISTS song\n",
    "(\n",
    "    song_id         varchar       NOT NULL,\n",
    "    artist_id       varchar       NOT NULL,\n",
    "    title           varchar,\n",
    "    duration        numeric(18,0) NOT NULL,\n",
    "    year            integer       NOT NULL,\n",
    "    PRIMARY KEY(song_id)\n",
    ") DISTKEY(artist_id) SORTKEY(artist_id);\n",
    "\"\"\")\n",
    "\n",
    "artist_table_create = (\"\"\"\n",
    "CREATE TABLE IF NOT EXISTS artist\n",
    "(\n",
    "    artist_id        varchar,\n",
    "    artist_name      varchar,\n",
    "    location         varchar,\n",
    "    artist_latitude  numeric(18,0),\n",
    "    artist_longitude numeric(18,0),\n",
    "    PRIMARY KEY(artist_id)\n",
    ") DISTKEY(artist_id) SORTKEY(artist_id);\n",
    "\"\"\")\n",
    "\n",
    "time_table_create = (\"\"\"\n",
    "CREATE TABLE IF NOT EXISTS time\n",
    "(\n",
    "    ts       bigint,\n",
    "    hour             integer          NOT NULL,\n",
    "    day              integer          NOT NULL,\n",
    "    week             integer          NOT NULL,\n",
    "    month            integer          NOT NULL,\n",
    "    year             integer          NOT NULL,\n",
    "    weekday          integer          NOT NULL,\n",
    "    PRIMARY KEY(ts)\n",
    ") DISTSTYLE ALL;\n",
    "\"\"\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "config = configparser.ConfigParser()\n",
    "config.read('dwh.cfg')\n",
    "DWH_ROLE_ARN= config.get('IAM_ROLE','ARN')\n",
    "#LOG_DATA= config.get('S3','LOG_DATA')\n",
    "#LOG_JSONPATH= config.get('S3','LOG_JSONPATH')\n",
    "SONG_DATA= config.get('S3','SONG_DATA')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "staging_events_copy = (\"\"\"\n",
    "COPY staging_events\n",
    "FROM '{}'\n",
    "credentials 'aws_iam_role={}'\n",
    "compupdate off region 'us-west-2'\n",
    "json '{}'\n",
    "BLANKSASNULL\n",
    "EMPTYASNULL maxerror 50000;\n",
    "\"\"\").format(config['S3']['LOG_DATA'], config['IAM_ROLE']['ARN'], config['S3']['LOG_JSONPATH'])\n",
    "\n",
    "staging_songs_copy = (\"\"\"\n",
    "COPY staging_songs\n",
    "FROM '{}'\n",
    "credentials 'aws_iam_role={}'\n",
    "compupdate off region 'us-west-2'\n",
    "format as json 'auto'\n",
    "BLANKSASNULL\n",
    "EMPTYASNULL maxerror 50000;\n",
    "\"\"\").format(SONG_DATA, DWH_ROLE_ARN)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# FINAL TABLES\n",
    "\n",
    "songplay_table_insert = (\"\"\"\n",
    "INSERT INTO songplay(ts,\n",
    "                     user_id,\n",
    "                     level,\n",
    "                     song_id,\n",
    "                     artist_id,\n",
    "                     session_id,\n",
    "                     location,\n",
    "                     user_agent)\n",
    "SELECT  staging_events.ts,\n",
    "        staging_events.user_id,\n",
    "        staging_events.level,\n",
    "        staging_songs.song_id,\n",
    "        staging_songs.artist_id, \n",
    "        staging_events.session_id, \n",
    "        staging_events.location, \n",
    "        staging_events.user_agent \n",
    "FROM staging_events JOIN staging_songs ON \n",
    "    (staging_events.artist=staging_songs.artist_name AND staging_events.song=staging_songs.title)\n",
    "WHERE page='NextSong' and user_id is NOT NULL;\n",
    "\"\"\")\n",
    "\n",
    "user_table_insert = (\"\"\"\n",
    "INSERT INTO users (user_id,\n",
    "                  first_name, \n",
    "                  last_name, \n",
    "                  gender, \n",
    "                  level)\n",
    "SELECT DISTINCT user_id,\n",
    "                first_name,\n",
    "                last_name,\n",
    "                gender,\n",
    "                level \n",
    "FROM staging_events\n",
    "WHERE page = 'NextSong';\n",
    "\"\"\")\n",
    "\n",
    "song_table_insert = (\"\"\"\n",
    "INSERT INTO song (song_id,\n",
    "                  artist_id,\n",
    "                  title,\n",
    "                  duration,\n",
    "                  year)\n",
    "SELECT DISTINCT song_id,\n",
    "                artist_id,\n",
    "                title,\n",
    "                duration,\n",
    "                year \n",
    "FROM staging_songs;\n",
    "\"\"\")\n",
    "\n",
    "artist_table_insert = (\"\"\"\n",
    "INSERT INTO artist( artist_id,\n",
    "                    artist_name,\n",
    "                    location,\n",
    "                    artist_latitude,\n",
    "                    artist_longitude)\n",
    "SELECT DISTINCT artist_id,\n",
    "                artist_name,\n",
    "                location,\n",
    "                artist_latitude,\n",
    "                artist_longitude \n",
    "FROM staging_events\n",
    "JOIN staging_songs ON\n",
    "    (staging_events.artist =staging_songs.artist_name)\n",
    "WHERE page = 'NextSong';\n",
    "\"\"\")\n",
    "\n",
    "time_table_insert = (\"\"\"\n",
    "INSERT INTO time(ts,\n",
    "                 hour,\n",
    "                 day,\n",
    "                 week,\n",
    "                 month,\n",
    "                 year,\n",
    "                 weekday)\n",
    "SELECT DISTINCT ts,\n",
    "                extract(hour from TIMESTAMP 'epoch' + ts/1000 * INTERVAL '1 second'),\n",
    "                extract(day from TIMESTAMP 'epoch' + ts/1000 * INTERVAL '1 second'),\n",
    "                extract(week from TIMESTAMP 'epoch' + ts/1000 * INTERVAL '1 second'),\n",
    "                extract(month from TIMESTAMP 'epoch' + ts/1000 * INTERVAL '1 second'),\n",
    "                extract(year from TIMESTAMP 'epoch' + ts/1000 * INTERVAL '1 second'),\n",
    "                extract(weekday from TIMESTAMP 'epoch' + ts/1000 * INTERVAL '1 second')\n",
    "FROM staging_events;\n",
    "\"\"\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "create_table_queries = [staging_events_table_create, staging_songs_table_create, songplay_table_create, user_table_create, song_table_create, artist_table_create, time_table_create]\n",
    "drop_table_queries = [staging_events_table_drop, staging_songs_table_drop, songplay_table_drop, user_table_drop, song_table_drop, artist_table_drop, time_table_drop]\n",
    "copy_table_queries = [staging_events_copy, staging_songs_copy]\n",
    "insert_table_queries = [songplay_table_insert, user_table_insert, song_table_insert, artist_table_insert, time_table_insert]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "import psycopg2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "def drop_tables(cur, conn):\n",
    "    for query in drop_table_queries:\n",
    "        cur.execute(query)\n",
    "        conn.commit()\n",
    "\n",
    "\n",
    "def create_tables(cur, conn):\n",
    "    for query in create_table_queries:\n",
    "        cur.execute(query)\n",
    "        conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['dwh.cfg']"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "config = configparser.ConfigParser()\n",
    "config.read('dwh.cfg')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "conn = psycopg2.connect(\"host={} dbname={} user={} password={} port={}\".format(*config['CLUSTER'].values()))\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "ename": "InternalError",
     "evalue": "current transaction is aborted, commands ignored until end of transaction block\n",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mInternalError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-37-3b30bc91a3fd>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdrop_tables\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcur\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m<ipython-input-17-544adaf121ee>\u001b[0m in \u001b[0;36mdrop_tables\u001b[0;34m(cur, conn)\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0mdrop_tables\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcur\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      2\u001b[0m     \u001b[0;32mfor\u001b[0m \u001b[0mquery\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mdrop_table_queries\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m         \u001b[0mcur\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      4\u001b[0m         \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcommit\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mInternalError\u001b[0m: current transaction is aborted, commands ignored until end of transaction block\n"
     ]
    }
   ],
   "source": [
    "drop_tables(cur, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "create_tables(cur, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "config = configparser.ConfigParser()\n",
    "config.read('dwh.cfg')\n",
    "\n",
    "conn = psycopg2.connect(\"host={} dbname={} user={} password={} port={}\".format(*config['CLUSTER'].values()))\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "cur.execute(staging_events_copy)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "cur.execute(staging_songs_copy)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# songplay_table_insert, user_table_insert, song_table_insert, artist_table_insert, time_table_insert]        cur.execute(query)\n",
    "cur.execute(\"\"\"rollback;\"\"\")\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "cur.execute(artist_table_insert)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": 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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}