HOW TO SEND DATA from Python to PSQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# HOW TO: Insert Data From Python Into PSQL\n",
    "\n",
    "`--------------------------`\n",
    "`With Mr Fugu Data Science` `-------------------------`\n",
    "\n",
    "\n",
    "[Follow me on Github](https://github.com/MrFuguDataScience) | [Check me out on YouTube](https://www.youtube.com/channel/UCbni-TDI-Ub8VlGaP8HLTNw?view_as=subscriber)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Create Files to interact with Psycopg & Hide Database User credentials**:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "+ If you are working in a real world scenario, you will most likely need to hide your credentials,   permissions, login & password\n",
    "that is where these files shine. \n",
    "\n",
    "[create init and config files](https://towardsdatascience.com/python-and-postgresql-how-to-access-a-postgresql-database-like-a-data-scientist-b5a9c5a0ea43) | [Python_configparser doc](https://docs.python.org/3/library/configparser.html) | [Postgres_tutorial_Python_PSQL](https://www.postgresqltutorial.com/postgresql-python/connect/)\n",
    "\n",
    "1<sup>st</sup>) : create the initialization file:\n",
    "\n",
    "+ use your terminal or notepad, unless your favorite editor directly converts to this format `.ini` file.\n",
    "    + on `Mac` I did `touch database_file_init.ini`\n",
    "        + `vi database_file.ini`     *then inside the file type following lines:*\n",
    "            + `[postgresql]`\n",
    "            + `host=localhost`\n",
    "            + `database=what_databse_you_want_to_access`\n",
    "            + `user= some_user_you_created_for_this_user_in_psql`\n",
    "            + `password= some_password_you_have_to_this_db`\n",
    "            \n",
    "`------------------------------------------------`\n",
    "\n",
    "2<sup>nd</sup>) : now the `config.py` file this will be used to take data from `init file` and outputs a `dictionary`.\n",
    "\n",
    "+ this file will look like this as an **example**:\n",
    "`{‘host’: ‘localhost’, ‘database’: ‘suppliers’, ‘user’: ‘postgres’, ‘password’: ‘postgres’}` when it is read.\n",
    "\n",
    "`HERE IS THE CODE`:\n",
    "\n",
    "`____________________________________________________`\n",
    "\n",
    "#!/usr/bin/python    (can also do virtenv,env)\n",
    "\n",
    "from configparser import ConfigParser\n",
    " \n",
    "def config(filename='database.ini', section='postgresql'):\n",
    "    # create a parser\n",
    "    parser = ConfigParser()\n",
    "    # read config file\n",
    "    parser.read(filename)\n",
    " \n",
    "    # get section, default to postgresql\n",
    "    db = {}\n",
    "    \n",
    "    # Checks to see if section (postgresql) parser exists\n",
    "    if parser.has_section(section):\n",
    "        params = parser.items(section)\n",
    "        for param in params:\n",
    "            db[param[0]] = param[1]\n",
    "         \n",
    "    # Returns an error if a parameter is called that is not listed in the initialization file\n",
    "    else:\n",
    "        raise Exception('Section {0} not found in the {1} file'.format(section, filename))\n",
    " \n",
    "    return db\n",
    "    \n",
    "    \n",
    "**This code was adpated from online material on Postgres website and used in 1<sup>st</sup> & 3<sup>rd</sup> link above**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`_____________________________________________________________________________________`\n",
    "# Install Psycopg2:\n",
    "\n",
    "+ `python -m pip install psycopg2`\n",
    "\n",
    "* if this doesn't work, try changing `pip` to `pip3` depending on what version of Python you are using\n",
    "\n",
    "+ if that doesn't work either try to do: `conda install -c anaconda psycopg2`\n",
    "\n",
    "\n",
    "**For my installation I found problems with installation: I did two steps**: `pip3 install psycopg2` followed by the `conda install -c anaconda psycopg2`. The first install showed complete but did not work, I feel that I may have been imcomplete with all the dependencies needed. Also, I was getting a `Python 2.7 error hashing`. `I suggest that you first check the version of Python and Location PATH. I feel that my default Location may be anaconda calling Python if I remember correctly.`\n",
    "\n",
    "+ I thought this was from using a wrong default version of Python, changed my default and still didn't work. That was not the entire case, try the above and hope this works for you.\n",
    "\n",
    "+ Separate source for installing: depending on situation. [install on Mac](https://www.youtube.com/watch?v=N4RxnQH2pVY)\n",
    "\n",
    "`________________________________`\n",
    "\n",
    "\n",
    "# Install: Memory_Profiler\n",
    "\n",
    "+ `pip3 install memory-profiler`  (pip,pip3.5 all depends what version you have of Python)  \n",
    "\n",
    "\n",
    "+ **Anaconda version**: \n",
    "\n",
    "`conda config --add channels conda-forge`\n",
    "\n",
    "`conda install memory_profiler`\n",
    "\n",
    "[memory_profiler doc](https://pypi.org/project/memory-profiler/)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `Create New User If Needed To Work Within PSQL`:\n",
    "\n",
    "\n",
    "`psql postgres`   (if you have `Homebrew` install)\n",
    "\n",
    "`CREATE ROLE` **Somename_youlike**` WITH LOGIN PASSWORD` **'Some_password_you_want'**`;`\n",
    "\n",
    "`ALTER` **Your_new_user_name** `CREATEDB;`   (*Giving permissions*)\n",
    "\n",
    "*Now You Can Check if it worked here is my printout*:\n",
    "\n",
    "| Role name        \t| List of roles Role  name,Attributes                          \t| Member of \t|\n",
    "|------------------\t|--------------------------------------------------------------\t|-----------\t|\n",
    "| mrfugu           \t| Create DB                                                    \t| {}        \t|\n",
    "| my_computer_name \t| Superuser, Create role,  Create DB, Replication,  Bypass RLS \t| {}        \t|\n",
    "\n",
    "`________________________________________________`\n",
    "\n",
    "# Create a new DataBase:\n",
    "\n",
    "`createdb -O UserName DB_NameYouMade`  (can create outside of `psql`)\n",
    "\n",
    "`psql postgress` \n",
    "\n",
    "and do the command `\\l` to see what databases are available and to who\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2             # python->psql connection\n",
    "import psycopg2.extras\n",
    "\n",
    "import pandas as pd         # create dataframes \n",
    "import os                   # fetch files\n",
    "\n",
    "import time                 # timing operations\n",
    "import memory_profiler      # managing memory usage\n",
    "from memory_profiler import memory_usage\n",
    "\n",
    "from functools import wraps # decorator/wrapper\n",
    "\n",
    "from typing import Iterator, Optional, Dict, Any,List  # Create Iterator for One-By-One Loading \n",
    "\n",
    "import io\n",
    "\n",
    "# Import the 'config' function from the config_user_dta.py file:\n",
    "from config_user_dta import config"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Verify Psycog2 Install\n",
    "!pip3 show psycopg2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "+ `Connection`: this is a class is responsible for *Transactions*\n",
    "    \n",
    "    * Two `methods` for *terminating a Transaction*: `commit()` and `rollback()`\n",
    "        \n",
    "        * `commit()`: if you want to `permanently` change database\n",
    "        \n",
    "        * `rollback()`: if you want to `change` database, this is convient if there is a failure somewhere and you want to create an exception and not lose, accept partial data, or corrupted data.  \n",
    "\n",
    "+ `Cursor`: when you use first issue a statement for `PSQL`, the `Cursor` *object* is creating a Transaction in `psycopg2`. \n",
    "    \n",
    "    * From this moment all statements in the same Transaction will execute, unless *you abort or there is a failure.* \n",
    "        \n",
    "       \n",
    "        \n",
    "        \n",
    "[Official PSQL docs](https://www.postgresqltutorial.com/postgresql-python/transaction/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Establish a connection to the database by creating a cursor object\n",
    "\n",
    "# Get the config params\n",
    "params_ = config()\n",
    "\n",
    "# Connect to the Postgres_DB:\n",
    "conn = psycopg2.connect(**params_)\n",
    "\n",
    "# Create new_cursor allowing us to write Python to execute PSQL:\n",
    "cur = conn.cursor()\n",
    "\n",
    "conn.autocommit = True  # read documentation understanding when to Use & NOT use (TRUE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connecting to the PostgreSQL database...\n",
      "PostgreSQL database version:\n",
      "('PostgreSQL 12.2 on x86_64-apple-darwin17.7.0, compiled by Apple LLVM version 10.0.0 (clang-1000.11.45.5), 64-bit',)\n",
      "Database connection closed.\n"
     ]
    }
   ],
   "source": [
    "def connect():\n",
    "    \"\"\" Connect to the PostgreSQL database server \"\"\"\n",
    "    conn = None\n",
    "    try:\n",
    "        # read connection parameters\n",
    "        params = config()\n",
    " \n",
    "        # connect to the PostgreSQL server\n",
    "        print('Connecting to the PostgreSQL database...')\n",
    "        conn = psycopg2.connect(**params)\n",
    "      \n",
    "        # create a cursor\n",
    "        cur = conn.cursor()\n",
    "        \n",
    "   # execute a statement\n",
    "        print('PostgreSQL database version:')\n",
    "        cur.execute('SELECT version()')\n",
    " \n",
    "        # display the PostgreSQL database server version\n",
    "        db_version = cur.fetchone()\n",
    "        print(db_version)\n",
    "       \n",
    "       # close the communication with the PostgreSQL\n",
    "        cur.close()\n",
    "    except (Exception, psycopg2.DatabaseError) as error:\n",
    "        print(error)\n",
    "    finally:\n",
    "        if conn is not None:\n",
    "            conn.close()\n",
    "            print('Database connection closed.')\n",
    " \n",
    " \n",
    "if __name__ == '__main__':\n",
    "    connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/Users/zatoichi59/Desktop/Projects\n",
      "fake_users_R.csv\n",
      "/Users/zatoichi59/Desktop/Projects\n"
     ]
    }
   ],
   "source": [
    "# locate file in entire directory:\n",
    "\n",
    "print(os.getcwd())\n",
    "def os_dir_search(file):\n",
    "    u=[]\n",
    "    for p,n,f in os.walk(os.getcwd()):\n",
    "        \n",
    "        for a in f:\n",
    "            a = str(a)\n",
    "            if a.endswith(file): # can be (.csv) or a file like I did and search \n",
    "                print(a)\n",
    "                print(p)\n",
    "                t=pd.read_csv(p+'/'+file,names=['row_id','credit_card',\n",
    "                                                'email','first_name','last_name','primary_phone'],header=0)\n",
    "            \n",
    "    return t\n",
    "\n",
    "# need to use (.csv,.png, etc) because it is looking by file type ending\n",
    "\n",
    "\n",
    "addr_df=os_dir_search('fake_users_R.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>credit_card</th>\n",
       "      <th>email</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>primary_phone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5399-3484-4724-7187</td>\n",
       "      <td>gso@qiegan.sqe</td>\n",
       "      <td>Donyell Ann</td>\n",
       "      <td>Ospina</td>\n",
       "      <td>5219459148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1630-5261-6108-7631</td>\n",
       "      <td>xnji@gfruaxqnvm.fha</td>\n",
       "      <td>Bishop</td>\n",
       "      <td>Siyed</td>\n",
       "      <td>4164254716</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4435-3866-1076-3595</td>\n",
       "      <td>dvyco@tkzhsop.zxg</td>\n",
       "      <td>Connor</td>\n",
       "      <td>Powers</td>\n",
       "      <td>3627413915</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3489-7099-9906-8660</td>\n",
       "      <td>fy@uvfhplatmz.cam</td>\n",
       "      <td>Kylie</td>\n",
       "      <td>Her</td>\n",
       "      <td>3562764561</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8631-4500-5666-1510</td>\n",
       "      <td>rztkvliou@dkeinhgysf.deo</td>\n",
       "      <td>Anthony</td>\n",
       "      <td>Vo</td>\n",
       "      <td>7345795348</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           credit_card                     email   first_name last_name  \\\n",
       "0  5399-3484-4724-7187            gso@qiegan.sqe  Donyell Ann    Ospina   \n",
       "1  1630-5261-6108-7631       xnji@gfruaxqnvm.fha       Bishop     Siyed   \n",
       "2  4435-3866-1076-3595         dvyco@tkzhsop.zxg       Connor    Powers   \n",
       "3  3489-7099-9906-8660         fy@uvfhplatmz.cam        Kylie       Her   \n",
       "4  8631-4500-5666-1510  rztkvliou@dkeinhgysf.deo      Anthony        Vo   \n",
       "\n",
       "   primary_phone  \n",
       "0     5219459148  \n",
       "1     4164254716  \n",
       "2     3627413915  \n",
       "3     3562764561  \n",
       "4     7345795348  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "addr_df_=addr_df.iloc[:,1:]\n",
    "addr_df_.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# CREATE TABLE FOR PSQL: staging_fake_ppl\n",
    "\n",
    "def create_staging_table(cursor) -> None:\n",
    "    cursor.execute(\"\"\"\n",
    "        DROP TABLE IF EXISTS staging_fake_ppl;\n",
    "        CREATE UNLOGGED TABLE staging_fake_ppl (\n",
    "            credit_card         TEXT,\n",
    "            email               TEXT,\n",
    "            first_name          TEXT,\n",
    "            last_name           TEXT,\n",
    "            primary_phone       TEXT\n",
    "        );\"\"\")\n",
    "\n",
    "# look at the documentation of PSQL (UNLOGGED TABLE vs TEMP)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `Cursor` and `Connection` are Context Managers :\n",
    "+ allowing you to use the `with` statment, and `psycopg2 will commit Transaction unless there is an error`\n",
    "\n",
    "[further reading](https://www.postgresqltutorial.com/postgresql-python/transaction/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "with conn.cursor() as cursor:\n",
    "    create_staging_table(cursor)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "start = time.perf_counter()  # using the highest resolution timer\n",
    "time.sleep(1) # do work\n",
    "elapsed = time.perf_counter() - start"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Decorator Function: \n",
    "\"\"\" will display:  [function] we call\n",
    "                   [Time] it takes to run operations\n",
    "                   [Memory] Used\n",
    "\"\"\"\n",
    "def profile(fn):\n",
    "    @wraps(fn)\n",
    "    def inner(*args, **kwargs):\n",
    "        fn_kwargs_str = ', '.join(f'{k}={v}' for k, v in kwargs.items())\n",
    "        print(f'\\n{fn.__name__}({fn_kwargs_str})')\n",
    "\n",
    "        # Measure time\n",
    "        t = time.perf_counter()\n",
    "        retval = fn(*args, **kwargs)\n",
    "        elapsed = time.perf_counter() - t\n",
    "        print(f'Time   {elapsed:0.4}')\n",
    "\n",
    "        # Measure memory\n",
    "        mem, retval = memory_usage((fn, args, kwargs), retval=True, timeout=200, interval=1e-7)\n",
    "\n",
    "        print(f'Memory {max(mem) - min(mem)}')\n",
    "        return retval\n",
    "\n",
    "    return inner"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "s_=addr_df_.to_dict('records')\n",
    "# s_[:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Send .CSV Python --> PSQL "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# addr_df_.to_csv('address_Python_convertR.csv',index=False)\n",
    "\n",
    "# sql = \"COPY %s FROM STDIN WITH CSV HEADER DELIMITER AS ','\"\n",
    "# file = open('address_Python_convertR.csv', \"r\")\n",
    "# table = 'staging_fake_ppl'\n",
    "# with conn.cursor() as cur:\n",
    "#     cur.execute(\"truncate \" + table + \";\")  #avoiding uploading duplicate data!\n",
    "#     cur.copy_expert(sql=sql % table, file=file)\n",
    "#     conn.commit()\n",
    "#     cur.close()\n",
    "#     conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "send_csv_to_psql()\n",
      "Time   0.0584\n",
      "Memory 0.0\n"
     ]
    }
   ],
   "source": [
    "addr_df_.to_csv('address_Python_convertR.csv',index=False)\n",
    "\n",
    "@profile\n",
    "def send_csv_to_psql(connection,csv,table_):\n",
    "    sql = \"COPY %s FROM STDIN WITH CSV HEADER DELIMITER AS ','\"\n",
    "    file = open(csv, \"r\")\n",
    "    table = table_\n",
    "    with connection.cursor() as cur:\n",
    "        cur.execute(\"truncate \" + table + \";\")  #avoiding uploading duplicate data!\n",
    "        cur.copy_expert(sql=sql % table, file=file)\n",
    "        conn.commit()\n",
    "#         cur.close()\n",
    "#         conn.close()\n",
    "    return conn.commit()\n",
    "\n",
    "send_csv_to_psql(conn,'address_Python_convertR.csv','staging_fake_ppl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "connect() missing 1 required positional argument: 'params_dic'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-25-f75325e7afcc>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m     11\u001b[0m     \u001b[0;32mreturn\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     12\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 13\u001b[0;31m \u001b[0mconnect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m: connect() missing 1 required positional argument: 'params_dic'"
     ]
    }
   ],
   "source": [
    "# def connect(params_dic):\n",
    "#     \"\"\" Connect to the PostgreSQL database server \"\"\"\n",
    "#     conn = None\n",
    "#     try:\n",
    "#         # connect to the PostgreSQL server\n",
    "#         print('Connecting to the PostgreSQL database...')\n",
    "#         conn = psycopg2.connect(**params_dic)\n",
    "#     except (Exception, psycopg2.DatabaseError) as error:\n",
    "#         print(error)\n",
    "#         sys.exit(1) \n",
    "#     return conn\n",
    "\n",
    "# connect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Simple Query with `Psycog2`:\n",
    "\n",
    "+ when doing a `SELECT` query use: `fetchone(), fetchall() or fetchmany()` methods\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5826,)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_=\"SELECT COUNT(*) FROM staging_fake_ppl\"\n",
    "cur.execute(sql_)\n",
    "cur.fetchone()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `Alternate` Way to Query:\n",
    "\n",
    "+ Quick and dirty way to `Query` PSQL and bring data into Python as a DF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas.io.sql as sqlio\n",
    "# conn = psycopg2.connect(\"host='{}' port={} dbname='{}' user={} password={}\".format(host, port, dbname, username, pwd))\n",
    "sql = \"select count(*) from staging_fake_ppl;\"\n",
    "dat = sqlio.read_sql_query(sql, conn)\n",
    "conn = None\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5826</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count\n",
       "0   5826"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dat\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Convert `Df --> List(Dict())` then sending from `Python-->PSQL`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_staging_table_(cursor) -> None:\n",
    "    cursor.execute(\"\"\"\n",
    "        DROP TABLE IF EXISTS staging_fake_ppl02;\n",
    "        CREATE UNLOGGED TABLE staging_fake_ppl02 (\n",
    "            credit_card         TEXT,\n",
    "            email               TEXT,\n",
    "            first_name          TEXT,\n",
    "            last_name           TEXT,\n",
    "            primary_phone       TEXT\n",
    "        );\"\"\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = psycopg2.connect(**params_)\n",
    "\n",
    "# Create new_cursor allowing us to write Python to execute PSQL:\n",
    "cur = conn.cursor()\n",
    "\n",
    "conn.autocommit = True  # read documentation understanding when to Use & NOT use (TRUE)\n",
    "\n",
    "\n",
    "with conn.cursor() as cursor:\n",
    "    create_staging_table_(cursor)\n",
    "# cur.close()\n",
    "# conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import sql  # the patched version (file is named sql.py)\n",
    "# sql.write_frame(addr_df_, 'staging_fake_ppl02', conn, flavor='postgresql')\n",
    "\n",
    "@profile\n",
    "\n",
    "def fcn(df,table,cur):\n",
    "#     df=addr_df_\n",
    "#     table='staging_fake_ppl02'\n",
    "    # df is the dataframe\n",
    "    if len(df) > 0:\n",
    "        df_columns = list(df)\n",
    "        # create (col1,col2,...)\n",
    "        columns = \",\".join(df_columns)\n",
    "\n",
    "        # create VALUES('%s', '%s\",...) one '%s' per column\n",
    "        values = \"VALUES({})\".format(\",\".join([\"%s\" for _ in df_columns])) \n",
    "\n",
    "        #create INSERT INTO table (columns) VALUES('%s',...)\n",
    "        insert_stmt = \"INSERT INTO {} ({}) {}\".format(table,columns,values)\n",
    "        cur.execute(\"truncate \" + table + \";\")  #avoiding uploading duplicate data!\n",
    "        cur = conn.cursor()\n",
    "        psycopg2.extras.execute_batch(cur, insert_stmt, df.values)\n",
    "    conn.commit()\n",
    "   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "fcn()\n",
      "Time   0.2678\n",
      "Memory 0.0\n"
     ]
    }
   ],
   "source": [
    "fcn(addr_df_,'staging_fake_ppl02',cur)\n",
    "\n",
    "\n",
    "# Close the cursor and connection to so the server can allocate\n",
    "# bandwidth to other requests\n",
    "cur.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# UPDATE TABLE:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ALTER TABLE vendors ADD COLUMN ID SERIAL PRIMARY KEY;\n",
    "\n",
    "# https://www.tutorialspoint.com/python_data_access/python_postgresql_update_table.htm\n",
    "\n",
    "\n",
    "# https://www.postgresqltutorial.com/postgresql-python/transaction/  (i≠≠nserts and updates)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# INSERTS:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://www.postgresqltutorial.com/postgresql-python/insert/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Citations:\n",
    "\n",
    "https://www.datacamp.com/community/tutorials/tutorial-postgresql-python\n",
    "\n",
    "https://stackoverflow.com/questions/49264194/import-py-file-in-another-directory-in-jupyter-notebook\n",
    "\n",
    "https://www.postgresqltutorial.com/postgresql-python/connect/\n",
    "\n",
    "https://stackoverflow.com/questions/39767810/cant-install-psycopg2-package-through-pip-install-is-this-because-of-sierra\n",
    "\n",
    "https://opensource.com/article/19/5/python-3-default-mac\n",
    "\n",
    "https://dev.to/irfnhm/how-to-set-python3-as-a-default-python-version-on-mac-4jjf\n",
    "\n",
    "https://www.google.com/search?q=ImportError%3A+cannot+import+name+md5&oq=ImportError%3A+cannot+import+name+md5&aqs=chrome..69i57j69i58.436j0j7&sourceid=chrome&ie=UTF-8\n",
    "\n",
    "https://stackoverflow.com/questions/34617452/how-to-update-xcode-from-command-line\n",
    "\n",
    "https://www.postgresql.org/docs/12/auth-password.html\n",
    "\n",
    "https://hakibenita.com/fast-load-data-python-postgresql\n",
    "\n",
    "https://pypi.org/project/memory-profiler/\n",
    "\n",
    "https://docs.python.org/3/library/functools.html\n",
    "\n",
    "https://medium.com/@viviennediegoencarnacion/getting-started-with-postgresql-on-mac-e6a5f48ee399\n",
    "\n",
    "https://lerner.co.il/2019/05/05/making-your-python-decorators-even-better-with-functool-wraps/ (decorators/wrappers)\n",
    "\n",
    "https://docs.python.org/3/library/configparser.html\n",
    "\n",
    "https://hackersandslackers.com/psycopg2-postgres-python/\n",
    "\n",
    "https://pynative.com/python-postgresql-tutorial/\n",
    "\n",
    "https://hackersandslackers.com/psycopg2-postgres-python/  (create Classes)\n",
    "\n",
    "https://stackoverflow.com/questions/23103962/how-to-write-dataframe-to-postgres-table  (df_to_psql_table)\n",
    "\n",
    "https://alvinalexander.com/blog/post/postgresql/log-in-postgresql-database/  (cmd line shortcuts psql)\n",
    "\n",
    "https://stackoverflow.com/questions/35651586/psycopg2-cursor-already-closed"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}