intro_sqlalchemy.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1>Introduction to <i style=\"color:red;\">SQLAlchemy</i></h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## <i>by Jack Camier, Python Developer</i>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<img src=\"sqla_logo.png\" width=\"300\"/>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%HTML\n",
    "<img src=\"sqla_logo.png\" width=\"300\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SQLAlchemy is well documented at: https://www.sqlalchemy.org/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2>created by <i style=\"color:blue;\">Mike Bayer</i></h2>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "https://github.com/zzzeek"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## There are a lot of tutorials on the subject, honestly too many to count.\n",
    "## I hope to provide a tutorial that provides, some cookbook recipes (code snippets) and a little bit more visualization via a Jupyter Notebook to help developers better grasp the concepts than what I have found on the internet\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## \"SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL\" - Mike Bayer\n",
    "https://www.sqlalchemy.org/\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Our tutorial today will be using SQLite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<img src=\"sqlite_logo.gif\" width=\"200\"/>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%HTML\n",
    "<img src=\"sqlite_logo.gif\" width=\"200\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "https://www.sqlite.org/index.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The reason being you won't have to download or install anything in regards to a database perspective to do this tutorial \n",
    "## Also, `sqlite3` comes built-in with standard `Cpython`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3 # That's it!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## \"SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day.\"\n",
    "https://www.sqlite.org/index.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now you could do things like the following to work with sqlite using the standard library `sqlite3` such as:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'id': '7804be65-26fa-4b0a-9fa0-5dc9d58c5dcd',\n",
       " 'first_name': 'Bill',\n",
       " 'last_name': 'Smith',\n",
       " 'email': 'b.smith@mail.com'}"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import uuid\n",
    "\n",
    "sql_values = {\n",
    "\"id\": str(uuid.uuid4()),\n",
    "\"first_name\": \"Bill\",\n",
    "\"last_name\": \"Smith\",\n",
    "\"email\": \"b.smith@mail.com\"\n",
    "}\n",
    "\n",
    "sql_values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x10609ef80>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create connection session and database\n",
    "conn = sqlite3.connect('example.db')\n",
    "\n",
    "# Create cursor and assign it to `c`\n",
    "c = conn.cursor()\n",
    "\n",
    "# Create table using raw SQL\n",
    "c.execute('''CREATE TABLE users (id text, first_name text, last_name text, email text)''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Insert a row of data \n",
    "c.execute('INSERT INTO users VALUES (:id, :first_name, :last_name, :email)', sql_values)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('7804be65-26fa-4b0a-9fa0-5dc9d58c5dcd', 'Bill', 'Smith', 'b.smith@mail.com')\n"
     ]
    }
   ],
   "source": [
    "# Select the data and print out row results\n",
    "sql = c.execute('''SELECT * from users''')\n",
    "for row in sql:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove temporary `example.db` database\n",
    "import os\n",
    "os.remove(\"example.db\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## For simple things like this, we probably don't need SQLAlchemy. But normally within a short period of time, requirments of a project or feautures of a product change rapidly and we need to do something more like:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"\\nselect \\n  'today - ' || to_char(trunc(sysdate),'Mon FMDDFM'),\\n  trunc(sysdate) as deadline\\nfrom dual \\nUNION\\nselect \\n  'tomorrow - '|| to_char(trunc(sysdate+1),'Mon FMDDFM'), \\n  trunc(sysdate+1) as deadline\\nfrom dual \\nUNION\\nselect\\n  'next week - '|| to_char(trunc(sysdate+7),'Mon FMDDFM'), \\n  trunc(sysdate+7) as deadline\\nfrom dual \\nUNION\\nselect \\n  'next month - '|| to_char(trunc(ADD_MONTHS(sysdate,1)),'Mon FMDDFM'), \\n  trunc(ADD_MONTHS(sysdate,1)) as deadline \\nfrom dual\\nUNION\\nselect \\n  name || ' - ' || to_char(deadline, 'Mon FMDDFM'), \\n  deadline\\nfrom ticket_deadlines \\nwhere project_id = :project_id\\nand deadline >= trunc(sysdate)\\norder by deadline\\n\""
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"\n",
    "select \n",
    "  'today - ' || to_char(trunc(sysdate),'Mon FMDDFM'),\n",
    "  trunc(sysdate) as deadline\n",
    "from dual \n",
    "UNION\n",
    "select \n",
    "  'tomorrow - '|| to_char(trunc(sysdate+1),'Mon FMDDFM'), \n",
    "  trunc(sysdate+1) as deadline\n",
    "from dual \n",
    "UNION\n",
    "select\n",
    "  'next week - '|| to_char(trunc(sysdate+7),'Mon FMDDFM'), \n",
    "  trunc(sysdate+7) as deadline\n",
    "from dual \n",
    "UNION\n",
    "select \n",
    "  'next month - '|| to_char(trunc(ADD_MONTHS(sysdate,1)),'Mon FMDDFM'), \n",
    "  trunc(ADD_MONTHS(sysdate,1)) as deadline \n",
    "from dual\n",
    "UNION\n",
    "select \n",
    "  name || ' - ' || to_char(deadline, 'Mon FMDDFM'), \n",
    "  deadline\n",
    "from ticket_deadlines \n",
    "where project_id = :project_id\n",
    "and deadline >= trunc(sysdate)\n",
    "order by deadline\n",
    "\"\"\"\n",
    "# https://philip.greenspun.com/sql/complex-queries.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## \"I wrote a reporting query once that was 700 lines long and visited 27 different tables in lookups or joins\" - Greg Kemnitz\n",
    "https://www.quora.com/Whats-the-most-complex-SQL-query-you-ever-wrote"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Figuring out how to do this in raw SQL can be very challenging if you are not a DBA or well versed in SQL syntax.\n",
    "## With SQLAlchemy we can use the abstraction layer and write python code we are more familiar with and let the toolkit do the SQL magic behind the scenes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## First there are two main components of SQLAlchemy:\n",
    "## \"SQLAlchemy consists of two distinct components, known as the <i style=\"color:red;\">Core</i> and the <i style=\"color:red;\">ORM</i>. \n",
    "## The Core is itself a fully featured SQL abstraction toolkit, providing a smooth layer of abstraction over a wide variety of DBAPI implementations and behaviors, as well as a SQL Expression Language which allows expression of the SQL language via generative Python expressions.\"\n",
    "\n",
    "## \"The Object Relational Mapper (ORM) is then an optional package which builds upon the Core.\"\n",
    "https://www.sqlalchemy.org/features.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<img src=\"sqla_arch_small.png\" width=\"500\"/>\n",
       "\n",
       "https://docs.sqlalchemy.org/en/13/intro.html\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%HTML\n",
    "<img src=\"sqla_arch_small.png\" width=\"500\"/>\n",
    "\n",
    "https://docs.sqlalchemy.org/en/13/intro.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## To get sqlalchemy installed, all you have to do is:\n",
    "`pip install SQLAlchemy`\n",
    "## Then"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## In this talk, I am using version:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1.3.9'"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sqlalchemy.__version__"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# To get started, we will commence looking at the Core component of SQLAlchemy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Please note that individual database flavors, also called `dialects`, enumerate the available DBAPIs for each database\n",
    "## Most seasoned developers learn that each database has its own nuances and slight variations of SQL syntax depending on the database. \n",
    "## Here are a list of popular `dialects` for SQLAlchemy:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# - PostgreSQL\n",
    "https://docs.sqlalchemy.org/en/13/dialects/postgresql.html\n",
    "# - MySQL\n",
    "https://docs.sqlalchemy.org/en/13/dialects/mysql.html\n",
    "# - SQLite\n",
    "https://docs.sqlalchemy.org/en/13/dialects/sqlite.html\n",
    "# - Oracle\n",
    "https://docs.sqlalchemy.org/en/13/dialects/oracle.html\n",
    "# - Microsoft SQL Server\n",
    "https://docs.sqlalchemy.org/en/13/dialects/mssql.html\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Since our focus will be SQLite, here are the main data type affinities:\n",
    "* TEXT\n",
    "* NUMERIC\n",
    "* INTEGER\n",
    "* REAL\n",
    "* BLOB\n",
    "\n",
    "https://www.sqlite.org/datatype3.html#affinity\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## \"The SQLAlchemy Expression Language presents a system of representing relational database structures and expressions using Python constructs. These constructs are modeled to resemble those of the underlying database as closely as possible, while providing a modicum of abstraction of the various implementation differences between database backends.\"\n",
    "https://docs.sqlalchemy.org/en/13/core/tutorial.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  <i style=\"color:red;\">Huh!?</i> Translation:\n",
    "## `SQLAlchemy Expression Language` is a way to represent everything dealing with a database using Python. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## For this tutorial we will use an in-memory-only SQLite database. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "\n",
    "# To make the connection to the sqlite database\n",
    "engine = create_engine('sqlite:///:memory:', echo=True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The `echo` flag is a shortcut to setting up SQLAlchemy logging using Python’s standard logging module.\n",
    "## With it enabled, we’ll see all the generated SQL scripts produced. I believe this is very helpful to see visually what is being done behind the scenes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## \"The `engine` is the starting point for any SQLAlchemy application. It’s “home base” for the actual database and its DBAPI, delivered to the SQLAlchemy application through a connection pool and a Dialect...\"\n",
    "https://docs.sqlalchemy.org/en/13/core/engines.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<img src=\"sqla_engine_arch.png\" width=\"700\"/>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%HTML\n",
    "<img src=\"sqla_engine_arch.png\" width=\"700\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## To use the `create_engine` method to an actual sqlite database, here are some examples:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# where <path> is relative:\n",
    "engine = create_engine('sqlite:///foo.db')\n",
    "\n",
    "# Linux/Mac - 4 initial slashes in total\n",
    "engine = create_engine('sqlite:////absolute/path/to/foo.db')\n",
    "\n",
    "# Windows\n",
    "engine = create_engine('sqlite:///C:\\\\path\\\\to\\\\foo.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/Users/jacquescamier/JupyterProjects'"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## I personally like to do something more like:\n",
    "\n",
    "PROJECT_DIR = os.path.dirname(os.path.abspath(''))\n",
    "\n",
    "# Note you can also do:\n",
    "\n",
    "# os.path.dirname(os.path.abspath(__file__))\n",
    "\n",
    "# But this only works with a python .py file, not in Jupyter notebook .ipynb\n",
    "\n",
    "PROJECT_DIR"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Note: the file path will be different for you since `os.path.dirname` etc. is specific to your computer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'sqlite:////Users/jacquescamier/JupyterProjects/your_database.db'"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "database_path = os.path.join(PROJECT_DIR, 'your_database.db')\n",
    "sqlite_path = f\"sqlite:///{database_path}\"\n",
    "\n",
    "sqlite_path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Engine(sqlite:////Users/jacquescamier/JupyterProjects/your_database.db)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine = create_engine(sqlite_path)\n",
    "\n",
    "engine"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's go back to our in-memory example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine('sqlite:///:memory:', echo=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now let's import the necessary objects to build a table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey, Boolean"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## A collection of metadata entities is stored in an object called MetaData\n",
    "## \"MetaData is a container object that keeps together many different features of a database (or multiple databases) being described\"\n",
    "https://docs.sqlalchemy.org/en/13/core/metadata.html#metadata-describing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Instantiate the MetaData object\n",
    "metadata = MetaData()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define the `users` table:\n",
    "users = Table('users', metadata,\n",
    "              Column('id', Integer, primary_key=True),\n",
    "              Column('first_name', String),\n",
    "              Column('last_name', String),\n",
    "             )\n",
    "\n",
    "# Define the `email` table:\n",
    "email = Table('email', metadata,\n",
    "                 Column('id', Integer, primary_key=True),\n",
    "                 Column('user_id', None, ForeignKey('users.id')),\n",
    "                 Column('email_address', String, nullable=False),\n",
    "                 Column('is_active', Boolean, default=True)\n",
    "                 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:44:42,673 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1\n",
      "2020-04-19 15:44:42,674 INFO sqlalchemy.engine.base.Engine ()\n",
      "2020-04-19 15:44:42,676 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1\n",
      "2020-04-19 15:44:42,676 INFO sqlalchemy.engine.base.Engine ()\n",
      "2020-04-19 15:44:42,678 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(\"users\")\n",
      "2020-04-19 15:44:42,679 INFO sqlalchemy.engine.base.Engine ()\n",
      "2020-04-19 15:44:42,680 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info(\"users\")\n",
      "2020-04-19 15:44:42,681 INFO sqlalchemy.engine.base.Engine ()\n",
      "2020-04-19 15:44:42,682 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(\"email\")\n",
      "2020-04-19 15:44:42,683 INFO sqlalchemy.engine.base.Engine ()\n",
      "2020-04-19 15:44:42,684 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info(\"email\")\n",
      "2020-04-19 15:44:42,684 INFO sqlalchemy.engine.base.Engine ()\n",
      "2020-04-19 15:44:42,686 INFO sqlalchemy.engine.base.Engine \n",
      "CREATE TABLE users (\n",
      "\tid INTEGER NOT NULL, \n",
      "\tfirst_name VARCHAR, \n",
      "\tlast_name VARCHAR, \n",
      "\tPRIMARY KEY (id)\n",
      ")\n",
      "\n",
      "\n",
      "2020-04-19 15:44:42,686 INFO sqlalchemy.engine.base.Engine ()\n",
      "2020-04-19 15:44:42,687 INFO sqlalchemy.engine.base.Engine COMMIT\n",
      "2020-04-19 15:44:42,688 INFO sqlalchemy.engine.base.Engine \n",
      "CREATE TABLE email (\n",
      "\tid INTEGER NOT NULL, \n",
      "\tuser_id INTEGER, \n",
      "\temail_address VARCHAR NOT NULL, \n",
      "\tis_active BOOLEAN, \n",
      "\tPRIMARY KEY (id), \n",
      "\tFOREIGN KEY(user_id) REFERENCES users (id), \n",
      "\tCHECK (is_active IN (0, 1))\n",
      ")\n",
      "\n",
      "\n",
      "2020-04-19 15:44:42,689 INFO sqlalchemy.engine.base.Engine ()\n",
      "2020-04-19 15:44:42,690 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "# Create the tables:\n",
    "metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ^ As you can see SQLAlchemy did a lot behind the scenes from our few lines of Python code"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's insert some data into our table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "ins = users.insert().values(first_name='Bill', last_name='Smith')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## To see the insert SQL statement, we can use the str() function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'INSERT INTO users (first_name, last_name) VALUES (:first_name, :last_name)'"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "str(ins)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## To make a connection to the database, we use the `connect` method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = engine.connect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now, let's pass the our assigned SQL insert `ins` to the connection\n",
    "## This is where it actual writes to the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:44:46,509 INFO sqlalchemy.engine.base.Engine INSERT INTO users (first_name, last_name) VALUES (?, ?)\n",
      "2020-04-19 15:44:46,511 INFO sqlalchemy.engine.base.Engine ('Bill', 'Smith')\n",
      "2020-04-19 15:44:46,512 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "result = conn.execute(ins)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's insert another record in the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "ins = users.insert().values(first_name='Sally', last_name='Jackson')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:44:47,874 INFO sqlalchemy.engine.base.Engine INSERT INTO users (first_name, last_name) VALUES (?, ?)\n",
      "2020-04-19 15:44:47,875 INFO sqlalchemy.engine.base.Engine ('Sally', 'Jackson')\n",
      "2020-04-19 15:44:47,877 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "result = conn.execute(ins)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now let's select the data from the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:44:48,764 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.first_name, users.last_name \n",
      "FROM users\n",
      "2020-04-19 15:44:48,765 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy.sql import select\n",
    "\n",
    "select_users = select([users])\n",
    "\n",
    "result = conn.execute(select_users)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'Bill', 'Smith')\n",
      "(2, 'Sally', 'Jackson')\n"
     ]
    }
   ],
   "source": [
    "# Iterate through the results:\n",
    "\n",
    "for row in result:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## In most cases, you are going to want to insert several records at a time by passing a list dictionaries:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:44:50,050 INFO sqlalchemy.engine.base.Engine INSERT INTO email (user_id, email_address, is_active) VALUES (?, ?, ?)\n",
      "2020-04-19 15:44:50,051 INFO sqlalchemy.engine.base.Engine ((1, 'billsmith@somemail.com', 1), (2, 's.jackson@somemail.com', 1))\n",
      "2020-04-19 15:44:50,053 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x106592490>"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# You can pass a list of dictionaries\n",
    "conn.execute(email.insert(), [\n",
    "    {'user_id': 1, 'email_address' : 'billsmith@somemail.com'},\n",
    "    {'user_id': 2, 'email_address' : 's.jackson@somemail.com'},\n",
    "    ])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# To do a select statement to get one record:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:44:50,956 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.first_name, users.last_name \n",
      "FROM users\n",
      "2020-04-19 15:44:50,957 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    }
   ],
   "source": [
    "result = conn.execute(select_users)\n",
    "\n",
    "row = result.fetchone()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'Bill', 'Smith')\n"
     ]
    }
   ],
   "source": [
    "print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using a list comprehension:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:44:52,302 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.first_name, users.last_name \n",
      "FROM users\n",
      "2020-04-19 15:44:52,303 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    }
   ],
   "source": [
    "list_rows = [row for row in conn.execute(select_users)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1, 'Bill', 'Smith')"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Print first record\n",
    "list_rows[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# You can get specific `columns` by using the `c` attribute"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:44:53,652 INFO sqlalchemy.engine.base.Engine SELECT users.first_name \n",
      "FROM users\n",
      "2020-04-19 15:44:53,654 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('Bill',), ('Sally',)]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select_first_names = select([users.c.first_name])\n",
    "\n",
    "[row for row in conn.execute(select_first_names)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# We can do some unpacking magic and get rid of the tuple, notice the `,` after the `for row` expression"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:44:54,499 INFO sqlalchemy.engine.base.Engine SELECT users.first_name \n",
      "FROM users\n",
      "2020-04-19 15:44:54,500 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['Bill', 'Sally']"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[row for row, in conn.execute(select_first_names)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Please recall that we declared a `ForeignKey` relationship between the users and email tables:\n",
    "`Column('user_id', None, ForeignKey('users.id'))`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## This sets us up to do joins such as:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:44:55,810 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.first_name, users.last_name, email.id, email.user_id, email.email_address, email.is_active \n",
      "FROM users, email \n",
      "WHERE users.id = email.user_id\n",
      "2020-04-19 15:44:55,811 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'Bill', 'Smith', 1, 1, 'billsmith@somemail.com', True),\n",
       " (2, 'Sally', 'Jackson', 2, 2, 's.jackson@somemail.com', True)]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select_join_user_and_email = select([users, email]).where(users.c.id == email.c.user_id)\n",
    "\n",
    "[row for row in conn.execute(select_join_user_and_email)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Here is another way to do joins, albeit a bit more complicated"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:44:56,661 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.first_name, users.last_name, email.id, email.user_id, email.email_address, email.is_active \n",
      "FROM users JOIN email ON users.id = email.user_id\n",
      "2020-04-19 15:44:56,662 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'Bill', 'Smith', 1, 1, 'billsmith@somemail.com', True),\n",
       " (2, 'Sally', 'Jackson', 2, 2, 's.jackson@somemail.com', True)]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import join\n",
    "\n",
    "j = users.join(email, users.c.id == email.c.user_id)\n",
    "select_users_email_join = select([users, email]).select_from(j)\n",
    "\n",
    "[row for row in conn.execute(select_users_email_join)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now Let's load some data from a csv file and write to the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'first_name': 'Jerome', 'last_name': 'Walters'},\n",
       " {'first_name': 'Rick', 'last_name': 'Stein'},\n",
       " {'first_name': 'Sam', 'last_name': 'West'},\n",
       " {'first_name': 'Jill', 'last_name': 'Smith'},\n",
       " {'first_name': 'Jan', 'last_name': 'West'},\n",
       " {'first_name': 'Linda', 'last_name': 'Rodriguez'}]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import csv\n",
    "\n",
    "with open('users_data.csv', mode='r') as user_file:\n",
    "    reader = csv.reader(user_file, delimiter=',')\n",
    "    headers = next(reader, None)\n",
    "    import_list = [{headers[0]: row[0], headers[1]: row[1]} for idx, row in enumerate(reader)]\n",
    "    \n",
    "\n",
    "import_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:44:57,931 INFO sqlalchemy.engine.base.Engine INSERT INTO users (first_name, last_name) VALUES (?, ?)\n",
      "2020-04-19 15:44:57,932 INFO sqlalchemy.engine.base.Engine (('Jerome', 'Walters'), ('Rick', 'Stein'), ('Sam', 'West'), ('Jill', 'Smith'), ('Jan', 'West'), ('Linda', 'Rodriguez'))\n",
      "2020-04-19 15:44:57,933 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x1065a0050>"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Insert rows to database\n",
    "conn.execute(users.insert(), import_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:44:58,394 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.first_name, users.last_name \n",
      "FROM users\n",
      "2020-04-19 15:44:58,395 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'Bill', 'Smith'),\n",
       " (2, 'Sally', 'Jackson'),\n",
       " (3, 'Jerome', 'Walters'),\n",
       " (4, 'Rick', 'Stein'),\n",
       " (5, 'Sam', 'West'),\n",
       " (6, 'Jill', 'Smith'),\n",
       " (7, 'Jan', 'West'),\n",
       " (8, 'Linda', 'Rodriguez')]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select all users\n",
    "result = conn.execute(select([users]))\n",
    "\n",
    "[row for row in result]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Select all users where last name is Smith"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:44:59,309 INFO sqlalchemy.engine.base.Engine SELECT users.last_name || ? || users.first_name AS anon_1 \n",
      "FROM users \n",
      "WHERE users.last_name = ?\n",
      "2020-04-19 15:44:59,310 INFO sqlalchemy.engine.base.Engine (', ', 'Smith')\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('Smith, Bill',), ('Smith, Jill',)]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = select([(users.c.last_name + \", \" + users.c.first_name)]).where(users.c.last_name==\"Smith\")\n",
    "\n",
    "conn.execute(s).fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's import more csv data from the `email_data.csv` file to do some more interesting filtering (where clauses)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'user_id': '3', 'email_address': 'j.walters@gmail.com'},\n",
       " {'user_id': '4', 'email_address': 'ricky.stein1@gmail.com'},\n",
       " {'user_id': '5', 'email_address': 's.w@somemail.com'},\n",
       " {'user_id': '6', 'email_address': 'jill.smith@somemail.com'},\n",
       " {'user_id': '7', 'email_address': 'j.west@gmail.com'},\n",
       " {'user_id': '8', 'email_address': 'linda123@somemail.com'}]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open('email_data.csv', mode='r') as email_file:\n",
    "    reader = csv.reader(email_file, delimiter=',')\n",
    "    headers = next(reader, None)\n",
    "    email_import_list = [{headers[0]: row[0], headers[1]: row[1]} for idx, row in enumerate(reader)]\n",
    "    \n",
    "\n",
    "email_import_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:45:00,649 INFO sqlalchemy.engine.base.Engine INSERT INTO email (user_id, email_address, is_active) VALUES (?, ?, ?)\n",
      "2020-04-19 15:45:00,651 INFO sqlalchemy.engine.base.Engine (('3', 'j.walters@gmail.com', 1), ('4', 'ricky.stein1@gmail.com', 1), ('5', 's.w@somemail.com', 1), ('6', 'jill.smith@somemail.com', 1), ('7', 'j.west@gmail.com', 1), ('8', 'linda123@somemail.com', 1))\n",
      "2020-04-19 15:45:00,652 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x106592d50>"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Insert rows to database\n",
    "conn.execute(email.insert(), email_import_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using our previous where clause script"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:45:01,521 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.first_name, users.last_name, email.id, email.user_id, email.email_address, email.is_active \n",
      "FROM users, email \n",
      "WHERE users.id = email.user_id\n",
      "2020-04-19 15:45:01,523 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(1, 'Bill', 'Smith', 1, 1, 'billsmith@somemail.com', True),\n",
       " (2, 'Sally', 'Jackson', 2, 2, 's.jackson@somemail.com', True),\n",
       " (3, 'Jerome', 'Walters', 3, 3, 'j.walters@gmail.com', True),\n",
       " (4, 'Rick', 'Stein', 4, 4, 'ricky.stein1@gmail.com', True),\n",
       " (5, 'Sam', 'West', 5, 5, 's.w@somemail.com', True),\n",
       " (6, 'Jill', 'Smith', 6, 6, 'jill.smith@somemail.com', True),\n",
       " (7, 'Jan', 'West', 7, 7, 'j.west@gmail.com', True),\n",
       " (8, 'Linda', 'Rodriguez', 8, 8, 'linda123@somemail.com', True)]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select_join_user_and_email = select([users, email]).where(users.c.id == email.c.user_id)\n",
    "\n",
    "[row for row in conn.execute(select_join_user_and_email)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## This might be a good time to introduce the ORM (Object Relational Mapper)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## \"The SQLAlchemy Object Relational Mapper presents a method of associating user-defined Python classes with database tables, and instances of those classes (objects) with rows in their corresponding tables. It includes a system that transparently synchronizes all changes in state between objects and their related rows, called a unit of work, as well as a system for expressing database queries in terms of the user defined classes and their defined relationships between each other.\"\n",
    "https://docs.sqlalchemy.org/en/13/orm/tutorial.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Think of it like another approach to use SQLAlchemy that provides a nice abstraction layer. I personally prefer the ORM over the core especially when it comes to querying the database. \n",
    "\n",
    "## *Remember: it is built on top of the Core*\n",
    "## We already made the engine instance:\n",
    "    engine = create_engine('sqlite:///:memory:', echo=True)\n",
    "## So the next step is to create the mapper: `declarative_base`, a construct for the base class of declarative class definitions.\n",
    "https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/api.html#sqlalchemy.ext.declarative.declarative_base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "\n",
    "Base = declarative_base()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Don't worry too much about what it is, but just remember you need to instantiate this to use the ORM when working with tables\n",
    "\n",
    "## Now we can create another table such as:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import Column, Integer, String\n",
    "\n",
    "class Addresses(Base): # Notice that `Address` inherits from Base which is an instance of `declarative_base`\n",
    "    __tablename__ = 'addresses'\n",
    "    \n",
    "    id = Column(Integer, primary_key=True)\n",
    "    user_id = Column('user_id', None, ForeignKey('users.id'))\n",
    "    street = Column('street', String)\n",
    "    city = Column('city', String)\n",
    "    state = Column('state', String)\n",
    "    zip_code = Column('zip_code', String)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Note a class using Declarative at a minimum needs a `__tablename__` attribute, and at least one `Column` which is part of a primary key."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now for the key `Session` object we need to instantiate for querying:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy.orm import sessionmaker\n",
    "\n",
    "Session = sessionmaker(bind=engine) # define a Session class which is a factory for new Session objects\n",
    "\n",
    "session = Session()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using the `session` object we now `INSERT` records using the `add` method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "'Table' object is not callable",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-51-3be898cadb0c>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0med_user\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0musers\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfirst_name\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'Ed'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mlast_name\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'Jones'\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      2\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0msession\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0madd\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0med_user\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mTypeError\u001b[0m: 'Table' object is not callable"
     ]
    }
   ],
   "source": [
    "ed_user = users(first_name='Ed', last_name='Jones')\n",
    "\n",
    "session.add(ed_user) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ....Not quite... we unfortuantely, need to create the `Users` class object again, inheriting from `Base` this time. We should aslo create the `Email` class object in order to take advantage of the ORM's functionality."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "class Users(Base):\n",
    "    __tablename__ = 'users'\n",
    "    \n",
    "    id = Column(Integer, primary_key=True)\n",
    "    first_name = Column('first_name', String)\n",
    "    last_name = Column('last_name', String)\n",
    "    \n",
    "    \n",
    "class Email(Base):\n",
    "    __tablename__ = 'email'\n",
    "    \n",
    "    id = Column(Integer, primary_key=True)\n",
    "    user_id= Column('user_id', None, ForeignKey('users.id'))\n",
    "    email_address = Column('email_address', String, nullable=False)\n",
    "    is_active = Column('is_active', Boolean, default=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now we can use the `metadata.create_all` method of the `Base` instance to create the table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:45:08,759 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(\"addresses\")\n",
      "2020-04-19 15:45:08,760 INFO sqlalchemy.engine.base.Engine ()\n",
      "2020-04-19 15:45:08,762 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info(\"addresses\")\n",
      "2020-04-19 15:45:08,762 INFO sqlalchemy.engine.base.Engine ()\n",
      "2020-04-19 15:45:08,764 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(\"users\")\n",
      "2020-04-19 15:45:08,765 INFO sqlalchemy.engine.base.Engine ()\n",
      "2020-04-19 15:45:08,766 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(\"email\")\n",
      "2020-04-19 15:45:08,766 INFO sqlalchemy.engine.base.Engine ()\n",
      "2020-04-19 15:45:08,768 INFO sqlalchemy.engine.base.Engine \n",
      "CREATE TABLE addresses (\n",
      "\tid INTEGER NOT NULL, \n",
      "\tuser_id INTEGER, \n",
      "\tstreet VARCHAR, \n",
      "\tcity VARCHAR, \n",
      "\tstate VARCHAR, \n",
      "\tzip_code VARCHAR, \n",
      "\tPRIMARY KEY (id), \n",
      "\tFOREIGN KEY(user_id) REFERENCES users (id)\n",
      ")\n",
      "\n",
      "\n",
      "2020-04-19 15:45:08,769 INFO sqlalchemy.engine.base.Engine ()\n",
      "2020-04-19 15:45:08,770 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "Base.metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## As you can see this is very similar to the Core but without the `Base` class:\n",
    "    Base.metadata.create_all(engine) ORM\n",
    "    metadata.create_all(engine) CORE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now, let's try inserting the user again using the `session` object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "ed_user = Users(first_name='Ed', last_name='Jones')\n",
    "\n",
    "session.add(ed_user) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## We can query our database using the `query` method and passing the `Users` class Table. Also, we can use the `filter_by` method i.e. `WHERE` clause and the `first` method i.e. `LIMIT`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:45:11,006 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2020-04-19 15:45:11,010 INFO sqlalchemy.engine.base.Engine INSERT INTO users (first_name, last_name) VALUES (?, ?)\n",
      "2020-04-19 15:45:11,011 INFO sqlalchemy.engine.base.Engine ('Ed', 'Jones')\n",
      "2020-04-19 15:45:11,012 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.first_name AS users_first_name, users.last_name AS users_last_name \n",
      "FROM users \n",
      "WHERE users.first_name = ?\n",
      " LIMIT ? OFFSET ?\n",
      "2020-04-19 15:45:11,013 INFO sqlalchemy.engine.base.Engine ('Ed', 1, 0)\n"
     ]
    }
   ],
   "source": [
    "q = session.query(Users).filter_by(first_name='Ed').first() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now with our assigned query object of `q` we can get the attributes (Table fields) of the `Users` class:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'ID: 9, First name: Ed, Last name: Jones'"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f\"ID: {q.id}, First name: {q.first_name}, Last name: {q.last_name}\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Similarily, we can insert many records using the `add_all` method in a list of tuples:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.add_all([\n",
    "    Users(first_name='Wendy', last_name='Williams'),\n",
    "    Users(first_name='Chris', last_name='Walters'),\n",
    "]) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's do a select all using SQLAlchemy's `all()` method on the `session.query` object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:45:13,641 INFO sqlalchemy.engine.base.Engine INSERT INTO users (first_name, last_name) VALUES (?, ?)\n",
      "2020-04-19 15:45:13,643 INFO sqlalchemy.engine.base.Engine ('Wendy', 'Williams')\n",
      "2020-04-19 15:45:13,644 INFO sqlalchemy.engine.base.Engine INSERT INTO users (first_name, last_name) VALUES (?, ?)\n",
      "2020-04-19 15:45:13,645 INFO sqlalchemy.engine.base.Engine ('Chris', 'Walters')\n",
      "2020-04-19 15:45:13,647 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.first_name AS users_first_name, users.last_name AS users_last_name \n",
      "FROM users\n",
      "2020-04-19 15:45:13,647 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    }
   ],
   "source": [
    "query = session.query(Users).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `query` is of the type `list`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "list"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Similarily, we can iterate through the list object `query` and get the first names by using the attributre `first_name` of the `Users` class"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Bill\n",
      "Sally\n",
      "Jerome\n",
      "Rick\n",
      "Sam\n",
      "Jill\n",
      "Jan\n",
      "Linda\n",
      "Ed\n",
      "Wendy\n",
      "Chris\n"
     ]
    }
   ],
   "source": [
    "for row in query:\n",
    "    print(row.first_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Another usefull method is the `order_by` which is equivalent to SQL `ORDER BY` clause"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:45:16,273 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.first_name AS users_first_name, users.last_name AS users_last_name \n",
      "FROM users ORDER BY users.last_name\n",
      "2020-04-19 15:45:16,274 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    }
   ],
   "source": [
    "query = session.query(Users).order_by(Users.last_name).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sally Jackson\n",
      "Ed Jones\n",
      "Linda Rodriguez\n",
      "Bill Smith\n",
      "Jill Smith\n",
      "Rick Stein\n",
      "Jerome Walters\n",
      "Chris Walters\n",
      "Sam West\n",
      "Jan West\n",
      "Wendy Williams\n"
     ]
    }
   ],
   "source": [
    "# See query results sorted alphabetically due to order_by method above\n",
    "for row in query:\n",
    "    print(row.first_name, row.last_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The `filter` method combined with the `in_` method is also very usefull:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = session.query(Users).filter(Users.first_name.in_(['Ed', 'Bill', 'Jan']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:45:17,974 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.first_name AS users_first_name, users.last_name AS users_last_name \n",
      "FROM users \n",
      "WHERE users.first_name IN (?, ?, ?)\n",
      "2020-04-19 15:45:17,975 INFO sqlalchemy.engine.base.Engine ('Ed', 'Bill', 'Jan')\n",
      "Bill Smith\n",
      "Jan West\n",
      "Ed Jones\n"
     ]
    }
   ],
   "source": [
    "for row in query:\n",
    "    print(row.first_name, row.last_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Another good use of the filter(in_) combo is by passing a list of criteria such as:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "criteria_list = [\"Smith\", \"West\"]\n",
    "\n",
    "query = session.query(Users).filter(Users.last_name.in_(criteria_list))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:45:19,378 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.first_name AS users_first_name, users.last_name AS users_last_name \n",
      "FROM users \n",
      "WHERE users.last_name IN (?, ?)\n",
      "2020-04-19 15:45:19,380 INFO sqlalchemy.engine.base.Engine ('Smith', 'West')\n",
      "Bill Smith\n",
      "Sam West\n",
      "Jill Smith\n",
      "Jan West\n"
     ]
    }
   ],
   "source": [
    "for row in query:\n",
    "    print(row.first_name, row.last_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's add some more data so we can use some additional filtering methods with SQLAlchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'user_id': '1',\n",
       "  'street': '123 Some St.',\n",
       "  'city': 'Dallas',\n",
       "  'state': 'TX',\n",
       "  'zip_code': '75201'},\n",
       " {'user_id': '2',\n",
       "  'street': '124 Some St.',\n",
       "  'city': 'Plano',\n",
       "  'state': 'TX',\n",
       "  'zip_code': '75024'},\n",
       " {'user_id': '3',\n",
       "  'street': '125 Some St.',\n",
       "  'city': 'Plano',\n",
       "  'state': 'TX',\n",
       "  'zip_code': '75024'},\n",
       " {'user_id': '4',\n",
       "  'street': '126 Some St.',\n",
       "  'city': 'Austin',\n",
       "  'state': 'TX',\n",
       "  'zip_code': '78768'},\n",
       " {'user_id': '5',\n",
       "  'street': '127 Some St.',\n",
       "  'city': 'Milwaukee',\n",
       "  'state': 'WI',\n",
       "  'zip_code': '53202'},\n",
       " {'user_id': '6',\n",
       "  'street': '128 Some St.',\n",
       "  'city': 'Milwaukee',\n",
       "  'state': 'WI',\n",
       "  'zip_code': '53202'},\n",
       " {'user_id': '7',\n",
       "  'street': '129 Some St.',\n",
       "  'city': 'San Francisco',\n",
       "  'state': 'CA',\n",
       "  'zip_code': '94105'},\n",
       " {'user_id': '8',\n",
       "  'street': '130 Some St.',\n",
       "  'city': 'New York',\n",
       "  'state': 'NY',\n",
       "  'zip_code': '10004'}]"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open('address_data.csv', mode='r') as address_file:\n",
    "    reader = csv.reader(address_file, delimiter=',')\n",
    "    headers = next(reader, None)\n",
    "    address_file_import_list = [{headers[0] : row[0], headers[1] : row[1], headers[2] : row[2],\n",
    "                                 headers[3] : row[3], headers[4] : row[4]} for idx, row in enumerate(reader)]   \n",
    "\n",
    "address_file_import_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:45:20,798 INFO sqlalchemy.engine.base.Engine INSERT INTO addresses (user_id, street, city, state, zip_code) VALUES (?, ?, ?, ?, ?)\n",
      "2020-04-19 15:45:20,800 INFO sqlalchemy.engine.base.Engine (('1', '123 Some St.', 'Dallas', 'TX', '75201'), ('2', '124 Some St.', 'Plano', 'TX', '75024'), ('3', '125 Some St.', 'Plano', 'TX', '75024'), ('4', '126 Some St.', 'Austin', 'TX', '78768'), ('5', '127 Some St.', 'Milwaukee', 'WI', '53202'), ('6', '128 Some St.', 'Milwaukee', 'WI', '53202'), ('7', '129 Some St.', 'San Francisco', 'CA', '94105'), ('8', '130 Some St.', 'New York', 'NY', '10004'))\n",
      "2020-04-19 15:45:20,801 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "# Insert rows to database\n",
    "session.execute(Addresses.__table__.insert(), address_file_import_list)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Some really helpful methods are `and_`, `or_`, and `like`. Their functionality is self-explanatory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import and_, or_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = session.query(Users, Addresses, Email).filter(Users.id == Email.user_id).filter(Users.id == Addresses.user_id)\n",
    "query = query.filter(or_(Addresses.state == 'TX', Addresses.state == 'WI'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:48:37,644 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.first_name AS users_first_name, users.last_name AS users_last_name, addresses.id AS addresses_id, addresses.user_id AS addresses_user_id, addresses.street AS addresses_street, addresses.city AS addresses_city, addresses.state AS addresses_state, addresses.zip_code AS addresses_zip_code, email.id AS email_id, email.user_id AS email_user_id, email.email_address AS email_email_address, email.is_active AS email_is_active \n",
      "FROM users, addresses, email \n",
      "WHERE users.id = email.user_id AND users.id = addresses.user_id\n",
      "2020-04-19 15:48:37,645 INFO sqlalchemy.engine.base.Engine ()\n",
      "\n",
      "\n",
      "Bill Smith Dallas, TX\n",
      "\n",
      "\n",
      "Sally Jackson Plano, TX\n",
      "\n",
      "\n",
      "Jerome Walters Plano, TX\n",
      "\n",
      "\n",
      "Rick Stein Austin, TX\n",
      "\n",
      "\n",
      "Sam West Milwaukee, WI\n",
      "\n",
      "\n",
      "Jill Smith Milwaukee, WI\n",
      "\n",
      "\n",
      "Jan West San Francisco, CA\n",
      "\n",
      "\n",
      "Linda Rodriguez New York, NY\n"
     ]
    }
   ],
   "source": [
    "for row in query:\n",
    "    print(\"\\n\")\n",
    "    print(f\"{row.Users.first_name} {row.Users.last_name} {row.Addresses.city}, {row.Addresses.state}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "query2 = session.query(Users, Addresses, Email).filter(Users.id == Email.user_id).filter(Users.id == Addresses.user_id)\n",
    "query2 = query.filter(Email.email_address.like('%gmail%'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:49:32,620 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.first_name AS users_first_name, users.last_name AS users_last_name, addresses.id AS addresses_id, addresses.user_id AS addresses_user_id, addresses.street AS addresses_street, addresses.city AS addresses_city, addresses.state AS addresses_state, addresses.zip_code AS addresses_zip_code, email.id AS email_id, email.user_id AS email_user_id, email.email_address AS email_email_address, email.is_active AS email_is_active \n",
      "FROM users, addresses, email \n",
      "WHERE users.id = email.user_id AND users.id = addresses.user_id AND email.email_address LIKE ?\n",
      "2020-04-19 15:49:32,622 INFO sqlalchemy.engine.base.Engine ('%gmail%',)\n",
      "\n",
      "\n",
      "Jerome Walters, j.walters@gmail.com, TX\n",
      "\n",
      "\n",
      "Rick Stein, ricky.stein1@gmail.com, TX\n",
      "\n",
      "\n",
      "Jan West, j.west@gmail.com, CA\n"
     ]
    }
   ],
   "source": [
    "for row in query2:\n",
    "    print(\"\\n\")\n",
    "    print(f\"{row.Users.first_name} {row.Users.last_name}, {row.Email.email_address}, {row.Addresses.state}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "query3 = session.query(Users, Addresses, Email).filter(Users.id == Email.user_id).filter(Users.id == Addresses.user_id)\n",
    "query3 = query.filter(and_(Addresses.state == 'TX', Email.email_address.like('%gmail%')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-19 15:52:20,404 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.first_name AS users_first_name, users.last_name AS users_last_name, addresses.id AS addresses_id, addresses.user_id AS addresses_user_id, addresses.street AS addresses_street, addresses.city AS addresses_city, addresses.state AS addresses_state, addresses.zip_code AS addresses_zip_code, email.id AS email_id, email.user_id AS email_user_id, email.email_address AS email_email_address, email.is_active AS email_is_active \n",
      "FROM users, addresses, email \n",
      "WHERE users.id = email.user_id AND users.id = addresses.user_id AND addresses.state = ? AND email.email_address LIKE ?\n",
      "2020-04-19 15:52:20,405 INFO sqlalchemy.engine.base.Engine ('TX', '%gmail%')\n",
      "\n",
      "\n",
      "Jerome Walters, j.walters@gmail.com, TX\n",
      "\n",
      "\n",
      "Rick Stein, ricky.stein1@gmail.com, TX\n"
     ]
    }
   ],
   "source": [
    "for row in query3:\n",
    "    print(\"\\n\")\n",
    "    print(f\"{row.Users.first_name} {row.Users.last_name}, {row.Email.email_address}, {row.Addresses.state}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## There is so much more you can do with SQLAlchemy, but hopefully you got a good idea of how awesome of a Python tool kit this is. You can now start playing around with it or use in in your projects"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Thank you!"
   ]
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}