2. Postgres_to_csv.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "import os\n",
    "from os.path import join, dirname\n",
    "from dotenv import load_dotenv\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load a .env properties file to environment variables\n",
    "dotenv_path = 'env_vars2.env'\n",
    "load_dotenv(dotenv_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connect to Heroku Postgresql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<connection object at 0x000002BCF5846268; dsn: 'user=aewttyniajaqmn password=xxx dbname=dbakbpdimgqbtc host=ec2-184-72-238-22.compute-1.amazonaws.com', closed: 0>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# connect to the database server\n",
    "connection = psycopg2.connect(dbname=os.getenv('DBNAME'), host=os.getenv('HOST'),\n",
    "                              user=os.getenv('USER'), password=os.getenv('PASSWORD'))\n",
    "connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<cursor object at 0x000002BCF5800C88; closed: 0>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a Cursor, to be used for a query to the database\n",
    "cursor = connection.cursor()\n",
    "cursor"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Select all the data from SQL table, and get column names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"select * from close\")\n",
    "allResults = cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "column_names = [i[0] for i in cursor.description]\n",
    "#column_names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# close cursor and connection\n",
    "if(connection):\n",
    "    cursor.close()\n",
    "    connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>MMM</th>\n",
       "      <th>ABT</th>\n",
       "      <th>ABBV</th>\n",
       "      <th>ABMD</th>\n",
       "      <th>ACN</th>\n",
       "      <th>ATVI</th>\n",
       "      <th>ADBE</th>\n",
       "      <th>AMD</th>\n",
       "      <th>AAP</th>\n",
       "      <th>...</th>\n",
       "      <th>WLTW</th>\n",
       "      <th>WYNN</th>\n",
       "      <th>XEL</th>\n",
       "      <th>XRX</th>\n",
       "      <th>XLNX</th>\n",
       "      <th>XYL</th>\n",
       "      <th>YUM</th>\n",
       "      <th>ZBH</th>\n",
       "      <th>ZION</th>\n",
       "      <th>ZTS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-20</td>\n",
       "      <td>192.438171</td>\n",
       "      <td>61.074303</td>\n",
       "      <td>93.817474</td>\n",
       "      <td>446.209991</td>\n",
       "      <td>158.987991</td>\n",
       "      <td>76.227661</td>\n",
       "      <td>251.240005</td>\n",
       "      <td>16.52</td>\n",
       "      <td>138.946564</td>\n",
       "      <td>...</td>\n",
       "      <td>151.024399</td>\n",
       "      <td>169.843735</td>\n",
       "      <td>42.602283</td>\n",
       "      <td>26.176083</td>\n",
       "      <td>67.574547</td>\n",
       "      <td>68.487350</td>\n",
       "      <td>79.490181</td>\n",
       "      <td>111.165855</td>\n",
       "      <td>54.826199</td>\n",
       "      <td>86.439659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-06-21</td>\n",
       "      <td>189.951111</td>\n",
       "      <td>60.690987</td>\n",
       "      <td>91.239296</td>\n",
       "      <td>438.269989</td>\n",
       "      <td>157.277893</td>\n",
       "      <td>75.443962</td>\n",
       "      <td>248.830002</td>\n",
       "      <td>15.65</td>\n",
       "      <td>139.346115</td>\n",
       "      <td>...</td>\n",
       "      <td>149.991013</td>\n",
       "      <td>167.468033</td>\n",
       "      <td>42.573154</td>\n",
       "      <td>25.407610</td>\n",
       "      <td>66.629448</td>\n",
       "      <td>65.682693</td>\n",
       "      <td>78.379318</td>\n",
       "      <td>110.590637</td>\n",
       "      <td>55.158062</td>\n",
       "      <td>86.459526</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-06-22</td>\n",
       "      <td>190.903183</td>\n",
       "      <td>60.877731</td>\n",
       "      <td>89.272217</td>\n",
       "      <td>427.290009</td>\n",
       "      <td>156.884796</td>\n",
       "      <td>75.374519</td>\n",
       "      <td>243.630005</td>\n",
       "      <td>15.80</td>\n",
       "      <td>138.696838</td>\n",
       "      <td>...</td>\n",
       "      <td>151.723190</td>\n",
       "      <td>165.657043</td>\n",
       "      <td>42.573154</td>\n",
       "      <td>25.359579</td>\n",
       "      <td>65.694199</td>\n",
       "      <td>65.623436</td>\n",
       "      <td>78.998642</td>\n",
       "      <td>110.838577</td>\n",
       "      <td>54.181988</td>\n",
       "      <td>85.267258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-06-25</td>\n",
       "      <td>190.223129</td>\n",
       "      <td>59.727795</td>\n",
       "      <td>88.852081</td>\n",
       "      <td>416.200012</td>\n",
       "      <td>153.474472</td>\n",
       "      <td>74.104713</td>\n",
       "      <td>239.679993</td>\n",
       "      <td>15.11</td>\n",
       "      <td>137.118576</td>\n",
       "      <td>...</td>\n",
       "      <td>151.132675</td>\n",
       "      <td>162.151886</td>\n",
       "      <td>43.835304</td>\n",
       "      <td>24.447020</td>\n",
       "      <td>64.463600</td>\n",
       "      <td>65.445671</td>\n",
       "      <td>77.258629</td>\n",
       "      <td>109.817070</td>\n",
       "      <td>53.323044</td>\n",
       "      <td>84.154480</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-06-26</td>\n",
       "      <td>190.980911</td>\n",
       "      <td>59.767113</td>\n",
       "      <td>87.964043</td>\n",
       "      <td>428.369995</td>\n",
       "      <td>154.162430</td>\n",
       "      <td>74.670174</td>\n",
       "      <td>242.250000</td>\n",
       "      <td>15.50</td>\n",
       "      <td>136.918793</td>\n",
       "      <td>...</td>\n",
       "      <td>150.020538</td>\n",
       "      <td>162.794495</td>\n",
       "      <td>44.010063</td>\n",
       "      <td>24.331749</td>\n",
       "      <td>64.955849</td>\n",
       "      <td>65.751816</td>\n",
       "      <td>78.654579</td>\n",
       "      <td>111.631966</td>\n",
       "      <td>52.737396</td>\n",
       "      <td>83.468918</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 506 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date         MMM        ABT       ABBV        ABMD         ACN  \\\n",
       "0  2018-06-20  192.438171  61.074303  93.817474  446.209991  158.987991   \n",
       "1  2018-06-21  189.951111  60.690987  91.239296  438.269989  157.277893   \n",
       "2  2018-06-22  190.903183  60.877731  89.272217  427.290009  156.884796   \n",
       "3  2018-06-25  190.223129  59.727795  88.852081  416.200012  153.474472   \n",
       "4  2018-06-26  190.980911  59.767113  87.964043  428.369995  154.162430   \n",
       "\n",
       "        ATVI        ADBE    AMD         AAP    ...            WLTW  \\\n",
       "0  76.227661  251.240005  16.52  138.946564    ...      151.024399   \n",
       "1  75.443962  248.830002  15.65  139.346115    ...      149.991013   \n",
       "2  75.374519  243.630005  15.80  138.696838    ...      151.723190   \n",
       "3  74.104713  239.679993  15.11  137.118576    ...      151.132675   \n",
       "4  74.670174  242.250000  15.50  136.918793    ...      150.020538   \n",
       "\n",
       "         WYNN        XEL        XRX       XLNX        XYL        YUM  \\\n",
       "0  169.843735  42.602283  26.176083  67.574547  68.487350  79.490181   \n",
       "1  167.468033  42.573154  25.407610  66.629448  65.682693  78.379318   \n",
       "2  165.657043  42.573154  25.359579  65.694199  65.623436  78.998642   \n",
       "3  162.151886  43.835304  24.447020  64.463600  65.445671  77.258629   \n",
       "4  162.794495  44.010063  24.331749  64.955849  65.751816  78.654579   \n",
       "\n",
       "          ZBH       ZION        ZTS  \n",
       "0  111.165855  54.826199  86.439659  \n",
       "1  110.590637  55.158062  86.459526  \n",
       "2  110.838577  54.181988  85.267258  \n",
       "3  109.817070  53.323044  84.154480  \n",
       "4  111.631966  52.737396  83.468918  \n",
       "\n",
       "[5 rows x 506 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(allResults)\n",
    "df.columns = column_names\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('SP.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "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.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}