python/postgresql_from_python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Connecting to PostgreSQL \n",
    "\n",
    "Example of connecting to PostgreSQL databases from Python using the [`psycopg2`](http://initd.org/psycopg/) package.\n",
    "\n",
    "`psycopg2` is the most popular package for connecting to PostgreSQL databases.  There are frameworks, such as [`sqlalchemy`](https://www.sqlalchemy.org/), built on top of it that add additional functionality and ways of working with databases.  \n",
    "\n",
    "You need to have installed `psycopg2` before running this notebook.\n",
    "\n",
    "The packages for connecting to other types of SQL databases have a similar syntax and workflow.  They are governed by the [Python Database API Specification](https://www.python.org/dev/peps/pep-0249/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# install if needed\n",
    "!pip install psycopg2-binary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connect\n",
    "\n",
    "Fill in the connection information below.\n",
    "\n",
    "Note that in scripts that you write, especially those that you share, you probably don't want to hard code passwords into your file.  Alternatives are to read in an environment variable, read values from a configuration file that you don't share (just share a blank template), or include a step in your script or application to read the password from a command line argument or input from the user."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# example connection: update with your specifics\n",
    "conn = psycopg2.connect(dbname=\"workshop\", host=\"localhost\", \n",
    "                        user=\"myusername\", password=\"mypassword\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get a Cursor\n",
    "\n",
    "You need a cursor to execute queries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Execute Statements\n",
    "\n",
    "Using the cursor, you can then execute SQL statements.  By default, statements are part of a transaction and are not permanent until you commit (or you can rollback) the connection. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(\"\"\"CREATE TABLE test (id serial PRIMARY KEY, \n",
    "            num float, name text);\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insert some random data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import random\n",
    "from string import ascii_lowercase\n",
    "for val in ascii_lowercase:\n",
    "    cur.execute(\"INSERT INTO test (num, name) VALUES (%s, %s)\", \n",
    "                (random.random(), val))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Above, val will get quoted automatically when inserted into the statement.\n",
    "\n",
    "Now, make sure to commit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 0.474799792173992, 'a'),\n",
       " (2, 0.4343197397984, 'b'),\n",
       " (3, 0.734075711878846, 'c'),\n",
       " (4, 0.574939913433025, 'd'),\n",
       " (5, 0.648294282823083, 'e'),\n",
       " (6, 0.533967073414023, 'f'),\n",
       " (7, 0.14715557097833, 'g'),\n",
       " (8, 0.496855813127589, 'h'),\n",
       " (9, 0.212784192378674, 'i'),\n",
       " (10, 0.0284934256490874, 'j'),\n",
       " (11, 0.75534720558168, 'k'),\n",
       " (12, 0.912260279041763, 'l'),\n",
       " (13, 0.857371521730459, 'm'),\n",
       " (14, 0.547891220371794, 'n'),\n",
       " (15, 0.043662623849676, 'o'),\n",
       " (16, 0.0582995647720028, 'p'),\n",
       " (17, 0.738787678610912, 'q'),\n",
       " (18, 0.808302296259653, 'r'),\n",
       " (19, 0.200124286398337, 's'),\n",
       " (20, 0.984151212129189, 't'),\n",
       " (21, 0.733976443304754, 'u'),\n",
       " (22, 0.265577206110272, 'v'),\n",
       " (23, 0.863835908047373, 'w'),\n",
       " (24, 0.646911543436201, 'x'),\n",
       " (25, 0.101474656849782, 'y'),\n",
       " (26, 0.661636446923057, 'z')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute(\"select * from test\")\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.commit() ## conn, not cur"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Getting Results\n",
    "\n",
    "You fetch results back into Python."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 0.474799792173992, 'a')\n"
     ]
    }
   ],
   "source": [
    "cur.execute(\"SELECT * FROM test;\")\n",
    "print(cur.fetchone()) ## get one result (you get tuples)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once that first row is fetched, it's not the result set anymore.  If we pull more results, the first row won't be included.  This means also that if we re-run a fetch command, previously fetched results will be gone."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ID: 2\n",
      "NUM: 0.4343197397984;\n",
      "NAME: b\n",
      "\n",
      "ID: 3\n",
      "NUM: 0.734075711878846;\n",
      "NAME: c\n",
      "\n",
      "ID: 4\n",
      "NUM: 0.574939913433025;\n",
      "NAME: d\n",
      "\n",
      "ID: 5\n",
      "NUM: 0.648294282823083;\n",
      "NAME: e\n",
      "\n",
      "ID: 6\n",
      "NUM: 0.533967073414023;\n",
      "NAME: f\n",
      "\n",
      "ID: 7\n",
      "NUM: 0.14715557097833;\n",
      "NAME: g\n",
      "\n",
      "ID: 8\n",
      "NUM: 0.496855813127589;\n",
      "NAME: h\n",
      "\n",
      "ID: 9\n",
      "NUM: 0.212784192378674;\n",
      "NAME: i\n",
      "\n",
      "ID: 10\n",
      "NUM: 0.0284934256490874;\n",
      "NAME: j\n",
      "\n",
      "ID: 11\n",
      "NUM: 0.75534720558168;\n",
      "NAME: k\n",
      "\n",
      "ID: 12\n",
      "NUM: 0.912260279041763;\n",
      "NAME: l\n",
      "\n",
      "ID: 13\n",
      "NUM: 0.857371521730459;\n",
      "NAME: m\n",
      "\n",
      "ID: 14\n",
      "NUM: 0.547891220371794;\n",
      "NAME: n\n",
      "\n",
      "ID: 15\n",
      "NUM: 0.043662623849676;\n",
      "NAME: o\n",
      "\n",
      "ID: 16\n",
      "NUM: 0.0582995647720028;\n",
      "NAME: p\n",
      "\n",
      "ID: 17\n",
      "NUM: 0.738787678610912;\n",
      "NAME: q\n",
      "\n",
      "ID: 18\n",
      "NUM: 0.808302296259653;\n",
      "NAME: r\n",
      "\n",
      "ID: 19\n",
      "NUM: 0.200124286398337;\n",
      "NAME: s\n",
      "\n",
      "ID: 20\n",
      "NUM: 0.984151212129189;\n",
      "NAME: t\n",
      "\n",
      "ID: 21\n",
      "NUM: 0.733976443304754;\n",
      "NAME: u\n",
      "\n",
      "ID: 22\n",
      "NUM: 0.265577206110272;\n",
      "NAME: v\n",
      "\n",
      "ID: 23\n",
      "NUM: 0.863835908047373;\n",
      "NAME: w\n",
      "\n",
      "ID: 24\n",
      "NUM: 0.646911543436201;\n",
      "NAME: x\n",
      "\n",
      "ID: 25\n",
      "NUM: 0.101474656849782;\n",
      "NAME: y\n",
      "\n",
      "ID: 26\n",
      "NUM: 0.661636446923057;\n",
      "NAME: z\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for row in cur.fetchall(): ## fetch everything still in the result set\n",
    "    print(\"ID: {}\\nNUM: {};\\nNAME: {}\\n\".format(*row)) # * unpacks the row tuple"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that results are unnamed tuples.  We have to keep track ourselves of what value is in what position in the results.  We can get a list of column names if we need:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['id', 'num', 'name']\n"
     ]
    }
   ],
   "source": [
    "colnames = [desc[0] for desc in cur.description]\n",
    "print(colnames)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or we could use `pandas` (see below)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## RISK: SQL Injection\n",
    "\n",
    "If you're not careful, if you accept values from a user and use them directly in a database query, a malicious user can insert code to harm your database.  To avoid this, don't construct SQL queries as strings on your own.  Instead, use the built in functionality to put placeholders for values in the query, and then send a tuple with Python objects that the values will be read from.  The latter way, the values get sanitized.\n",
    "\n",
    "For more details, see [`psycopg2` documentation](http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries).\n",
    "\n",
    "![xkcd sql injection](https://imgs.xkcd.com/comics/exploits_of_a_mom.png)\n",
    "Source: https://xkcd.com/327/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# BAD!!!!\n",
    "\n",
    "myval = 3\n",
    "cur.execute(\"select * from test where id=\"+str(myval)+\";\")\n",
    "print(cur.fetchone())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(3, 0.734075711878846, 'c')\n"
     ]
    }
   ],
   "source": [
    "# CORRECT\n",
    "\n",
    "myval = 3\n",
    "cur.execute(\"select * from test where id=%s;\", [myval]) ## use a list or tuple for values\n",
    "print(cur.fetchone())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Close Cursor and Connection\n",
    "\n",
    "They will get closed when your script terminates, but it's good practice anyway."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Notes\n",
    "\n",
    "There are methods for copying data to the database from a file and copying a table from the database to a file.  See the `psycopg2` documentation for details and examples."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas\n",
    "\n",
    "You can read data directly into a `pandas` data frame too."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# change the specific here for your use case\n",
    "conn = psycopg2.connect(dbname=\"workshop\", host=\"localhost\", \n",
    "                        user=\"myusername\", password=\"mypassword\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>num</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.474800</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>0.434320</td>\n",
       "      <td>b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>0.734076</td>\n",
       "      <td>c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>0.574940</td>\n",
       "      <td>d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>0.648294</td>\n",
       "      <td>e</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>0.533967</td>\n",
       "      <td>f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>0.147156</td>\n",
       "      <td>g</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>0.496856</td>\n",
       "      <td>h</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>0.212784</td>\n",
       "      <td>i</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>0.028493</td>\n",
       "      <td>j</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>0.755347</td>\n",
       "      <td>k</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>0.912260</td>\n",
       "      <td>l</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>0.857372</td>\n",
       "      <td>m</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>0.547891</td>\n",
       "      <td>n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>0.043663</td>\n",
       "      <td>o</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16</td>\n",
       "      <td>0.058300</td>\n",
       "      <td>p</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>17</td>\n",
       "      <td>0.738788</td>\n",
       "      <td>q</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18</td>\n",
       "      <td>0.808302</td>\n",
       "      <td>r</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19</td>\n",
       "      <td>0.200124</td>\n",
       "      <td>s</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20</td>\n",
       "      <td>0.984151</td>\n",
       "      <td>t</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>21</td>\n",
       "      <td>0.733976</td>\n",
       "      <td>u</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>22</td>\n",
       "      <td>0.265577</td>\n",
       "      <td>v</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>23</td>\n",
       "      <td>0.863836</td>\n",
       "      <td>w</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>24</td>\n",
       "      <td>0.646912</td>\n",
       "      <td>x</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>25</td>\n",
       "      <td>0.101475</td>\n",
       "      <td>y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>26</td>\n",
       "      <td>0.661636</td>\n",
       "      <td>z</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id       num name\n",
       "0    1  0.474800    a\n",
       "1    2  0.434320    b\n",
       "2    3  0.734076    c\n",
       "3    4  0.574940    d\n",
       "4    5  0.648294    e\n",
       "5    6  0.533967    f\n",
       "6    7  0.147156    g\n",
       "7    8  0.496856    h\n",
       "8    9  0.212784    i\n",
       "9   10  0.028493    j\n",
       "10  11  0.755347    k\n",
       "11  12  0.912260    l\n",
       "12  13  0.857372    m\n",
       "13  14  0.547891    n\n",
       "14  15  0.043663    o\n",
       "15  16  0.058300    p\n",
       "16  17  0.738788    q\n",
       "17  18  0.808302    r\n",
       "18  19  0.200124    s\n",
       "19  20  0.984151    t\n",
       "20  21  0.733976    u\n",
       "21  22  0.265577    v\n",
       "22  23  0.863836    w\n",
       "23  24  0.646912    x\n",
       "24  25  0.101475    y\n",
       "25  26  0.661636    z"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_sql_query(\"select * from test;\", conn)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are functions for creating tables from pandas data frames, but these currently only work when using SQLite unless you are also using `sqlalchemy`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (conda p3)",
   "language": "python",
   "name": "p3"
  },
  "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.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}