RDS Access.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "5c28a6a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "from keys import secrets\n",
    "import csv\n",
    "import pandas as pd\n",
    "from sqlalchemy import URL, create_engine\n",
    "import io"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "5262b9f8",
   "metadata": {},
   "outputs": [],
   "source": [
    "url_object = URL.create(\n",
    "                \"postgresql+psycopg2\",\n",
    "                host = 'steam-db.c5m99euxia00.us-east-1.rds.amazonaws.com',\n",
    "                username = secrets.get('user'),\n",
    "                port=5432,\n",
    "                password = secrets.get('password'),\n",
    "                database='steam_db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d0625f91",
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(url_object)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "374baa05",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.base.Connection at 0x1d9cd2f1070>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ee271063",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a986e7e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a6f7bbdc",
   "metadata": {},
   "outputs": [],
   "source": [
    "steam_path = 'data/steam.csv'\n",
    "tags_path = 'data/tags.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "57a6a094",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "steam = pd.read_csv(steam_path)\n",
    "tags = pd.read_csv(tags_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "ff5c8d6d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "201"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connection = engine.raw_connection()\n",
    "cursor = connection.cursor()\n",
    "command = \"DROP TABLE IF EXISTS {};\".format('steam')\n",
    "cursor.execute(command)\n",
    "connection.commit()\n",
    "cursor.close()\n",
    "\n",
    "steam.to_sql(name='steam', con=engine, if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "be19546c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "443"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tags.to_sql(name='tags', con=engine, if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b1b88182",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\admin\\AppData\\Local\\Temp\\ipykernel_2696\\3230189287.py:7: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  pd.read_sql(sql, con=connection)\n"
     ]
    },
    {
     "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>index</th>\n",
       "      <th>title</th>\n",
       "      <th>tags</th>\n",
       "      <th>win_comp</th>\n",
       "      <th>mac_comp</th>\n",
       "      <th>linux_comp</th>\n",
       "      <th>review</th>\n",
       "      <th>percent_review</th>\n",
       "      <th>total_review</th>\n",
       "      <th>date_released</th>\n",
       "      <th>discount</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>S.T.A.L.K.E.R. 2: Heart of Chornobyl</td>\n",
       "      <td>[1695,1663,3835,1662,122,1774,4166]</td>\n",
       "      <td>windows</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>December 2023</td>\n",
       "      <td>No Discount</td>\n",
       "      <td>59.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>The Elder Scrolls Online: Necrom</td>\n",
       "      <td>[122,19,21,128,1684,1695,1754]</td>\n",
       "      <td>windows</td>\n",
       "      <td>mac</td>\n",
       "      <td>None</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>Jun 5, 2023</td>\n",
       "      <td>No Discount</td>\n",
       "      <td>39.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Street Fighter 6</td>\n",
       "      <td>[19,21,4736,1773,4231,1743,4747]</td>\n",
       "      <td>windows</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>Jun 1, 2023</td>\n",
       "      <td>No Discount</td>\n",
       "      <td>59.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Etrian Odyssey II HD</td>\n",
       "      <td>[21,122,4434,1720,3871,4191,4325]</td>\n",
       "      <td>windows</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>May 31, 2023</td>\n",
       "      <td>No Discount</td>\n",
       "      <td>39.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Etrian Odyssey III HD</td>\n",
       "      <td>[21,122,4434,1720,3871,4191,4325]</td>\n",
       "      <td>windows</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>May 31, 2023</td>\n",
       "      <td>No Discount</td>\n",
       "      <td>39.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9196</th>\n",
       "      <td>9196</td>\n",
       "      <td>Warhammer 40,000: Gladius - Lord of Skulls</td>\n",
       "      <td>[9]</td>\n",
       "      <td>windows</td>\n",
       "      <td>None</td>\n",
       "      <td>linux</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>None</td>\n",
       "      <td>No Discount</td>\n",
       "      <td>2.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9197</th>\n",
       "      <td>9197</td>\n",
       "      <td>DRAGON BALL XENOVERSE 2 - Pre-Order Bonus</td>\n",
       "      <td>[19,21,128,597]</td>\n",
       "      <td>windows</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>None</td>\n",
       "      <td>No Discount</td>\n",
       "      <td>4.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9198</th>\n",
       "      <td>9198</td>\n",
       "      <td>Divinity: Original Sin Enhanced Edition - Offi...</td>\n",
       "      <td>[122,9,21,492,7948]</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Positive</td>\n",
       "      <td>92% of positive reviews</td>\n",
       "      <td>13 total reviews</td>\n",
       "      <td>None</td>\n",
       "      <td>-40%</td>\n",
       "      <td>2.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9199</th>\n",
       "      <td>9199</td>\n",
       "      <td>Sam &amp;amp; Max 301: The Penal Zone</td>\n",
       "      <td>[21,1698,1719,5613,4242,5716,19]</td>\n",
       "      <td>windows</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Very Positive</td>\n",
       "      <td>92% of positive reviews</td>\n",
       "      <td>272 total reviews</td>\n",
       "      <td>None</td>\n",
       "      <td>No Discount</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9200</th>\n",
       "      <td>9200</td>\n",
       "      <td>Little Nightmares - Scarecrow Sack</td>\n",
       "      <td>[21,1667]</td>\n",
       "      <td>windows</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>Not Rated</td>\n",
       "      <td>None</td>\n",
       "      <td>No Discount</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9201 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      index                                              title  \\\n",
       "0         0               S.T.A.L.K.E.R. 2: Heart of Chornobyl   \n",
       "1         1                   The Elder Scrolls Online: Necrom   \n",
       "2         2                                   Street Fighter 6   \n",
       "3         3                               Etrian Odyssey II HD   \n",
       "4         4                              Etrian Odyssey III HD   \n",
       "...     ...                                                ...   \n",
       "9196   9196         Warhammer 40,000: Gladius - Lord of Skulls   \n",
       "9197   9197          DRAGON BALL XENOVERSE 2 - Pre-Order Bonus   \n",
       "9198   9198  Divinity: Original Sin Enhanced Edition - Offi...   \n",
       "9199   9199                  Sam &amp; Max 301: The Penal Zone   \n",
       "9200   9200                 Little Nightmares - Scarecrow Sack   \n",
       "\n",
       "                                     tags win_comp mac_comp linux_comp  \\\n",
       "0     [1695,1663,3835,1662,122,1774,4166]  windows     None       None   \n",
       "1          [122,19,21,128,1684,1695,1754]  windows      mac       None   \n",
       "2        [19,21,4736,1773,4231,1743,4747]  windows     None       None   \n",
       "3       [21,122,4434,1720,3871,4191,4325]  windows     None       None   \n",
       "4       [21,122,4434,1720,3871,4191,4325]  windows     None       None   \n",
       "...                                   ...      ...      ...        ...   \n",
       "9196                                  [9]  windows     None      linux   \n",
       "9197                      [19,21,128,597]  windows     None       None   \n",
       "9198                  [122,9,21,492,7948]     None     None       None   \n",
       "9199     [21,1698,1719,5613,4242,5716,19]  windows     None       None   \n",
       "9200                            [21,1667]  windows     None       None   \n",
       "\n",
       "             review           percent_review       total_review  \\\n",
       "0         Not Rated                Not Rated          Not Rated   \n",
       "1         Not Rated                Not Rated          Not Rated   \n",
       "2         Not Rated                Not Rated          Not Rated   \n",
       "3         Not Rated                Not Rated          Not Rated   \n",
       "4         Not Rated                Not Rated          Not Rated   \n",
       "...             ...                      ...                ...   \n",
       "9196      Not Rated                Not Rated          Not Rated   \n",
       "9197      Not Rated                Not Rated          Not Rated   \n",
       "9198       Positive  92% of positive reviews   13 total reviews   \n",
       "9199  Very Positive  92% of positive reviews  272 total reviews   \n",
       "9200      Not Rated                Not Rated          Not Rated   \n",
       "\n",
       "      date_released     discount  price  \n",
       "0     December 2023  No Discount  59.99  \n",
       "1       Jun 5, 2023  No Discount  39.99  \n",
       "2       Jun 1, 2023  No Discount  59.99  \n",
       "3      May 31, 2023  No Discount  39.99  \n",
       "4      May 31, 2023  No Discount  39.99  \n",
       "...             ...          ...    ...  \n",
       "9196           None  No Discount   2.99  \n",
       "9197           None  No Discount   4.99  \n",
       "9198           None         -40%   2.99  \n",
       "9199           None  No Discount   None  \n",
       "9200           None  No Discount   0.99  \n",
       "\n",
       "[9201 rows x 12 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read from table steam\n",
    "\n",
    "sql = \"\"\"\n",
    "SELECT * FROM steam\n",
    "\"\"\"\n",
    "\n",
    "pd.read_sql(sql, con=connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a11dc9be",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\admin\\AppData\\Local\\Temp\\ipykernel_2696\\3703861811.py:7: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  pd.read_sql(sql, con=connection)\n"
     ]
    },
    {
     "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>index</th>\n",
       "      <th>492</th>\n",
       "      <th>Indie</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1003823</td>\n",
       "      <td>Profile Features Limited</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>Action</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>597</td>\n",
       "      <td>Casual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>4182</td>\n",
       "      <td>Singleplayer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>21</td>\n",
       "      <td>Adventure</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>438</th>\n",
       "      <td>438</td>\n",
       "      <td>363767</td>\n",
       "      <td>Snooker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>439</th>\n",
       "      <td>439</td>\n",
       "      <td>42329</td>\n",
       "      <td>Coding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>440</th>\n",
       "      <td>440</td>\n",
       "      <td>30927</td>\n",
       "      <td>Fox</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>441</th>\n",
       "      <td>441</td>\n",
       "      <td>1220528</td>\n",
       "      <td>Hobby Sim</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>442</th>\n",
       "      <td>442</td>\n",
       "      <td>6214</td>\n",
       "      <td>Birds</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>443 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     index      492                     Indie\n",
       "0        0  1003823  Profile Features Limited\n",
       "1        1       19                    Action\n",
       "2        2      597                    Casual\n",
       "3        3     4182              Singleplayer\n",
       "4        4       21                 Adventure\n",
       "..     ...      ...                       ...\n",
       "438    438   363767                   Snooker\n",
       "439    439    42329                    Coding\n",
       "440    440    30927                       Fox\n",
       "441    441  1220528                 Hobby Sim\n",
       "442    442     6214                     Birds\n",
       "\n",
       "[443 rows x 3 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read from table tags\n",
    "\n",
    "sql = \"\"\"\n",
    "SELECT * FROM tags\n",
    "\"\"\"\n",
    "\n",
    "pd.read_sql(sql, con=connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "3bbfb0b8",
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = psycopg2.connect(\n",
    "    host = 'steam-db.c5m99euxia00.us-east-1.rds.amazonaws.com',\n",
    "    user = secrets.get('user'),\n",
    "    port=5432,\n",
    "    password = secrets.get('password'),\n",
    "    database='steam_db'\n",
    "    )\n",
    "cursor=connection.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1c98cdbb",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3e3ba8ca",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6a4325d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e5f64d5e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a9a73a29",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "1a0e473b",
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = psycopg2.connect(\n",
    "    host = 'steam-db.c5m99euxia00.us-east-1.rds.amazonaws.com',\n",
    "    user = secrets.get('user'),\n",
    "    password = secrets.get('password'),\n",
    "    database='steam_db'\n",
    "    )\n",
    "cursor=connection.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "39c0cea3",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\admin\\AppData\\Local\\Temp\\ipykernel_2696\\3882954002.py:1: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  steam.to_sql('steam', con=connection)\n"
     ]
    },
    {
     "ename": "DatabaseError",
     "evalue": "Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': relation \"sqlite_master\" does not exist\nLINE 1: SELECT name FROM sqlite_master WHERE type='table' AND name=?...\n                         ^\n",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mUndefinedTable\u001b[0m                            Traceback (most recent call last)",
      "File \u001b[1;32m~\\miniconda3\\envs\\steam\\lib\\site-packages\\pandas\\io\\sql.py:2018\u001b[0m, in \u001b[0;36mSQLiteDatabase.execute\u001b[1;34m(self, *args, **kwargs)\u001b[0m\n\u001b[0;32m   2017\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m-> 2018\u001b[0m     \u001b[43mcur\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43margs\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   2019\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m cur\n",
      "\u001b[1;31mUndefinedTable\u001b[0m: relation \"sqlite_master\" does not exist\nLINE 1: SELECT name FROM sqlite_master WHERE type='table' AND name=?...\n                         ^\n",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[1;31mDatabaseError\u001b[0m                             Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[13], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m \u001b[43msteam\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mto_sql\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43msteam\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mcon\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mconnection\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\miniconda3\\envs\\steam\\lib\\site-packages\\pandas\\core\\generic.py:2987\u001b[0m, in \u001b[0;36mNDFrame.to_sql\u001b[1;34m(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)\u001b[0m\n\u001b[0;32m   2830\u001b[0m \u001b[38;5;250m\u001b[39m\u001b[38;5;124;03m\"\"\"\u001b[39;00m\n\u001b[0;32m   2831\u001b[0m \u001b[38;5;124;03mWrite records stored in a DataFrame to a SQL database.\u001b[39;00m\n\u001b[0;32m   2832\u001b[0m \n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m   2983\u001b[0m \u001b[38;5;124;03m[(1,), (None,), (2,)]\u001b[39;00m\n\u001b[0;32m   2984\u001b[0m \u001b[38;5;124;03m\"\"\"\u001b[39;00m  \u001b[38;5;66;03m# noqa:E501\u001b[39;00m\n\u001b[0;32m   2985\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01mpandas\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mio\u001b[39;00m \u001b[38;5;28;01mimport\u001b[39;00m sql\n\u001b[1;32m-> 2987\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43msql\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mto_sql\u001b[49m\u001b[43m(\u001b[49m\n\u001b[0;32m   2988\u001b[0m \u001b[43m    \u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2989\u001b[0m \u001b[43m    \u001b[49m\u001b[43mname\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2990\u001b[0m \u001b[43m    \u001b[49m\u001b[43mcon\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2991\u001b[0m \u001b[43m    \u001b[49m\u001b[43mschema\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mschema\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2992\u001b[0m \u001b[43m    \u001b[49m\u001b[43mif_exists\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mif_exists\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2993\u001b[0m \u001b[43m    \u001b[49m\u001b[43mindex\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mindex\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2994\u001b[0m \u001b[43m    \u001b[49m\u001b[43mindex_label\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mindex_label\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2995\u001b[0m \u001b[43m    \u001b[49m\u001b[43mchunksize\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mchunksize\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2996\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdtype\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mdtype\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2997\u001b[0m \u001b[43m    \u001b[49m\u001b[43mmethod\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mmethod\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2998\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\miniconda3\\envs\\steam\\lib\\site-packages\\pandas\\io\\sql.py:695\u001b[0m, in \u001b[0;36mto_sql\u001b[1;34m(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method, engine, **engine_kwargs)\u001b[0m\n\u001b[0;32m    690\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(frame, DataFrame):\n\u001b[0;32m    691\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mNotImplementedError\u001b[39;00m(\n\u001b[0;32m    692\u001b[0m         \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mframe\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m argument should be either a Series or a DataFrame\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m    693\u001b[0m     )\n\u001b[1;32m--> 695\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mpandas_sql\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mto_sql\u001b[49m\u001b[43m(\u001b[49m\n\u001b[0;32m    696\u001b[0m \u001b[43m    \u001b[49m\u001b[43mframe\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    697\u001b[0m \u001b[43m    \u001b[49m\u001b[43mname\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    698\u001b[0m \u001b[43m    \u001b[49m\u001b[43mif_exists\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mif_exists\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    699\u001b[0m \u001b[43m    \u001b[49m\u001b[43mindex\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mindex\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    700\u001b[0m \u001b[43m    \u001b[49m\u001b[43mindex_label\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mindex_label\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    701\u001b[0m \u001b[43m    \u001b[49m\u001b[43mschema\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mschema\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    702\u001b[0m \u001b[43m    \u001b[49m\u001b[43mchunksize\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mchunksize\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    703\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdtype\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mdtype\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    704\u001b[0m \u001b[43m    \u001b[49m\u001b[43mmethod\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mmethod\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    705\u001b[0m \u001b[43m    \u001b[49m\u001b[43mengine\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mengine\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    706\u001b[0m \u001b[43m    \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mengine_kwargs\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    707\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\miniconda3\\envs\\steam\\lib\\site-packages\\pandas\\io\\sql.py:2187\u001b[0m, in \u001b[0;36mSQLiteDatabase.to_sql\u001b[1;34m(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method, **kwargs)\u001b[0m\n\u001b[0;32m   2176\u001b[0m             \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mValueError\u001b[39;00m(\u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;132;01m{\u001b[39;00mcol\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m (\u001b[39m\u001b[38;5;132;01m{\u001b[39;00mmy_type\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m) not a string\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m   2178\u001b[0m table \u001b[38;5;241m=\u001b[39m SQLiteTable(\n\u001b[0;32m   2179\u001b[0m     name,\n\u001b[0;32m   2180\u001b[0m     \u001b[38;5;28mself\u001b[39m,\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m   2185\u001b[0m     dtype\u001b[38;5;241m=\u001b[39mdtype,\n\u001b[0;32m   2186\u001b[0m )\n\u001b[1;32m-> 2187\u001b[0m \u001b[43mtable\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcreate\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   2188\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m table\u001b[38;5;241m.\u001b[39minsert(chunksize, method)\n",
      "File \u001b[1;32m~\\miniconda3\\envs\\steam\\lib\\site-packages\\pandas\\io\\sql.py:827\u001b[0m, in \u001b[0;36mSQLTable.create\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    826\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mcreate\u001b[39m(\u001b[38;5;28mself\u001b[39m) \u001b[38;5;241m-\u001b[39m\u001b[38;5;241m>\u001b[39m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[1;32m--> 827\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexists\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m:\n\u001b[0;32m    828\u001b[0m         \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mif_exists \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mfail\u001b[39m\u001b[38;5;124m\"\u001b[39m:\n\u001b[0;32m    829\u001b[0m             \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mValueError\u001b[39;00m(\u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mTable \u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;132;01m{\u001b[39;00m\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mname\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m already exists.\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "File \u001b[1;32m~\\miniconda3\\envs\\steam\\lib\\site-packages\\pandas\\io\\sql.py:814\u001b[0m, in \u001b[0;36mSQLTable.exists\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    813\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mexists\u001b[39m(\u001b[38;5;28mself\u001b[39m):\n\u001b[1;32m--> 814\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mpd_sql\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mhas_table\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mname\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mschema\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\miniconda3\\envs\\steam\\lib\\site-packages\\pandas\\io\\sql.py:2195\u001b[0m, in \u001b[0;36mSQLiteDatabase.has_table\u001b[1;34m(self, name, schema)\u001b[0m\n\u001b[0;32m   2192\u001b[0m wld \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m?\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m   2193\u001b[0m query \u001b[38;5;241m=\u001b[39m \u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mSELECT name FROM sqlite_master WHERE type=\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mtable\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m AND name=\u001b[39m\u001b[38;5;132;01m{\u001b[39;00mwld\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m;\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[1;32m-> 2195\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mlen\u001b[39m(\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[43mquery\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43m[\u001b[49m\u001b[43mname\u001b[49m\u001b[43m]\u001b[49m\u001b[43m)\u001b[49m\u001b[38;5;241m.\u001b[39mfetchall()) \u001b[38;5;241m>\u001b[39m \u001b[38;5;241m0\u001b[39m\n",
      "File \u001b[1;32m~\\miniconda3\\envs\\steam\\lib\\site-packages\\pandas\\io\\sql.py:2030\u001b[0m, in \u001b[0;36mSQLiteDatabase.execute\u001b[1;34m(self, *args, **kwargs)\u001b[0m\n\u001b[0;32m   2027\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m ex \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01minner_exc\u001b[39;00m\n\u001b[0;32m   2029\u001b[0m ex \u001b[38;5;241m=\u001b[39m DatabaseError(\u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mExecution failed on sql \u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;132;01m{\u001b[39;00margs[\u001b[38;5;241m0\u001b[39m]\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m: \u001b[39m\u001b[38;5;132;01m{\u001b[39;00mexc\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m\"\u001b[39m)\n\u001b[1;32m-> 2030\u001b[0m \u001b[38;5;28;01mraise\u001b[39;00m ex \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01mexc\u001b[39;00m\n",
      "\u001b[1;31mDatabaseError\u001b[0m: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': relation \"sqlite_master\" does not exist\nLINE 1: SELECT name FROM sqlite_master WHERE type='table' AND name=?...\n                         ^\n"
     ]
    }
   ],
   "source": [
    "steam.to_sql('steam', con=connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "481fd2b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create steam tags table\n",
    "# cursor.execute (\"\"\"CREATE TABLE tags(\n",
    "# id SERIAL PRIMARY KEY,\n",
    "# genre text\n",
    "# )\"\"\")\n",
    "\n",
    "# Create steam games table\n",
    "cursor.execute(\"\"\"CREATE TABLE steam(\n",
    "name text,\n",
    "tags int,\n",
    "windows text,\n",
    "mac text,\n",
    "linux text,\n",
    "reviews text,\n",
    "percents text,\n",
    "total_reviews text,\n",
    "released text,\n",
    "discount text,\n",
    "price double precision\n",
    ")\"\"\")\n",
    "\n",
    "connection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03dcd51d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Gather all available tables and columns info\n",
    "\n",
    "sql = \"\"\"\n",
    "SELECT \"table_name\",\"column_name\", \"data_type\", \"table_schema\"\n",
    "FROM INFORMATION_SCHEMA.COLUMNS\n",
    "WHERE \"table_schema\" = 'public'\n",
    "ORDER BY table_name  \n",
    "\"\"\"\n",
    "pd.read_sql(sql, con=connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "826673b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use tags to create table\n",
    "# with open('data/tags.csv', 'r') as row:\n",
    "#     cursor.copy_from(row, 'tags', sep=',')\n",
    "\n",
    "# Use steam to create table\n",
    "with open('data/steam.csv', 'r') as row:\n",
    "    cursor.copy_from(row, 'steam', sep=',')\n",
    "\n",
    "connection.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a227f2d",
   "metadata": {},
   "source": [
    "# Create a new role for additional access\n",
    "cursor.execute(\"\"\"CREATE ROLE guest WITH LOGIN PASSWORD 'guest_access'\"\"\")\n",
    "\n",
    "# Grant different privileges\n",
    "cursor.execute(\"\"\"GRANT CONNECT ON DATABASE steam_db TO guest\"\"\")\n",
    "cursor.execute(\"\"\"GRANT SELECT, UPDATE, INSERT ON tags TO guest\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb5e40a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read from table tags\n",
    "\n",
    "sql = \"\"\"\n",
    "SELECT * FROM tags\n",
    "\"\"\"\n",
    "\n",
    "pd.read_sql(sql, con=connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "063b323c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read from table steam\n",
    "\n",
    "sql = \"\"\"\n",
    "SELECT * FROM steam\n",
    "\"\"\"\n",
    "\n",
    "pd.read_sql(sql, con=connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2233ecbe",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1a3ad94",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "steam",
   "language": "python",
   "name": "steam"
  },
  "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.8.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}