database_connection.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c2da3a90",
   "metadata": {},
   "outputs": [],
   "source": [
    "# !pip install ipython-sql\n",
    "# !pip install sqlalchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "92618173",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: psycopg2-binary in c:\\users\\heckr\\anaconda3\\envs\\machinelearningenv\\lib\\site-packages (2.9.3)\n"
     ]
    }
   ],
   "source": [
    "!pip install psycopg2-binary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "15432cca",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import psycopg2 as ps\n",
    "import pandas as pd\n",
    "from config import db_host, db_port, db_name, db_user, db_password"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "3dc43f22",
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = ps.connect(\n",
    "    host=db_host,\n",
    "    port=db_port,\n",
    "    dbname=db_name,\n",
    "    user=db_user,\n",
    "    password=db_password)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "8300af51",
   "metadata": {},
   "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>object_id</th>\n",
       "      <th>game_name</th>\n",
       "      <th>year_published</th>\n",
       "      <th>min_players</th>\n",
       "      <th>max_players</th>\n",
       "      <th>min_playtime</th>\n",
       "      <th>max_playtime</th>\n",
       "      <th>category_1</th>\n",
       "      <th>category_2</th>\n",
       "      <th>category_3</th>\n",
       "      <th>...</th>\n",
       "      <th>mech_4</th>\n",
       "      <th>mech_5</th>\n",
       "      <th>mech_6</th>\n",
       "      <th>total_votes</th>\n",
       "      <th>users_rated</th>\n",
       "      <th>average</th>\n",
       "      <th>baverage</th>\n",
       "      <th>stddev</th>\n",
       "      <th>complexity</th>\n",
       "      <th>site_views</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>174430</td>\n",
       "      <td>Gloomhaven</td>\n",
       "      <td>2017</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>60</td>\n",
       "      <td>120</td>\n",
       "      <td>Adventure</td>\n",
       "      <td>Exploration</td>\n",
       "      <td>Fantasy</td>\n",
       "      <td>...</td>\n",
       "      <td>Hand Management</td>\n",
       "      <td>Legacy Game</td>\n",
       "      <td>Modular Board</td>\n",
       "      <td>827</td>\n",
       "      <td>31254</td>\n",
       "      <td>8.85292</td>\n",
       "      <td>8.58424</td>\n",
       "      <td>1.59819</td>\n",
       "      <td>3.8078</td>\n",
       "      <td>8933078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>161936</td>\n",
       "      <td>Pandemic Legacy  Season 1</td>\n",
       "      <td>2015</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>60</td>\n",
       "      <td>60</td>\n",
       "      <td>Environmental</td>\n",
       "      <td>Medical</td>\n",
       "      <td>error</td>\n",
       "      <td>...</td>\n",
       "      <td>Legacy Game</td>\n",
       "      <td>Point to Point Movement</td>\n",
       "      <td>Set Collection</td>\n",
       "      <td>549</td>\n",
       "      <td>34729</td>\n",
       "      <td>8.62499</td>\n",
       "      <td>8.47159</td>\n",
       "      <td>1.59463</td>\n",
       "      <td>2.8301</td>\n",
       "      <td>2971746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>167791</td>\n",
       "      <td>Terraforming Mars</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>120</td>\n",
       "      <td>120</td>\n",
       "      <td>Economic</td>\n",
       "      <td>Environmental</td>\n",
       "      <td>industry_manuf</td>\n",
       "      <td>...</td>\n",
       "      <td>Income</td>\n",
       "      <td>Set Collection</td>\n",
       "      <td>Take That</td>\n",
       "      <td>1252</td>\n",
       "      <td>48339</td>\n",
       "      <td>8.42299</td>\n",
       "      <td>8.26781</td>\n",
       "      <td>1.36938</td>\n",
       "      <td>3.2313</td>\n",
       "      <td>4724387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>182028</td>\n",
       "      <td>Through the Ages  A New Story of Civilization</td>\n",
       "      <td>2015</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>120</td>\n",
       "      <td>120</td>\n",
       "      <td>card</td>\n",
       "      <td>Civilization</td>\n",
       "      <td>Economic</td>\n",
       "      <td>...</td>\n",
       "      <td>Card Drafting</td>\n",
       "      <td>Income</td>\n",
       "      <td>NaN</td>\n",
       "      <td>436</td>\n",
       "      <td>18269</td>\n",
       "      <td>8.49419</td>\n",
       "      <td>8.23513</td>\n",
       "      <td>1.49542</td>\n",
       "      <td>4.3850</td>\n",
       "      <td>2448074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>224517</td>\n",
       "      <td>Brass  Birmingham</td>\n",
       "      <td>2018</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>60</td>\n",
       "      <td>120</td>\n",
       "      <td>Economic</td>\n",
       "      <td>industry_manuf</td>\n",
       "      <td>Transportation</td>\n",
       "      <td>...</td>\n",
       "      <td>Market</td>\n",
       "      <td>Network and Route Building</td>\n",
       "      <td>Tech Trees / Tech Tracks</td>\n",
       "      <td>242</td>\n",
       "      <td>10070</td>\n",
       "      <td>8.62031</td>\n",
       "      <td>8.20459</td>\n",
       "      <td>1.22876</td>\n",
       "      <td>3.9122</td>\n",
       "      <td>894621</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   object_id                                      game_name  year_published  \\\n",
       "0     174430                                     Gloomhaven            2017   \n",
       "1     161936                      Pandemic Legacy  Season 1            2015   \n",
       "2     167791                              Terraforming Mars            2016   \n",
       "3     182028  Through the Ages  A New Story of Civilization            2015   \n",
       "4     224517                              Brass  Birmingham            2018   \n",
       "\n",
       "   min_players  max_players  min_playtime  max_playtime     category_1  \\\n",
       "0            1            4            60           120      Adventure   \n",
       "1            2            4            60            60  Environmental   \n",
       "2            1            5           120           120       Economic   \n",
       "3            2            4           120           120           card   \n",
       "4            2            4            60           120       Economic   \n",
       "\n",
       "        category_2       category_3  ...            mech_4  \\\n",
       "0      Exploration          Fantasy  ...   Hand Management   \n",
       "1          Medical            error  ...       Legacy Game   \n",
       "2    Environmental   industry_manuf  ...            Income   \n",
       "3     Civilization         Economic  ...     Card Drafting   \n",
       "4   industry_manuf   Transportation  ...            Market   \n",
       "\n",
       "                        mech_5                     mech_6 total_votes  \\\n",
       "0                  Legacy Game              Modular Board         827   \n",
       "1      Point to Point Movement             Set Collection         549   \n",
       "2               Set Collection                  Take That        1252   \n",
       "3                       Income                        NaN         436   \n",
       "4   Network and Route Building   Tech Trees / Tech Tracks         242   \n",
       "\n",
       "  users_rated  average baverage   stddev complexity  site_views  \n",
       "0       31254  8.85292  8.58424  1.59819     3.8078     8933078  \n",
       "1       34729  8.62499  8.47159  1.59463     2.8301     2971746  \n",
       "2       48339  8.42299  8.26781  1.36938     3.2313     4724387  \n",
       "3       18269  8.49419  8.23513  1.49542     4.3850     2448074  \n",
       "4       10070  8.62031  8.20459  1.22876     3.9122      894621  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "boardgames_df = pd.read_sql_query(\"select * from bg_plusstats\", con=connection)\n",
    "boardgames_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "06f83bd8",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "MachineLearningEnv",
   "language": "python",
   "name": "machinelearningenv"
  },
  "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.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}