03 Introduction to Databases in Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Basics of Relational Databases"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Datasets:\n",
    "    Census (CSV)\n",
    "    Census (SQLite)\n",
    "    Employees (SQLite)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Engines and Connection Strings\n",
    "\n",
    "Alright, it's time to create your first engine! An engine is just a common interface to a database, and the information it requires to connect to one is contained in a connection string, such as sqlite:///census_nyc.sqlite. Here, sqlite is the database driver, while census_nyc.sqlite is a SQLite file contained in the local directory.\n",
    "\n",
    "You can learn a lot more about connection strings in the SQLAlchemy documentation.\n",
    "\n",
    "Your job in this exercise is to create an engine that connects to a local SQLite file named census.sqlite. Then, print the names of the tables it contains using the .table_names() method. Note that when you just want to print the table names, you do not need to use engine.connect() after creating the engine."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:47:57.260542Z",
     "start_time": "2019-01-21T18:47:56.801749Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[]\n"
     ]
    }
   ],
   "source": [
    "# Import create_engine\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "# Create an engine that connects to the census.sqlite file: engine\n",
    "engine = create_engine('sqlite:///census.sqlite')\n",
    "\n",
    "# Print table names\n",
    "print(engine.table_names())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Autoloading Tables from a Database\n",
    "\n",
    "SQLAlchemy can be used to automatically load tables from a database using something called reflection. Reflection is the process of reading the database and building the metadata based on that information. It's the opposite of creating a Table by hand and is very useful for working with existing databases. To perform reflection, you need to import the Table object from the SQLAlchemy package. Then, you use this Table object to read your table from the engine and autoload the columns. Using the Table object in this manner is a lot like passing arguments to a function. For example, to autoload the columns with the engine, you have to specify the keyword arguments autoload=True and autoload_with=engine to Table().\n",
    "\n",
    "In this exercise, your job is to reflect the census table available on your engine into a variable called census. The metadata has already been loaded for you using MetaData() and is available in the variable metadata."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:48:09.151829Z",
     "start_time": "2019-01-21T18:48:09.051099Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'metadata' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-2-2e4295bca27b>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Reflect census table from the engine: census\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 5\u001b[1;33m \u001b[0mcensus\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mTable\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'census'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmetadata\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mautoload\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mautoload_with\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      6\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      7\u001b[0m \u001b[1;31m# Print census table metadata\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'metadata' is not defined"
     ]
    }
   ],
   "source": [
    "# Import Table\n",
    "from sqlalchemy import Table\n",
    "\n",
    "# Reflect census table from the engine: census\n",
    "census = Table('census', metadata, autoload=True, autoload_with=engine)\n",
    "\n",
    "# Print census table metadata\n",
    "print(repr(census))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Viewing Table Details\n",
    "\n",
    "Great job reflecting the census table! Now you can begin to learn more about the columns and structure of your table. It is important to get an understanding of your database by examining the column names. This can be done by using the .columns attribute and accessing the .keys() method. For example, census.columns.keys() would return a list of column names of the census table.\n",
    "\n",
    "Following this, we can use the metadata container to find out more details about the reflected table such as the columns and their types. For example, table objects are stored in the metadata.tables dictionary, so you can get the metadata of your census table with metadata.tables['census']. This is similar to your use of the repr() function on the census table from the previous exercise."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:48:21.597683Z",
     "start_time": "2019-01-21T18:48:21.586711Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'metadata' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-3-73557955c22f>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Reflect the census table from the engine: census\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mcensus\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mTable\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'census'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmetadata\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mautoload\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mautoload_with\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Print the column names\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mkeys\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'metadata' is not defined"
     ]
    }
   ],
   "source": [
    "# Reflect the census table from the engine: census\n",
    "census = Table('census', metadata, autoload=True, autoload_with=engine)\n",
    "\n",
    "# Print the column names\n",
    "print(census.columns.keys())\n",
    "\n",
    "# Print full table metadata\n",
    "print(repr(metadata.tables['census']))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Selecting data from a Table: raw SQL\n",
    "\n",
    "Using what we just learned about SQL and applying the .execute() method on our connection, we can leverage a raw SQL query to query all the records in our census table. The object returned by the .execute() method is a ResultProxy. On this ResultProxy, we can then use the .fetchall() method to get our results - that is, the ResultSet.\n",
    "\n",
    "In this exercise, you'll use a traditional SQL query. In the next exercise, you'll move to SQLAlchemy and begin to understand its advantages. Go for it!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:48:37.481874Z",
     "start_time": "2019-01-21T18:48:37.470899Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'connection' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-4-54aa0e010571>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Execute the statement and fetch the results: results\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 5\u001b[1;33m \u001b[0mresults\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mconnection\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstmt\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfetchall\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      6\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      7\u001b[0m \u001b[1;31m# Print Results\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'connection' is not defined"
     ]
    }
   ],
   "source": [
    "# Build select statement for census table: stmt\n",
    "stmt = 'select * from census'\n",
    "\n",
    "# Execute the statement and fetch the results: results\n",
    "results = connection.execute(stmt).fetchall()\n",
    "\n",
    "# Print Results\n",
    "print(results)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Selecting data from a Table with SQLAlchemy\n",
    "\n",
    "Excellent work so far! It's now time to build your first select statement using SQLAlchemy. SQLAlchemy provides a nice \"Pythonic\" way of interacting with databases. So rather than dealing with the differences between specific dialects of traditional SQL such as MySQL or PostgreSQL, you can leverage the Pythonic framework of SQLAlchemy to streamline your workflow and more efficiently query your data. For this reason, it is worth learning even if you may already be familiar with traditional SQL.\n",
    "\n",
    "In this exercise, you'll once again build a statement to query all records from the census table. This time, however, you'll make use of the select() function of the sqlalchemy module. This function requires a list of tables or columns as the only required argument.\n",
    "\n",
    "Table and MetaData have already been imported. The metadata is available as metadata and the connection to the database as connection."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:48:49.302789Z",
     "start_time": "2019-01-21T18:48:49.289825Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'metadata' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-5-b1510e23c410>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Reflect census table via engine: census\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 5\u001b[1;33m \u001b[0mcensus\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mTable\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'census'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmetadata\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mautoload\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mautoload_with\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      6\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      7\u001b[0m \u001b[1;31m# Build select statement for census table: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'metadata' is not defined"
     ]
    }
   ],
   "source": [
    "# Import select\n",
    "from sqlalchemy import select\n",
    "\n",
    "# Reflect census table via engine: census\n",
    "census = Table('census', metadata, autoload=True, autoload_with=engine)\n",
    "\n",
    "# Build select statement for census table: stmt\n",
    "stmt = select([census])\n",
    "\n",
    "# Print the emitted statement to see the SQL emitted\n",
    "print(stmt)\n",
    "\n",
    "# Execute the statement and print the results\n",
    "print(connection.execute(stmt).fetchall())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Handling a ResultSet\n",
    "\n",
    "Recall the differences between a ResultProxy and a ResultSet:\n",
    "\n",
    "    ResultProxy: The object returned by the .execute() method. It can be used in a variety of ways to get the data returned by the query.\n",
    "    ResultSet: The actual data asked for in the query when using a fetch method such as .fetchall() on a ResultProxy.\n",
    "\n",
    "This separation between the ResultSet and ResultProxy allows us to fetch as much or as little data as we desire.\n",
    "\n",
    "Once we have a ResultSet, we can use Python to access all the data within it by column name and by list style indexes. For example, you can get the first row of the results by using results[0]. With that first row then assigned to a variable first_row, you can get data from the first column by either using first_row[0] or by column name such as first_row['column_name']. You'll now practice exactly this using the ResultSet you obtained from the census table in the previous exercise. It is stored in the variable results. Enjoy!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:49:02.084610Z",
     "start_time": "2019-01-21T18:49:02.071642Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'results' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-6-5edea4569f6d>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Get the first row of the results by using an index: first_row\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mfirst_row\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mresults\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Print the first row of the results\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfirst_row\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'results' is not defined"
     ]
    }
   ],
   "source": [
    "# Get the first row of the results by using an index: first_row\n",
    "first_row = results[0]\n",
    "\n",
    "# Print the first row of the results\n",
    "print(first_row)\n",
    "\n",
    "# Print the first column of the first row by using an index\n",
    "print(first_row[0])\n",
    "\n",
    "# Print the 'state' column of the first row by using its name\n",
    "print(first_row['state'])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Applying Filtering, Ordering and Grouping to Queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connecting to a PostgreSQL Database\n",
    "\n",
    "In these exercises, you will be working with real databases hosted on the cloud via Amazon Web Services (AWS)!\n",
    "\n",
    "Let's begin by connecting to a PostgreSQL database. When connecting to a PostgreSQL database, many prefer to use the psycopg2 database driver as it supports practically all of PostgreSQL's features efficiently and is the standard dialect for PostgreSQL in SQLAlchemy.\n",
    "\n",
    "You might recall from Chapter 1 that we use the create_engine() function and a connection string to connect to a database.\n",
    "\n",
    "There are three components to the connection string in this exercise: the dialect and driver ('postgresql+psycopg2://'), followed by the username and password ('student:datacamp'), followed by the host and port ('@postgresql.csrrinzqubik.us-east-1.rds.amazonaws.com:5432/'), and finally, the database name ('census'). You will have to pass this string as an argument to create_engine() in order to connect to the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:50:42.376495Z",
     "start_time": "2019-01-21T18:50:42.206919Z"
    }
   },
   "outputs": [
    {
     "ename": "ModuleNotFoundError",
     "evalue": "No module named 'psycopg2'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-7-128039949415>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Create an engine to the census database\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m engine = create_engine(\n\u001b[1;32m----> 6\u001b[1;33m     'postgresql+psycopg2://student:datacamp@postgresql.csrrinzqubik.us-east-1.rds.amazonaws.com:5432/census')\n\u001b[0m\u001b[0;32m      7\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      8\u001b[0m \u001b[1;31m# Use the .table_names() method on the engine to print the table names\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\__init__.py\u001b[0m in \u001b[0;36mcreate_engine\u001b[1;34m(*args, **kwargs)\u001b[0m\n\u001b[0;32m    423\u001b[0m     \u001b[0mstrategy\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mkwargs\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mpop\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'strategy'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdefault_strategy\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    424\u001b[0m     \u001b[0mstrategy\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mstrategies\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mstrategies\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mstrategy\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 425\u001b[1;33m     \u001b[1;32mreturn\u001b[0m \u001b[0mstrategy\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcreate\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    426\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    427\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\strategies.py\u001b[0m in \u001b[0;36mcreate\u001b[1;34m(self, name_or_url, **kwargs)\u001b[0m\n\u001b[0;32m     79\u001b[0m                 \u001b[1;32mif\u001b[0m \u001b[0mk\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mkwargs\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     80\u001b[0m                     \u001b[0mdbapi_args\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mk\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpop_kwarg\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mk\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 81\u001b[1;33m             \u001b[0mdbapi\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdialect_cls\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdbapi\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m**\u001b[0m\u001b[0mdbapi_args\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     82\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     83\u001b[0m         \u001b[0mdialect_args\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'dbapi'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdbapi\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\sqlalchemy\\dialects\\postgresql\\psycopg2.py\u001b[0m in \u001b[0;36mdbapi\u001b[1;34m(cls)\u001b[0m\n\u001b[0;32m    582\u001b[0m     \u001b[1;33m@\u001b[0m\u001b[0mclassmethod\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    583\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mdbapi\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcls\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 584\u001b[1;33m         \u001b[1;32mimport\u001b[0m \u001b[0mpsycopg2\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    585\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mpsycopg2\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    586\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mModuleNotFoundError\u001b[0m: No module named 'psycopg2'"
     ]
    }
   ],
   "source": [
    "# Import create_engine function\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "# Create an engine to the census database\n",
    "engine = create_engine(\n",
    "    'postgresql+psycopg2://student:datacamp@postgresql.csrrinzqubik.us-east-1.rds.amazonaws.com:5432/census')\n",
    "\n",
    "# Use the .table_names() method on the engine to print the table names\n",
    "print(engine.table_names())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter data selected from a Table - Simple\n",
    "\n",
    "Having connected to the database, it's now time to practice filtering your queries!\n",
    "\n",
    "As mentioned in the video, a where() clause is used to filter the data that a statement returns. For example, to select all the records from the census table where the sex is Female (or 'F') we would do the following:\n",
    "\n",
    "select([census]).where(census.columns.sex == 'F')\n",
    "\n",
    "In addition to == we can use basically any python comparison operator (such as <=, !=, etc) in the where() clause."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:50:55.458047Z",
     "start_time": "2019-01-21T18:50:55.444104Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'census' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-8-daf818538880>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Create a select query: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mselect\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Add a where clause to filter the results to only those for New York\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mstmt\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mwhere\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mstate\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m'New York'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'census' is not defined"
     ]
    }
   ],
   "source": [
    "# Create a select query: stmt\n",
    "stmt = select([census])\n",
    "\n",
    "# Add a where clause to filter the results to only those for New York\n",
    "stmt = stmt.where(census.columns.state == 'New York')\n",
    "\n",
    "# Execute the query to retrieve all the data returned: results\n",
    "results = connection.execute(stmt).fetchall()\n",
    "\n",
    "# Loop over the results and print the age, sex, and pop2008\n",
    "for result in results:\n",
    "    print(result.age, result.sex, result.pop2008)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter data selected from a Table - Expressions\n",
    "\n",
    "In addition to standard Python comparators, we can also use methods such as in_() to create more powerful where() clauses. You can see a full list of expressions in the SQLAlchemy Documentation.\n",
    "\n",
    "We've already created a list of some of the most densely populated states."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:51:07.219068Z",
     "start_time": "2019-01-21T18:51:07.208098Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'census' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-9-2106a8eab44d>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Create a query for the census table: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mselect\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Append a where clause to match all the states in_ the list states\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mstmt\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mwhere\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mstate\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0min_\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstates\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'census' is not defined"
     ]
    }
   ],
   "source": [
    "# Create a query for the census table: stmt\n",
    "stmt = select([census])\n",
    "\n",
    "# Append a where clause to match all the states in_ the list states\n",
    "stmt = stmt.where(census.columns.state.in_(states))\n",
    "\n",
    "# Loop over the ResultProxy and print the state and its population in 2000\n",
    "for result in connection.execute(stmt):\n",
    "    print(result.state, result.pop2000)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter data selected from a Table - Advanced\n",
    "\n",
    "You're really getting the hang of this! SQLAlchemy also allows users to use conjunctions such as and_(), or_(), and not_() to build more complex filtering. For example, we can get a set of records for people in New York who are 21 or 37 years old with the following code:\n",
    "\n",
    "select([census]).where(\n",
    "  and_(census.columns.state == 'New York',\n",
    "       or_(census.columns.age == 21,\n",
    "          census.columns.age == 37\n",
    "         )\n",
    "      )\n",
    "  )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:51:19.745710Z",
     "start_time": "2019-01-21T18:51:19.735716Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'census' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-10-0128fbf64de5>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Build a query for the census table: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 5\u001b[1;33m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mselect\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      6\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      7\u001b[0m \u001b[1;31m# Append a where clause to select only non-male records from California using and_\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'census' is not defined"
     ]
    }
   ],
   "source": [
    "# Import and_\n",
    "from sqlalchemy import and_\n",
    "\n",
    "# Build a query for the census table: stmt\n",
    "stmt = select([census])\n",
    "\n",
    "# Append a where clause to select only non-male records from California using and_\n",
    "stmt = stmt.where(\n",
    "    # The state of California with a non-male sex\n",
    "    and_(census.columns.state == 'California',\n",
    "         census.columns.sex != 'M'\n",
    "         )\n",
    ")\n",
    "\n",
    "# Loop over the ResultProxy printing the age and sex\n",
    "for result in connection.execute(stmt):\n",
    "    print(result.age, result.sex)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ordering by a Single Column\n",
    "\n",
    "To sort the result output by a field, we use the .order_by() method. By default, the .order_by() method sorts from lowest to highest on the supplied column. You just have to pass in the name of the column you want sorted to .order_by().\n",
    "\n",
    "In the video, for example, Jason used stmt.order_by(census.columns.state) to sort the result output by the state column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:51:34.068908Z",
     "start_time": "2019-01-21T18:51:34.056939Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'census' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-11-9a80b5ccddfa>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Build a query to select the state column: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mselect\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mstate\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Order stmt by the state column\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mstmt\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0morder_by\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mstate\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'census' is not defined"
     ]
    }
   ],
   "source": [
    "# Build a query to select the state column: stmt\n",
    "stmt = select([census.columns.state])\n",
    "\n",
    "# Order stmt by the state column\n",
    "stmt = stmt.order_by(census.columns.state)\n",
    "\n",
    "# Execute the query and store the results: results\n",
    "results = connection.execute(stmt).fetchall()\n",
    "\n",
    "# Print the first 10 results\n",
    "print(results[:10])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ordering in Descending Order by a Single Column\n",
    "\n",
    "You can also use .order_by() to sort from highest to lowest by wrapping a column in the desc() function. Although you haven't seen this function in action, it generalizes what you have already learned.\n",
    "\n",
    "Pass desc() (for \"descending\") inside an .order_by() with the name of the column you want to sort by. For instance, stmt.order_by(desc(table.columns.column_name)) sorts column_name in descending order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:51:46.278694Z",
     "start_time": "2019-01-21T18:51:46.265733Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'census' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-12-8aedcbef5ec3>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Build a query to select the state column: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 5\u001b[1;33m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mselect\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mstate\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      6\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      7\u001b[0m \u001b[1;31m# Order stmt by state in descending order: rev_stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'census' is not defined"
     ]
    }
   ],
   "source": [
    "# Import desc\n",
    "from sqlalchemy import desc\n",
    "\n",
    "# Build a query to select the state column: stmt\n",
    "stmt = select([census.columns.state])\n",
    "\n",
    "# Order stmt by state in descending order: rev_stmt\n",
    "rev_stmt = stmt.order_by(desc(census.columns.state))\n",
    "\n",
    "# Execute the query and store the results: rev_results\n",
    "rev_results = connection.execute(rev_stmt).fetchall()\n",
    "\n",
    "# Print the first 10 rev_results\n",
    "print(rev_results[:10])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ordering by Multiple Columns\n",
    "\n",
    "We can pass multiple arguments to the .order_by() method to order by multiple columns. In fact, we can also sort in ascending or descending order for each individual column. Each column in the .order_by() method is fully sorted from left to right. This means that the first column is completely sorted, and then within each matching group of values in the first column, it's sorted by the next column in the .order_by() method. This process is repeated until all the columns in the .order_by() are sorted."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:51:58.354600Z",
     "start_time": "2019-01-21T18:51:58.342649Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'census' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-13-207de094371c>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Build a query to select state and age: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mselect\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mstate\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mage\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Append order by to ascend by state and descend by age\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mstmt\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0morder_by\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mstate\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdesc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mage\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'census' is not defined"
     ]
    }
   ],
   "source": [
    "# Build a query to select state and age: stmt\n",
    "stmt = select([census.columns.state, census.columns.age])\n",
    "\n",
    "# Append order by to ascend by state and descend by age\n",
    "stmt = stmt.order_by(census.columns.state, desc(census.columns.age))\n",
    "\n",
    "# Execute the statement and store all the records: results\n",
    "results = connection.execute(stmt).fetchall()\n",
    "\n",
    "# Print the first 20 results\n",
    "print(results[:20])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Counting Distinct Data\n",
    "\n",
    "As mentioned in the video, SQLAlchemy's func module provides access to built-in SQL functions that can make operations like counting and summing faster and more efficient.\n",
    "\n",
    "In the video, Jason used func.sum() to get a sum of the pop2008 column of census as shown below:\n",
    "\n",
    "select([func.sum(census.columns.pop2008)])\n",
    "\n",
    "If instead you want to count the number of values in pop2008, you could use func.count() like this:\n",
    "\n",
    "select([func.count(census.columns.pop2008)])\n",
    "\n",
    "Furthermore, if you only want to count the distinct values of pop2008, you can use the .distinct() method:\n",
    "\n",
    "select([func.count(census.columns.pop2008.distinct())])\n",
    "\n",
    "In this exercise, you will practice using func.count() and .distinct() to get a count of the distinct number of states in census.\n",
    "\n",
    "So far, you've seen .fetchall() and .first() used on a ResultProxy to get the results. The ResultProxy also has a method called .scalar() for getting just the value of a query that returns only one row and column.\n",
    "\n",
    "This can be very useful when you are querying for just a count or sum."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:52:20.563868Z",
     "start_time": "2019-01-21T18:52:20.550899Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'func' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-14-b5962dad0ee9>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Build a query to count the distinct states values: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mselect\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mfunc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcount\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mstate\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdistinct\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Execute the query and store the scalar result: distinct_state_count\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mdistinct_state_count\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mconnection\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstmt\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mscalar\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'func' is not defined"
     ]
    }
   ],
   "source": [
    "# Build a query to count the distinct states values: stmt\n",
    "stmt = select([func.count(census.columns.state.distinct())])\n",
    "\n",
    "# Execute the query and store the scalar result: distinct_state_count\n",
    "distinct_state_count = connection.execute(stmt).scalar()\n",
    "\n",
    "# Print the distinct_state_count\n",
    "print(distinct_state_count)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Count of Records by State\n",
    "\n",
    "Often, we want to get a count for each record with a particular value in another column. The .group_by() method helps answer this type of query. You can pass a column to the .group_by() method and use in an aggregate function like sum() or count(). Much like the .order_by() method, .group_by() can take multiple columns as arguments."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:52:32.599550Z",
     "start_time": "2019-01-21T18:52:32.587608Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'census' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-15-003cd3152d8c>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Build a query to select the state and count of ages by state: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 5\u001b[1;33m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mselect\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mstate\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcount\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mage\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      6\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      7\u001b[0m \u001b[1;31m# Group stmt by state\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'census' is not defined"
     ]
    }
   ],
   "source": [
    "# Import func\n",
    "from sqlalchemy import func\n",
    "\n",
    "# Build a query to select the state and count of ages by state: stmt\n",
    "stmt = select([census.columns.state, func.count(census.columns.age)])\n",
    "\n",
    "# Group stmt by state\n",
    "stmt = stmt.group_by(census.columns.state)\n",
    "\n",
    "# Execute the statement and store all the records: results\n",
    "results = connection.execute(stmt).fetchall()\n",
    "\n",
    "# Print results\n",
    "print(results)\n",
    "\n",
    "# Print the keys/column names of the results returned\n",
    "print(results[0].keys())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Determining the Population Sum by State\n",
    "\n",
    "To avoid confusion with query result column names like count_1, we can use the .label() method to provide a name for the resulting column. This gets appendedto the function method we are using, and its argument is the name we want to use.\n",
    "\n",
    "We can pair func.sum() with .group_by() to get a sum of the population by State and use the label() method to name the output.\n",
    "\n",
    "We can also create the func.sum() expression before using it in the select statement. We do it the same way we would inside the select statement and store it in a variable. Then we use that variable in the select statement where the func.sum() would normally be."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:52:44.334703Z",
     "start_time": "2019-01-21T18:52:44.319744Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'census' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-16-fcd0b41c6b18>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Build an expression to calculate the sum of pop2008 labeled as population\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 5\u001b[1;33m \u001b[0mpop2008_sum\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msum\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mpop2008\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mlabel\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'population'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      6\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      7\u001b[0m \u001b[1;31m# Build a query to select the state and sum of pop2008: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'census' is not defined"
     ]
    }
   ],
   "source": [
    "# Import func\n",
    "from sqlalchemy import func\n",
    "\n",
    "# Build an expression to calculate the sum of pop2008 labeled as population\n",
    "pop2008_sum = func.sum(census.columns.pop2008).label('population')\n",
    "\n",
    "# Build a query to select the state and sum of pop2008: stmt\n",
    "stmt = select([census.columns.state, pop2008_sum])\n",
    "\n",
    "# Group stmt by state\n",
    "stmt = stmt.group_by(census.columns.state)\n",
    "\n",
    "# Execute the statement and store all the records: results\n",
    "results = connection.execute(stmt).fetchall()\n",
    "\n",
    "# Print results\n",
    "print(results)\n",
    "\n",
    "# Print the keys/column names of the results returned\n",
    "print(results[0].keys())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SQLAlchemy ResultsProxy and Pandas Dataframes\n",
    "\n",
    "We can feed a ResultProxy directly into a pandas DataFrame, which is the workhorse of many Data Scientists in PythonLand. Jason demonstrated this in the video. In this exercise, you'll follow exactly the same approach to convert a ResultProxy into a DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:53:04.217341Z",
     "start_time": "2019-01-21T18:53:03.878246Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'results' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-17-e24fcceffd50>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Create a DataFrame from the results: df\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 5\u001b[1;33m \u001b[0mdf\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mDataFrame\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mresults\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      6\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      7\u001b[0m \u001b[1;31m# Set column names\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'results' is not defined"
     ]
    }
   ],
   "source": [
    "# import pandas\n",
    "import pandas as pd\n",
    "\n",
    "# Create a DataFrame from the results: df\n",
    "df = pd.DataFrame(results)\n",
    "\n",
    "# Set column names\n",
    "df.columns = results[0].keys()\n",
    "\n",
    "# Print the Dataframe\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## From SQLAlchemy results to a Graph\n",
    "\n",
    "We can also take advantage of pandas and Matplotlib to build figures of our data. Remember that data visualization is essential for both exploratory data analysis and communication of your data!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:53:16.200062Z",
     "start_time": "2019-01-21T18:53:15.985635Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'results' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-18-c301104289a6>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Create a DataFrame from the results: df\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 5\u001b[1;33m \u001b[0mdf\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mDataFrame\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mresults\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      6\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      7\u001b[0m \u001b[1;31m# Set Column names\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'results' is not defined"
     ]
    }
   ],
   "source": [
    "# Import Pyplot as plt from matplotlib\n",
    "from matplotlib import pyplot as plt\n",
    "\n",
    "# Create a DataFrame from the results: df\n",
    "df = pd.DataFrame(results)\n",
    "\n",
    "# Set Column names\n",
    "df.columns = results[0].keys()\n",
    "\n",
    "# Print the DataFrame\n",
    "print(df)\n",
    "\n",
    "# Plot the DataFrame\n",
    "df.plot.bar()\n",
    "plt.show()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Advanced SQLAlchemy Queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connecting to a MySQL Database\n",
    "\n",
    "Before you jump into the calculation exercises, let's begin by connecting to our database. Recall that in the last chapter you connected to a PostgreSQL database. Now, you'll connect to a MySQL database, for which many prefer to use the pymysql database driver, which, like psycopg2 for PostgreSQL, you have to install prior to use.\n",
    "\n",
    "This connection string is going to start with 'mysql+pymysql://', indicating which dialect and driver you're using to establish the connection. The dialect block is followed by the 'username:password' combo. Next, you specify the host and port with the following '@host:port/'. Finally, you wrap up the connection string with the 'database_name'.\n",
    "\n",
    "Now you'll practice connecting to a MySQL database: it will be the same census database that you have already been working with. One of the great things about SQLAlchemy is that, after connecting, it abstracts over the type of database it has connected to and you can write the same SQLAlchemy code, regardless!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:53:42.545169Z",
     "start_time": "2019-01-21T18:53:42.406562Z"
    }
   },
   "outputs": [
    {
     "ename": "ModuleNotFoundError",
     "evalue": "No module named 'pymysql'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-19-17cf0f8e25bf>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Create an engine to the census database\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 5\u001b[1;33m \u001b[0mengine\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mcreate_engine\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'mysql+pymysql://student:datacamp@courses.csrrinzqubik.us-east-1.rds.amazonaws.com:3306/census'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      6\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      7\u001b[0m \u001b[1;31m# Print the table names\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\__init__.py\u001b[0m in \u001b[0;36mcreate_engine\u001b[1;34m(*args, **kwargs)\u001b[0m\n\u001b[0;32m    423\u001b[0m     \u001b[0mstrategy\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mkwargs\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mpop\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'strategy'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdefault_strategy\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    424\u001b[0m     \u001b[0mstrategy\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mstrategies\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mstrategies\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mstrategy\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 425\u001b[1;33m     \u001b[1;32mreturn\u001b[0m \u001b[0mstrategy\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcreate\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    426\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    427\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\strategies.py\u001b[0m in \u001b[0;36mcreate\u001b[1;34m(self, name_or_url, **kwargs)\u001b[0m\n\u001b[0;32m     79\u001b[0m                 \u001b[1;32mif\u001b[0m \u001b[0mk\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mkwargs\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     80\u001b[0m                     \u001b[0mdbapi_args\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mk\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpop_kwarg\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mk\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 81\u001b[1;33m             \u001b[0mdbapi\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdialect_cls\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdbapi\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m**\u001b[0m\u001b[0mdbapi_args\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     82\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     83\u001b[0m         \u001b[0mdialect_args\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'dbapi'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdbapi\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\sqlalchemy\\dialects\\mysql\\pymysql.py\u001b[0m in \u001b[0;36mdbapi\u001b[1;34m(cls)\u001b[0m\n\u001b[0;32m     60\u001b[0m     \u001b[1;33m@\u001b[0m\u001b[0mclassmethod\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     61\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mdbapi\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcls\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 62\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0m__import__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'pymysql'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     63\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     64\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[0mpy3k\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mModuleNotFoundError\u001b[0m: No module named 'pymysql'"
     ]
    }
   ],
   "source": [
    "# Import create_engine function\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "# Create an engine to the census database\n",
    "engine = create_engine('mysql+pymysql://student:datacamp@courses.csrrinzqubik.us-east-1.rds.amazonaws.com:3306/census')\n",
    "\n",
    "# Print the table names\n",
    "print(engine.table_names())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculating a Difference between Two Columns\n",
    "\n",
    "Often, you'll need to perform math operations as part of a query, such as if you wanted to calculate the change in population from 2000 to 2008. For math operations on numbers, the operators in SQLAlchemy work the same way as they do in Python.\n",
    "\n",
    "You can use these operators to perform addition (+), subtraction (-), multiplication (*), division (/), and modulus (%) operations. Note: They behave differently when used with non-numeric column types.\n",
    "\n",
    "Let's now find the top 5 states by population growth between 2000 and 2008."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:53:54.916356Z",
     "start_time": "2019-01-21T18:53:54.901418Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'census' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-20-ef76013a07b9>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Build query to return state names by population difference from 2008 to 2000: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mselect\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mstate\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m(\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mpop2008\u001b[0m \u001b[1;33m-\u001b[0m \u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mpop2000\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mlabel\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'pop_change'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Append group by for the state: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mstmt\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mgroup_by\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mstate\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'census' is not defined"
     ]
    }
   ],
   "source": [
    "# Build query to return state names by population difference from 2008 to 2000: stmt\n",
    "stmt = select([census.columns.state, (census.columns.pop2008 - census.columns.pop2000).label('pop_change')])\n",
    "\n",
    "# Append group by for the state: stmt\n",
    "stmt = stmt.group_by(census.columns.state)\n",
    "\n",
    "# Append order by for pop_change descendingly: stmt\n",
    "stmt = stmt.order_by(desc('pop_change'))\n",
    "\n",
    "# Return only 5 results: stmt\n",
    "stmt = stmt.limit(5)\n",
    "\n",
    "# Use connection to execute the statement and fetch all results\n",
    "results = connection.execute(stmt).fetchall()\n",
    "\n",
    "# Print the state and population change for each record\n",
    "for result in results:\n",
    "    print('{}:{}'.format(result.state, result.pop_change))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Determining the Overall Percentage of Females\n",
    "\n",
    "It's possible to combine functions and operators in a single select statement as well. These combinations can be exceptionally handy when we want to calculate percentages or averages, and we can also use the case() expression to operate on data that meets specific criteria while not affecting the query as a whole. The case() expression accepts a list of conditions to match and the column to return if the condition matches, followed by an else_ if none of the conditions match. We can wrap this entire expression in any function or math operation we like.\n",
    "\n",
    "Often when performing integer division, we want to get a float back. While some databases will do this automatically, you can use the cast() function to convert an expression to a particular type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:54:06.376008Z",
     "start_time": "2019-01-21T18:54:06.361022Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'census' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-21-5d4779533898>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      5\u001b[0m female_pop2000 = func.sum(\n\u001b[0;32m      6\u001b[0m     case([\n\u001b[1;32m----> 7\u001b[1;33m         \u001b[1;33m(\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msex\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m'F'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mpop2000\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      8\u001b[0m     ], else_=0))\n\u001b[0;32m      9\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'census' is not defined"
     ]
    }
   ],
   "source": [
    "# import case, cast and Float from sqlalchemy\n",
    "from sqlalchemy import case, cast, Float\n",
    "\n",
    "# Build an expression to calculate female population in 2000\n",
    "female_pop2000 = func.sum(\n",
    "    case([\n",
    "        (census.columns.sex == 'F', census.columns.pop2000)\n",
    "    ], else_=0))\n",
    "\n",
    "# Cast an expression to calculate total population in 2000 to Float\n",
    "total_pop2000 = cast(func.sum(census.columns.pop2000), Float)\n",
    "\n",
    "# Build a query to calculate the percentage of females in 2000: stmt\n",
    "stmt = select([female_pop2000 / total_pop2000 * 100])\n",
    "\n",
    "# Execute the query and store the scalar result: percent_female\n",
    "percent_female = connection.execute(stmt).scalar()\n",
    "\n",
    "# Print the percentage\n",
    "print(percent_female)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Automatic Joins with an Established Relationship\n",
    "\n",
    "If you have two tables that already have an established relationship, you can automatically use that relationship by just adding the columns we want from each table to the select statement. Recall that Jason constructed the following query:\n",
    "\n",
    "stmt = select([census.columns.pop2008, state_fact.columns.abbreviation])\n",
    "\n",
    "in order to join the census and state_fact tables and select the pop2008 column from the first and the abbreviation column from the second. In this case, the census and state_fact tables had a pre-defined relationship: the state column of the former corresponded to the name column of the latter.\n",
    "\n",
    "In this exercise, you'll use the same predefined relationship to select the pop2000 and abbreviation columns!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:54:20.428804Z",
     "start_time": "2019-01-21T18:54:20.413841Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'census' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-22-958a5e4ca6dd>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Build a statement to join census and state_fact tables: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mselect\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mpop2000\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstate_fact\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mabbreviation\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Execute the statement and get the first result: result\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mconnection\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstmt\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfirst\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'census' is not defined"
     ]
    }
   ],
   "source": [
    "# Build a statement to join census and state_fact tables: stmt\n",
    "stmt = select([census.columns.pop2000, state_fact.columns.abbreviation])\n",
    "\n",
    "# Execute the statement and get the first result: result\n",
    "result = connection.execute(stmt).first()\n",
    "\n",
    "# Loop over the keys in the result object and print the key and value\n",
    "for key in result.keys():\n",
    "    print(key, getattr(result, key))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Joins\n",
    "\n",
    "If you aren't selecting columns from both tables or the two tables don't have a defined relationship, you can still use the .join() method on a table to join it with another table and get extra data related to our query. The join() takes the table object you want to join in as the first argument and a condition that indicates how the tables are related to the second argument. Finally, you use the .select_from() method on the select statement to wrap the join clause. For example, in the video, Jason executed the following code to join the census table to the state_fact table such that the state column of the census table corresponded to the name column of the state_fact table.\n",
    "\n",
    "stmt = stmt.select_from(\n",
    "    census.join(\n",
    "        state_fact, census.columns.state == \n",
    "        state_fact.columns.name)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:54:33.839831Z",
     "start_time": "2019-01-21T18:54:33.827861Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'census' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-23-b51a571fb5dc>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Build a statement to select the census and state_fact tables: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mselect\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstate_fact\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Add a select_from clause that wraps a join for the census and state_fact\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[1;31m# tables where the census state column and state_fact name column match\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'census' is not defined"
     ]
    }
   ],
   "source": [
    "# Build a statement to select the census and state_fact tables: stmt\n",
    "stmt = select([census, state_fact])\n",
    "\n",
    "# Add a select_from clause that wraps a join for the census and state_fact\n",
    "# tables where the census state column and state_fact name column match\n",
    "stmt = stmt.select_from(\n",
    "    census.join(state_fact, census.columns.state == state_fact.columns.name))\n",
    "\n",
    "# Execute the statement and get the first result: result\n",
    "result = connection.execute(stmt).first()\n",
    "\n",
    "# Loop over the keys in the result object and print the key and value\n",
    "for key in result.keys():\n",
    "    print(key, getattr(result, key))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## More Practice with Joins\n",
    "\n",
    "You can use the same select statement you built in the last exercise, however, let's add a twist and only return a few columns and use the other table in a group_by() clause."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:54:49.784633Z",
     "start_time": "2019-01-21T18:54:49.768677Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'census' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-24-b1743eaa3c02>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Build a statement to select the state, sum of 2008 population and census\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      2\u001b[0m \u001b[1;31m# division name: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 3\u001b[1;33m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mselect\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mstate\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msum\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mpop2008\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstate_fact\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcensus_division_name\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      4\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[1;31m# Append select_from to join the census and state_fact tables by the census state and state_fact name columns\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'census' is not defined"
     ]
    }
   ],
   "source": [
    "# Build a statement to select the state, sum of 2008 population and census\n",
    "# division name: stmt\n",
    "stmt = select([census.columns.state, func.sum(census.columns.pop2008), state_fact.columns.census_division_name])\n",
    "\n",
    "# Append select_from to join the census and state_fact tables by the census state and state_fact name columns\n",
    "stmt = stmt.select_from(\n",
    "    census.join(state_fact, census.columns.state == state_fact.columns.name)\n",
    ")\n",
    "\n",
    "# Append a group by for the state_fact name column\n",
    "stmt = stmt.group_by(state_fact.columns.name)\n",
    "\n",
    "# Execute the statement and get the results: results\n",
    "results = connection.execute(stmt).fetchall()\n",
    "\n",
    "# Loop over the the results object and print each record.\n",
    "for record in results:\n",
    "    print(record)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using alias to handle same table joined queries\n",
    "\n",
    "Often, you'll have tables that contain hierarchical data, such as employees and managers who are also employees. For this reason, you may wish to join a table to itself on different columns. The .alias() method, which creates a copy of a table, helps accomplish this task. Because it's the same table, you only need a where clause to specify the join condition.\n",
    "\n",
    "Here, you'll use the .alias() method to build a query to join the employees table against itself to determine to whom everyone reports."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:55:07.369483Z",
     "start_time": "2019-01-21T18:55:07.351554Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'employees' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-25-834465e93100>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Make an alias of the employees table: managers\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mmanagers\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0memployees\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0malias\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Build a query to select manager's and their employees names: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m stmt = select(\n",
      "\u001b[1;31mNameError\u001b[0m: name 'employees' is not defined"
     ]
    }
   ],
   "source": [
    "# Make an alias of the employees table: managers\n",
    "managers = employees.alias()\n",
    "\n",
    "# Build a query to select manager's and their employees names: stmt\n",
    "stmt = select(\n",
    "    [managers.columns.name.label('manager'),\n",
    "     employees.columns.name.label('employee')]\n",
    ")\n",
    "\n",
    "# Match managers id with employees mgr: stmt\n",
    "stmt = stmt.where(managers.columns.id == employees.columns.mgr)\n",
    "\n",
    "# Order the statement by the managers name: stmt\n",
    "stmt = stmt.order_by(managers.columns.name)\n",
    "\n",
    "# Execute statement: results\n",
    "results = connection.execute(stmt).fetchall()\n",
    "\n",
    "# Print records\n",
    "for record in results:\n",
    "    print(record)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Leveraging Functions and Group_bys with Hierarchical Data\n",
    "\n",
    "It's also common to want to roll up data which is in a hierarchical table. Rolling up data requires making sure you're careful which alias you use to perform the group_bys and which table you use for the function.\n",
    "\n",
    "Here, your job is to get a count of employees for each manager."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:55:20.955643Z",
     "start_time": "2019-01-21T18:55:20.943674Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'employees' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-26-179622a41c12>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Make an alias of the employees table: managers\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mmanagers\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0memployees\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0malias\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Build a query to select managers and counts of their employees: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mselect\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mmanagers\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcount\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0memployees\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mid\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'employees' is not defined"
     ]
    }
   ],
   "source": [
    "# Make an alias of the employees table: managers\n",
    "managers = employees.alias()\n",
    "\n",
    "# Build a query to select managers and counts of their employees: stmt\n",
    "stmt = select([managers.columns.name, func.count(employees.columns.id)])\n",
    "\n",
    "# Append a where clause that ensures the manager id and employee mgr are equal\n",
    "stmt = stmt.where(managers.columns.id == employees.columns.mgr)\n",
    "\n",
    "# Group by Managers Name\n",
    "stmt = stmt.group_by(managers.columns.name)\n",
    "\n",
    "# Execute statement: results\n",
    "results = connection.execute(stmt).fetchall()\n",
    "\n",
    "# print manager\n",
    "for record in results:\n",
    "    print(record)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Working on Blocks of Records\n",
    "\n",
    "Fantastic work so far! As Jason discussed in the video, sometimes you may have the need to work on a large ResultProxy, and you may not have the memory to load all the results at once. To work around that issue, you can get blocks of rows from the ResultProxy by using the .fetchmany() method inside a loop. With .fetchmany(), give it an argument of the number of records you want. When you reach an empty list, there are no more rows left to fetch, and you have processed all the results of the query. Then you need to use the .close() method to close out the connection to the database.\n",
    "\n",
    "You'll now have the chance to practice this on a large ResultProxy called results_proxy that has been pre-loaded for you to work with."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:55:35.339890Z",
     "start_time": "2019-01-21T18:55:35.323935Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'more_results' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-27-73a1410d2ae2>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Start a while loop checking for more results\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[1;32mwhile\u001b[0m \u001b[0mmore_results\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m     \u001b[1;31m# Fetch the first 50 results from the ResultProxy: partial_results\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m     \u001b[0mpartial_results\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mresults_proxy\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfetchmany\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m50\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'more_results' is not defined"
     ]
    }
   ],
   "source": [
    "# Start a while loop checking for more results\n",
    "while more_results:\n",
    "    # Fetch the first 50 results from the ResultProxy: partial_results\n",
    "    partial_results = results_proxy.fetchmany(50)\n",
    "\n",
    "    # if empty list, set more_results to False\n",
    "    if partial_results == []:\n",
    "        more_results = False\n",
    "\n",
    "    # Loop over the fetched records and increment the count for the state\n",
    "    for row in partial_results:\n",
    "        if row.state in state_count:\n",
    "            state_count[row.state] +=1\n",
    "        else:\n",
    "            state_count[row.state] = 1\n",
    "\n",
    "# Close the ResultProxy, and thus the connection\n",
    "results_proxy.close()\n",
    "\n",
    "# Print the count by state\n",
    "print(state_count)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating and Manipulating your own Databases"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating Tables with SQLAlchemy\n",
    "\n",
    "Previously, you used the Table object to reflect a table from an existing database, but what if you wanted to create a new table? You'd still use the Table object; however, you'd need to replace the autoload and autoload_with parameters with Column objects.\n",
    "\n",
    "The Column object takes a name, a SQLAlchemy type with an optional format, and optional keyword arguments for different constraints.\n",
    "\n",
    "When defining the table, recall how in the video Jason passed in 255 as the maximum length of a String by using Column('name', String(255)). Checking out the slides from the video may help: you can download them by clicking on 'Slides' next to the IPython Shell.\n",
    "\n",
    "After defining the table, you can create the table in the database by using the .create_all() method on metadata and supplying the engine as the only parameter. Go for it!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:56:15.023138Z",
     "start_time": "2019-01-21T18:56:15.010197Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'metadata' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-28-58c463e72193>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Define a new table with a name, count, amount, and valid column: data\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 5\u001b[1;33m data = Table('data', metadata,\n\u001b[0m\u001b[0;32m      6\u001b[0m              \u001b[0mColumn\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'name'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mString\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m255\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      7\u001b[0m              \u001b[0mColumn\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'count'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mInteger\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'metadata' is not defined"
     ]
    }
   ],
   "source": [
    "# Import Table, Column, String, Integer, Float, Boolean from sqlalchemy\n",
    "from sqlalchemy import Table, Column, String, Integer, Float, Boolean\n",
    "\n",
    "# Define a new table with a name, count, amount, and valid column: data\n",
    "data = Table('data', metadata,\n",
    "             Column('name', String(255)),\n",
    "             Column('count', Integer()),\n",
    "             Column('amount', Float()),\n",
    "             Column('valid', Boolean())\n",
    ")\n",
    "\n",
    "# Use the metadata to create the table\n",
    "metadata.create_all(engine)\n",
    "\n",
    "# Print table details\n",
    "print(repr(data))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Constraints and Data Defaults\n",
    "\n",
    "You're now going to practice creating a table with some constraints! Often, you'll need to make sure that a column is unique, nullable, a positive value, or related to a column in another table. This is where constraints come in.\n",
    "\n",
    "As Jason showed you in the video, in addition to constraints, you can also set a default value for the column if no data is passed to it via the default keyword on the column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:56:29.018328Z",
     "start_time": "2019-01-21T18:56:29.003369Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'metadata' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-29-01b8edf4f10a>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Define a new table with a name, count, amount, and valid column: data\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 5\u001b[1;33m data = Table('data', metadata,\n\u001b[0m\u001b[0;32m      6\u001b[0m              \u001b[0mColumn\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'name'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mString\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m255\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0munique\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      7\u001b[0m              \u001b[0mColumn\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'count'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mInteger\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdefault\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'metadata' is not defined"
     ]
    }
   ],
   "source": [
    "# Import Table, Column, String, Integer, Float, Boolean from sqlalchemy\n",
    "from sqlalchemy import Table, Column, String, Integer, Float, Boolean\n",
    "\n",
    "# Define a new table with a name, count, amount, and valid column: data\n",
    "data = Table('data', metadata,\n",
    "             Column('name', String(255), unique=True),\n",
    "             Column('count', Integer(), default=1),\n",
    "             Column('amount', Float()),\n",
    "             Column('valid', Boolean(), default=False)\n",
    ")\n",
    "\n",
    "# Use the metadata to create the table\n",
    "metadata.create_all(engine)\n",
    "\n",
    "# Print the table details\n",
    "print(repr(metadata.tables['data']))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Inserting a single row with an insert() statement\n",
    "\n",
    "There are several ways to perform an insert with SQLAlchemy; however, we are going to focus on the one that follows the same pattern as the select statement.\n",
    "\n",
    "It uses an insert statement where you specify the table as an argument, and supply the data you wish to insert into the value via the .values() method as keyword arguments.\n",
    "\n",
    "Here, the name of the table is data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:56:44.172663Z",
     "start_time": "2019-01-21T18:56:44.158701Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'data' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-30-dd91eb02adf5>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Build an insert statement to insert a record into the data table: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 5\u001b[1;33m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0minsert\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'Anna'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcount\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mamount\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m1000.00\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mvalid\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      6\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      7\u001b[0m \u001b[1;31m# Execute the statement via the connection: results\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'data' is not defined"
     ]
    }
   ],
   "source": [
    "# Import insert and select from sqlalchemy\n",
    "from sqlalchemy import insert, select\n",
    "\n",
    "# Build an insert statement to insert a record into the data table: stmt\n",
    "stmt = insert(data).values(name='Anna', count=1, amount=1000.00, valid=True)\n",
    "\n",
    "# Execute the statement via the connection: results\n",
    "results = connection.execute(stmt)\n",
    "\n",
    "# Print result rowcount\n",
    "print(results.rowcount)\n",
    "\n",
    "# Build a select statement to validate the insert\n",
    "stmt = select([data]).where(data.columns.name == 'Anna')\n",
    "\n",
    "# Print the result of executing the query.\n",
    "print(connection.execute(stmt).first())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Inserting Multiple Records at Once\n",
    "\n",
    "It's time to practice inserting multiple records at once!\n",
    "\n",
    "As Jason showed you in the video, you'll want to first build a list of dictionaries that represents the data you want to insert. Then, in the .execute() method, you can pair this list of dictionaries with an insert statement, which will insert all the records in your list of dictionaries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:56:58.613837Z",
     "start_time": "2019-01-21T18:56:58.602868Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'data' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-31-da7c6a2b55e8>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      6\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      7\u001b[0m \u001b[1;31m# Build an insert statement for the data table: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 8\u001b[1;33m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0minsert\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      9\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     10\u001b[0m \u001b[1;31m# Execute stmt with the values_list: results\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'data' is not defined"
     ]
    }
   ],
   "source": [
    "# Build a list of dictionaries: values_list\n",
    "values_list = [\n",
    "    {'name': 'Anna', 'count': 1, 'amount': 1000.00, 'valid': True},\n",
    "    {'name': 'Taylor', 'count': 1, 'amount': 750.00, 'valid': False}\n",
    "]\n",
    "\n",
    "# Build an insert statement for the data table: stmt\n",
    "stmt = insert(data)\n",
    "\n",
    "# Execute stmt with the values_list: results\n",
    "results = connection.execute(stmt, values_list)\n",
    "\n",
    "# Print rowcount\n",
    "print(results.rowcount)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:57:05.800493Z",
     "start_time": "2019-01-21T18:57:05.792490Z"
    }
   },
   "source": [
    "## Loading a CSV into a Table\n",
    "\n",
    "You've done a great job so far at inserting data into tables! You're now going to learn how to load the contents of a CSV file into a table.\n",
    "\n",
    "We have used the csv module to set up a csv_reader, which is just a reader object that can iterate over the lines in a given CSV file - in this case, a census CSV file. Using the enumerate() function, you can loop over the csv_reader to handle the results one at a time. Here, for example, the first line it would return is:\n",
    "\n",
    "0 ['Illinois', 'M', '0', '89600', '95012']\n",
    "\n",
    "0 is the idx - or line number - while ['Illinois', 'M', '0', '89600', '95012'] is the row, corresponding to the column names 'state' , 'sex', 'age', 'pop2000 'and 'pop2008'. 'Illinois' can be accessed with row[0], 'M' with row[1], and so on. You can create a dictionary containing this information where the keys are the column names and the values are the entries in each line. Then, by appending this dictionary to a list, you can combine it with an insert statement to load it all into a table!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:57:18.383840Z",
     "start_time": "2019-01-21T18:57:18.370854Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'census' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-33-75543ed62f18>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Create a insert statement for census: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0minsert\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Create an empty list and zeroed row count: values_list, total_rowcount\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mvalues_list\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'census' is not defined"
     ]
    }
   ],
   "source": [
    "# Create a insert statement for census: stmt\n",
    "stmt = insert(census)\n",
    "\n",
    "# Create an empty list and zeroed row count: values_list, total_rowcount\n",
    "values_list = []\n",
    "total_rowcount = 0\n",
    "\n",
    "# Enumerate the rows of csv_reader\n",
    "for idx, row in enumerate(csv_reader):\n",
    "    #create data and append to values_list\n",
    "    data = {'state': row[0], 'sex': row[1], 'age': row[2], 'pop2000': row[3],\n",
    "            'pop2008': row[4]}\n",
    "    values_list.append(data)\n",
    "\n",
    "    # Check to see if divisible by 51\n",
    "    if idx % 51 == 0:\n",
    "        results = connection.execute(stmt, values_list)\n",
    "        total_rowcount += results.rowcount\n",
    "        values_list = []\n",
    "\n",
    "# Print total rowcount\n",
    "print(total_rowcount)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:57:27.132107Z",
     "start_time": "2019-01-21T18:57:27.124140Z"
    }
   },
   "source": [
    "## Updating individual records\n",
    "\n",
    "The update statement is very similar to an insert statement, except that it also typically uses a where clause to help us determine what data to update. You'll be using the FIPS state code using here, which is appropriated by the U.S. government to identify U.S. states and certain other associated areas. Recall that you can update all wages in the employees table as follows:\n",
    "\n",
    "stmt = update(employees).values(wage=100.00)\n",
    "\n",
    "For your convenience, the names of the tables and columns of interest in this exercise are: state_fact (Table), name (Column), and fips_state (Column)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:57:42.661345Z",
     "start_time": "2019-01-21T18:57:42.649352Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'state_fact' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-35-837fe604e56e>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Build a select statement: select_stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mselect_stmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mselect\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mstate_fact\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mwhere\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstate_fact\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mname\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m'New York'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Print the results of executing the select_stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mconnection\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mselect_stmt\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfetchall\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'state_fact' is not defined"
     ]
    }
   ],
   "source": [
    "# Build a select statement: select_stmt\n",
    "select_stmt = select([state_fact]).where(state_fact.columns.name == 'New York')\n",
    "\n",
    "# Print the results of executing the select_stmt\n",
    "print(connection.execute(select_stmt).fetchall())\n",
    "\n",
    "# Build a statement to update the fips_state to 36: stmt\n",
    "stmt = update(state_fact).values(fips_state=36)\n",
    "\n",
    "# Append a where clause to limit it to records for New York state\n",
    "stmt = stmt.where(state_fact.columns.name == 'New York')\n",
    "\n",
    "# Execute the statement: results\n",
    "results = connection.execute(stmt)\n",
    "\n",
    "# Print rowcount\n",
    "print(results.rowcount)\n",
    "\n",
    "# Execute the select_stmt again to view the changes\n",
    "print(connection.execute(select_stmt).fetchall())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Updating Multiple Records\n",
    "\n",
    "As Jason discussed in the video, by using a where clause that selects more records, you can update multiple records at once. It's time now to practice this!\n",
    "\n",
    "For your convenience, the names of the tables and columns of interest in this exercise are: state_fact (Table), notes (Column), and census_region_name (Column)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:57:57.442005Z",
     "start_time": "2019-01-21T18:57:57.431036Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'update' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-36-d490ad3da608>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Build a statement to update the notes to 'The Wild West': stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mupdate\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstate_fact\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mnotes\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'The Wild West'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Append a where clause to match the West census region records\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mstmt\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mwhere\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstate_fact\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcensus_region_name\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m'West'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'update' is not defined"
     ]
    }
   ],
   "source": [
    "# Build a statement to update the notes to 'The Wild West': stmt\n",
    "stmt = update(state_fact).values(notes='The Wild West')\n",
    "\n",
    "# Append a where clause to match the West census region records\n",
    "stmt = stmt.where(state_fact.columns.census_region_name == 'West')\n",
    "\n",
    "# Execute the statement: results\n",
    "results = connection.execute(stmt)\n",
    "\n",
    "# Print rowcount\n",
    "print(results.rowcount)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Correlated Updates\n",
    "\n",
    "You can also update records with data from a select statement. This is called a correlated update. It works by defining a select statement that returns the value you want to update the record with and assigning that as the value in an update statement.\n",
    "\n",
    "You'll be using a flat_census in this exercise as the target of your correlated update. The flat_census table is a summarized copy of your census table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:58:14.170210Z",
     "start_time": "2019-01-21T18:58:14.156271Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'state_fact' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-37-68abc885995e>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Build a statement to select name from state_fact: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mfips_stmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mselect\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mstate_fact\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Append a where clause to Match the fips_state to flat_census fips_code\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m fips_stmt = fips_stmt.where(\n",
      "\u001b[1;31mNameError\u001b[0m: name 'state_fact' is not defined"
     ]
    }
   ],
   "source": [
    "# Build a statement to select name from state_fact: stmt\n",
    "fips_stmt = select([state_fact.columns.name])\n",
    "\n",
    "# Append a where clause to Match the fips_state to flat_census fips_code\n",
    "fips_stmt = fips_stmt.where(\n",
    "    state_fact.columns.fips_state == flat_census.columns.fips_code)\n",
    "\n",
    "# Build an update statement to set the name to fips_stmt: update_stmt\n",
    "update_stmt = update(flat_census).values(state_name=fips_stmt)\n",
    "\n",
    "# Execute update_stmt: results\n",
    "results = connection.execute(update_stmt)\n",
    "\n",
    "# Print rowcount\n",
    "print(results.rowcount)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deleting all the records from a table\n",
    "\n",
    "Often, you'll need to empty a table of all of its records so you can reload the data. You can do this with a delete statement with just the table as an argument. For example, in the video, Jason deleted the table extra_employees by executing as follows:\n",
    "\n",
    "delete_stmt = delete(extra_employees)\n",
    "result_proxy = connection.execute(delete_stmt)\n",
    "\n",
    "Do be careful, though, as deleting cannot be undone!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:58:32.961569Z",
     "start_time": "2019-01-21T18:58:32.948627Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'census' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-38-4bf1cc1b6fc6>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Build a statement to empty the census table: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 5\u001b[1;33m \u001b[0mstmt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdelete\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcensus\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      6\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      7\u001b[0m \u001b[1;31m# Execute the statement: results\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'census' is not defined"
     ]
    }
   ],
   "source": [
    "# Import delete, select\n",
    "from sqlalchemy import delete, select\n",
    "\n",
    "# Build a statement to empty the census table: stmt\n",
    "stmt = delete(census)\n",
    "\n",
    "# Execute the statement: results\n",
    "results = connection.execute(stmt)\n",
    "\n",
    "# Print affected rowcount\n",
    "print(results.rowcount)\n",
    "\n",
    "# Build a statement to select all records from the census table\n",
    "stmt = select([census])\n",
    "\n",
    "# Print the results of executing the statement to verify there are no rows\n",
    "print(connection.execute(stmt).fetchall())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deleting specific records\n",
    "\n",
    "By using a where() clause, you can target the delete statement to remove only certain records. For example, Jason deleted all rows from the employees table that had id 3 with the following delete statement:\n",
    "\n",
    "delete(employees).where(employees.columns.id == 3) \n",
    "\n",
    "Here you'll delete ALL rows which have 'M' in the sex column and 36 in the age column. We have included code at the start which computes the total number of these rows. It is important to make sure that this is the number of rows that you actually delete."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:58:45.892675Z",
     "start_time": "2019-01-21T18:58:45.877713Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'census' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-39-94920501a2ac>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Build a statement to count records using the sex column for Men ('M') age 36: stmt\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m stmt = select([func.count(census.columns.sex)]).where(\n\u001b[0m\u001b[0;32m      3\u001b[0m     and_(census.columns.sex == 'M',\n\u001b[0;32m      4\u001b[0m          census.columns.age == 36)\n\u001b[0;32m      5\u001b[0m )\n",
      "\u001b[1;31mNameError\u001b[0m: name 'census' is not defined"
     ]
    }
   ],
   "source": [
    "# Build a statement to count records using the sex column for Men ('M') age 36: stmt\n",
    "stmt = select([func.count(census.columns.sex)]).where(\n",
    "    and_(census.columns.sex == 'M',\n",
    "         census.columns.age == 36)\n",
    ")\n",
    "\n",
    "# Execute the select statement and use the scalar() fetch method to save the record count\n",
    "to_delete = connection.execute(stmt).scalar()\n",
    "\n",
    "# Build a statement to delete records from the census table: stmt_del\n",
    "stmt_del = delete(census)\n",
    "\n",
    "# Append a where clause to target Men ('M') age 36\n",
    "stmt_del = stmt_del.where(\n",
    "    and_(census.columns.sex == 'M',\n",
    "         census.columns.age == 36)\n",
    ")\n",
    "\n",
    "# Execute the statement: results\n",
    "results = connection.execute(stmt_del)\n",
    "\n",
    "# Print affected rowcount and to_delete record count, make sure they match\n",
    "print(results.rowcount, to_delete)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deleting a Table Completely\n",
    "\n",
    "You're now going to practice dropping individual tables from a database with the .drop() method, as well as all tables in a database with the .drop_all() method!\n",
    "\n",
    "As Spider-Man's Uncle Ben (as well as Jason, in the video!) said: With great power, comes great responsibility. Do be careful when deleting tables, as it's not simple or fast to restore large databases! Remember, you can check to see if a table exists with the .exists() method.\n",
    "\n",
    "This is the final exercise in this chapter: After this, you'll be ready to apply everything you've learned to a case study in the final chapter of this course!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:58:58.794314Z",
     "start_time": "2019-01-21T18:58:58.783343Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'state_fact' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-40-ee81269e5625>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# Drop the state_fact table\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mstate_fact\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdrop\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Check to see if state_fact exists\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstate_fact\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexists\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'state_fact' is not defined"
     ]
    }
   ],
   "source": [
    "# Drop the state_fact table\n",
    "state_fact.drop(engine)\n",
    "\n",
    "# Check to see if state_fact exists\n",
    "print(state_fact.exists(engine))\n",
    "\n",
    "# Drop all tables\n",
    "metadata.drop_all(engine)\n",
    "\n",
    "# Check to see if census exists\n",
    "print(census.exists(engine))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Putting it all together"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup the Engine and MetaData\n",
    "\n",
    "In this exercise, your job is to create an engine to the database that will be used in this chapter. Then, you need to initialize its metadata.\n",
    "\n",
    "Recall how you did this in Chapter 1 by leveraging create_engine() and MetaData."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:59:31.045033Z",
     "start_time": "2019-01-21T18:59:31.038049Z"
    }
   },
   "outputs": [],
   "source": [
    "# Import create_engine, MetaData\n",
    "from sqlalchemy import create_engine, MetaData\n",
    "\n",
    "# Define an engine to connect to chapter5.sqlite: engine\n",
    "engine = create_engine('sqlite:///chapter5.sqlite')\n",
    "\n",
    "# Initialize MetaData: metadata\n",
    "metadata = MetaData()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create the Table to the Database\n",
    "\n",
    "Having setup the engine and initialized the metadata, you will now define the census table object and then create it in the database using the metadata and engine from the previous exercise. To create it in the database, you will have to use the .create_all() method on the metadata with engine as the argument.\n",
    "\n",
    "It may help to refer back to the Chapter 4 exercise in which you learned how to create a table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T18:59:44.819015Z",
     "start_time": "2019-01-21T18:59:44.788099Z"
    }
   },
   "outputs": [],
   "source": [
    "# Import Table, Column, String, and Integer\n",
    "from sqlalchemy import Table, Column, String, Integer\n",
    "\n",
    "# Build a census table: census\n",
    "census = Table('census', metadata,\n",
    "               Column('state', String(30) ),\n",
    "               Column('sex', String(1) ),\n",
    "               Column('age', Integer() ),\n",
    "               Column('pop2000', Integer() ),\n",
    "               Column('pop2008', Integer()))\n",
    "\n",
    "# Create the table in the database\n",
    "metadata.create_all(engine)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading the Data from the CSV\n",
    "\n",
    "Leverage the Python CSV module from the standard library and load the data into a list of dictionaries.\n",
    "\n",
    "It may help to refer back to the Chapter 4 exercise in which you did something similar."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T19:00:00.272152Z",
     "start_time": "2019-01-21T19:00:00.258188Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'csv_reader' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-43-231e86fc0c7b>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;31m# Iterate over the rows\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 5\u001b[1;33m \u001b[1;32mfor\u001b[0m \u001b[0mrow\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mcsv_reader\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      6\u001b[0m     \u001b[1;31m# Create a dictionary with the values\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      7\u001b[0m     data = {'state': row[0], 'sex': row[1], 'age' : row[2], 'pop2000' : row[3],\n",
      "\u001b[1;31mNameError\u001b[0m: name 'csv_reader' is not defined"
     ]
    }
   ],
   "source": [
    "# Create an empty list: values_list\n",
    "values_list = []\n",
    "\n",
    "# Iterate over the rows\n",
    "for row in csv_reader:\n",
    "    # Create a dictionary with the values\n",
    "    data = {'state': row[0], 'sex': row[1], 'age' : row[2], 'pop2000' : row[3],\n",
    "            'pop2008' : row[4]}\n",
    "    # Append the dictionary to the values list\n",
    "    values_list.append(data)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Data from a list into the Table\n",
    "\n",
    "Using the multiple insert pattern, in this exercise, you will load the data from values_list into the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T19:00:13.542018Z",
     "start_time": "2019-01-21T19:00:13.530074Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'connection' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-44-2ef0fd3bd069>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      6\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      7\u001b[0m \u001b[1;31m# Use values_list to insert data: results\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 8\u001b[1;33m \u001b[0mresults\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mconnection\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstmt\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mvalues_list\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      9\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     10\u001b[0m \u001b[1;31m# Print rowcount\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'connection' is not defined"
     ]
    }
   ],
   "source": [
    "# Import insert\n",
    "from sqlalchemy import insert\n",
    "\n",
    "# Build insert statement: stmt\n",
    "stmt = insert(census)\n",
    "\n",
    "# Use values_list to insert data: results\n",
    "results = connection.execute(stmt, values_list)\n",
    "\n",
    "# Print rowcount\n",
    "print(results.rowcount)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Build a Query to Determine the Average Age by Population\n",
    "\n",
    "In this exercise, you will use the func.sum() and group_by() methods to first determine the average age weighted by the population in 2008, and then group by sex.\n",
    "\n",
    "As Jason discussed in the video, a weighted average is calculated as the sum of the product of the weights and averages divided by the sum of all the weights.\n",
    "\n",
    "For example, the following statement determines the average age weighted by the population in 2000:\n",
    "\n",
    "stmt = select([census.columns.sex,\n",
    "               (func.sum(census.columns.pop2000 * census.columns.age) /\n",
    "                func.sum(census.columns.pop2000)).label('average_age')\n",
    "               ])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T19:00:33.662298Z",
     "start_time": "2019-01-21T19:00:33.648352Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'connection' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-45-a99e9afd71c3>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     12\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     13\u001b[0m \u001b[1;31m# Execute the query and store the results: results\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 14\u001b[1;33m \u001b[0mresults\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mconnection\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstmt\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfetchall\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     15\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     16\u001b[0m \u001b[1;31m# Print the average age by sex\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'connection' is not defined"
     ]
    }
   ],
   "source": [
    "# Import select\n",
    "from sqlalchemy import select\n",
    "\n",
    "# Calculate weighted average age: stmt\n",
    "stmt = select([census.columns.sex,\n",
    "               (func.sum(census.columns.pop2008 * census.columns.age) /\n",
    "                func.sum(census.columns.pop2008)).label('average_age')\n",
    "               ])\n",
    "\n",
    "# Group by sex\n",
    "stmt = stmt.group_by(census.columns.sex)\n",
    "\n",
    "# Execute the query and store the results: results\n",
    "results = connection.execute(stmt).fetchall()\n",
    "\n",
    "# Print the average age by sex\n",
    "for result in results:\n",
    "    print(result.sex, result.average_age)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Build a Query to Determine the Percentage of Population by Gender and State\n",
    "\n",
    "In this exercise, you will write a query to determine the percentage of the population in 2000 that comprised of women. You will group this query by state."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T19:00:46.517117Z",
     "start_time": "2019-01-21T19:00:46.502159Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'connection' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-46-bf282084c304>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     15\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     16\u001b[0m \u001b[1;31m# Execute the query and store the results: results\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 17\u001b[1;33m \u001b[0mresults\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mconnection\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstmt\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfetchall\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     18\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     19\u001b[0m \u001b[1;31m# Print the percentage\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'connection' is not defined"
     ]
    }
   ],
   "source": [
    "# import case, cast and Float from sqlalchemy\n",
    "from sqlalchemy import case, cast, Float\n",
    "\n",
    "# Build a query to calculate the percentage of females in 2000: stmt\n",
    "stmt = select([census.columns.state,\n",
    "    (func.sum(\n",
    "        case([\n",
    "            (census.columns.sex == 'F', census.columns.pop2000)\n",
    "        ], else_=0)) /\n",
    "     cast(func.sum(census.columns.pop2000), Float) * 100).label('percent_female')\n",
    "])\n",
    "\n",
    "# Group By state\n",
    "stmt = stmt.group_by(census.columns.state)\n",
    "\n",
    "# Execute the query and store the results: results\n",
    "results = connection.execute(stmt).fetchall()\n",
    "\n",
    "# Print the percentage\n",
    "for result in results:\n",
    "    print(result.state, result.percent_female)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T19:00:53.421952Z",
     "start_time": "2019-01-21T19:00:53.415983Z"
    }
   },
   "source": [
    "## Build a Query to Determine the Difference by State from the 2000 and 2008 Censuses\n",
    "\n",
    "In this final exercise, you will write a query to calculate the states that changed the most in population. You will limit your query to display only the top 10 states."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-01-21T19:01:05.432230Z",
     "start_time": "2019-01-21T19:01:05.418252Z"
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'connection' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-48-11c018a40005>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m     14\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     15\u001b[0m \u001b[1;31m# Use connection to execute the statement and fetch all results\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 16\u001b[1;33m \u001b[0mresults\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mconnection\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstmt\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfetchall\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     17\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     18\u001b[0m \u001b[1;31m# Print the state and population change for each record\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'connection' is not defined"
     ]
    }
   ],
   "source": [
    "# Build query to return state name and population difference from 2008 to 2000\n",
    "stmt = select([census.columns.state,\n",
    "     (census.columns.pop2008 - census.columns.pop2000).label('pop_change')\n",
    "])\n",
    "\n",
    "# Group by State\n",
    "stmt = stmt.group_by(census.columns.state)\n",
    "\n",
    "# Order by Population Change\n",
    "stmt = stmt.order_by(desc('pop_change'))\n",
    "\n",
    "# Limit to top 10\n",
    "stmt = stmt.limit(10)\n",
    "\n",
    "# Use connection to execute the statement and fetch all results\n",
    "results = connection.execute(stmt).fetchall()\n",
    "\n",
    "# Print the state and population change for each record\n",
    "for result in results:\n",
    "    print('{}:{}'.format(result.state, result.pop_change))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.7"
  },
  "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": true
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}