012_dbs_2_rds.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "<a href=\"https://colab.research.google.com/github/rzl-ds/gu511/blob/master/012_dbs_2_rds.ipynb\" target=\"_parent\">\n",
    "    <img src=\"https://colab.research.google.com/assets/colab-badge.svg\"/>\n",
    "</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# `aws rds`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "`aws rds` (Relational Database Service) is a remotely hosted service for spinning up databases.\n",
    "\n",
    "what follows will cover the most important elements of the `rds` service, and will loosely follow [the `aws rds` documentation](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Welcome.html), so look there for more details"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## why `rds`?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "it is absolutely possible to install a database on our remote `ec2` servers.\n",
    "\n",
    "if we did that, we'd have total control over that database's configuration, security, resource usage, replication, archival... total control is a two-edged sword though: maybe we don't want the responsibility, or have the expertise?\n",
    "\n",
    "we can let `aws` take on some of that burden"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "the tradeoff is the same as it has been for almost all of the `aws` services:\n",
    "\n",
    "+ self-run: more granular control; but more admin overhead, complexity, and responsibility\n",
    "+ managed: less admin overhead, complexity, and responsibility; but less granular control"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "additionally, it is almost guaranteed that the managed option will be easier and cheaper to operate at large scales (not that that matters to us in this moment!)\n",
    "\n",
    "scaling to meet resource consumption or consumer demand, replication and backup, security, and other nice-to-have features are click-button options in `rds`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## what makes up an `rds` instance\n",
    "\n",
    "an `rds` instance is\n",
    "\n",
    "+ a database\n",
    "+ an availability zone\n",
    "+ a security group\n",
    "+ parameterization"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "think about `ec2` for a second: what we want is just a computer we can use, right? the `ec2` service involves more than \"just the computer\", there are other configuration and networking options we have to set up, like a security group, a mounted storage volume, `iam` roles, etc.\n",
    "\n",
    "`rds` is the service that gives us \"just a database\", but it also allows / requires us to set up and manage some infrastructure around that database, too. this is why I say an `rds` instance includes a database, but also some other things."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### database instances\n",
    "\n",
    "each `rds` instance has a database instance -- actual database service software running on some `aws`-managed machine.\n",
    "\n",
    "as with `ec2` services before them, this means you must choose the hardware (memory and storage) and software (database service) that you want to use"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### hardware choices\n",
    "\n",
    "the main dimensions here are\n",
    "\n",
    "1. memory (will allow you to perform queries involving larger datasets)\n",
    "2. cpu (will allow you to perform queries faster)\n",
    "3. storage (will allow you to save more records)\n",
    "\n",
    "at it's heart, you're doing the same thing you did when you set up your `ec2` server: identifying which of the above are important to you and selecting the instance type that performs best for your use case.\n",
    "\n",
    "or just picking the free one. there's always that."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "####  software choices\n",
    "\n",
    "by \"software\" here we mean the installed sql database flavor. there are many flavors of relational databases, and `aws` supports some of the most popular:\n",
    "\n",
    "1. `mysql`\n",
    "2. `mariadb` (a special flavor / fork of `mysql`)\n",
    "3. `postgres`\n",
    "4. `oracle`\n",
    "5. `mssql`\n",
    "6. `amazon aurora` (an in-house modification of `mysql`)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### regions and availability zones\n",
    "\n",
    "each instance you create will be located -- that is, physically -- in one or more (your choice) data centers in whatever region you select.\n",
    "\n",
    "a *region* is a particular geographic area (*e.g.* US East 1, Northern Virginia).\n",
    "\n",
    "an *availability zone* is an isolated datacenter within a given region. different availability zones are designed to be completely isolated, such that a problem or outage at one does not affect the others."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "the default behavior (and the only free behavior) is to create *one* instance in *one* availability zone. in a sensitive and robust production setting, you would want to have multiple availability zones."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### security groups\n",
    "\n",
    "just as with `ec2`, each database will need a security group to control access to and from this database. you can choose to open the database to the entire world, just your ip address, *etc*. it's the same song and dance."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### db parameter and option groups\n",
    "\n",
    "there are *a lot* of configuration parameters and options associated with database management. `aws` separates them into two groups, and you created a normalized configuration object called a group for each type:\n",
    "\n",
    "1. db parameter group\n",
    "    1. these are parameters which determine how the database *itself* is configured\n",
    "    2. car analogy: every car has an engine; the number of cylinders in the engine is a parameter of the engine\n",
    "2. db option group\n",
    "    1. these are parameters which determine how optional, extra features (database dependent) are configured, *if* they are activated or utilized\n",
    "    2. car analogy: spoiler color; not every car has a spoiler, but if you want yours to be **AWESOME** and **FAST**, you should get a red one"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**<div align=\"center\">PAUSE FOR ZOOM BREAK</div>**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## enough talk, let's make a database!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "let's create some databases.\n",
    "\n",
    "follow along while I create a `psql` database. start at the `rds` console: https://console.aws.amazon.com/rds/home\n",
    "\n",
    "<br><div align=\"center\">**walkthrough: create a `postgres` database**</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "1. navigate to [the `rds` console](https://console.aws.amazon.com/rds/home)\n",
    "1. create a new `rds` instance **the easy way**\n",
    "    1. from the dashboard, click on the \"create database\" button\n",
    "    1. select \"Easy Create\"\n",
    "        1. this is basically setting a bunch of options you'd have to determine in the \"Standard Create\" path\n",
    "    1. engine type\n",
    "        1. click each to see how our instance options change (specifically instance type and free tier eligibility)\n",
    "        1. click `postgresql`\n",
    "        1. select the free tier option\n",
    "    1. change the instance identifier name to whatever you want (e.g. `gu511-postgresql-free-[your gui id here]`)\n",
    "    1. choose a password **you won't forget**\n",
    "    1. launch!!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "and now, not doing it the standard way, to see what was auto-populated above:\n",
    "\n",
    "1. create a new `rds` instance **the long way**\n",
    "    1. from the dashboard, click on the \"create database\" button\n",
    "    1. select \"Standard Create\"\n",
    "        1. this is basically setting a bunch of options you'd have to determine in the \"Standard Create\" path\n",
    "    1. engine\n",
    "        1. click each to see how our instance options change\n",
    "        1. click `postgresql`\n",
    "        1. now we pick which *version* of `postgres` we want `aws` to install for us\n",
    "        1. select the most recent (as of 2020-11, this was `12.3-R1`)\n",
    "    1. template\n",
    "        1. we can cut *some* corners, even in the long way, by using a template\n",
    "        1. select the \"Free tier\" template\n",
    "    1. settings\n",
    "        1. change the instance identifier name to whatever you want (e.g. `gu511-postgresql-free`)\n",
    "        1. choose a password **you won't forget**\n",
    "    1. db instance\n",
    "        1. we will have limited our options by the \"free tier\" template above to just `db.t2.micro`, so stick with this\n",
    "    1. pay specific attention to the \"publically accessible\" option here\n",
    "    1. for all other sections, the defaults are good"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### a few things to talk to\n",
    "\n",
    "now that we've created an `rds` database, we will have *three different things* we could connect to / interact with\n",
    "\n",
    "1. the `rds` service\n",
    "1. the database service (`postgresql`, our `rdbms`)\n",
    "1. one of possibly many databases managed by the database service\n",
    "\n",
    "generally speaking, when we discuss connections in what follows we're talking about connecting *to the database service* to access data in one of the *databases*.\n",
    "\n",
    "don't forget, though, that we can connect to and manipulate the `rds` service as well, via the `aws cli` or the `boto3` library."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## connecting to this database service\n",
    "\n",
    "in the remainder of this lecture we will focus on ways to connect to this database, including:\n",
    "\n",
    "+ `datagrip`, a gui interface\n",
    "+ the `psql` command line client\n",
    "+ via `python` using the `psycopg2`\n",
    "+ via `python` using the `sqlalchemy` and `psycopg2` libraries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### connecting to our db: `datagrip` gui\n",
    "\n",
    "while `aws` is spinning up that postgres database (takes a few minutes), let's install the `datagrip` program. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "<br><div align=\"center\">**mini exercise: download and install `datagrip`**</div>\n",
    "\n",
    "1. sign up for a student's license from jetbrains (the company that makes datagrip, pycharm, and other IDE tools): https://www.jetbrains.com/shop/eform/students\n",
    "    1. sign up as a student and use your GU email address\n",
    "1. you should get a verification email. follow the instructions\n",
    "    1. create a jetbrains account\n",
    "    1. end up on https://account.jetbrains.com/licenses\n",
    "    1. verify that there is a license box on that page and that it includes the products `datagrip` and `pycharm`\n",
    "    1. copy your license (the 10-character string of random letters and numbers)\n",
    "1. go here: https://www.jetbrains.com/toolbox-app/ and install the toolbox app\n",
    "    1. log in to this app using your jetbrains account credentials (on the settings tab once you open the app, on a mac this is a toolbar app)\n",
    "1. use the toolbox app to install datagrip"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "`datagrip` is not the *only* gui available for interacting with `postgres` databases, but it is a very good one that can be used to connect to many different types of database -- hence my choice here."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "alright! status check: we should have the following:\n",
    "\n",
    "1. `datagrip` installed\n",
    "1. a `postgresql` database in an \"available\" state\n",
    "\n",
    "let's connect to our db with `datagrip`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**<div align=\"center\">mini exercise: make your `postgresql` instance accssible</div>**\n",
    "\n",
    "1. allow traffic from your `ip` address to your `psql` server\n",
    "    1. on your `rds` instance dashboard, find your \"security group rules\" window\n",
    "    1. verify you have an inbound rule of type `CIDR/IP - Inbound` for your IP address, if not, add one!\n",
    "        1. click on the security group link\n",
    "        1. open the page for the particular security group id\n",
    "        1. edit inbound rules\n",
    "        1. add a rule: `type = PostgreSQL`, `Source = My IP`, description is whatever you want\n",
    "1. make your db publically accessible\n",
    "    1. by default these dbs are completely inaccessible, even with security group tweaks\n",
    "    1. go to the main page for your `rds` instance\n",
    "    1. wait for your db to be in the \"Available\" state\n",
    "    1. click the \"modify\" button\n",
    "    1. scroll to the \"Connectivity\" section\n",
    "        1. click the \"Additional connectivity configuration\" dropdown\n",
    "        1. select \"Publicly accessible\"\n",
    "    1. scroll to the bottom of the window and click \"Continue\"\n",
    "    1. select \"immediately\" for the schedule\n",
    "    1. click the \"Modify DB instance\" button"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**<div align=\"center\">mini exercise: connect to your `postgresql` instance using `datagrip`</div>**\n",
    "\n",
    "1. add a new datasource in `datagrip`\n",
    "    1. either `File > New... > Datasource > PostgreSQL` or the plus sign in the top left corner of the window\n",
    "    1. click the \"Download missing driver files\" link on this page (should download without issue)\n",
    "    1. for name, I suggest the `rds` identifier name (e.g. `gu511-postgresql-free-[your gui id here]`\n",
    "    1. for Host, check the \"Endpoint\" field in `rds`\n",
    "    1. for username, default was `postgres`\n",
    "    1. for password, you better remember!\n",
    "    1. click the test connection button\n",
    "        1. if you don't get a green checkmark and success message, drop in the zoom room\n",
    "    1. click \"Ok\"\n",
    "1. look at the tables in the `Database` panel on the left side of `datagrip`\n",
    "    1. wait while `datagrip` populates the schema\n",
    "    1. should eventually see a dropdown like `name > postgres > public` with nothing in it\n",
    "1. mock build a new table\n",
    "    1. right click in `public` > new table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "you probably know what's next by now: we just connected to a thing we built using a nice gui with helpful pictures and drop down menus and click-button options, so naturally it is time to eschew all of those finer things and access the same database from *the command line only*!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### connecting to our db: `psql cli`\n",
    "\n",
    "generally, we view a database as a thing we access from our programs. this can be\n",
    "\n",
    "1. as part of a series of actions contained in a `bash` script\n",
    "1. within a `python` script (using `psycopg2` and/or `sqlalchemy`) or `R` script (using `odbc` or `RPostgres`)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**<div align=\"center\">mini exercise: install `psql` command line client</div>**\n",
    "\n",
    "1. open your `ec2` server\n",
    "2. install: `sudo apt install postgresql-client`\n",
    "3. verify: `which psql`\n",
    "4. rtfm: `man psql`\n",
    "    1. figure out how to specify `host`, `port`, `username`, `dbname`, and `password`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "the `psql` command is yet another programming language shell. just like the `python` or `R` command, this will open a `shell` (an environment where certain typed commands will do certain things) and allow us to write `postgres sql` statements to query a database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**<div align=\"center\">mini exercise: collecting the details we need to connect to our `postgres` server using `psql` command line client</div>**\n",
    "\n",
    "find the following pieces of information:\n",
    "\n",
    "+ on the \"Connectivity & security\" tab\n",
    "    + `host` (called `endpoint`)\n",
    "    + `port`\n",
    "+ on the \"Configuration\" tab\n",
    "    + `username` (called \"master username\")\n",
    "+ in your memory\n",
    "    + `password` (you set this! you can't get it back!)\n",
    "+ new info\n",
    "    + `dbname` is (by default) `postgres`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**<div align=\"center\">mini exercise: connecting to our `postgres` server using `psql` command line client</div>**\n",
    "\n",
    "connect to your database by passing the `host`, `port`, `username`, and `dbname`, and then typing the `password`:\n",
    "\n",
    "```bash\n",
    "psql --host HOSTNAME \\\n",
    "     --port PORT_NUMBER \\\n",
    "     --username USER_NAME \\\n",
    "     --dbname DATABASE_NAME\n",
    "```\n",
    "\n",
    "wait a few (about 60) seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "what happened? why do you think that happened?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**<div align=\"center\">mini exercise: add your `ec2` servers to your security group and *then* connecting</div>**\n",
    "\n",
    "1. go to your `rds` instance's page on the \"Connectivity & security\" tab\n",
    "1. find the active security group in the \"Security group rules\" section and click it\n",
    "1. edit the `inbound` rules\n",
    "    1. one of the options in the \"Type\" dropdown is `postgresql`, select that\n",
    "    1. look up your `ec2` instances ***private*** ip address\n",
    "    1. add TCP access to port 5432 for your `ec2` server's ***private*** IP address (`xxx.xxx.xxx.xxx/32`)\n",
    "1. try to connect from your `ec2` server again\n",
    "\n",
    "```bash\n",
    "psql --host HOSTNAME --port PORT_NUMBER --username USER_NAME --dbname DATABASE_NAME\n",
    "# if you followed me:\n",
    "psql --host HOSTNAME --username postgres\n",
    "```\n",
    "\n",
    "notice a difference?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "note: if you don't pass `dbname` explicitly, `psql` will try to connect to a database with name `USER_NAME` by default. this will usually not exist, but it's not uncommon to forget this and receive a\n",
    "\n",
    "```\n",
    "psql: FATAL:  database \"USER_NAME\" does not exist\n",
    "```\n",
    "\n",
    "error"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### a review of `psql` commands\n",
    "\n",
    "when in the `psql` shell, you effectively have access to two languages:\n",
    "\n",
    "1. traditional `sql`: `SELECT ... FROM ... WHERE` and the `LIKE`\n",
    "2. `psql` commands\n",
    "    1. these all start with the `\\` character (e.g. `\\h`, or `\\password`)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "to get an idea of the options available, try the following:\n",
    "\n",
    "1. `\\h`: a general `sql` help function\n",
    "    1. on its own, it lists all the `sql` commands available\n",
    "2. `\\h SQL_COMMAND`: a concise help menu for a given `sql` command\n",
    "    1. example: `\\h select`\n",
    "3. `\\?`: lists all the `psql` commands available with a short description\n",
    "    1. example: `\\d`\n",
    "    2. example: `\\l`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**<div align=\"center\">mini exercise: create a new table in our database</div>**\n",
    "\n",
    "1. first, make sure you're connected to the database you think you are\n",
    "    1. check the prompt: `databasename=>`, or the results of command `\\c`\n",
    "1. list the existing tables (relations): `\\d`\n",
    "1. execute the below `sql` statement\n",
    "1. run `\\d`\n",
    "\n",
    "```sql\n",
    "CREATE TABLE people (\n",
    "    id SERIAL NOT NULL\n",
    "    , name VARCHAR\n",
    ");\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "in the previous slide, we created a new table `people` which will hold records of people's names and assign a unique incremental identifier (`serial`) to any inserted records."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "let's use the `psql \\d` command to list the relations available to us.\n",
    "\n",
    "we should now see that two new relations exist:\n",
    "\n",
    "1. `people` (a table)\n",
    "2. `people_id_seq` (a sequence)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "inserting new records into that table is easy using the standard `sql INSERT` statement:\n",
    "\n",
    "```sql\n",
    "INSERT INTO people (name) VALUES\n",
    "    ('zach lamberty')\n",
    "    , ('caitlin moran')\n",
    ";\n",
    "```\n",
    "\n",
    "review with\n",
    "\n",
    "```sql\n",
    "SELECT * FROM people;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### connecting to our database: `python`\n",
    "\n",
    "there are two primary packages we will use when we interact with a `postgres` database in `python`: `psycopg2` and `sqlalchemy`. they accomplish two different functions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "[`psycopg2`](http://initd.org/psycopg/) is the most popular `python` database adapter (it takes the database's `api` and implements all functionality in `python`).\n",
    "\n",
    "we use this primarily to create connections to our database and make all of our basic queries by executing `sql` statements"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "[`sqlalchemy`](https://www.sqlalchemy.org/) is an *ORM* (object-relational mapper), which means that it attempts to *map* the *relations* (tables) in any `rdbm` (so, not *only* `postgres`) to `python` *objects*. it converts `sql` operations and relationships (primary and foreign keys) into `python` functions and object methods."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "this may seem complicated, but what is going on in an ORM is actually pretty similar to what happened in the `boto3` library between the `boto3 resource` and `client` objects.\n",
    "\n",
    "+ the `client` object was a direct 1-to-1 implementation of the `REST api` provided by an `aws` service\n",
    "+ the `resource` object was a re-working of that functionality to have a more standard and \"pythonic\" interface"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "in a similar way, an ORM can help take standard database functions and an individual database (set of tables with relationships and constraints) and built from them `python` objects that are more natural to work with in our `python` code."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### `psycopg2`\n",
    "\n",
    "we previously connected to `postgres` service from the command line using the `psql` *client*. let's make another scripted connection from within a `python` shell using the `psycopg2` library."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "let's start by installing `psycopg2`.\n",
    "\n",
    "1. `ssh` into your `ec2` instance\n",
    "2. `conda install psycopg2`\n",
    "    + note: this may downgrade your environment's `python` version -- this happens some time! a package you want/need to use isn't available in the latest and greates `python` version. watch out for it."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "full documentation of how to use `psycopg2` is [here](http://initd.org/psycopg/docs/) and this does not replace that. let's focus on a few simple tasks:\n",
    "\n",
    "1. connect to a database\n",
    "2. create a *cursor* (an object which uses the connection to perform queries)\n",
    "3. perform a `SELECT` query\n",
    "4. perform an `INSERT` query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "##### connecting to the database\n",
    "\n",
    "the `psycopg2.connect` function will create a database *connection* (an object which holds our credentials and can create *cursors* for us). this is analogous in many ways to the `boto3 session` objects we created to manage our communication with `aws resource`s"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "try the following (using your own usernames, passwords, *etc.*):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import getpass\n",
    "\n",
    "host = 'gu511-postgresql-free-2020.cdmknaubrmaw.us-east-1.rds.amazonaws.com'\n",
    "user = 'postgres'\n",
    "dbname = 'postgres'\n",
    "pw = getpass.getpass('password: ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %%sh\n",
    "# pip install psycopg2 --upgrade"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "\n",
    "# if this hangs, **double check your host, port, and security group!!**\n",
    "connection = psycopg2.connect(\n",
    "    host=host,\n",
    "    port=5432,\n",
    "    user=user,\n",
    "    password=pw,\n",
    "    dbname=dbname\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "##### create a cursor\n",
    "\n",
    "the `connection` object we just created has a method `connection.cursor` which will create a *cursor*. a *cursor* is an object which can read lines in tables in the database and make transactions (changes) to those tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "cur = connection.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "##### perform a `SELECT` query\n",
    "\n",
    "every `cursor` object has a `cursor.execute` method which can take `sql` strings and execute them on the `sql` server. the *results* of that execution (*e.g.* a table of records) can be *fetched* from the server using the `connection.fetch` or `connection.fetchall` methods."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "let's use those `execute` and `fetchall` functions to select all the records in our `people` table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "cur.execute('SELECT * FROM people;')\n",
    "people = cur.fetchall()\n",
    "people"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "note that fetching records is like a generator -- it is a one-time thing. executing `fetchall` again will yield nothing (the results of no transaction are nothing)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "# note that this is a one-time thing; `fetchall` again will yield nothing\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "it's also worth noting that *after* executing a statement, the cursor retains some meta-information about the executed query. for example, the fieldnames are available as an attribute:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "cur.description"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "the query above was short and sweet, and didn't depend on anything outside of itself. that's usually not the case, though. when we write queries in applications we often want to execute those queries based on some parameters.\n",
    "\n",
    "you may be tempted to just build those query strings live (for example, using the string formatting method we've discussed) but you should [**never ever do that**](https://xkcd.com/327/)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "any `sql` integration that is worth anything will implement parameterized queries and take care of command sanitization for you. `psycopg2` is worth anything. pass in parameterized queries using the `%s` syntax:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "# NOTE THE COMMA AFTER MY NAME!\n",
    "cur.execute(\"SELECT * FROM people WHERE name = %s\",\n",
    "            ('zach lamberty',))\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "or the dictionary version"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "cur.execute(\"SELECT * FROM people WHERE name = %(name)s\",\n",
    "            {'name': 'zach lamberty'})\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "##### perform an `INSERT` query\n",
    "\n",
    "we were able to use the cursor to read (`SELECT`) from the database, but we can also easily use it to update the database. let's execute an `INSERT` query and then *commit* that transaction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "cur.execute(\"INSERT INTO people (name) VALUES (%s)\", ('eamon lamberty',))\n",
    "cur.execute(\"SELECT * FROM people\")\n",
    "people = cur.fetchall()\n",
    "people"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "at this point our *cursor* sees that we `INSERT`ed `eamon lamberty`, but this is actually not completely accurate. our currently connected `cursor` object sees that as the state of the world, but it hasn't yet `commit` that fact to the database -- other cursors of other sessions or users do not yet see this new record.\n",
    "\n",
    "*note: we can verify that in real-time if we still have a `psql` session running*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "to make a *change* to the database, we must `commit` our transactions, and that happens on the `connection` level"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "connection.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "##### cleaning up after yourself and context managers\n",
    "\n",
    "when writing an application that connects to a database, we want to make sure that we don't leave these `connection` and `cursor` objects lying around unused but taking up precious resources. just like with file pointer objects, when we are done with them we should close them:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.close()\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "also like file pointer objects, the designers of the `psycopg2` library have taken care of this annoying cleanup by implementing a `context manager` for the connection and for the cursor:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "with psycopg2.connect(host=host, port=5432, user=user, password=pw, dbname=dbname) as conn:\n",
    "    with conn.cursor() as selectcur1:\n",
    "        selectcur1.execute('SELECT * FROM people')\n",
    "        people1 = selectcur1.fetchall()\n",
    "\n",
    "    with conn.cursor() as insertcur:\n",
    "        insertcur.execute(\"INSERT INTO people (name) VALUES (%s)\", ('stuart price',))\n",
    "\n",
    "    with conn.cursor() as selectcur2:\n",
    "        selectcur2.execute('SELECT * FROM people')\n",
    "        people2 = selectcur2.fetchall()\n",
    "\n",
    "print(f'people1: {people1}')\n",
    "print(f'people2: {people2}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "##### using `pandas` and `psycopg2` together for blisssssss\n",
    "\n",
    "as it turns out, with just the connection object we defined above we can pretty easily leverage `pandas` dataframes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "with psycopg2.connect(host=host, port=5432, user=user, password=pw, dbname=dbname) as con:\n",
    "    dfpeople = pd.read_sql('SELECT * FROM people', con=con, index_col='id')\n",
    "\n",
    "dfpeople"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "not too bad, gang"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### `sqlalchemy`\n",
    "\n",
    "as I mentioned up above, `sqlalchemy` is a useful `sql` toolbox and, in particular, ORM. we won't get too deep into this library either, other than to show you the basics of how to create a `sqlalchemy` object and use it to perform some simple queries and insert statements."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "this is also no substitute for [the documentation](https://www.sqlalchemy.org/library.html#reference) (be sure to check your version like we do below, the `api` has seen significant changes over time)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "```sh\n",
    "conda install sqlalchemy\n",
    "```\n",
    "\n",
    "*note: this may downgrade you `python` installation, just like with `psycopg2` above, which will require every package you've installed so far to be downgraded, too! this happens. watch for and think about it!*\n",
    "\n",
    "check your version"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy\n",
    "sqlalchemy.__version__"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "##### side-track: what are `url`s really?\n",
    "\n",
    "in order to help services of different types talk to and reference each other, computer scientists have needed to create shared conventions. one of the big agreed-upon conventions  is how you name them, and the agreed-upon naming convention is the [uniform resource identifier, or `uri`](https://en.wikipedia.org/wiki/Uniform_Resource_Identifier). the most common type of `uri` is a uniform resource *locator*, or `url`. you're familiar with those, no doubt."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "a `url` isn't just some `https://something.com/` though -- it's a bit more generic than that. it is a combination of a\n",
    "\n",
    "```\n",
    "scheme://host/path\n",
    "```\n",
    "\n",
    "([good blog post on this](https://danielmiessler.com/study/url-uri/))\n",
    "\n",
    "the `scheme` is usually determining the protocol we will use to communicate (`http`, `ssh`, `ftp`, etc), and the `host` often is made up of four parts:\n",
    "\n",
    "```\n",
    "[user[:password]@]hostname[:port]\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "so for the `url`s you're used to, the `scheme` is `http` or `https`, the `host` is an ip address with a port or a domain name (e.g. `www.georgetown.edu`), and a trailing path that defines the resource at that host.\n",
    "\n",
    "we've also used different `url`s for [github repos](https://gist.github.com/grawity/4392747), including `http(s)` and `ssh`. for example, `git @github.com:rzl-ds/gu511.git` is actually shorthand for `ssh://git@github.com/rzl-ds/gu511.git`. can you see how that maps into\n",
    "\n",
    "```\n",
    "schema://[user[:password]@]host[:port]/path\n",
    "```\n",
    "?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "databases resources like our `postgres` service are described similarly, except we now communicate via a different `scheme` and the way we specify `hosts` includes our authentication information (like `ssh` examples above).\n",
    "\n",
    "the `schema` for a database is often a `dialect+driver` combination, where `dialect` is the type of database and `driver` is the software that will do the communication for us.\n",
    "\n",
    "```\n",
    "dialect+driver://username:password@host:port/database\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "##### back to `sqlalchemy`\n",
    "\n",
    "in `sqlalchemy`, we connect with `url`s. they can be hard-coded if you know how to update the above, or they can be \"smartly\" built for us by functions within `sqlalchemy`.\n",
    "\n",
    "in order to avoid hard-coding our password here, we can build one using the `sqlalchemy.engine.url.URL` object. we can print that `url` using the `sqlalchemy.engine.url.make_url` function (without printing the password directly)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "url = sqlalchemy.engine.url.URL(\n",
    "    drivername='postgres+psycopg2',\n",
    "    username=user,\n",
    "    password=pw,\n",
    "    host=host,\n",
    "    port=5432,\n",
    "    database=dbname\n",
    ")\n",
    "sqlalchemy.engine.url.make_url(url)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "the first entrypoint for the `sqlalchemy` library is an `engine`, which is an object that *creates* connections. this is slightly different than the previous `psycopg2` library, as the main access point was the connection object itself.\n",
    "\n",
    "we can then use the url we just built to make a connection engine:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "engine = sqlalchemy.create_engine(url)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "we can then use the created `engine` to auto-generate table objects for all the tables in our database. These can be accessed using a `MetaData` object in `sqlalchemy`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "meta = sqlalchemy.schema.MetaData()\n",
    "meta.reflect(bind=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "people = meta.tables['people']\n",
    "people"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "the rest of the utility in `sqlalchemy` revolves around using `python` functions to (basically) create `sql` queries in a normalized way, and to execute those queries using the `engine` we created above. for example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with engine.connect() as conn:\n",
    "    # insert a new person record\n",
    "    insertqry = people.insert().values(name='shree taylor')\n",
    "    conn.execute(insertqry)\n",
    "\n",
    "    # select everyone again\n",
    "    selectqry = people.select().where(people.c.name != 'zach lamberty')\n",
    "    results = conn.execute(selectqry)\n",
    "\n",
    "list(results)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "the items we created along the way were auto-constructed `sql` queries. they can be resolved easily:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(insertqry)\n",
    "print()\n",
    "print(selectqry)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "this may seem like overkill, but this is actually taking care of two pretty important things for us:\n",
    "\n",
    "1. it's writing the `sql` queries in the most correct and safest way every time\n",
    "2. it's opening up the possibility of having conditional breaks within a query\n",
    "    + for example, we could have chosen *whether or not* to exclude `zach lamberty` from our `SELECT`. think about how you would have done that in regular `sql`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "<!--div align=\"center\">***DROP joke WHERE is_bad***</div>\n",
    "<img align=\"middle\" src=\"\"></img-->\n",
    "\n",
    "# END OF LECTURE\n",
    "\n",
    "next lecture: [`aws` `dynamodb`](012_dbs_3_dynamodb.ipynb)"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "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"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}