1_Fetching_the_data_eda.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL in Python - Connecting to and retrieving data from PostgreSQL\n",
    "\n",
    "Previously, you have learned how to connect to a SQL database by using a SQL client such as DBeaver. Apart from connecting to databases, DBeaver also allows you to run SQL queries against the database, create new tables and populate them with data as well as retrieving the data.\n",
    "\n",
    "Python also allows executing SQL queries and getting the result into a Python object, for example a Pandas data frame. Instead of exporting a .csv file from DBeaver you can directly get the data you need into Python and continue your work. In addition we can reduce the steps by connecting to the database from Python directly, eliminating the need for a separate SQL client.\n",
    "\n",
    "After you have the data in Python in the required shape you can export the data into a .csv file. This file is for your own reference, please avoid sending .csv files around - database is the point of reference when it comes to data. \n",
    "\n",
    "Having a copy of a .csv file (or another format) can speed up your analysis work. Imagine that the query takes 25 minutes to run. If you made some mistakes in your Python code you might need to go back to the original dataset. Instead of having to rerun the SQL query and having to wait you can read in the .csv file you have previously saved on your hard disk into Python and continue with your analysis work. \n",
    "\n",
    "**In this notebook you will see 2 ways to connect to SQL-Databases and export the data to a CSV file**\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating a connection to a PostgreSQL database with Python"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 2 python packages that are the \"go-to\" when it comes to connecting to SQL-Databases: `psycopg2` and `sqlalchemy` "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connecting via psycopg2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import psycopg2"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "In order to create a connection to our PostgreSQL database we need the following information:\n",
    "\n",
    "- host = the address of the machine the database is hosted on\n",
    "- port = the virtual gate number through which communication will be allowed\n",
    "- database = the name of the database\n",
    "- user = the name of the user\n",
    "- password = the password of the user\n",
    "\n",
    "Because we don't want that the database information is published on github we put it into a `.env` file which is added into the `.gitignore`. \n",
    "In these kind of files you can store information that is not supposed to be published.\n",
    "With the `dotenv` package you can read the `.env` files and get the variables.\n",
    "(We will share the file with you on Slack!)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from dotenv import load_dotenv\n",
    "\n",
    "load_dotenv()\n",
    "\n",
    "DATABASE = os.getenv('DATABASE')\n",
    "USER_DB = os.getenv('USER_DB')\n",
    "PASSWORD = os.getenv('PASSWORD')\n",
    "HOST = os.getenv('HOST')\n",
    "PORT = os.getenv('PORT')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The function from the psycopg2 package to create a connection is called `connect()`.\n",
    "`connect()` expects the parameters listed above as input in order to connect to the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create connection object conn\n",
    "conn = psycopg2.connect(\n",
    "    database=DATABASE,\n",
    "    user=USER_DB,\n",
    "    password=PASSWORD,\n",
    "    host=HOST,\n",
    "    port=PORT\n",
    ")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Retrieving data from the database with psycopg2\n",
    "\n",
    "Before we can use our connection to get data, we have to create a cursor. A cursor allows Python code to execute PostgreSQL commmands in a database session.\n",
    "A cursor has to be created with the `cursor()` method of our connection object conn."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can run SQL-Queries with `cur.execute('QUERY')` and then run `cur.fetchall()` to get the data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(datetime.date(2014, 10, 13), 221900.0, 7129300520, 1),\n",
       " (datetime.date(2014, 12, 9), 538000.0, 6414100192, 2),\n",
       " (datetime.date(2015, 2, 25), 180000.0, 5631500400, 3),\n",
       " (datetime.date(2014, 12, 9), 604000.0, 2487200875, 4),\n",
       " (datetime.date(2015, 2, 18), 510000.0, 1954400510, 5),\n",
       " (datetime.date(2014, 5, 12), 1230000.0, 7237550310, 6),\n",
       " (datetime.date(2014, 6, 27), 257500.0, 1321400060, 7),\n",
       " (datetime.date(2015, 1, 15), 291850.0, 2008000270, 8),\n",
       " (datetime.date(2015, 4, 15), 229500.0, 2414600126, 9),\n",
       " (datetime.date(2015, 3, 12), 323000.0, 3793500160, 10)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('SELECT * FROM eda.king_county_house_sales LIMIT 10')\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With `conn.close()` you can close the connection again."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#close the connection\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But we want to work with the data. The easiest way is to import the data into pandas dataframes. We can use `pd.read_sql_query` or `pd.read_sql_table` or for convenience `pd.read_sql`.\n",
    "\n",
    "This function is a convenience wrapper around read_sql_table and read_sql_query (for backward compatibility). It will delegate to the specific function depending on the provided input. A SQL query will be routed to read_sql_query , while a database table name will be routed to read_sql_table . Note that the delegated function might have more specific notes about their functionality not listed here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Open connection again because we closed it\n",
    "conn = psycopg2.connect(\n",
    "    database=DATABASE,\n",
    "    user=USER_DB,\n",
    "    password=PASSWORD,\n",
    "    host=HOST,\n",
    "    port=PORT\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import the data into a pandas dataframe\n",
    "query_string = \"SELECT * FROM eda.king_county_house_sales LIMIT 10\"\n",
    "df_psycopg = pd.read_sql(query_string, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#close the connection\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>date</th>\n",
       "      <th>price</th>\n",
       "      <th>house_id</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014-10-13</td>\n",
       "      <td>221900.0</td>\n",
       "      <td>7129300520</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2014-12-09</td>\n",
       "      <td>538000.0</td>\n",
       "      <td>6414100192</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-02-25</td>\n",
       "      <td>180000.0</td>\n",
       "      <td>5631500400</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2014-12-09</td>\n",
       "      <td>604000.0</td>\n",
       "      <td>2487200875</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-02-18</td>\n",
       "      <td>510000.0</td>\n",
       "      <td>1954400510</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date     price    house_id  id\n",
       "0  2014-10-13  221900.0  7129300520   1\n",
       "1  2014-12-09  538000.0  6414100192   2\n",
       "2  2015-02-25  180000.0  5631500400   3\n",
       "3  2014-12-09  604000.0  2487200875   4\n",
       "4  2015-02-18  510000.0  1954400510   5"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_psycopg.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#export the data to a csv-file\n",
    "df_psycopg.to_csv('data/eda.csv',index=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connecting and retrieving data via SQLAlchemy\n",
    "\n",
    "`sqlalchemy` works similarly. Here you have to create an engine with the database sting (a link that includes every information we entered in the conn object)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "\n",
    "#read the database string from the .env\n",
    "load_dotenv()\n",
    "\n",
    "DB_STRING = os.getenv('DB_STRING')\n",
    "\n",
    "db = create_engine(DB_STRING)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And then you can import that engine with a query into a pandas dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import the data to a pandas dataframe\n",
    "query_string = \"SELECT * FROM eda.king_county_house_sales\"\n",
    "df_sqlalchemy = pd.read_sql(query_string, db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>date</th>\n",
       "      <th>price</th>\n",
       "      <th>house_id</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014-10-13</td>\n",
       "      <td>221900.0</td>\n",
       "      <td>7129300520</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2014-12-09</td>\n",
       "      <td>538000.0</td>\n",
       "      <td>6414100192</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-02-25</td>\n",
       "      <td>180000.0</td>\n",
       "      <td>5631500400</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2014-12-09</td>\n",
       "      <td>604000.0</td>\n",
       "      <td>2487200875</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-02-18</td>\n",
       "      <td>510000.0</td>\n",
       "      <td>1954400510</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date     price    house_id  id\n",
       "0  2014-10-13  221900.0  7129300520   1\n",
       "1  2014-12-09  538000.0  6414100192   2\n",
       "2  2015-02-25  180000.0  5631500400   3\n",
       "3  2014-12-09  604000.0  2487200875   4\n",
       "4  2015-02-18  510000.0  1954400510   5"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sqlalchemy.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Because we don't want to run the queries over and over again we can export the data into a .csv file in order to use it in other notebooks as well. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#export the data to a csv-file\n",
    "df_sqlalchemy.to_csv('eda.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import the data from a csv-file\n",
    "df_import = pd.read_csv('data/eda.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.12 ('.venv': venv)",
   "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.9.12"
  },
  "vscode": {
   "interpreter": {
    "hash": "86bfcc733a99316639cfae738bd2c12342c3c5e2c4e3f470908e9f9639795c12"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}