{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Setting the Stage\n",
    "\n",
    "Consider the scenario:\n",
    "\n",
    "Let's say you have an Oracle database and a MySQL database each with multiple schemas and each one of these schemas have multiple tables. You are working on a project that needs different tables from these different databases. You need to carry your own investigation and check for table metadata; column names, data type and range. There is software that will allow you to conduct this investigation but hardly enough automation or easy ways to take notes on discoveries.\n",
    "\n",
    "You could do this investigation with SQLAlchemy, in a jupyter notebook if you want and easily take notes all without switching from tool to tool.\n",
    "\n",
    "Say furthermore that you worked on a project in MySQL and used a MySQL only and dedicated python package. You had multiline SQL queries and hardcoded different filters. One day your company decides to migrate to something different; say [snowflake](https://www.snowflake.com/product/architecture/) for example. Your models need to be tuned and your data processing will need to be adjusted. You have to re-create some of the tables you were in charge of, but obviously some of the queries you wrote will need to be changed! You will probably have a hard time refactoring your code, sql queries etc. But with SQLAlchemy, it will be easier. Your tables can be re-created with nearly no additional effort. You can update/tune your filters (as we will see later) and all will go as planned. \n",
    "\n",
    "From my experience, using SQLAlchemy has helped with metadata investigation, consistency with different SQL dialects and it has made refactoring and maintenance extremely easier than expected. These are reasons why I personally love using SQLAlchemy whenever possible."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Why SQLAlchemy?\n",
    "\n",
    "SQLAlchemy and ORM packages are very popular with pythond developers working on non-data science related software/webapps. I think one of the main reasons are \"protection against sql injection attacks\" but it is database agnostic and as such, it allows for great consistency and light maintenance during database migrations. The following 4 points are my main reasons for using SQLAlchemy.\n",
    "\n",
    "<ol>\n",
    "    <li>Easy access to table metadata</li>\n",
    "    <li>Consitent cursor/engine methods</li>\n",
    "    <li>Better control over table architecture</li>\n",
    "    <li>Refractoring is made EASY</li>\n",
    "</ol>\n",
    "\n",
    "Notes:\n",
    "<ol>\n",
    "    <li>\n",
    "        There are methods and objects that come with SQLAlchemy that allow the user to quickly derive table metadata. This saves time from switching between your python IDE and Oracle SQL Developer for example.\n",
    "    </li>\n",
    "    <li>\n",
    "        Interfacing with databases is not always straigh forward in python. Although the [DBAPI](https://www.python.org/dev/peps/pep-0249/) specs provides a standard, different developers are free to structure/name their methods as they see fit.\n",
    "    </li>\n",
    "    <li>\n",
    "        There is nothing like having great control over the structure of the table you are responsible for. You must make sure that all primary keys are properly identified, that all columns that cannot be null are distinguishable and that database defaults are set.\n",
    "    </li>\n",
    "    <li>\n",
    "        If you use an IDE like Pycharm, you probably love how easy it is to refractor objects. Refractoring objects/variables are very easy and the fact that SQLAlchemy provides the object representation of tables and columns; refractoring is often (not always) a piece of cake.\n",
    "    </li>\n",
    "    \n",
    "</ol>\n",
    "\n",
    "### Con\n",
    "The main complaint is always efficiency/speed of sqlalchemy compared to raw sql paired with dedicated packages for each SQL dialect."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pathlib\n",
    "import pandas as pd\n",
    "import sqlalchemy as sa\n",
    "import sqlalchemy.orm as orm\n",
    "from sqlalchemy.engine import reflection\n",
    "from sqlalchemy.ext.hybrid import hybrid_property\n",
    "from sqlalchemy.ext.declarative import declarative_base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/Users/josiahhounyo/Documents/GitHub/uchicago/temp_db.db\n"
     ]
    }
   ],
   "source": [
    "# create a database\n",
    "db_file = pathlib.Path.cwd() / \"temp_db.db\"\n",
    "db_file.touch()\n",
    "print(db_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = sa.create_engine(f\"sqlite:///{db_file}\")\n",
    "ny_sat = r\"https://data.cityofnewyork.us/api/views/zt9s-n5aj/rows.csv?accessType=DOWNLOAD\"\n",
    "data_docs = r\"https://catalog.data.gov/dataset/sat-college-board-2010-school-level-results-5c6d6\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Please go to https://sqlitebrowser.org/dl/ and install the sqlite browser\n",
    "\n",
    "After installation, click on **Open Database** and open the file created"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>dbn</th>\n",
       "      <th>school_name</th>\n",
       "      <th>number_of_test_takers</th>\n",
       "      <th>critical_reading_mean</th>\n",
       "      <th>mathematics_mean</th>\n",
       "      <th>writing_mean</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>01M292</td>\n",
       "      <td>Henry Street School for International Studies</td>\n",
       "      <td>31.0</td>\n",
       "      <td>391.0</td>\n",
       "      <td>425.0</td>\n",
       "      <td>385.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>01M448</td>\n",
       "      <td>University Neighborhood High School</td>\n",
       "      <td>60.0</td>\n",
       "      <td>394.0</td>\n",
       "      <td>419.0</td>\n",
       "      <td>387.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>01M450</td>\n",
       "      <td>East Side Community High School</td>\n",
       "      <td>69.0</td>\n",
       "      <td>418.0</td>\n",
       "      <td>431.0</td>\n",
       "      <td>402.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>01M458</td>\n",
       "      <td>SATELLITE ACADEMY FORSYTH ST</td>\n",
       "      <td>26.0</td>\n",
       "      <td>385.0</td>\n",
       "      <td>370.0</td>\n",
       "      <td>378.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>01M509</td>\n",
       "      <td>CMSP HIGH SCHOOL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      dbn                                     school_name  \\\n",
       "0  01M292  Henry Street School for International Studies    \n",
       "1  01M448            University Neighborhood High School    \n",
       "2  01M450                East Side Community High School    \n",
       "3  01M458                   SATELLITE ACADEMY FORSYTH ST    \n",
       "4  01M509                               CMSP HIGH SCHOOL    \n",
       "\n",
       "   number_of_test_takers  critical_reading_mean  mathematics_mean  \\\n",
       "0                   31.0                  391.0             425.0   \n",
       "1                   60.0                  394.0             419.0   \n",
       "2                   69.0                  418.0             431.0   \n",
       "3                   26.0                  385.0             370.0   \n",
       "4                    NaN                    NaN               NaN   \n",
       "\n",
       "   writing_mean  \n",
       "0         385.0  \n",
       "1         387.0  \n",
       "2         402.0  \n",
       "3         378.0  \n",
       "4           NaN  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(ny_sat)\n",
    "df.columns = [column.lower().replace(' ', '_') for column in df.columns]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Metadata - Inspecting Data Available in a Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['main']"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inspector = reflection.Inspector.from_engine(engine)\n",
    "\n",
    "# If we want all the schemas we have access to we can do this\n",
    "inspector.get_schema_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# To get all the tables in a schema we can do the following\n",
    "inspector.get_table_names(schema='main')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's add the data downloaded\n",
    "df.to_sql('pandas_table', engine, index=False, if_exists='replace') \n",
    "# you can check the data in your sqlite database browser (we could have used indices as key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table('pandas_table', MetaData(bind=Engine(sqlite:////Users/josiahhounyo/Documents/GitHub/uchicago/temp_db.db)), Column('dbn', TEXT(), table=<pandas_table>), Column('school_name', TEXT(), table=<pandas_table>), Column('number_of_test_takers', FLOAT(), table=<pandas_table>), Column('critical_reading_mean', FLOAT(), table=<pandas_table>), Column('mathematics_mean', FLOAT(), table=<pandas_table>), Column('writing_mean', FLOAT(), table=<pandas_table>), schema=None)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Reflecting tables\n",
    "metadata = sa.MetaData(engine)\n",
    "pandas_table = sa.Table('pandas_table', metadata, autoload_with=engine)\n",
    "pandas_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Column('dbn', TEXT(), table=<pandas_table>),\n",
       " Column('school_name', TEXT(), table=<pandas_table>),\n",
       " Column('number_of_test_takers', FLOAT(), table=<pandas_table>),\n",
       " Column('critical_reading_mean', FLOAT(), table=<pandas_table>),\n",
       " Column('mathematics_mean', FLOAT(), table=<pandas_table>),\n",
       " Column('writing_mean', FLOAT(), table=<pandas_table>)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(pandas_table.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sqlalchemy.sql.schema.Column"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# column names - let's inspect a column object\n",
    "column = pandas_table.columns['dbn'] # you can access columns using .columns or .c\n",
    "type(column)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "column name: dbn, type: TEXT, primary key: False, python type: <class 'str'>\n"
     ]
    }
   ],
   "source": [
    "print(\n",
    "    f\"\"\"column name: {column.name}, type: {column.type}, primary key: {column.primary_key}, python type: {column.type.python_type}\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Consistent Cursor/Engine Methods\n",
    "\n",
    "Inserting or Updating data in a table is usually done via SQL queries. The specific syntax could differ from one dialect to another, Columns with special characters (spaces etc...) will need to be handled properly and on top of that, parsing data from a pandas dataframe to complete a sql query is not always straighforward.\n",
    "\n",
    "Consider the following dataframe. How would you insert the data into the table?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_to_insert_into_table = pd.DataFrame({\n",
    "    'dbn': ['01M998', '01M999'],\n",
    "    'school_name': ['My Made Up School', 'New School In Town']\n",
    "})\n",
    "\n",
    "data_to_update_in_table = pd.DataFrame({\n",
    "    'dbn_to_update': ['01M292', '01M448'],\n",
    "    'school_name_to_update': ['Updated School #1', 'Updated School #2']\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>dbn_to_update</th>\n",
       "      <th>school_name_to_update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>01M292</td>\n",
       "      <td>Updated School #1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>01M448</td>\n",
       "      <td>Updated School #2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  dbn_to_update school_name_to_update\n",
       "0        01M292     Updated School #1\n",
       "1        01M448     Updated School #2"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_to_update_in_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# You could do something like this\n",
    "sql = \"\"\"\n",
    "insert into pandas_table (dbn, school_name) values (:1, :2)\n",
    "\"\"\"\n",
    "\n",
    "results = engine.execute(sql, ('01M997', 'School Just Newly Added'))\n",
    "# check the data in your browser - you can refresh it and check the end"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sqlalchemy.engine.result.ResultProxy"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# you can tell that you inserted one record\n",
    "results.rowcount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INSERT INTO pandas_table (dbn, school_name, number_of_test_takers, critical_reading_mean, mathematics_mean, writing_mean) VALUES (?, ?, ?, ?, ?, ?)\n"
     ]
    }
   ],
   "source": [
    "# closer look at native properties of SQLAlchemy Table objects - very similar to what we had\n",
    "print(pandas_table.insert())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "UPDATE pandas_table SET dbn=?, school_name=?, number_of_test_takers=?, critical_reading_mean=?, mathematics_mean=?, writing_mean=?\n"
     ]
    }
   ],
   "source": [
    "# update properties - not very sufficient here!\n",
    "print(pandas_table.update())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dictionaries are key to inserting and updating using these methods and Pandas have great support for [turning the data into dictionaries](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_dict.html#pandas.DataFrame.to_dict)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'dbn': '01M998', 'school_name': 'My Made Up School'},\n",
       " {'dbn': '01M999', 'school_name': 'New School In Town'}]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_to_insert_into_table.to_dict(orient='records') # I believe record as reference to database/table record?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "_ = engine.execute(pandas_table.insert(), data_to_insert_into_table.to_dict(orient='records'))\n",
    "# check the database in your explorer - at the end of the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "UPDATE pandas_table SET school_name=? WHERE pandas_table.dbn = ?\n"
     ]
    }
   ],
   "source": [
    "# for updates, you need to specify which fields you want to check and those you want to set\n",
    "update_statement = pandas_table.update().where(\n",
    "    pandas_table.columns['dbn'] == sa.bindparam('dbn_to_update')\n",
    ").values(school_name=sa.bindparam('school_name_to_update'))\n",
    "\n",
    "print(update_statement)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'dbn_to_update': '01M292', 'school_name_to_update': 'Updated School #1'},\n",
       " {'dbn_to_update': '01M448', 'school_name_to_update': 'Updated School #2'}]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_to_update_in_table.to_dict(orient='records')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# What is bindparam doing?\n",
    "# What if I have a more complex where clause?\n",
    "# What if I had more values to set?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "_ = engine.execute(update_statement, data_to_update_in_table.to_dict(orient='records'))\n",
    "# check the first two rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Benefits of the approach above\n",
    "\n",
    "<ul>\n",
    "    <li>Database Agnostic</li>\n",
    "    <li>Auto-generated table objects and insert/update statement with all escaping taken care of!</li>\n",
    "    <li>Quickly and Easily turn Pandas Dataframes into Actual Records on Database</li>\n",
    "    ...\n",
    "</ul>\n",
    "\n",
    "Keep in mind that there are some columns of certain types, datetime for example, can cause problems but that usually just requires further data processing."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Table Structure\n",
    "\n",
    "When you have to create/maintain tables, you MUST make sure constraints are not violated. If you inspect the data structure in your sqlite browser, you will notice that there are no primary keys. PRIMARY KEYS are sometimes necessary for the integrity of the data. Let's now create the table with two columns as primary keys."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop the previous table\n",
    "pandas_table.drop(engine, checkfirst=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x11bac79e8>"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Answer here\n",
    "sql = \"\"\"\n",
    "CREATE TABLE IF NOT EXISTS ny_sat(\n",
    "   dbn VARCHAR(15),\n",
    "   school_name VARCHAR(100),\n",
    "   number_of_test_takers INT,\n",
    "   critical_reading_mean DOUBLE,\n",
    "   mathematics_mean DOUBLE,\n",
    "   writing_mean DOUBLE,\n",
    "   PRIMARY KEY (dbn, school_name)\n",
    ")\n",
    "\"\"\"\n",
    "\n",
    "engine.execute(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['ny_sat']"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.table_names()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With more columns and more constraints, the query can get longer and harder to read. I've seen some rather long table specifications in sql and they can be hard to maintain. But a fun fact is that some dialects allow/require different syntax on how to create tables. So if you were to switch dialects, you could face minor headaches. SQLAlchemy can help!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "Base = declarative_base()\n",
    "\n",
    "class NewYorkCitySat(Base):\n",
    "    __tablename__ = 'new_york_sat'\n",
    "    __table_args__ = {'schema' : None} # this is if you want to specify the schema\n",
    "    \n",
    "    dbn = sa.Column(sa.String(15), primary_key=True)\n",
    "    school_name = sa.Column(sa.String(100), primary_key=True)\n",
    "    number_of_test_takers = sa.Column(sa.Integer, nullable=True)\n",
    "    critical_reading_mean = sa.Column(sa.Float, nullable=True)\n",
    "    mathematics_mean = sa.Column(sa.Float, nullable=True)\n",
    "    writing_mean = sa.Column(sa.Float, nullable=True)\n",
    "    \n",
    "    # since this is a class, we could provide an __init__ just because\n",
    "    def __init__(self, dbn, school_name, number_of_test_takers, critical_reading_mean, mathematics_mean,\n",
    "                writing_mean):\n",
    "        self.dbn = dbn\n",
    "        self.school_name = school_name\n",
    "        self.number_of_test_takers = number_of_test_takers\n",
    "        self.critical_reading_mean = critical_reading_mean\n",
    "        self.mathematics_mean = mathematics_mean\n",
    "        self.writing_mean = writing_mean\n",
    "    \n",
    "    # say we want to have some sort of computation that tells us when a school is a \"good\" school\n",
    "    # the criteria for this is all made up and could be complicated but we focus on an easy one\n",
    "    @hybrid_property\n",
    "    def good_school(self):\n",
    "        return self.critical_reading_mean >= 466 and self.mathematics_mean >= 489 and self.writing_mean >= 464\n",
    "    \n",
    "    # We obviously could not use that propery directy as a filter on a query on the database\n",
    "    @good_school.expression\n",
    "    def good_school(cls):\n",
    "        return sa.and_(\n",
    "            cls.critical_reading_mean >= 466,\n",
    "            cls.mathematics_mean >= 489,\n",
    "            cls.writing_mean >= 464\n",
    "        )\n",
    "        \n",
    "# there are others types for the columns. There are also ways to set server default values!\n",
    "# you could add other methods to the class. You could even add custom validators (only for instances of the class)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We could actually create a `Table` object but instead we used the ORM approach. Just a heads up, `np.nan` does not play well with these methods (although I believe you could configure sqlalchemy to treat them as `None`) so we will be replacing them with `None` since `None` is the real equivalent of `NULL`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table('new_york_sat', MetaData(bind=None), Column('dbn', String(length=15), table=<new_york_sat>, primary_key=True, nullable=False), Column('school_name', String(length=100), table=<new_york_sat>, primary_key=True, nullable=False), Column('number_of_test_takers', Integer(), table=<new_york_sat>), Column('critical_reading_mean', Float(), table=<new_york_sat>), Column('mathematics_mean', Float(), table=<new_york_sat>), Column('writing_mean', Float(), table=<new_york_sat>), schema=None)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# That might have been tedious yes. But it not only has an easy to understand object, it also has the easy to use\n",
    "# Table object accessible like this\n",
    "\n",
    "NewYorkCitySat.__table__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# one way of creating the table on the database\n",
    "NewYorkCitySat.__table__.create(bind = engine, checkfirst = True)\n",
    "\n",
    "# equivalent to\n",
    "Base.metadata.tables['new_york_sat'].create(bind = engine, checkfirst = True)\n",
    "\n",
    "# another way\n",
    "Base.metadata.create_all(bind = engine, tables = [NewYorkCitySat.__table__], checkfirst = True)\n",
    "\n",
    "# preference is left to the user - oh if the second command didn't have checkfirst, there would be an error\n",
    "# try it and see the error message, take a peek at the sql query used"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "__main__.NewYorkCitySat"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creating instances of the class -  this is what we would do without the __init__ method\n",
    "one_record = NewYorkCitySat(\n",
    "    dbn = '01M292',\n",
    "    school_name = 'My High School',\n",
    "    number_of_test_takers = 12,\n",
    "    critical_reading_mean = 190,\n",
    "    mathematics_mean = 200,\n",
    "    writing_mean = 180\n",
    ")\n",
    "\n",
    "type(one_record)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating instances by relying on the __init__ method since we have it\n",
    "other_record = NewYorkCitySat('01M292', 'My High School', 12, 190, 200, 180)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "the only difference is that without the \\_\\_init\\_\\_ method, we need to create records as keyword arguments but with the \\_\\_init\\_\\_ method, we can use positional arguments. We could even do some final processing by record (though it's always best to do the processing at DataFrame level)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Inserting the data using orm sessions - create instances from the dataframe\n",
    "session = orm.Session(engine)\n",
    "session.add(one_record)\n",
    "session.commit()\n",
    "session.close()\n",
    "\n",
    "# check the new_york_sat table in the database browser"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(sqlite3.IntegrityError) UNIQUE constraint failed: new_york_sat.dbn, new_york_sat.school_name\n"
     ]
    }
   ],
   "source": [
    "# what happens when we run this?\n",
    "try:\n",
    "    session = orm.Session(engine)\n",
    "    session.add(other_record)\n",
    "    session.commit()\n",
    "    session.close()\n",
    "except Exception as e:\n",
    "    print(e.args[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We get the error because we violated the primary key constraint. This can be a lifesaver because had we not added primary keys, we would silently introduce duplicate rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/josiahhounyo/anaconda3/envs/dsdbtest/lib/python3.7/site-packages/sqlalchemy/orm/session.py:552: SAWarning: Session's state has been changed on a non-active transaction - this state will be discarded.\n",
      "  \"Session's state has been changed on \"\n"
     ]
    }
   ],
   "source": [
    "# adding many records\n",
    "session.add_all([one_record, other_record])\n",
    "\n",
    "# sessions can be rolled back so they provide some safety\n",
    "session.rollback()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Adding the whole dataframe to the database. We already know that we can turn our dataframe into a list of records. We also must take of care `np.nan`s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'dbn': '01M292',\n",
       " 'school_name': 'Henry Street School for International Studies ',\n",
       " 'number_of_test_takers': 31.0,\n",
       " 'critical_reading_mean': 391.0,\n",
       " 'mathematics_mean': 425.0,\n",
       " 'writing_mean': 385.0}"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.to_dict(orient='records')[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<__main__.NewYorkCitySat at 0x11ba9f898>"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating records out of all the rows in the dataframe\n",
    "records_to_add = [NewYorkCitySat(**record) for record in df.to_dict(orient='records')]\n",
    "records_to_add[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<__main__.NewYorkCitySat at 0x11bc0b710>"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# remember that we had our __init__ method. we could do this mainly because we defined our columns in the same\n",
    "# order as the dataframe :)\n",
    "records_to_add_using_init = [NewYorkCitySat(*values) for values in df.values]\n",
    "records_to_add_using_init[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "records_to_add = [NewYorkCitySat(**record) for record in df.where(pd.notnull(df), None).to_dict(orient='records')]\n",
    "session = orm.Session(engine)\n",
    "session.add_all(records_to_add)\n",
    "session.commit()\n",
    "session.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Refactoring and Easy Maitenance\n",
    "\n",
    "My personal experience is that refactoring sql queries can be tedious. When you build a complex query that spans multiple lines, changing even one column in that string can cause major problems (or at least make debugging a nightmare). Building queries with the metadata classes on the other hand makes it easy to make changes to the query.\n",
    "\n",
    "Say I want to select all schools that are good. **Good means that critical reading mean is at least 466, mathematics mean is at least 489 and writing mean is at least 464**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>dbn</th>\n",
       "      <th>school_name</th>\n",
       "      <th>number_of_test_takers</th>\n",
       "      <th>critical_reading_mean</th>\n",
       "      <th>mathematics_mean</th>\n",
       "      <th>writing_mean</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>01M539</td>\n",
       "      <td>New Explorations into Sci, Tech and Math HS</td>\n",
       "      <td>47</td>\n",
       "      <td>568.0</td>\n",
       "      <td>583.0</td>\n",
       "      <td>568.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>01M696</td>\n",
       "      <td>BARD HIGH SCHOOL EARLY COLLEGE</td>\n",
       "      <td>138</td>\n",
       "      <td>630.0</td>\n",
       "      <td>608.0</td>\n",
       "      <td>630.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>02M411</td>\n",
       "      <td>Baruch College Campus High School</td>\n",
       "      <td>96</td>\n",
       "      <td>523.0</td>\n",
       "      <td>583.0</td>\n",
       "      <td>528.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>02M412</td>\n",
       "      <td>New York City Laboratory School Collab Studies</td>\n",
       "      <td>108</td>\n",
       "      <td>561.0</td>\n",
       "      <td>597.0</td>\n",
       "      <td>567.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>02M416</td>\n",
       "      <td>ELEANOR ROOSEVELT HIGH SCHOOL</td>\n",
       "      <td>122</td>\n",
       "      <td>555.0</td>\n",
       "      <td>596.0</td>\n",
       "      <td>567.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>02M418</td>\n",
       "      <td>Millennium High School</td>\n",
       "      <td>140</td>\n",
       "      <td>512.0</td>\n",
       "      <td>554.0</td>\n",
       "      <td>523.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>02M475</td>\n",
       "      <td>STUYVESANT HIGH SCHOOL</td>\n",
       "      <td>804</td>\n",
       "      <td>674.0</td>\n",
       "      <td>735.0</td>\n",
       "      <td>678.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>03M479</td>\n",
       "      <td>BEACON SCHOOL</td>\n",
       "      <td>237</td>\n",
       "      <td>573.0</td>\n",
       "      <td>563.0</td>\n",
       "      <td>575.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>03M485</td>\n",
       "      <td>LAGUARDIA HIGH SCH MUSIC ART</td>\n",
       "      <td>594</td>\n",
       "      <td>558.0</td>\n",
       "      <td>555.0</td>\n",
       "      <td>567.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>03M541</td>\n",
       "      <td>MANHATTAN/HUNTER COLL HS SCI</td>\n",
       "      <td>80</td>\n",
       "      <td>481.0</td>\n",
       "      <td>525.0</td>\n",
       "      <td>469.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>04M435</td>\n",
       "      <td>Manhattan Center for Science and Math</td>\n",
       "      <td>312</td>\n",
       "      <td>485.0</td>\n",
       "      <td>531.0</td>\n",
       "      <td>475.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>05M692</td>\n",
       "      <td>High School For Math Science Engineering City ...</td>\n",
       "      <td>106</td>\n",
       "      <td>592.0</td>\n",
       "      <td>627.0</td>\n",
       "      <td>575.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>10X445</td>\n",
       "      <td>BRONX HIGH SCHOOL OF SCIENCE</td>\n",
       "      <td>683</td>\n",
       "      <td>632.0</td>\n",
       "      <td>685.0</td>\n",
       "      <td>643.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>10X696</td>\n",
       "      <td>HS of American Studies at Lehman College</td>\n",
       "      <td>74</td>\n",
       "      <td>635.0</td>\n",
       "      <td>630.0</td>\n",
       "      <td>619.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>13K430</td>\n",
       "      <td>BROOKLYN TECHNICAL HIGH SCHOOL</td>\n",
       "      <td>1047</td>\n",
       "      <td>588.0</td>\n",
       "      <td>652.0</td>\n",
       "      <td>581.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>14K449</td>\n",
       "      <td>BROOKLYN LATIN SCHOOL</td>\n",
       "      <td>50</td>\n",
       "      <td>536.0</td>\n",
       "      <td>534.0</td>\n",
       "      <td>527.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>21K525</td>\n",
       "      <td>EDWARD R MURROW HIGH SCHOOL</td>\n",
       "      <td>686</td>\n",
       "      <td>472.0</td>\n",
       "      <td>503.0</td>\n",
       "      <td>468.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>22K405</td>\n",
       "      <td>Midwood High School at Brooklyn College</td>\n",
       "      <td>725</td>\n",
       "      <td>493.0</td>\n",
       "      <td>543.0</td>\n",
       "      <td>491.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>22K535</td>\n",
       "      <td>LEON M GOLDSTEIN HIGH SCHOOL</td>\n",
       "      <td>239</td>\n",
       "      <td>543.0</td>\n",
       "      <td>578.0</td>\n",
       "      <td>559.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>24Q299</td>\n",
       "      <td>BARD HIGH SCHOOL EARLY COLLEGE II</td>\n",
       "      <td>42</td>\n",
       "      <td>545.0</td>\n",
       "      <td>548.0</td>\n",
       "      <td>541.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>25Q525</td>\n",
       "      <td>Townsend Harris High School at Queens College</td>\n",
       "      <td>273</td>\n",
       "      <td>637.0</td>\n",
       "      <td>644.0</td>\n",
       "      <td>642.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>21</td>\n",
       "      <td>26Q415</td>\n",
       "      <td>BENJAMIN CARDOZO HIGH SCHOOL</td>\n",
       "      <td>697</td>\n",
       "      <td>486.0</td>\n",
       "      <td>551.0</td>\n",
       "      <td>492.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>26Q495</td>\n",
       "      <td>BAYSIDE HIGH SCHOOL</td>\n",
       "      <td>732</td>\n",
       "      <td>472.0</td>\n",
       "      <td>531.0</td>\n",
       "      <td>467.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>28Q680</td>\n",
       "      <td>Queens Gateway to Health Sciences Secondary Sc...</td>\n",
       "      <td>89</td>\n",
       "      <td>525.0</td>\n",
       "      <td>532.0</td>\n",
       "      <td>515.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24</td>\n",
       "      <td>28Q687</td>\n",
       "      <td>QUEENS HS FOR SCIENCE YORK COL</td>\n",
       "      <td>99</td>\n",
       "      <td>613.0</td>\n",
       "      <td>650.0</td>\n",
       "      <td>612.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25</td>\n",
       "      <td>30Q501</td>\n",
       "      <td>Frank Sinatra School of the Arts</td>\n",
       "      <td>153</td>\n",
       "      <td>506.0</td>\n",
       "      <td>495.0</td>\n",
       "      <td>499.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>26</td>\n",
       "      <td>30Q575</td>\n",
       "      <td>ACADEMY OF AMERICAN STUDIES</td>\n",
       "      <td>120</td>\n",
       "      <td>502.0</td>\n",
       "      <td>505.0</td>\n",
       "      <td>515.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27</td>\n",
       "      <td>30Q580</td>\n",
       "      <td>BACCALAUREATE SCH GLOBAL EDUC</td>\n",
       "      <td>64</td>\n",
       "      <td>560.0</td>\n",
       "      <td>587.0</td>\n",
       "      <td>570.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>31R080</td>\n",
       "      <td>MICHAEL J PETRIDES SCHOOL</td>\n",
       "      <td>86</td>\n",
       "      <td>508.0</td>\n",
       "      <td>523.0</td>\n",
       "      <td>502.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>29</td>\n",
       "      <td>31R605</td>\n",
       "      <td>STATEN ISLAND TECHNICAL HIGH SCHOOL</td>\n",
       "      <td>287</td>\n",
       "      <td>638.0</td>\n",
       "      <td>673.0</td>\n",
       "      <td>617.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       dbn                                        school_name  \\\n",
       "0   01M539       New Explorations into Sci, Tech and Math HS    \n",
       "1   01M696                    BARD HIGH SCHOOL EARLY COLLEGE    \n",
       "2   02M411                 Baruch College Campus High School    \n",
       "3   02M412    New York City Laboratory School Collab Studies    \n",
       "4   02M416                     ELEANOR ROOSEVELT HIGH SCHOOL    \n",
       "5   02M418                            Millennium High School    \n",
       "6   02M475                            STUYVESANT HIGH SCHOOL    \n",
       "7   03M479                                     BEACON SCHOOL    \n",
       "8   03M485                      LAGUARDIA HIGH SCH MUSIC ART    \n",
       "9   03M541                      MANHATTAN/HUNTER COLL HS SCI    \n",
       "10  04M435             Manhattan Center for Science and Math    \n",
       "11  05M692  High School For Math Science Engineering City ...   \n",
       "12  10X445                      BRONX HIGH SCHOOL OF SCIENCE    \n",
       "13  10X696          HS of American Studies at Lehman College    \n",
       "14  13K430                    BROOKLYN TECHNICAL HIGH SCHOOL    \n",
       "15  14K449                             BROOKLYN LATIN SCHOOL    \n",
       "16  21K525                       EDWARD R MURROW HIGH SCHOOL    \n",
       "17  22K405           Midwood High School at Brooklyn College    \n",
       "18  22K535                      LEON M GOLDSTEIN HIGH SCHOOL    \n",
       "19  24Q299                 BARD HIGH SCHOOL EARLY COLLEGE II    \n",
       "20  25Q525     Townsend Harris High School at Queens College    \n",
       "21  26Q415                      BENJAMIN CARDOZO HIGH SCHOOL    \n",
       "22  26Q495                               BAYSIDE HIGH SCHOOL    \n",
       "23  28Q680  Queens Gateway to Health Sciences Secondary Sc...   \n",
       "24  28Q687                    QUEENS HS FOR SCIENCE YORK COL    \n",
       "25  30Q501                  Frank Sinatra School of the Arts    \n",
       "26  30Q575                       ACADEMY OF AMERICAN STUDIES    \n",
       "27  30Q580                     BACCALAUREATE SCH GLOBAL EDUC    \n",
       "28  31R080                         MICHAEL J PETRIDES SCHOOL    \n",
       "29  31R605               STATEN ISLAND TECHNICAL HIGH SCHOOL    \n",
       "\n",
       "    number_of_test_takers  critical_reading_mean  mathematics_mean  \\\n",
       "0                      47                  568.0             583.0   \n",
       "1                     138                  630.0             608.0   \n",
       "2                      96                  523.0             583.0   \n",
       "3                     108                  561.0             597.0   \n",
       "4                     122                  555.0             596.0   \n",
       "5                     140                  512.0             554.0   \n",
       "6                     804                  674.0             735.0   \n",
       "7                     237                  573.0             563.0   \n",
       "8                     594                  558.0             555.0   \n",
       "9                      80                  481.0             525.0   \n",
       "10                    312                  485.0             531.0   \n",
       "11                    106                  592.0             627.0   \n",
       "12                    683                  632.0             685.0   \n",
       "13                     74                  635.0             630.0   \n",
       "14                   1047                  588.0             652.0   \n",
       "15                     50                  536.0             534.0   \n",
       "16                    686                  472.0             503.0   \n",
       "17                    725                  493.0             543.0   \n",
       "18                    239                  543.0             578.0   \n",
       "19                     42                  545.0             548.0   \n",
       "20                    273                  637.0             644.0   \n",
       "21                    697                  486.0             551.0   \n",
       "22                    732                  472.0             531.0   \n",
       "23                     89                  525.0             532.0   \n",
       "24                     99                  613.0             650.0   \n",
       "25                    153                  506.0             495.0   \n",
       "26                    120                  502.0             505.0   \n",
       "27                     64                  560.0             587.0   \n",
       "28                     86                  508.0             523.0   \n",
       "29                    287                  638.0             673.0   \n",
       "\n",
       "    writing_mean  \n",
       "0          568.0  \n",
       "1          630.0  \n",
       "2          528.0  \n",
       "3          567.0  \n",
       "4          567.0  \n",
       "5          523.0  \n",
       "6          678.0  \n",
       "7          575.0  \n",
       "8          567.0  \n",
       "9          469.0  \n",
       "10         475.0  \n",
       "11         575.0  \n",
       "12         643.0  \n",
       "13         619.0  \n",
       "14         581.0  \n",
       "15         527.0  \n",
       "16         468.0  \n",
       "17         491.0  \n",
       "18         559.0  \n",
       "19         541.0  \n",
       "20         642.0  \n",
       "21         492.0  \n",
       "22         467.0  \n",
       "23         515.0  \n",
       "24         612.0  \n",
       "25         499.0  \n",
       "26         515.0  \n",
       "27         570.0  \n",
       "28         502.0  \n",
       "29         617.0  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "select * from new_york_sat where critical_reading_mean >= 466 and mathematics_mean >= 489\n",
    "and writing_mean >= 464\n",
    "\"\"\"\n",
    "\n",
    "pd.read_sql(sql, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "new_york_sat.critical_reading_mean >= :critical_reading_mean_1 AND new_york_sat.mathematics_mean >= :mathematics_mean_1 AND new_york_sat.writing_mean >= :writing_mean_1\n"
     ]
    }
   ],
   "source": [
    "# now let's look at the good_school expression we created. It is exactly what we would normally write\n",
    "# each time we have to use the condition as a filter\n",
    "print(NewYorkCitySat.good_school.expression)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT new_york_sat.dbn, new_york_sat.school_name, new_york_sat.number_of_test_takers, new_york_sat.critical_reading_mean, new_york_sat.mathematics_mean, new_york_sat.writing_mean \n",
      "FROM new_york_sat \n",
      "WHERE new_york_sat.critical_reading_mean >= :critical_reading_mean_1 AND new_york_sat.mathematics_mean >= :mathematics_mean_1 AND new_york_sat.writing_mean >= :writing_mean_1\n"
     ]
    }
   ],
   "source": [
    "# Create a query using goog_school filter - as mentioned, the threshold could change and case statements added\n",
    "# we just define the good_school condition at that one place and make use of it everywhere that needs it\n",
    "\n",
    "session = orm.Session()\n",
    "query = session.query(NewYorkCitySat).filter(NewYorkCitySat.good_school)\n",
    "print(query.statement)\n",
    "\n",
    "# If I had a models.py with the class defined, I could update my good_school property\n",
    "# and all my code that depend on it will remain the same"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(30, 6)"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(query.statement, engine).shape # equivalent to len(query.all()) with session bound to engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>dbn</th>\n",
       "      <th>school_name</th>\n",
       "      <th>number_of_test_takers</th>\n",
       "      <th>critical_reading_mean</th>\n",
       "      <th>mathematics_mean</th>\n",
       "      <th>writing_mean</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>01M539</td>\n",
       "      <td>New Explorations into Sci, Tech and Math HS</td>\n",
       "      <td>47</td>\n",
       "      <td>568.0</td>\n",
       "      <td>583.0</td>\n",
       "      <td>568.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>01M696</td>\n",
       "      <td>BARD HIGH SCHOOL EARLY COLLEGE</td>\n",
       "      <td>138</td>\n",
       "      <td>630.0</td>\n",
       "      <td>608.0</td>\n",
       "      <td>630.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>02M411</td>\n",
       "      <td>Baruch College Campus High School</td>\n",
       "      <td>96</td>\n",
       "      <td>523.0</td>\n",
       "      <td>583.0</td>\n",
       "      <td>528.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>02M412</td>\n",
       "      <td>New York City Laboratory School Collab Studies</td>\n",
       "      <td>108</td>\n",
       "      <td>561.0</td>\n",
       "      <td>597.0</td>\n",
       "      <td>567.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>02M416</td>\n",
       "      <td>ELEANOR ROOSEVELT HIGH SCHOOL</td>\n",
       "      <td>122</td>\n",
       "      <td>555.0</td>\n",
       "      <td>596.0</td>\n",
       "      <td>567.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      dbn                                      school_name  \\\n",
       "0  01M539     New Explorations into Sci, Tech and Math HS    \n",
       "1  01M696                  BARD HIGH SCHOOL EARLY COLLEGE    \n",
       "2  02M411               Baruch College Campus High School    \n",
       "3  02M412  New York City Laboratory School Collab Studies    \n",
       "4  02M416                   ELEANOR ROOSEVELT HIGH SCHOOL    \n",
       "\n",
       "   number_of_test_takers  critical_reading_mean  mathematics_mean  \\\n",
       "0                     47                  568.0             583.0   \n",
       "1                    138                  630.0             608.0   \n",
       "2                     96                  523.0             583.0   \n",
       "3                    108                  561.0             597.0   \n",
       "4                    122                  555.0             596.0   \n",
       "\n",
       "   writing_mean  \n",
       "0         568.0  \n",
       "1         630.0  \n",
       "2         528.0  \n",
       "3         567.0  \n",
       "4         567.0  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(query.statement, engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If I had more queries all based on that one filtering condition, once the condition changes and I have to involve things like case statements; I will have to remember where I used the condition and properly adjust it everywhere. I can easily make mistakes and have unbalanced parentheses etc.\n",
    "\n",
    "But with the ORM defined with the filtering as a property, I can focus on changing the condition as I see fit and all queries that use it will work well"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "if db_file.exists():\n",
    "    engine.dispose()\n",
    "    db_file.unlink()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
