01-docker-terraform/2_docker_sql/pg-test-connection.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8d7e4358",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5b2042e",
   "metadata": {},
   "source": [
    "```bash\n",
    "pip install sqlalchemy psycopg2-binary \n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a1096f72",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f569baa5",
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine('postgresql://root:root@localhost:5432/ny_taxi')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "523f5cf5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.base.Connection at 0x23600a83580>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "9f5a61a1",
   "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>number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   number\n",
       "0       1"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT 1 as number;\n",
    "\"\"\"\n",
    "\n",
    "pd.read_sql(query, con=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "e1c509ea",
   "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>schemaname</th>\n",
       "      <th>tablename</th>\n",
       "      <th>tableowner</th>\n",
       "      <th>tablespace</th>\n",
       "      <th>hasindexes</th>\n",
       "      <th>hasrules</th>\n",
       "      <th>hastriggers</th>\n",
       "      <th>rowsecurity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>public</td>\n",
       "      <td>yellow_tripdata_trip</td>\n",
       "      <td>root</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  schemaname             tablename tableowner tablespace  hasindexes  \\\n",
       "0     public  yellow_tripdata_trip       root       None       False   \n",
       "\n",
       "   hasrules  hastriggers  rowsecurity  \n",
       "0     False        False        False  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT *\n",
    "FROM pg_catalog.pg_tables\n",
    "WHERE schemaname != 'pg_catalog' AND \n",
    "    schemaname != 'information_schema';\n",
    "\"\"\"\n",
    "\n",
    "pd.read_sql(query, con=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a1d1f971",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('yellow_tripdata_2021-01.csv', nrows=100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f9e76d7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.tpep_pickup_datetime = pd.to_datetime(df.tpep_pickup_datetime)\n",
    "df.tpep_dropoff_datetime = pd.to_datetime(df.tpep_dropoff_datetime)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "f711ab67",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_sql(name='yellow_tripdata_trip', con=engine, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55ca3779",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "9549987c",
   "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>VendorID</th>\n",
       "      <th>tpep_pickup_datetime</th>\n",
       "      <th>tpep_dropoff_datetime</th>\n",
       "      <th>passenger_count</th>\n",
       "      <th>trip_distance</th>\n",
       "      <th>RatecodeID</th>\n",
       "      <th>store_and_fwd_flag</th>\n",
       "      <th>PULocationID</th>\n",
       "      <th>DOLocationID</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>extra</th>\n",
       "      <th>mta_tax</th>\n",
       "      <th>tip_amount</th>\n",
       "      <th>tolls_amount</th>\n",
       "      <th>improvement_surcharge</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>congestion_surcharge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2021-01-01 00:30:10</td>\n",
       "      <td>2021-01-01 00:36:12</td>\n",
       "      <td>1</td>\n",
       "      <td>2.10</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>142</td>\n",
       "      <td>43</td>\n",
       "      <td>2</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>11.80</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2021-01-01 00:51:20</td>\n",
       "      <td>2021-01-01 00:52:19</td>\n",
       "      <td>1</td>\n",
       "      <td>0.20</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>238</td>\n",
       "      <td>151</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>4.30</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2021-01-01 00:43:30</td>\n",
       "      <td>2021-01-01 01:11:06</td>\n",
       "      <td>1</td>\n",
       "      <td>14.70</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>132</td>\n",
       "      <td>165</td>\n",
       "      <td>1</td>\n",
       "      <td>42.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>8.65</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>51.95</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2021-01-01 00:15:48</td>\n",
       "      <td>2021-01-01 00:31:01</td>\n",
       "      <td>0</td>\n",
       "      <td>10.60</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>138</td>\n",
       "      <td>132</td>\n",
       "      <td>1</td>\n",
       "      <td>29.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>6.05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>36.35</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-01 00:31:49</td>\n",
       "      <td>2021-01-01 00:48:21</td>\n",
       "      <td>1</td>\n",
       "      <td>4.94</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>68</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "      <td>16.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>4.06</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>24.36</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>2021-01-01 00:16:29</td>\n",
       "      <td>2021-01-01 00:24:30</td>\n",
       "      <td>1</td>\n",
       "      <td>1.60</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>224</td>\n",
       "      <td>68</td>\n",
       "      <td>1</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.35</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>14.15</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>2021-01-01 00:00:28</td>\n",
       "      <td>2021-01-01 00:17:28</td>\n",
       "      <td>1</td>\n",
       "      <td>4.10</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>95</td>\n",
       "      <td>157</td>\n",
       "      <td>2</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>17.30</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>2021-01-01 00:12:29</td>\n",
       "      <td>2021-01-01 00:30:34</td>\n",
       "      <td>1</td>\n",
       "      <td>5.70</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>90</td>\n",
       "      <td>40</td>\n",
       "      <td>2</td>\n",
       "      <td>18.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>21.80</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>2021-01-01 00:39:16</td>\n",
       "      <td>2021-01-01 01:00:13</td>\n",
       "      <td>1</td>\n",
       "      <td>9.10</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>97</td>\n",
       "      <td>129</td>\n",
       "      <td>4</td>\n",
       "      <td>27.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>28.80</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>2021-01-01 00:26:12</td>\n",
       "      <td>2021-01-01 00:39:46</td>\n",
       "      <td>2</td>\n",
       "      <td>2.70</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>263</td>\n",
       "      <td>142</td>\n",
       "      <td>1</td>\n",
       "      <td>12.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>3.15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>18.95</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   VendorID tpep_pickup_datetime tpep_dropoff_datetime  passenger_count  \\\n",
       "0         1  2021-01-01 00:30:10   2021-01-01 00:36:12                1   \n",
       "1         1  2021-01-01 00:51:20   2021-01-01 00:52:19                1   \n",
       "2         1  2021-01-01 00:43:30   2021-01-01 01:11:06                1   \n",
       "3         1  2021-01-01 00:15:48   2021-01-01 00:31:01                0   \n",
       "4         2  2021-01-01 00:31:49   2021-01-01 00:48:21                1   \n",
       "5         1  2021-01-01 00:16:29   2021-01-01 00:24:30                1   \n",
       "6         1  2021-01-01 00:00:28   2021-01-01 00:17:28                1   \n",
       "7         1  2021-01-01 00:12:29   2021-01-01 00:30:34                1   \n",
       "8         1  2021-01-01 00:39:16   2021-01-01 01:00:13                1   \n",
       "9         1  2021-01-01 00:26:12   2021-01-01 00:39:46                2   \n",
       "\n",
       "   trip_distance  RatecodeID store_and_fwd_flag  PULocationID  DOLocationID  \\\n",
       "0           2.10           1                  N           142            43   \n",
       "1           0.20           1                  N           238           151   \n",
       "2          14.70           1                  N           132           165   \n",
       "3          10.60           1                  N           138           132   \n",
       "4           4.94           1                  N            68            33   \n",
       "5           1.60           1                  N           224            68   \n",
       "6           4.10           1                  N            95           157   \n",
       "7           5.70           1                  N            90            40   \n",
       "8           9.10           1                  N            97           129   \n",
       "9           2.70           1                  N           263           142   \n",
       "\n",
       "   payment_type  fare_amount  extra  mta_tax  tip_amount  tolls_amount  \\\n",
       "0             2          8.0    3.0      0.5        0.00           0.0   \n",
       "1             2          3.0    0.5      0.5        0.00           0.0   \n",
       "2             1         42.0    0.5      0.5        8.65           0.0   \n",
       "3             1         29.0    0.5      0.5        6.05           0.0   \n",
       "4             1         16.5    0.5      0.5        4.06           0.0   \n",
       "5             1          8.0    3.0      0.5        2.35           0.0   \n",
       "6             2         16.0    0.5      0.5        0.00           0.0   \n",
       "7             2         18.0    3.0      0.5        0.00           0.0   \n",
       "8             4         27.5    0.5      0.5        0.00           0.0   \n",
       "9             1         12.0    3.0      0.5        3.15           0.0   \n",
       "\n",
       "   improvement_surcharge  total_amount  congestion_surcharge  \n",
       "0                    0.3         11.80                   2.5  \n",
       "1                    0.3          4.30                   0.0  \n",
       "2                    0.3         51.95                   0.0  \n",
       "3                    0.3         36.35                   0.0  \n",
       "4                    0.3         24.36                   2.5  \n",
       "5                    0.3         14.15                   2.5  \n",
       "6                    0.3         17.30                   0.0  \n",
       "7                    0.3         21.80                   2.5  \n",
       "8                    0.3         28.80                   0.0  \n",
       "9                    0.3         18.95                   2.5  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT * FROM yellow_tripdata_trip LIMIT 10\n",
    "\"\"\"\n",
    "\n",
    "pd.read_sql(query, con=engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bcd61c63",
   "metadata": {},
   "source": [
    "```sql\n",
    "SELECT *\n",
    "FROM pg_catalog.pg_tables\n",
    "WHERE schemaname != 'pg_catalog' AND \n",
    "    schemaname != 'information_schema';\n",
    "```\n",
    "\n",
    "Source: https://www.postgresqltutorial.com/postgresql-show-tables/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ffae4651",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}