CFES Problem 1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import Datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#from urllib.request import urlretrieve as retrieve\n",
    "import requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Create two dictionaries of 'url' addresses per state\n",
    "## - Each dictionary key will hold the name and extension we want our filed to be saved under\n",
    "    ## - Save the dowloaded file with State name and .html extension\n",
    "## - Each dictonary value will hold the direct download link for each xls file\n",
    "    ## - NOTE the file is acutally HTML saved as xls format \n",
    "        ## - Dictionary 1 will hold the Public District info within each State\n",
    "        ## - Dictionary 2 will hold the Public Schools info within each State\n",
    "\n",
    "url = { 'Alabama.html' : 'https://nces.ed.gov/tempfiles/excelcreator/ncesdata_5107175B.xls',\n",
    "        #'Alaska.html' : 'https://nces.ed.gov/tempfiles/excelcreator/ncesdata_C4078CFB.xls',\n",
    "        'Arizona.html' : 'https://nces.ed.gov/tempfiles/excelcreator/ncesdata_C9C543D0.xls',\n",
    "        #'Arkansas.html' : 'https://nces.ed.gov/tempfiles/excelcreator/ncesdata_3C3A77A2.xls',\n",
    "        'California.html' : 'https://nces.ed.gov/tempfiles/excelcreator/ncesdata_911234A6.xls'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Download and rename files\n",
    "import pandas as pd\n",
    "\n",
    "for i, address in url.items():\n",
    "    g = requests.get(address)\n",
    "    open(i, 'wb').write(g.content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Convert HTML to Pandas DataFrame and save under new dictonary 'states'\n",
    "import pandas as pd \n",
    "\n",
    "states = {}\n",
    "\n",
    "for i in url:\n",
    "    name = i[:-5]\n",
    "    df = pd.read_html(i)[0]  # Load HTML into a Pandas DataFrame\n",
    "    df.columns = ['NCES District ID', \n",
    "                  'State District ID', \n",
    "                  'District Name', \n",
    "                  'County Name',\n",
    "                  'Street Address', \n",
    "                  'City', 'State', \n",
    "                  'ZIP', \n",
    "                  'ZIP 4-digit', \n",
    "                  'Phone', \n",
    "                  'Students',\n",
    "                  'Teachers', \n",
    "                  'Schools', \n",
    "                  'Locale Code', \n",
    "                  'Locale', \n",
    "                  'Student Teacher Ratio', \n",
    "                  'Type']  # Assign New Header Column (Remove '*' in original header)\n",
    "    clean_data = df[6:].reset_index(drop=True) # Slice DataFrame and Reset Index         \n",
    "    temp_dict = { name : clean_data}\n",
    "    states.update(temp_dict)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Before cleaning Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "      <th>13</th>\n",
       "      <th>14</th>\n",
       "      <th>15</th>\n",
       "      <th>16</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>National Center for Education Statistics</td>\n",
       "      <td>National Center for Education Statistics</td>\n",
       "      <td>National Center for Education Statistics</td>\n",
       "      <td>National Center for Education Statistics</td>\n",
       "      <td>National Center for Education Statistics</td>\n",
       "      <td>National Center for Education Statistics</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CCD public school district data 2018-2019, 201...</td>\n",
       "      <td>CCD public school district data 2018-2019, 201...</td>\n",
       "      <td>CCD public school district data 2018-2019, 201...</td>\n",
       "      <td>CCD public school district data 2018-2019, 201...</td>\n",
       "      <td>CCD public school district data 2018-2019, 201...</td>\n",
       "      <td>CCD public school district data 2018-2019, 201...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>The file contains (2122) records based on your...</td>\n",
       "      <td>The file contains (2122) records based on your...</td>\n",
       "      <td>The file contains (2122) records based on your...</td>\n",
       "      <td>The file contains (2122) records based on your...</td>\n",
       "      <td>The file contains (2122) records based on your...</td>\n",
       "      <td>The file contains (2122) records based on your...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NOTES:* denotes a column with data from 2019-2...</td>\n",
       "      <td>NOTES:* denotes a column with data from 2019-2...</td>\n",
       "      <td>NOTES:* denotes a column with data from 2019-2...</td>\n",
       "      <td>NOTES:* denotes a column with data from 2019-2...</td>\n",
       "      <td>NOTES:* denotes a column with data from 2019-2...</td>\n",
       "      <td>NOTES:* denotes a column with data from 2019-2...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NCES is not responsible for the manner in whic...</td>\n",
       "      <td>NCES is not responsible for the manner in whic...</td>\n",
       "      <td>NCES is not responsible for the manner in whic...</td>\n",
       "      <td>NCES is not responsible for the manner in whic...</td>\n",
       "      <td>NCES is not responsible for the manner in whic...</td>\n",
       "      <td>NCES is not responsible for the manner in whic...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NCES District ID</td>\n",
       "      <td>State District ID</td>\n",
       "      <td>District Name</td>\n",
       "      <td>County Name*</td>\n",
       "      <td>Street Address</td>\n",
       "      <td>City</td>\n",
       "      <td>State</td>\n",
       "      <td>ZIP</td>\n",
       "      <td>ZIP 4-digit</td>\n",
       "      <td>Phone</td>\n",
       "      <td>Students*</td>\n",
       "      <td>Teachers*</td>\n",
       "      <td>Schools</td>\n",
       "      <td>Locale Code*</td>\n",
       "      <td>Locale*</td>\n",
       "      <td>Student Teacher Ratio*</td>\n",
       "      <td>Type</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0601620</td>\n",
       "      <td>CA-1964212</td>\n",
       "      <td>ABC Unified</td>\n",
       "      <td>Los Angeles County</td>\n",
       "      <td>16700 Norwalk Blvd.</td>\n",
       "      <td>Cerritos</td>\n",
       "      <td>CA</td>\n",
       "      <td>90703</td>\n",
       "      <td>1838</td>\n",
       "      <td>(562)926-5566</td>\n",
       "      <td>20465.00000</td>\n",
       "      <td>827.40000</td>\n",
       "      <td>30.00000</td>\n",
       "      <td>21</td>\n",
       "      <td>Suburb: Large</td>\n",
       "      <td>24.70000000</td>\n",
       "      <td>Regular School District</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0602291</td>\n",
       "      <td>CA-0109926</td>\n",
       "      <td>Academia Avance Charter District</td>\n",
       "      <td>Los Angeles County</td>\n",
       "      <td>PO Box 42095</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>CA</td>\n",
       "      <td>90042</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(323)230-7270</td>\n",
       "      <td>391.00000</td>\n",
       "      <td>18.61000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>11</td>\n",
       "      <td>City: Large</td>\n",
       "      <td>21.00000000</td>\n",
       "      <td>Other Education Agency</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0602361</td>\n",
       "      <td>CA-0120097</td>\n",
       "      <td>Academia Moderna District</td>\n",
       "      <td>Los Angeles County</td>\n",
       "      <td>2410 BRd.way</td>\n",
       "      <td>Walnut Park</td>\n",
       "      <td>CA</td>\n",
       "      <td>90255</td>\n",
       "      <td>6342</td>\n",
       "      <td>(323)923-0383</td>\n",
       "      <td>465.00000</td>\n",
       "      <td>19.00000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>21</td>\n",
       "      <td>Suburb: Large</td>\n",
       "      <td>24.50000000</td>\n",
       "      <td>Other Education Agency</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0601911</td>\n",
       "      <td>CA-3631207</td>\n",
       "      <td>Academy for Academic Excellence District</td>\n",
       "      <td>San Bernardino County</td>\n",
       "      <td>17500 Mana Rd.</td>\n",
       "      <td>Apple Valley</td>\n",
       "      <td>CA</td>\n",
       "      <td>92307</td>\n",
       "      <td>2181</td>\n",
       "      <td>(760)946-5414</td>\n",
       "      <td>1447.00000</td>\n",
       "      <td>62.82000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>21</td>\n",
       "      <td>Suburb: Large</td>\n",
       "      <td>23.00000000</td>\n",
       "      <td>Other Education Agency</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  0   \\\n",
       "0           National Center for Education Statistics   \n",
       "1  CCD public school district data 2018-2019, 201...   \n",
       "2  The file contains (2122) records based on your...   \n",
       "3  NOTES:* denotes a column with data from 2019-2...   \n",
       "4  NCES is not responsible for the manner in whic...   \n",
       "5                                   NCES District ID   \n",
       "6                                            0601620   \n",
       "7                                            0602291   \n",
       "8                                            0602361   \n",
       "9                                            0601911   \n",
       "\n",
       "                                                  1   \\\n",
       "0           National Center for Education Statistics   \n",
       "1  CCD public school district data 2018-2019, 201...   \n",
       "2  The file contains (2122) records based on your...   \n",
       "3  NOTES:* denotes a column with data from 2019-2...   \n",
       "4  NCES is not responsible for the manner in whic...   \n",
       "5                                  State District ID   \n",
       "6                                         CA-1964212   \n",
       "7                                         CA-0109926   \n",
       "8                                         CA-0120097   \n",
       "9                                         CA-3631207   \n",
       "\n",
       "                                                  2   \\\n",
       "0           National Center for Education Statistics   \n",
       "1  CCD public school district data 2018-2019, 201...   \n",
       "2  The file contains (2122) records based on your...   \n",
       "3  NOTES:* denotes a column with data from 2019-2...   \n",
       "4  NCES is not responsible for the manner in whic...   \n",
       "5                                      District Name   \n",
       "6                                        ABC Unified   \n",
       "7                   Academia Avance Charter District   \n",
       "8                          Academia Moderna District   \n",
       "9           Academy for Academic Excellence District   \n",
       "\n",
       "                                                  3   \\\n",
       "0           National Center for Education Statistics   \n",
       "1  CCD public school district data 2018-2019, 201...   \n",
       "2  The file contains (2122) records based on your...   \n",
       "3  NOTES:* denotes a column with data from 2019-2...   \n",
       "4  NCES is not responsible for the manner in whic...   \n",
       "5                                       County Name*   \n",
       "6                                 Los Angeles County   \n",
       "7                                 Los Angeles County   \n",
       "8                                 Los Angeles County   \n",
       "9                              San Bernardino County   \n",
       "\n",
       "                                                  4   \\\n",
       "0           National Center for Education Statistics   \n",
       "1  CCD public school district data 2018-2019, 201...   \n",
       "2  The file contains (2122) records based on your...   \n",
       "3  NOTES:* denotes a column with data from 2019-2...   \n",
       "4  NCES is not responsible for the manner in whic...   \n",
       "5                                     Street Address   \n",
       "6                                16700 Norwalk Blvd.   \n",
       "7                                       PO Box 42095   \n",
       "8                                       2410 BRd.way   \n",
       "9                                     17500 Mana Rd.   \n",
       "\n",
       "                                                  5      6      7   \\\n",
       "0           National Center for Education Statistics    NaN    NaN   \n",
       "1  CCD public school district data 2018-2019, 201...    NaN    NaN   \n",
       "2  The file contains (2122) records based on your...    NaN    NaN   \n",
       "3  NOTES:* denotes a column with data from 2019-2...    NaN    NaN   \n",
       "4  NCES is not responsible for the manner in whic...    NaN    NaN   \n",
       "5                                               City  State    ZIP   \n",
       "6                                           Cerritos     CA  90703   \n",
       "7                                        Los Angeles     CA  90042   \n",
       "8                                        Walnut Park     CA  90255   \n",
       "9                                       Apple Valley     CA  92307   \n",
       "\n",
       "            8              9            10         11        12            13  \\\n",
       "0          NaN            NaN          NaN        NaN       NaN           NaN   \n",
       "1          NaN            NaN          NaN        NaN       NaN           NaN   \n",
       "2          NaN            NaN          NaN        NaN       NaN           NaN   \n",
       "3          NaN            NaN          NaN        NaN       NaN           NaN   \n",
       "4          NaN            NaN          NaN        NaN       NaN           NaN   \n",
       "5  ZIP 4-digit          Phone    Students*  Teachers*   Schools  Locale Code*   \n",
       "6         1838  (562)926-5566  20465.00000  827.40000  30.00000            21   \n",
       "7          NaN  (323)230-7270    391.00000   18.61000   1.00000            11   \n",
       "8         6342  (323)923-0383    465.00000   19.00000   1.00000            21   \n",
       "9         2181  (760)946-5414   1447.00000   62.82000   1.00000            21   \n",
       "\n",
       "              14                      15                       16  \n",
       "0            NaN                     NaN                      NaN  \n",
       "1            NaN                     NaN                      NaN  \n",
       "2            NaN                     NaN                      NaN  \n",
       "3            NaN                     NaN                      NaN  \n",
       "4            NaN                     NaN                      NaN  \n",
       "5        Locale*  Student Teacher Ratio*                     Type  \n",
       "6  Suburb: Large             24.70000000  Regular School District  \n",
       "7    City: Large             21.00000000   Other Education Agency  \n",
       "8  Suburb: Large             24.50000000   Other Education Agency  \n",
       "9  Suburb: Large             23.00000000   Other Education Agency  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "california = pd.read_html('California.html')[0]\n",
    "california.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NCES District ID</th>\n",
       "      <th>State District ID</th>\n",
       "      <th>District Name</th>\n",
       "      <th>County Name</th>\n",
       "      <th>Street Address</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>ZIP</th>\n",
       "      <th>ZIP 4-digit</th>\n",
       "      <th>Phone</th>\n",
       "      <th>Students</th>\n",
       "      <th>Teachers</th>\n",
       "      <th>Schools</th>\n",
       "      <th>Locale Code</th>\n",
       "      <th>Locale</th>\n",
       "      <th>Student Teacher Ratio</th>\n",
       "      <th>Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0601620</td>\n",
       "      <td>CA-1964212</td>\n",
       "      <td>ABC Unified</td>\n",
       "      <td>Los Angeles County</td>\n",
       "      <td>16700 Norwalk Blvd.</td>\n",
       "      <td>Cerritos</td>\n",
       "      <td>CA</td>\n",
       "      <td>90703</td>\n",
       "      <td>1838</td>\n",
       "      <td>(562)926-5566</td>\n",
       "      <td>20465.00000</td>\n",
       "      <td>827.40000</td>\n",
       "      <td>30.00000</td>\n",
       "      <td>21</td>\n",
       "      <td>Suburb: Large</td>\n",
       "      <td>24.70000000</td>\n",
       "      <td>Regular School District</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0602291</td>\n",
       "      <td>CA-0109926</td>\n",
       "      <td>Academia Avance Charter District</td>\n",
       "      <td>Los Angeles County</td>\n",
       "      <td>PO Box 42095</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>CA</td>\n",
       "      <td>90042</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(323)230-7270</td>\n",
       "      <td>391.00000</td>\n",
       "      <td>18.61000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>11</td>\n",
       "      <td>City: Large</td>\n",
       "      <td>21.00000000</td>\n",
       "      <td>Other Education Agency</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0602361</td>\n",
       "      <td>CA-0120097</td>\n",
       "      <td>Academia Moderna District</td>\n",
       "      <td>Los Angeles County</td>\n",
       "      <td>2410 BRd.way</td>\n",
       "      <td>Walnut Park</td>\n",
       "      <td>CA</td>\n",
       "      <td>90255</td>\n",
       "      <td>6342</td>\n",
       "      <td>(323)923-0383</td>\n",
       "      <td>465.00000</td>\n",
       "      <td>19.00000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>21</td>\n",
       "      <td>Suburb: Large</td>\n",
       "      <td>24.50000000</td>\n",
       "      <td>Other Education Agency</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0601911</td>\n",
       "      <td>CA-3631207</td>\n",
       "      <td>Academy for Academic Excellence District</td>\n",
       "      <td>San Bernardino County</td>\n",
       "      <td>17500 Mana Rd.</td>\n",
       "      <td>Apple Valley</td>\n",
       "      <td>CA</td>\n",
       "      <td>92307</td>\n",
       "      <td>2181</td>\n",
       "      <td>(760)946-5414</td>\n",
       "      <td>1447.00000</td>\n",
       "      <td>62.82000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>21</td>\n",
       "      <td>Suburb: Large</td>\n",
       "      <td>23.00000000</td>\n",
       "      <td>Other Education Agency</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0602503</td>\n",
       "      <td>CA-0139055</td>\n",
       "      <td>Academy of Media Arts District</td>\n",
       "      <td>Los Angeles County</td>\n",
       "      <td>Po Box 7832</td>\n",
       "      <td>Beverly Hills</td>\n",
       "      <td>CA</td>\n",
       "      <td>90212</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(424)253-8984</td>\n",
       "      <td>†</td>\n",
       "      <td>†</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>11</td>\n",
       "      <td>City: Large</td>\n",
       "      <td>†</td>\n",
       "      <td>Other Education Agency</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0601650</td>\n",
       "      <td>CA-0761630</td>\n",
       "      <td>Acalanes Union High</td>\n",
       "      <td>Contra Costa County</td>\n",
       "      <td>1212 Pleasant Hill Rd.</td>\n",
       "      <td>Lafayette</td>\n",
       "      <td>CA</td>\n",
       "      <td>94549</td>\n",
       "      <td>2623</td>\n",
       "      <td>(925)280-3900</td>\n",
       "      <td>5683.00000</td>\n",
       "      <td>276.50000</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>21</td>\n",
       "      <td>Suburb: Large</td>\n",
       "      <td>20.60000000</td>\n",
       "      <td>Regular School District</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0601877</td>\n",
       "      <td>CA-2330454</td>\n",
       "      <td>Accelerated Achievement Academy District</td>\n",
       "      <td>Mendocino County</td>\n",
       "      <td>1031 N. State St.</td>\n",
       "      <td>Ukiah</td>\n",
       "      <td>CA</td>\n",
       "      <td>95482</td>\n",
       "      <td>3413</td>\n",
       "      <td>(707)463-7080</td>\n",
       "      <td>146.00000</td>\n",
       "      <td>7.00000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>33</td>\n",
       "      <td>Town: Remote</td>\n",
       "      <td>20.90000000</td>\n",
       "      <td>Other Education Agency</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0601471</td>\n",
       "      <td>CA-0100743</td>\n",
       "      <td>Accelerated Charter Elementary District</td>\n",
       "      <td>Los Angeles County</td>\n",
       "      <td>3914 S. Main St.</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>CA</td>\n",
       "      <td>90037</td>\n",
       "      <td>1253</td>\n",
       "      <td>(323)846-6694</td>\n",
       "      <td>501.00000</td>\n",
       "      <td>20.00000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>11</td>\n",
       "      <td>City: Large</td>\n",
       "      <td>25.10000000</td>\n",
       "      <td>Other Education Agency</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0601732</td>\n",
       "      <td>CA-6112536</td>\n",
       "      <td>Accelerated District</td>\n",
       "      <td>Los Angeles County</td>\n",
       "      <td>4000 S. Main St.</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>CA</td>\n",
       "      <td>90037</td>\n",
       "      <td>1022</td>\n",
       "      <td>(323)235-6343</td>\n",
       "      <td>787.00000</td>\n",
       "      <td>34.00000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>11</td>\n",
       "      <td>City: Large</td>\n",
       "      <td>23.10000000</td>\n",
       "      <td>Other Education Agency</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0601561</td>\n",
       "      <td>CA-0125617</td>\n",
       "      <td>ACE Charter High District</td>\n",
       "      <td>Santa Clara County</td>\n",
       "      <td>1776 Edal Park Dr.</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>CA</td>\n",
       "      <td>95133</td>\n",
       "      <td>1703</td>\n",
       "      <td>(408)251-1362</td>\n",
       "      <td>363.00000</td>\n",
       "      <td>14.03000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>11</td>\n",
       "      <td>City: Large</td>\n",
       "      <td>25.90000000</td>\n",
       "      <td>Other Education Agency</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  NCES District ID State District ID  \\\n",
       "0          0601620        CA-1964212   \n",
       "1          0602291        CA-0109926   \n",
       "2          0602361        CA-0120097   \n",
       "3          0601911        CA-3631207   \n",
       "4          0602503        CA-0139055   \n",
       "5          0601650        CA-0761630   \n",
       "6          0601877        CA-2330454   \n",
       "7          0601471        CA-0100743   \n",
       "8          0601732        CA-6112536   \n",
       "9          0601561        CA-0125617   \n",
       "\n",
       "                              District Name            County Name  \\\n",
       "0                               ABC Unified     Los Angeles County   \n",
       "1          Academia Avance Charter District     Los Angeles County   \n",
       "2                 Academia Moderna District     Los Angeles County   \n",
       "3  Academy for Academic Excellence District  San Bernardino County   \n",
       "4            Academy of Media Arts District     Los Angeles County   \n",
       "5                       Acalanes Union High    Contra Costa County   \n",
       "6  Accelerated Achievement Academy District       Mendocino County   \n",
       "7   Accelerated Charter Elementary District     Los Angeles County   \n",
       "8                      Accelerated District     Los Angeles County   \n",
       "9                 ACE Charter High District     Santa Clara County   \n",
       "\n",
       "           Street Address           City State    ZIP ZIP 4-digit  \\\n",
       "0     16700 Norwalk Blvd.       Cerritos    CA  90703        1838   \n",
       "1            PO Box 42095    Los Angeles    CA  90042         NaN   \n",
       "2            2410 BRd.way    Walnut Park    CA  90255        6342   \n",
       "3          17500 Mana Rd.   Apple Valley    CA  92307        2181   \n",
       "4             Po Box 7832  Beverly Hills    CA  90212         NaN   \n",
       "5  1212 Pleasant Hill Rd.      Lafayette    CA  94549        2623   \n",
       "6       1031 N. State St.          Ukiah    CA  95482        3413   \n",
       "7        3914 S. Main St.    Los Angeles    CA  90037        1253   \n",
       "8        4000 S. Main St.    Los Angeles    CA  90037        1022   \n",
       "9      1776 Edal Park Dr.       San Jose    CA  95133        1703   \n",
       "\n",
       "           Phone     Students   Teachers   Schools Locale Code         Locale  \\\n",
       "0  (562)926-5566  20465.00000  827.40000  30.00000          21  Suburb: Large   \n",
       "1  (323)230-7270    391.00000   18.61000   1.00000          11    City: Large   \n",
       "2  (323)923-0383    465.00000   19.00000   1.00000          21  Suburb: Large   \n",
       "3  (760)946-5414   1447.00000   62.82000   1.00000          21  Suburb: Large   \n",
       "4  (424)253-8984            †          †   1.00000          11    City: Large   \n",
       "5  (925)280-3900   5683.00000  276.50000   5.00000          21  Suburb: Large   \n",
       "6  (707)463-7080    146.00000    7.00000   1.00000          33   Town: Remote   \n",
       "7  (323)846-6694    501.00000   20.00000   1.00000          11    City: Large   \n",
       "8  (323)235-6343    787.00000   34.00000   1.00000          11    City: Large   \n",
       "9  (408)251-1362    363.00000   14.03000   1.00000          11    City: Large   \n",
       "\n",
       "  Student Teacher Ratio                     Type  \n",
       "0           24.70000000  Regular School District  \n",
       "1           21.00000000   Other Education Agency  \n",
       "2           24.50000000   Other Education Agency  \n",
       "3           23.00000000   Other Education Agency  \n",
       "4                     †   Other Education Agency  \n",
       "5           20.60000000  Regular School District  \n",
       "6           20.90000000   Other Education Agency  \n",
       "7           25.10000000   Other Education Agency  \n",
       "8           23.10000000   Other Education Agency  \n",
       "9           25.90000000   Other Education Agency  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "california = states['California']\n",
    "california.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "alabama['Students'] = alabama['Students'].replace( '–' , 0.000)\n",
    "alabama['Students'] = alabama['Students'].replace('†', 0.000)\n",
    "alabama['Student Teacher Ratio'] = alabama['Student Teacher Ratio'].replace('†', 0.000)\n",
    "alabama['Teachers'] = alabama['Teachers'].replace('†', 0.000)\n",
    "alabama['Locale Code'] = alabama['Locale Code'].replace('†', 0.000)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NCES District ID</th>\n",
       "      <th>State District ID</th>\n",
       "      <th>District Name</th>\n",
       "      <th>County Name</th>\n",
       "      <th>Street Address</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>ZIP</th>\n",
       "      <th>ZIP 4-digit</th>\n",
       "      <th>Phone</th>\n",
       "      <th>Students</th>\n",
       "      <th>Teachers</th>\n",
       "      <th>Schools</th>\n",
       "      <th>Locale Code</th>\n",
       "      <th>Locale</th>\n",
       "      <th>Student Teacher Ratio</th>\n",
       "      <th>Type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0100197</td>\n",
       "      <td>AL-800</td>\n",
       "      <td>Acceleration Day and Evening Academy</td>\n",
       "      <td>Mobile County</td>\n",
       "      <td>3100 Cottage Hill Road Bldg 3</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>AL</td>\n",
       "      <td>36606</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(251)301-0050</td>\n",
       "      <td>300.00000</td>\n",
       "      <td>10.00000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>12</td>\n",
       "      <td>City: Midsize</td>\n",
       "      <td>30.00000000</td>\n",
       "      <td>Other Education Agency</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0100009</td>\n",
       "      <td>AL-600</td>\n",
       "      <td>Al Inst Deaf And Blind</td>\n",
       "      <td>Talladega County</td>\n",
       "      <td>PO Box 698</td>\n",
       "      <td>Talladega</td>\n",
       "      <td>AL</td>\n",
       "      <td>35161</td>\n",
       "      <td>0698</td>\n",
       "      <td>(256)761-3200</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>32</td>\n",
       "      <td>Town: Distant</td>\n",
       "      <td>0</td>\n",
       "      <td>Regular School District</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0100183</td>\n",
       "      <td>AL-709</td>\n",
       "      <td>Alabama Clinical Schools</td>\n",
       "      <td>Jefferson County</td>\n",
       "      <td>PO Box 100968</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>AL</td>\n",
       "      <td>35210</td>\n",
       "      <td>4308</td>\n",
       "      <td>(205)836-9923</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>12</td>\n",
       "      <td>City: Midsize</td>\n",
       "      <td>0</td>\n",
       "      <td>Regional District</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0100018</td>\n",
       "      <td>AL-207</td>\n",
       "      <td>Alabama School of Fine Arts</td>\n",
       "      <td>Jefferson County</td>\n",
       "      <td>1800 Rev Abraham Woods Jr Blvd</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>AL</td>\n",
       "      <td>35203</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(205)252-9241</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>12</td>\n",
       "      <td>City: Midsize</td>\n",
       "      <td>0</td>\n",
       "      <td>Regular School District</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0100002</td>\n",
       "      <td>AL-210</td>\n",
       "      <td>Alabama Youth Services</td>\n",
       "      <td>Jefferson County</td>\n",
       "      <td>PO Box 66</td>\n",
       "      <td>Mount Meigs</td>\n",
       "      <td>AL</td>\n",
       "      <td>36057</td>\n",
       "      <td>0066</td>\n",
       "      <td>(334)215-3859</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>21</td>\n",
       "      <td>Suburb: Large</td>\n",
       "      <td>0</td>\n",
       "      <td>Regular School District</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0100190</td>\n",
       "      <td>AL-103</td>\n",
       "      <td>Alabaster City</td>\n",
       "      <td>Shelby County</td>\n",
       "      <td>10111 Highway 119</td>\n",
       "      <td>Alabaster</td>\n",
       "      <td>AL</td>\n",
       "      <td>35007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(205)663-8400</td>\n",
       "      <td>6184.00000</td>\n",
       "      <td>341.85000</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>21</td>\n",
       "      <td>Suburb: Large</td>\n",
       "      <td>18.10000000</td>\n",
       "      <td>Regular School District</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0100005</td>\n",
       "      <td>AL-101</td>\n",
       "      <td>Albertville City</td>\n",
       "      <td>Marshall County</td>\n",
       "      <td>107 W Main St</td>\n",
       "      <td>Albertville</td>\n",
       "      <td>AL</td>\n",
       "      <td>35950</td>\n",
       "      <td>1625</td>\n",
       "      <td>(256)891-1183</td>\n",
       "      <td>5636.00000</td>\n",
       "      <td>295.50000</td>\n",
       "      <td>6.00000</td>\n",
       "      <td>32</td>\n",
       "      <td>Town: Distant</td>\n",
       "      <td>19.10000000</td>\n",
       "      <td>Regular School District</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0100030</td>\n",
       "      <td>AL-102</td>\n",
       "      <td>Alexander City</td>\n",
       "      <td>Tallapoosa County</td>\n",
       "      <td>375 Lee St</td>\n",
       "      <td>Alexander City</td>\n",
       "      <td>AL</td>\n",
       "      <td>35010</td>\n",
       "      <td>2708</td>\n",
       "      <td>(256)234-5074</td>\n",
       "      <td>3065.00000</td>\n",
       "      <td>176.52000</td>\n",
       "      <td>5.00000</td>\n",
       "      <td>32</td>\n",
       "      <td>Town: Distant</td>\n",
       "      <td>17.40000000</td>\n",
       "      <td>Regular School District</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0100060</td>\n",
       "      <td>AL-104</td>\n",
       "      <td>Andalusia City</td>\n",
       "      <td>Covington County</td>\n",
       "      <td>1201 C C Baker Avenue</td>\n",
       "      <td>Andalusia</td>\n",
       "      <td>AL</td>\n",
       "      <td>36421</td>\n",
       "      <td>2101</td>\n",
       "      <td>(334)222-3186</td>\n",
       "      <td>1794.00000</td>\n",
       "      <td>98.24000</td>\n",
       "      <td>3.00000</td>\n",
       "      <td>33</td>\n",
       "      <td>Town: Remote</td>\n",
       "      <td>18.30000000</td>\n",
       "      <td>Regular School District</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0100090</td>\n",
       "      <td>AL-105</td>\n",
       "      <td>Anniston City</td>\n",
       "      <td>Calhoun County</td>\n",
       "      <td>PO Box 1500</td>\n",
       "      <td>Anniston</td>\n",
       "      <td>AL</td>\n",
       "      <td>36202</td>\n",
       "      <td>1500</td>\n",
       "      <td>(256)231-5000</td>\n",
       "      <td>1947.00000</td>\n",
       "      <td>110.50000</td>\n",
       "      <td>7.00000</td>\n",
       "      <td>13</td>\n",
       "      <td>City: Small</td>\n",
       "      <td>17.60000000</td>\n",
       "      <td>Regular School District</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0100100</td>\n",
       "      <td>AL-106</td>\n",
       "      <td>Arab City</td>\n",
       "      <td>Marshall County</td>\n",
       "      <td>750 Arabian Dr NE</td>\n",
       "      <td>Arab</td>\n",
       "      <td>AL</td>\n",
       "      <td>35016</td>\n",
       "      <td>1161</td>\n",
       "      <td>(256)586-6011</td>\n",
       "      <td>2632.00000</td>\n",
       "      <td>160.90000</td>\n",
       "      <td>4.00000</td>\n",
       "      <td>41</td>\n",
       "      <td>Rural: Fringe</td>\n",
       "      <td>16.40000000</td>\n",
       "      <td>Regular School District</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0100120</td>\n",
       "      <td>AL-107</td>\n",
       "      <td>Athens City</td>\n",
       "      <td>Limestone County</td>\n",
       "      <td>455 US Highway 31 North</td>\n",
       "      <td>Athens</td>\n",
       "      <td>AL</td>\n",
       "      <td>35611</td>\n",
       "      <td>2653</td>\n",
       "      <td>(256)233-6600</td>\n",
       "      <td>4284.00000</td>\n",
       "      <td>213.26000</td>\n",
       "      <td>8.00000</td>\n",
       "      <td>31</td>\n",
       "      <td>Town: Fringe</td>\n",
       "      <td>20.10000000</td>\n",
       "      <td>Regular School District</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0100180</td>\n",
       "      <td>AL-109</td>\n",
       "      <td>Attalla City</td>\n",
       "      <td>Etowah County</td>\n",
       "      <td>101 Case Ave</td>\n",
       "      <td>Attalla</td>\n",
       "      <td>AL</td>\n",
       "      <td>35954</td>\n",
       "      <td>3404</td>\n",
       "      <td>(256)459-7017</td>\n",
       "      <td>1526.00000</td>\n",
       "      <td>94.00000</td>\n",
       "      <td>3.00000</td>\n",
       "      <td>23</td>\n",
       "      <td>Suburb: Small</td>\n",
       "      <td>16.20000000</td>\n",
       "      <td>Regular School District</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>0100210</td>\n",
       "      <td>AL-110</td>\n",
       "      <td>Auburn City</td>\n",
       "      <td>Lee County</td>\n",
       "      <td>PO Box 3270</td>\n",
       "      <td>Auburn</td>\n",
       "      <td>AL</td>\n",
       "      <td>36831</td>\n",
       "      <td>3270</td>\n",
       "      <td>(334)887-2100</td>\n",
       "      <td>8738.00000</td>\n",
       "      <td>499.00000</td>\n",
       "      <td>13.00000</td>\n",
       "      <td>13</td>\n",
       "      <td>City: Small</td>\n",
       "      <td>17.50000000</td>\n",
       "      <td>Regular School District</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>0100240</td>\n",
       "      <td>AL-001</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>153 W 4th St</td>\n",
       "      <td>Prattville</td>\n",
       "      <td>AL</td>\n",
       "      <td>36067</td>\n",
       "      <td>3011</td>\n",
       "      <td>(334)365-5706</td>\n",
       "      <td>9094.00000</td>\n",
       "      <td>490.00000</td>\n",
       "      <td>15.00000</td>\n",
       "      <td>41</td>\n",
       "      <td>Rural: Fringe</td>\n",
       "      <td>18.60000000</td>\n",
       "      <td>Regular School District</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>0100270</td>\n",
       "      <td>AL-002</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>2600 North Hand Ave</td>\n",
       "      <td>Bay Minette</td>\n",
       "      <td>AL</td>\n",
       "      <td>36507</td>\n",
       "      <td>4180</td>\n",
       "      <td>(251)937-0308</td>\n",
       "      <td>32267.00000</td>\n",
       "      <td>1790.20000</td>\n",
       "      <td>44.00000</td>\n",
       "      <td>41</td>\n",
       "      <td>Rural: Fringe</td>\n",
       "      <td>18.00000000</td>\n",
       "      <td>Regular School District</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>0100300</td>\n",
       "      <td>AL-003</td>\n",
       "      <td>Barbour County</td>\n",
       "      <td>Barbour County</td>\n",
       "      <td>PO Box 429</td>\n",
       "      <td>Clayton</td>\n",
       "      <td>AL</td>\n",
       "      <td>36016</td>\n",
       "      <td>0429</td>\n",
       "      <td>(334)367-8280</td>\n",
       "      <td>755.00000</td>\n",
       "      <td>45.00000</td>\n",
       "      <td>3.00000</td>\n",
       "      <td>43</td>\n",
       "      <td>Rural: Remote</td>\n",
       "      <td>16.80000000</td>\n",
       "      <td>Regular School District</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>0100037</td>\n",
       "      <td>AL-666</td>\n",
       "      <td>BayPointe Residential Services</td>\n",
       "      <td>Mobile County</td>\n",
       "      <td>5800 Southland Drive</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>AL</td>\n",
       "      <td>36693</td>\n",
       "      <td>3316</td>\n",
       "      <td>(334)450-2223</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>12</td>\n",
       "      <td>City: Midsize</td>\n",
       "      <td>0</td>\n",
       "      <td>Regional District</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>0100330</td>\n",
       "      <td>AL-113</td>\n",
       "      <td>Bessemer City</td>\n",
       "      <td>Jefferson County</td>\n",
       "      <td>PO Box 1230</td>\n",
       "      <td>Bessemer</td>\n",
       "      <td>AL</td>\n",
       "      <td>35021</td>\n",
       "      <td>1230</td>\n",
       "      <td>(205)432-3000</td>\n",
       "      <td>3618.00000</td>\n",
       "      <td>184.50000</td>\n",
       "      <td>9.00000</td>\n",
       "      <td>21</td>\n",
       "      <td>Suburb: Large</td>\n",
       "      <td>19.60000000</td>\n",
       "      <td>Regular School District</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>0100360</td>\n",
       "      <td>AL-004</td>\n",
       "      <td>Bibb County</td>\n",
       "      <td>Bibb County</td>\n",
       "      <td>721 Walnut Street</td>\n",
       "      <td>Centreville</td>\n",
       "      <td>AL</td>\n",
       "      <td>35042</td>\n",
       "      <td>2277</td>\n",
       "      <td>(205)926-9881</td>\n",
       "      <td>3236.00000</td>\n",
       "      <td>183.25000</td>\n",
       "      <td>9.00000</td>\n",
       "      <td>32</td>\n",
       "      <td>Town: Distant</td>\n",
       "      <td>17.70000000</td>\n",
       "      <td>Regular School District</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   NCES District ID State District ID                         District Name  \\\n",
       "0           0100197            AL-800  Acceleration Day and Evening Academy   \n",
       "1           0100009            AL-600                Al Inst Deaf And Blind   \n",
       "2           0100183            AL-709              Alabama Clinical Schools   \n",
       "3           0100018            AL-207           Alabama School of Fine Arts   \n",
       "4           0100002            AL-210                Alabama Youth Services   \n",
       "5           0100190            AL-103                        Alabaster City   \n",
       "6           0100005            AL-101                      Albertville City   \n",
       "7           0100030            AL-102                        Alexander City   \n",
       "8           0100060            AL-104                        Andalusia City   \n",
       "9           0100090            AL-105                         Anniston City   \n",
       "10          0100100            AL-106                             Arab City   \n",
       "11          0100120            AL-107                           Athens City   \n",
       "12          0100180            AL-109                          Attalla City   \n",
       "13          0100210            AL-110                           Auburn City   \n",
       "14          0100240            AL-001                        Autauga County   \n",
       "15          0100270            AL-002                        Baldwin County   \n",
       "16          0100300            AL-003                        Barbour County   \n",
       "17          0100037            AL-666        BayPointe Residential Services   \n",
       "18          0100330            AL-113                         Bessemer City   \n",
       "19          0100360            AL-004                           Bibb County   \n",
       "\n",
       "          County Name                  Street Address            City State  \\\n",
       "0       Mobile County   3100 Cottage Hill Road Bldg 3          Mobile    AL   \n",
       "1    Talladega County                      PO Box 698       Talladega    AL   \n",
       "2    Jefferson County                   PO Box 100968      Birmingham    AL   \n",
       "3    Jefferson County  1800 Rev Abraham Woods Jr Blvd      Birmingham    AL   \n",
       "4    Jefferson County                       PO Box 66     Mount Meigs    AL   \n",
       "5       Shelby County               10111 Highway 119       Alabaster    AL   \n",
       "6     Marshall County                   107 W Main St     Albertville    AL   \n",
       "7   Tallapoosa County                      375 Lee St  Alexander City    AL   \n",
       "8    Covington County           1201 C C Baker Avenue       Andalusia    AL   \n",
       "9      Calhoun County                     PO Box 1500        Anniston    AL   \n",
       "10    Marshall County               750 Arabian Dr NE            Arab    AL   \n",
       "11   Limestone County         455 US Highway 31 North          Athens    AL   \n",
       "12      Etowah County                    101 Case Ave         Attalla    AL   \n",
       "13         Lee County                     PO Box 3270          Auburn    AL   \n",
       "14     Autauga County                    153 W 4th St      Prattville    AL   \n",
       "15     Baldwin County             2600 North Hand Ave     Bay Minette    AL   \n",
       "16     Barbour County                      PO Box 429         Clayton    AL   \n",
       "17      Mobile County            5800 Southland Drive          Mobile    AL   \n",
       "18   Jefferson County                     PO Box 1230        Bessemer    AL   \n",
       "19        Bibb County               721 Walnut Street     Centreville    AL   \n",
       "\n",
       "      ZIP ZIP 4-digit          Phone     Students    Teachers   Schools  \\\n",
       "0   36606         NaN  (251)301-0050    300.00000    10.00000   1.00000   \n",
       "1   35161        0698  (256)761-3200            0     0.00000   5.00000   \n",
       "2   35210        4308  (205)836-9923            0     0.00000   1.00000   \n",
       "3   35203         NaN  (205)252-9241            0     0.00000   0.00000   \n",
       "4   36057        0066  (334)215-3859            0     0.00000   0.00000   \n",
       "5   35007         NaN  (205)663-8400   6184.00000   341.85000   5.00000   \n",
       "6   35950        1625  (256)891-1183   5636.00000   295.50000   6.00000   \n",
       "7   35010        2708  (256)234-5074   3065.00000   176.52000   5.00000   \n",
       "8   36421        2101  (334)222-3186   1794.00000    98.24000   3.00000   \n",
       "9   36202        1500  (256)231-5000   1947.00000   110.50000   7.00000   \n",
       "10  35016        1161  (256)586-6011   2632.00000   160.90000   4.00000   \n",
       "11  35611        2653  (256)233-6600   4284.00000   213.26000   8.00000   \n",
       "12  35954        3404  (256)459-7017   1526.00000    94.00000   3.00000   \n",
       "13  36831        3270  (334)887-2100   8738.00000   499.00000  13.00000   \n",
       "14  36067        3011  (334)365-5706   9094.00000   490.00000  15.00000   \n",
       "15  36507        4180  (251)937-0308  32267.00000  1790.20000  44.00000   \n",
       "16  36016        0429  (334)367-8280    755.00000    45.00000   3.00000   \n",
       "17  36693        3316  (334)450-2223            0     0.00000   1.00000   \n",
       "18  35021        1230  (205)432-3000   3618.00000   184.50000   9.00000   \n",
       "19  35042        2277  (205)926-9881   3236.00000   183.25000   9.00000   \n",
       "\n",
       "   Locale Code         Locale Student Teacher Ratio                     Type  \n",
       "0           12  City: Midsize           30.00000000   Other Education Agency  \n",
       "1           32  Town: Distant                     0  Regular School District  \n",
       "2           12  City: Midsize                     0        Regional District  \n",
       "3           12  City: Midsize                     0  Regular School District  \n",
       "4           21  Suburb: Large                     0  Regular School District  \n",
       "5           21  Suburb: Large           18.10000000  Regular School District  \n",
       "6           32  Town: Distant           19.10000000  Regular School District  \n",
       "7           32  Town: Distant           17.40000000  Regular School District  \n",
       "8           33   Town: Remote           18.30000000  Regular School District  \n",
       "9           13    City: Small           17.60000000  Regular School District  \n",
       "10          41  Rural: Fringe           16.40000000  Regular School District  \n",
       "11          31   Town: Fringe           20.10000000  Regular School District  \n",
       "12          23  Suburb: Small           16.20000000  Regular School District  \n",
       "13          13    City: Small           17.50000000  Regular School District  \n",
       "14          41  Rural: Fringe           18.60000000  Regular School District  \n",
       "15          41  Rural: Fringe           18.00000000  Regular School District  \n",
       "16          43  Rural: Remote           16.80000000  Regular School District  \n",
       "17          12  City: Midsize                     0        Regional District  \n",
       "18          21  Suburb: Large           19.60000000  Regular School District  \n",
       "19          32  Town: Distant           17.70000000  Regular School District  "
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alabama.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "'Students*'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pandas/core/indexes/base.py\u001b[0m in \u001b[0;36mget_loc\u001b[0;34m(self, key, method, tolerance)\u001b[0m\n\u001b[1;32m   2645\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2646\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2647\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mKeyError\u001b[0m: 'Students*'",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-86-496fe2ffbee7>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0malabama\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'ZIP 4-digit'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfillna\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'NULL'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0minplace\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 6\u001b[0;31m \u001b[0malabama\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Students*'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0malabama\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Students*'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreplace\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'–'\u001b[0m \u001b[0;34m,\u001b[0m \u001b[0;34m'NULL'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      7\u001b[0m \u001b[0;31m#albama['Students*'] = albama['Students*'].replace('\\u+2020', 'NULL')\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      8\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36m__getitem__\u001b[0;34m(self, key)\u001b[0m\n\u001b[1;32m   2798\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mnlevels\u001b[0m \u001b[0;34m>\u001b[0m \u001b[0;36m1\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2799\u001b[0m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_getitem_multilevel\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2800\u001b[0;31m             \u001b[0mindexer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2801\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mis_integer\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mindexer\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2802\u001b[0m                 \u001b[0mindexer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0mindexer\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pandas/core/indexes/base.py\u001b[0m in \u001b[0;36mget_loc\u001b[0;34m(self, key, method, tolerance)\u001b[0m\n\u001b[1;32m   2646\u001b[0m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2647\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2648\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_maybe_cast_indexer\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2649\u001b[0m         \u001b[0mindexer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_indexer\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmethod\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtolerance\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mtolerance\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2650\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mindexer\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mndim\u001b[0m \u001b[0;34m>\u001b[0m \u001b[0;36m1\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mindexer\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msize\u001b[0m \u001b[0;34m>\u001b[0m \u001b[0;36m1\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mKeyError\u001b[0m: 'Students*'"
     ]
    }
   ],
   "source": [
    "alabama = states['Alabama'] \n",
    "#alabama.info()\n",
    "#alabama.head(10)\n",
    "\n",
    "alabama['ZIP 4-digit'].fillna('NULL', inplace = True)\n",
    "alabama['Students*'] = alabama['Students*'].replace('–' , 'NULL')\n",
    "#albama['Students*'] = albama['Students*'].replace('\\u+2020', 'NULL')\n",
    "\n",
    "print(albama.columns)\n",
    "alabama.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "\n",
    "engine = create_engine(\"mysql+pymysql://{user}:{pw}@localhost/{db}\"\n",
    "                       .format(user=\"root\",\n",
    "                               pw=\"flow2020\",\n",
    "                               db=\"cfes_schema\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [
    {
     "ename": "OperationalError",
     "evalue": "(pymysql.err.OperationalError) (1054, \"Unknown column 'index' in 'field list'\")\n[SQL: INSERT INTO alabama2 (`index`, `NCES District ID`, `State District ID`, `District Name`, `County Name`, `Street Address`, `City`, `State`, `ZIP`, `ZIP 4-digit`, `Phone`, `Students`, `Teachers`, `Schools`, `Locale Code`, `Locale`, `Student Teacher Ratio`, `Type`) VALUES (%(index)s, %(NCES District ID)s, %(State District ID)s, %(District Name)s, %(County Name)s, %(Street Address)s, %(City)s, %(State)s, %(ZIP)s, %(ZIP 4-digit)s, %(Phone)s, %(Students)s, %(Teachers)s, %(Schools)s, %(Locale Code)s, %(Locale)s, %(Student Teacher Ratio)s, %(Type)s)]\n[parameters: ({'index': 0, 'NCES District ID': '0100197', 'State District ID': 'AL-800', 'District Name': 'Acceleration Day and Evening Academy', 'County Name': 'Mobile County', 'Street Address': '3100 Cottage Hill Road Bldg 3', 'City': 'Mobile', 'State': 'AL', 'ZIP': '36606', 'ZIP 4-digit': None, 'Phone': '(251)301-0050', 'Students': '300.00000', 'Teachers': '10.00000', 'Schools': '1.00000', 'Locale Code': '12', 'Locale': 'City: Midsize', 'Student Teacher Ratio': '30.00000000', 'Type': 'Other Education Agency'}, {'index': 1, 'NCES District ID': '0100009', 'State District ID': 'AL-600', 'District Name': 'Al Inst Deaf And Blind', 'County Name': 'Talladega County', 'Street Address': 'PO Box 698', 'City': 'Talladega', 'State': 'AL', 'ZIP': '35161', 'ZIP 4-digit': '0698', 'Phone': '(256)761-3200', 'Students': 0.0, 'Teachers': '0.00000', 'Schools': '5.00000', 'Locale Code': '32', 'Locale': 'Town: Distant', 'Student Teacher Ratio': 0.0, 'Type': 'Regular School District'}, {'index': 2, 'NCES District ID': '0100183', 'State District ID': 'AL-709', 'District Name': 'Alabama Clinical Schools', 'County Name': 'Jefferson County', 'Street Address': 'PO Box 100968', 'City': 'Birmingham', 'State': 'AL', 'ZIP': '35210', 'ZIP 4-digit': '4308', 'Phone': '(205)836-9923', 'Students': 0.0, 'Teachers': '0.00000', 'Schools': '1.00000', 'Locale Code': '12', 'Locale': 'City: Midsize', 'Student Teacher Ratio': 0.0, 'Type': 'Regional District'}, {'index': 3, 'NCES District ID': '0100018', 'State District ID': 'AL-207', 'District Name': 'Alabama School of Fine Arts', 'County Name': 'Jefferson County', 'Street Address': '1800 Rev Abraham Woods Jr Blvd', 'City': 'Birmingham', 'State': 'AL', 'ZIP': '35203', 'ZIP 4-digit': None, 'Phone': '(205)252-9241', 'Students': 0.0, 'Teachers': '0.00000', 'Schools': '0.00000', 'Locale Code': '12', 'Locale': 'City: Midsize', 'Student Teacher Ratio': 0.0, 'Type': 'Regular School District'}, {'index': 4, 'NCES District ID': '0100002', 'State District ID': 'AL-210', 'District Name': 'Alabama Youth Services', 'County Name': 'Jefferson County', 'Street Address': 'PO Box 66', 'City': 'Mount Meigs', 'State': 'AL', 'ZIP': '36057', 'ZIP 4-digit': '0066', 'Phone': '(334)215-3859', 'Students': 0.0, 'Teachers': '0.00000', 'Schools': '0.00000', 'Locale Code': '21', 'Locale': 'Suburb: Large', 'Student Teacher Ratio': 0.0, 'Type': 'Regular School District'}, {'index': 5, 'NCES District ID': '0100190', 'State District ID': 'AL-103', 'District Name': 'Alabaster City', 'County Name': 'Shelby County', 'Street Address': '10111 Highway 119', 'City': 'Alabaster', 'State': 'AL', 'ZIP': '35007', 'ZIP 4-digit': None, 'Phone': '(205)663-8400', 'Students': '6184.00000', 'Teachers': '341.85000', 'Schools': '5.00000', 'Locale Code': '21', 'Locale': 'Suburb: Large', 'Student Teacher Ratio': '18.10000000', 'Type': 'Regular School District'}, {'index': 6, 'NCES District ID': '0100005', 'State District ID': 'AL-101', 'District Name': 'Albertville City', 'County Name': 'Marshall County', 'Street Address': '107 W Main St', 'City': 'Albertville', 'State': 'AL', 'ZIP': '35950', 'ZIP 4-digit': '1625', 'Phone': '(256)891-1183', 'Students': '5636.00000', 'Teachers': '295.50000', 'Schools': '6.00000', 'Locale Code': '32', 'Locale': 'Town: Distant', 'Student Teacher Ratio': '19.10000000', 'Type': 'Regular School District'}, {'index': 7, 'NCES District ID': '0100030', 'State District ID': 'AL-102', 'District Name': 'Alexander City', 'County Name': 'Tallapoosa County', 'Street Address': '375 Lee St', 'City': 'Alexander City', 'State': 'AL', 'ZIP': '35010', 'ZIP 4-digit': '2708', 'Phone': '(256)234-5074', 'Students': '3065.00000', 'Teachers': '176.52000', 'Schools': '5.00000', 'Locale Code': '32', 'Locale': 'Town: Distant', 'Student Teacher Ratio': '17.40000000', 'Type': 'Regular School District'}  ... displaying 10 of 182 total bound parameter sets ...  {'index': 180, 'NCES District ID': '0103580', 'State District ID': 'AL-067', 'District Name': 'Winston County', 'County Name': 'Winston County', 'Street Address': 'PO Box 9', 'City': 'Double Springs', 'State': 'AL', 'ZIP': '35553', 'ZIP 4-digit': '0009', 'Phone': '(205)489-5018', 'Students': '2375.00000', 'Teachers': '135.75000', 'Schools': '10.00000', 'Locale Code': '43', 'Locale': 'Rural: Remote', 'Student Teacher Ratio': '17.50000000', 'Type': 'Regular School District'}, {'index': 181, 'NCES District ID': '0100206', 'State District ID': 'AL-804', 'District Name': 'Woodland Preparatory', 'County Name': '†', 'Street Address': '21168 Hwy 17', 'City': 'Chatom', 'State': 'AL', 'ZIP': '36518', 'ZIP 4-digit': '6361', 'Phone': '(205)361-5267', 'Students': 0.0, 'Teachers': 0.0, 'Schools': '0.00000', 'Locale Code': 0.0, 'Locale': '†', 'Student Teacher Ratio': 0.0, 'Type': 'Other Education Agency'})]\n(Background on this error at: http://sqlalche.me/e/13/e3q8)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1256\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1257\u001b[0;31m                     self.dialect.do_executemany(\n\u001b[0m\u001b[1;32m   1258\u001b[0m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/dialects/mysql/mysqldb.py\u001b[0m in \u001b[0;36mdo_executemany\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    147\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_executemany\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 148\u001b[0;31m         \u001b[0mrowcount\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecutemany\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    149\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mcontext\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/cursors.py\u001b[0m in \u001b[0;36mexecutemany\u001b[0;34m(self, query, args)\u001b[0m\n\u001b[1;32m    187\u001b[0m             \u001b[0;32massert\u001b[0m \u001b[0mq_values\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m'('\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mq_values\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m-\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m')'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 188\u001b[0;31m             return self._do_execute_many(q_prefix, q_values, q_postfix, args,\n\u001b[0m\u001b[1;32m    189\u001b[0m                                          \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmax_stmt_length\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/cursors.py\u001b[0m in \u001b[0;36m_do_execute_many\u001b[0;34m(self, prefix, values, postfix, args, max_stmt_length, encoding)\u001b[0m\n\u001b[1;32m    226\u001b[0m             \u001b[0msql\u001b[0m \u001b[0;34m+=\u001b[0m \u001b[0mv\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 227\u001b[0;31m         \u001b[0mrows\u001b[0m \u001b[0;34m+=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msql\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0mpostfix\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    228\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrowcount\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mrows\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/cursors.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, query, args)\u001b[0m\n\u001b[1;32m    162\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 163\u001b[0;31m         \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    164\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_executed\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/cursors.py\u001b[0m in \u001b[0;36m_query\u001b[0;34m(self, q)\u001b[0m\n\u001b[1;32m    320\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_clear_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 321\u001b[0;31m         \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mq\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    322\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_do_get_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/connections.py\u001b[0m in \u001b[0;36mquery\u001b[0;34m(self, sql, unbuffered)\u001b[0m\n\u001b[1;32m    504\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execute_command\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mCOMMAND\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mCOM_QUERY\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msql\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 505\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_affected_rows\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_read_query_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0munbuffered\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0munbuffered\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    506\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_affected_rows\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/connections.py\u001b[0m in \u001b[0;36m_read_query_result\u001b[0;34m(self, unbuffered)\u001b[0m\n\u001b[1;32m    723\u001b[0m             \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mMySQLResult\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 724\u001b[0;31m             \u001b[0mresult\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    725\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_result\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/connections.py\u001b[0m in \u001b[0;36mread\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1068\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1069\u001b[0;31m             \u001b[0mfirst_packet\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_read_packet\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1070\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/connections.py\u001b[0m in \u001b[0;36m_read_packet\u001b[0;34m(self, packet_type)\u001b[0m\n\u001b[1;32m    675\u001b[0m                 \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_result\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0munbuffered_active\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mFalse\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 676\u001b[0;31m             \u001b[0mpacket\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_for_error\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    677\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mpacket\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/protocol.py\u001b[0m in \u001b[0;36mraise_for_error\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    222\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mDEBUG\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"errno =\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0merrno\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 223\u001b[0;31m         \u001b[0merr\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_mysql_exception\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_data\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    224\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/err.py\u001b[0m in \u001b[0;36mraise_mysql_exception\u001b[0;34m(data)\u001b[0m\n\u001b[1;32m    106\u001b[0m         \u001b[0merrorclass\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mInternalError\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0merrno\u001b[0m \u001b[0;34m<\u001b[0m \u001b[0;36m1000\u001b[0m \u001b[0;32melse\u001b[0m \u001b[0mOperationalError\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 107\u001b[0;31m     \u001b[0;32mraise\u001b[0m \u001b[0merrorclass\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0merrno\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0merrval\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mOperationalError\u001b[0m: (1054, \"Unknown column 'index' in 'field list'\")",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-152-7af06affc541>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0malabama\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_sql\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'alabama2'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcon\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mengine\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mif_exists\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m'append'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mchunksize\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;36m1000\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pandas/core/generic.py\u001b[0m in \u001b[0;36mto_sql\u001b[0;34m(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)\u001b[0m\n\u001b[1;32m   2651\u001b[0m         \u001b[0;32mfrom\u001b[0m \u001b[0mpandas\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mio\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0msql\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2652\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2653\u001b[0;31m         sql.to_sql(\n\u001b[0m\u001b[1;32m   2654\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2655\u001b[0m             \u001b[0mname\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mto_sql\u001b[0;34m(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)\u001b[0m\n\u001b[1;32m    510\u001b[0m         )\n\u001b[1;32m    511\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 512\u001b[0;31m     pandas_sql.to_sql(\n\u001b[0m\u001b[1;32m    513\u001b[0m         \u001b[0mframe\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    514\u001b[0m         \u001b[0mname\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mto_sql\u001b[0;34m(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method)\u001b[0m\n\u001b[1;32m   1315\u001b[0m         )\n\u001b[1;32m   1316\u001b[0m         \u001b[0mtable\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcreate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1317\u001b[0;31m         \u001b[0mtable\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0minsert\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmethod\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1318\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mname\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0misdigit\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mname\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mislower\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1319\u001b[0m             \u001b[0;31m# check for potentially case sensitivity issues (GH7815)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36minsert\u001b[0;34m(self, chunksize, method)\u001b[0m\n\u001b[1;32m    753\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    754\u001b[0m                 \u001b[0mchunk_iter\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mzip\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0marr\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mstart_i\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0mend_i\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0marr\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mdata_list\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 755\u001b[0;31m                 \u001b[0mexec_insert\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkeys\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mchunk_iter\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    756\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    757\u001b[0m     def _query_iterator(\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36m_execute_insert\u001b[0;34m(self, conn, keys, data_iter)\u001b[0m\n\u001b[1;32m    667\u001b[0m         \"\"\"\n\u001b[1;32m    668\u001b[0m         \u001b[0mdata\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0mdict\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mzip\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkeys\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mrow\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mrow\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mdata_iter\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 669\u001b[0;31m         \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtable\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0minsert\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdata\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    670\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    671\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_execute_insert_multi\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkeys\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdata_iter\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, object_, *multiparams, **params)\u001b[0m\n\u001b[1;32m   1012\u001b[0m             )\n\u001b[1;32m   1013\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1014\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mmeth\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1015\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1016\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_execute_function\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/sql/elements.py\u001b[0m in \u001b[0;36m_execute_on_connection\u001b[0;34m(self, connection, multiparams, params)\u001b[0m\n\u001b[1;32m    296\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_execute_on_connection\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    297\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msupports_execution\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 298\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execute_clauseelement\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    299\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    300\u001b[0m             \u001b[0;32mraise\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mObjectNotExecutableError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_clauseelement\u001b[0;34m(self, elem, multiparams, params)\u001b[0m\n\u001b[1;32m   1125\u001b[0m             )\n\u001b[1;32m   1126\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1127\u001b[0;31m         ret = self._execute_context(\n\u001b[0m\u001b[1;32m   1128\u001b[0m             \u001b[0mdialect\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1129\u001b[0m             \u001b[0mdialect\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecution_ctx_cls\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_init_compiled\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1315\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1316\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1317\u001b[0;31m             self._handle_dbapi_exception(\n\u001b[0m\u001b[1;32m   1318\u001b[0m                 \u001b[0me\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1319\u001b[0m             )\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[0;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[1;32m   1509\u001b[0m                 \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnewraise\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mwith_traceback\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfrom_\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1510\u001b[0m             \u001b[0;32melif\u001b[0m \u001b[0mshould_wrap\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1511\u001b[0;31m                 util.raise_(\n\u001b[0m\u001b[1;32m   1512\u001b[0m                     \u001b[0msqlalchemy_exception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mwith_traceback\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfrom_\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1513\u001b[0m                 )\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/util/compat.py\u001b[0m in \u001b[0;36mraise_\u001b[0;34m(***failed resolving arguments***)\u001b[0m\n\u001b[1;32m    176\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    177\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 178\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    179\u001b[0m         \u001b[0;32mfinally\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    180\u001b[0m             \u001b[0;31m# credit to\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1255\u001b[0m                             \u001b[0;32mbreak\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1256\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1257\u001b[0;31m                     self.dialect.do_executemany(\n\u001b[0m\u001b[1;32m   1258\u001b[0m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1259\u001b[0m                     )\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/sqlalchemy/dialects/mysql/mysqldb.py\u001b[0m in \u001b[0;36mdo_executemany\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    146\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    147\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_executemany\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 148\u001b[0;31m         \u001b[0mrowcount\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecutemany\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    149\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mcontext\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    150\u001b[0m             \u001b[0mcontext\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_rowcount\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mrowcount\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/cursors.py\u001b[0m in \u001b[0;36mexecutemany\u001b[0;34m(self, query, args)\u001b[0m\n\u001b[1;32m    186\u001b[0m             \u001b[0mq_postfix\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mm\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgroup\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;36m3\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0;34m''\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    187\u001b[0m             \u001b[0;32massert\u001b[0m \u001b[0mq_values\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m'('\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mq_values\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m-\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m')'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 188\u001b[0;31m             return self._do_execute_many(q_prefix, q_values, q_postfix, args,\n\u001b[0m\u001b[1;32m    189\u001b[0m                                          \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmax_stmt_length\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    190\u001b[0m                                          self._get_db().encoding)\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/cursors.py\u001b[0m in \u001b[0;36m_do_execute_many\u001b[0;34m(self, prefix, values, postfix, args, max_stmt_length, encoding)\u001b[0m\n\u001b[1;32m    225\u001b[0m                 \u001b[0msql\u001b[0m \u001b[0;34m+=\u001b[0m \u001b[0;34mb','\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    226\u001b[0m             \u001b[0msql\u001b[0m \u001b[0;34m+=\u001b[0m \u001b[0mv\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 227\u001b[0;31m         \u001b[0mrows\u001b[0m \u001b[0;34m+=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msql\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0mpostfix\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    228\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrowcount\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mrows\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    229\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mrows\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/cursors.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, query, args)\u001b[0m\n\u001b[1;32m    161\u001b[0m         \u001b[0mquery\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmogrify\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0margs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    162\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 163\u001b[0;31m         \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    164\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_executed\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    165\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/cursors.py\u001b[0m in \u001b[0;36m_query\u001b[0;34m(self, q)\u001b[0m\n\u001b[1;32m    319\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_last_executed\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mq\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    320\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_clear_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 321\u001b[0;31m         \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mq\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    322\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_do_get_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    323\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrowcount\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/connections.py\u001b[0m in \u001b[0;36mquery\u001b[0;34m(self, sql, unbuffered)\u001b[0m\n\u001b[1;32m    503\u001b[0m                 \u001b[0msql\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msql\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mencode\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mencoding\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'surrogateescape'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    504\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execute_command\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mCOMMAND\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mCOM_QUERY\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msql\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 505\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_affected_rows\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_read_query_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0munbuffered\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0munbuffered\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    506\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_affected_rows\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    507\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/connections.py\u001b[0m in \u001b[0;36m_read_query_result\u001b[0;34m(self, unbuffered)\u001b[0m\n\u001b[1;32m    722\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    723\u001b[0m             \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mMySQLResult\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 724\u001b[0;31m             \u001b[0mresult\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    725\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_result\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    726\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mserver_status\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/connections.py\u001b[0m in \u001b[0;36mread\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1067\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1068\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1069\u001b[0;31m             \u001b[0mfirst_packet\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_read_packet\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1070\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1071\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mfirst_packet\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mis_ok_packet\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/connections.py\u001b[0m in \u001b[0;36m_read_packet\u001b[0;34m(self, packet_type)\u001b[0m\n\u001b[1;32m    674\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_result\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_result\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0munbuffered_active\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    675\u001b[0m                 \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_result\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0munbuffered_active\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mFalse\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 676\u001b[0;31m             \u001b[0mpacket\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_for_error\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    677\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mpacket\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    678\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/protocol.py\u001b[0m in \u001b[0;36mraise_for_error\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    221\u001b[0m         \u001b[0merrno\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_uint16\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    222\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mDEBUG\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"errno =\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0merrno\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 223\u001b[0;31m         \u001b[0merr\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_mysql_exception\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_data\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    224\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    225\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdump\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/err.py\u001b[0m in \u001b[0;36mraise_mysql_exception\u001b[0;34m(data)\u001b[0m\n\u001b[1;32m    105\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0merrorclass\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    106\u001b[0m         \u001b[0merrorclass\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mInternalError\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0merrno\u001b[0m \u001b[0;34m<\u001b[0m \u001b[0;36m1000\u001b[0m \u001b[0;32melse\u001b[0m \u001b[0mOperationalError\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 107\u001b[0;31m     \u001b[0;32mraise\u001b[0m \u001b[0merrorclass\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0merrno\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0merrval\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mOperationalError\u001b[0m: (pymysql.err.OperationalError) (1054, \"Unknown column 'index' in 'field list'\")\n[SQL: INSERT INTO alabama2 (`index`, `NCES District ID`, `State District ID`, `District Name`, `County Name`, `Street Address`, `City`, `State`, `ZIP`, `ZIP 4-digit`, `Phone`, `Students`, `Teachers`, `Schools`, `Locale Code`, `Locale`, `Student Teacher Ratio`, `Type`) VALUES (%(index)s, %(NCES District ID)s, %(State District ID)s, %(District Name)s, %(County Name)s, %(Street Address)s, %(City)s, %(State)s, %(ZIP)s, %(ZIP 4-digit)s, %(Phone)s, %(Students)s, %(Teachers)s, %(Schools)s, %(Locale Code)s, %(Locale)s, %(Student Teacher Ratio)s, %(Type)s)]\n[parameters: ({'index': 0, 'NCES District ID': '0100197', 'State District ID': 'AL-800', 'District Name': 'Acceleration Day and Evening Academy', 'County Name': 'Mobile County', 'Street Address': '3100 Cottage Hill Road Bldg 3', 'City': 'Mobile', 'State': 'AL', 'ZIP': '36606', 'ZIP 4-digit': None, 'Phone': '(251)301-0050', 'Students': '300.00000', 'Teachers': '10.00000', 'Schools': '1.00000', 'Locale Code': '12', 'Locale': 'City: Midsize', 'Student Teacher Ratio': '30.00000000', 'Type': 'Other Education Agency'}, {'index': 1, 'NCES District ID': '0100009', 'State District ID': 'AL-600', 'District Name': 'Al Inst Deaf And Blind', 'County Name': 'Talladega County', 'Street Address': 'PO Box 698', 'City': 'Talladega', 'State': 'AL', 'ZIP': '35161', 'ZIP 4-digit': '0698', 'Phone': '(256)761-3200', 'Students': 0.0, 'Teachers': '0.00000', 'Schools': '5.00000', 'Locale Code': '32', 'Locale': 'Town: Distant', 'Student Teacher Ratio': 0.0, 'Type': 'Regular School District'}, {'index': 2, 'NCES District ID': '0100183', 'State District ID': 'AL-709', 'District Name': 'Alabama Clinical Schools', 'County Name': 'Jefferson County', 'Street Address': 'PO Box 100968', 'City': 'Birmingham', 'State': 'AL', 'ZIP': '35210', 'ZIP 4-digit': '4308', 'Phone': '(205)836-9923', 'Students': 0.0, 'Teachers': '0.00000', 'Schools': '1.00000', 'Locale Code': '12', 'Locale': 'City: Midsize', 'Student Teacher Ratio': 0.0, 'Type': 'Regional District'}, {'index': 3, 'NCES District ID': '0100018', 'State District ID': 'AL-207', 'District Name': 'Alabama School of Fine Arts', 'County Name': 'Jefferson County', 'Street Address': '1800 Rev Abraham Woods Jr Blvd', 'City': 'Birmingham', 'State': 'AL', 'ZIP': '35203', 'ZIP 4-digit': None, 'Phone': '(205)252-9241', 'Students': 0.0, 'Teachers': '0.00000', 'Schools': '0.00000', 'Locale Code': '12', 'Locale': 'City: Midsize', 'Student Teacher Ratio': 0.0, 'Type': 'Regular School District'}, {'index': 4, 'NCES District ID': '0100002', 'State District ID': 'AL-210', 'District Name': 'Alabama Youth Services', 'County Name': 'Jefferson County', 'Street Address': 'PO Box 66', 'City': 'Mount Meigs', 'State': 'AL', 'ZIP': '36057', 'ZIP 4-digit': '0066', 'Phone': '(334)215-3859', 'Students': 0.0, 'Teachers': '0.00000', 'Schools': '0.00000', 'Locale Code': '21', 'Locale': 'Suburb: Large', 'Student Teacher Ratio': 0.0, 'Type': 'Regular School District'}, {'index': 5, 'NCES District ID': '0100190', 'State District ID': 'AL-103', 'District Name': 'Alabaster City', 'County Name': 'Shelby County', 'Street Address': '10111 Highway 119', 'City': 'Alabaster', 'State': 'AL', 'ZIP': '35007', 'ZIP 4-digit': None, 'Phone': '(205)663-8400', 'Students': '6184.00000', 'Teachers': '341.85000', 'Schools': '5.00000', 'Locale Code': '21', 'Locale': 'Suburb: Large', 'Student Teacher Ratio': '18.10000000', 'Type': 'Regular School District'}, {'index': 6, 'NCES District ID': '0100005', 'State District ID': 'AL-101', 'District Name': 'Albertville City', 'County Name': 'Marshall County', 'Street Address': '107 W Main St', 'City': 'Albertville', 'State': 'AL', 'ZIP': '35950', 'ZIP 4-digit': '1625', 'Phone': '(256)891-1183', 'Students': '5636.00000', 'Teachers': '295.50000', 'Schools': '6.00000', 'Locale Code': '32', 'Locale': 'Town: Distant', 'Student Teacher Ratio': '19.10000000', 'Type': 'Regular School District'}, {'index': 7, 'NCES District ID': '0100030', 'State District ID': 'AL-102', 'District Name': 'Alexander City', 'County Name': 'Tallapoosa County', 'Street Address': '375 Lee St', 'City': 'Alexander City', 'State': 'AL', 'ZIP': '35010', 'ZIP 4-digit': '2708', 'Phone': '(256)234-5074', 'Students': '3065.00000', 'Teachers': '176.52000', 'Schools': '5.00000', 'Locale Code': '32', 'Locale': 'Town: Distant', 'Student Teacher Ratio': '17.40000000', 'Type': 'Regular School District'}  ... displaying 10 of 182 total bound parameter sets ...  {'index': 180, 'NCES District ID': '0103580', 'State District ID': 'AL-067', 'District Name': 'Winston County', 'County Name': 'Winston County', 'Street Address': 'PO Box 9', 'City': 'Double Springs', 'State': 'AL', 'ZIP': '35553', 'ZIP 4-digit': '0009', 'Phone': '(205)489-5018', 'Students': '2375.00000', 'Teachers': '135.75000', 'Schools': '10.00000', 'Locale Code': '43', 'Locale': 'Rural: Remote', 'Student Teacher Ratio': '17.50000000', 'Type': 'Regular School District'}, {'index': 181, 'NCES District ID': '0100206', 'State District ID': 'AL-804', 'District Name': 'Woodland Preparatory', 'County Name': '†', 'Street Address': '21168 Hwy 17', 'City': 'Chatom', 'State': 'AL', 'ZIP': '36518', 'ZIP 4-digit': '6361', 'Phone': '(205)361-5267', 'Students': 0.0, 'Teachers': 0.0, 'Schools': '0.00000', 'Locale Code': 0.0, 'Locale': '†', 'Student Teacher Ratio': 0.0, 'Type': 'Other Education Agency'})]\n(Background on this error at: http://sqlalche.me/e/13/e3q8)"
     ]
    }
   ],
   "source": [
    "alabama.to_sql('alabama2', con = engine, if_exists = 'append', chunksize = 1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql\n",
    "\n",
    "connection = pymysql.connect(host = 'localhost',\n",
    "                             user = 'root',\n",
    "                             password = 'flow2020',\n",
    "                             db = 'cfes_schema')\n",
    "\n",
    "cursor = connection.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<generator object DataFrame.iterrows at 0x118911b30>\n",
      "<generator object DataFrame.iterrows at 0x118911b30>\n",
      "<generator object DataFrame.iterrows at 0x118911b30>\n",
      "<generator object DataFrame.iterrows at 0x118911b30>\n",
      "<generator object DataFrame.iterrows at 0x118911b30>\n",
      "<generator object DataFrame.iterrows at 0x118911b30>\n",
      "<generator object DataFrame.iterrows at 0x118911b30>\n",
      "<generator object DataFrame.iterrows at 0x118911b30>\n",
      "<generator object DataFrame.iterrows at 0x118911b30>\n",
      "<generator object DataFrame.iterrows at 0x118911b30>\n",
      "<generator object DataFrame.iterrows at 0x118911b30>\n",
      "<generator object DataFrame.iterrows at 0x118911b30>\n",
      "<generator object DataFrame.iterrows at 0x118911b30>\n",
      "<generator object DataFrame.iterrows at 0x118911b30>\n",
      "<generator object DataFrame.iterrows at 0x118911b30>\n",
      "<generator object DataFrame.iterrows at 0x118911b30>\n",
      "<generator object DataFrame.iterrows at 0x118911b30>\n"
     ]
    }
   ],
   "source": [
    "for row in alabama"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "ename": "ProgrammingError",
     "evalue": "(1064, \"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''alabama' ('NCES District ID','State District ID','District Name','County Name*'' at line 1\")",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mProgrammingError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-71-2fc2ac5024b6>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mi\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mrow\u001b[0m \u001b[0;32min\u001b[0m \u001b[0malabama\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0miterrows\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m     \u001b[0msql\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m\"INSERT INTO 'alabama' ('\"\u001b[0m \u001b[0;34m+\u001b[0m\u001b[0mcols\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0;34m\"') VALUES (\"\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0;34m\"%s,\"\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mrow\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m-\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0;34m\"%s)\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 5\u001b[0;31m     \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msql\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtuple\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mrow\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      6\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      7\u001b[0m     \u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcommit\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/cursors.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, query, args)\u001b[0m\n\u001b[1;32m    161\u001b[0m         \u001b[0mquery\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmogrify\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0margs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    162\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 163\u001b[0;31m         \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    164\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_executed\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    165\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/cursors.py\u001b[0m in \u001b[0;36m_query\u001b[0;34m(self, q)\u001b[0m\n\u001b[1;32m    319\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_last_executed\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mq\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    320\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_clear_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 321\u001b[0;31m         \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mq\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    322\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_do_get_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    323\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrowcount\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/connections.py\u001b[0m in \u001b[0;36mquery\u001b[0;34m(self, sql, unbuffered)\u001b[0m\n\u001b[1;32m    503\u001b[0m                 \u001b[0msql\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msql\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mencode\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mencoding\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'surrogateescape'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    504\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execute_command\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mCOMMAND\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mCOM_QUERY\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msql\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 505\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_affected_rows\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_read_query_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0munbuffered\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0munbuffered\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    506\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_affected_rows\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    507\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/connections.py\u001b[0m in \u001b[0;36m_read_query_result\u001b[0;34m(self, unbuffered)\u001b[0m\n\u001b[1;32m    722\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    723\u001b[0m             \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mMySQLResult\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 724\u001b[0;31m             \u001b[0mresult\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    725\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_result\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    726\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mserver_status\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/connections.py\u001b[0m in \u001b[0;36mread\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1067\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1068\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1069\u001b[0;31m             \u001b[0mfirst_packet\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_read_packet\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1070\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1071\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mfirst_packet\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mis_ok_packet\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/connections.py\u001b[0m in \u001b[0;36m_read_packet\u001b[0;34m(self, packet_type)\u001b[0m\n\u001b[1;32m    674\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_result\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_result\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0munbuffered_active\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    675\u001b[0m                 \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_result\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0munbuffered_active\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mFalse\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 676\u001b[0;31m             \u001b[0mpacket\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_for_error\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    677\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mpacket\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    678\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/protocol.py\u001b[0m in \u001b[0;36mraise_for_error\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    221\u001b[0m         \u001b[0merrno\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_uint16\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    222\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mDEBUG\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"errno =\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0merrno\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 223\u001b[0;31m         \u001b[0merr\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_mysql_exception\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_data\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    224\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    225\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdump\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/opt/anaconda3/lib/python3.8/site-packages/pymysql/err.py\u001b[0m in \u001b[0;36mraise_mysql_exception\u001b[0;34m(data)\u001b[0m\n\u001b[1;32m    105\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0merrorclass\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    106\u001b[0m         \u001b[0merrorclass\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mInternalError\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0merrno\u001b[0m \u001b[0;34m<\u001b[0m \u001b[0;36m1000\u001b[0m \u001b[0;32melse\u001b[0m \u001b[0mOperationalError\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 107\u001b[0;31m     \u001b[0;32mraise\u001b[0m \u001b[0merrorclass\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0merrno\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0merrval\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mProgrammingError\u001b[0m: (1064, \"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''alabama' ('NCES District ID','State District ID','District Name','County Name*'' at line 1\")"
     ]
    }
   ],
   "source": [
    "cols = \"','\".join([str(i) for i in alabama.columns.tolist()])\n",
    "\n",
    "for i, row in alabama.iterrows():\n",
    "    sql = \"INSERT INTO 'alabama' ('\" +cols + \"') VALUES (\" + \"%s,\"*(len(row)-1) + \"%s)\"\n",
    "    cursor.execute(sql, tuple(row))\n",
    "    \n",
    "    connection.commit()"
   ]
  }
 ],
 "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}