Bonus - Using SQLAlchemy's ORM in Django.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1>Using <i style=\"color:red;\">SQLAlchemy</i>'s ORM in <i style=\"color:green;\">Django</i></h1>\n",
    "\n",
    "<h3><i>by Jack Camier, Python Developer</i></h3>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Django is one of the most popular web frameworks in Python. As of this talk, 2027 companies reportedly use Django in their tech stacks, including YouTube, Bitbucket, Dropbox, Instagram, Pinterest, Udemy just to name a few...\n",
    "https://stackshare.io/django"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%HTML\n",
    "<img src=\"django_logo.png\" width=\"300\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "https://www.djangoproject.com/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Personally, I am a big fan and use it often. However, one aspect of Django that I don't like that much is its ORM. I am not alone and have heard from developers their frustrations with the ORM. It works great for simple projects and queries but whenever you try to do something more complex, you can run into problems.\n",
    "\n",
    "## In this quick tutorial, I will show you a way you can use SQLAlchemy's ORM in conjunction with your Django Projects.\n",
    "\n",
    "## We will use the Django tutorial as our baseline to see how this can work.\n",
    "https://docs.djangoproject.com/en/3.0/intro/tutorial01/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## I am using the latest Django version as of this writing 3.0.5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import django\n",
    "\n",
    "django.VERSION"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Steps from the online tutorial, I had problems doing using the Jupyter Notebook. So these are the steps I did to get the project setup. django `mysite` is available in the git repo."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## created a virtualenv called `py37` with python 3.7.7\n",
    "\n",
    "## You could do the same with `conda create --name py37 python=3.7`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `django-admin startproject mysite`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Go into the `mysite` directory and `python manage.py startapp polls`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create a URLconf in the `polls` directory, create a file called `urls.py`"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# Write the following view:\n",
    "\n",
    "from django.urls import path\n",
    "\n",
    "from . import views\n",
    "\n",
    "urlpatterns = [\n",
    "    path('', views.index, name='index'),\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Next step is to point the root URLconf at the polls.urls module. In `mysite/urls.py`, add an import for django.urls.include and insert an include() in the urlpatterns list"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "from django.contrib import admin\n",
    "from django.urls import include, path\n",
    "\n",
    "urlpatterns = [\n",
    "    path('polls/', include('polls.urls')),\n",
    "    path('admin/', admin.site.urls),\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `python manage.py migrate`\n",
    "\n",
    "## The migrate command looks at the INSTALLED_APPS setting and creates any necessary database tables according to the database settings in your `mysite/settings.py`\n",
    "\n",
    "## `auth_user` is the key table that is created to manage users"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create the models (Tables) in the `polls/models.py` module"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "from django.db import models\n",
    "\n",
    "\n",
    "class Question(models.Model):\n",
    "    question_text = models.CharField(max_length=200)\n",
    "    pub_date = models.DateTimeField('date published')\n",
    "\n",
    "\n",
    "class Choice(models.Model):\n",
    "    question = models.ForeignKey(Question, on_delete=models.CASCADE)\n",
    "    choice_text = models.CharField(max_length=200)\n",
    "    votes = models.IntegerField(default=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add `'polls.apps.PollsConfig',` to the `mysite/settings.py` module. This is to allow Django to know that our app is in our project.\n",
    "\n",
    "## The format is normally app_name.apps.App_nameConfig.\n",
    "\n",
    "## So `polls` becomes `polls.apps.PollsConfig`\n",
    "\n",
    "## It should look something like this now:"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "INSTALLED_APPS = [\n",
    "    'django.contrib.admin',\n",
    "    'django.contrib.auth',\n",
    "    'django.contrib.contenttypes',\n",
    "    'django.contrib.sessions',\n",
    "    'django.contrib.messages',\n",
    "    'django.contrib.staticfiles',\n",
    "    'polls.apps.PollsConfig',\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Then, `python manage.py makemigrations polls` to update the migration"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Next, `python manage.py migrate` to build (create) the tables stored in migrations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# -------\n",
    "## Up to this point, this has all been standard Django functionality. Now, this is where I will show a way you can use SQLAlchemy to working with Django\n",
    "\n",
    "# -------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Make sure you have SQLAlchemy installed\n",
    "`pip install SQLAlchemy`\n",
    "## I have included it in my requirements.txt file already in case you want to `pip install -r requirements.txt`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## First I created a module called `session.py` in the `polls` directory, hence polls/session.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## These are all my imports:"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "import os\n",
    "import sqlalchemy\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "from sqlalchemy.engine import reflection"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The first thing we need access to is the SQLite database that was created by Django."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's cd into the path that contains `session.py ` "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/Users/jacquescamier/JupyterProjects/dfw_sqlalchemy_talk'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## To see our current directory\n",
    "import os\n",
    "\n",
    "CURRENT_DIR = os.path.abspath('') # Use '' for notebooks but __file__ for .py files\n",
    "\n",
    "CURRENT_DIR"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "session_dir = os.path.join(CURRENT_DIR, \"mysite\", \"polls\")\n",
    "\n",
    "session_dir\n",
    "\n",
    "os.chdir(session_dir)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `BASE_DIR` is a global variable assigned in the `mysite/mysite/settings.py` file.\n",
    "\n",
    "\n",
    "## We will use the same logic to access the SQLite database in the `session.py` module"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/Users/jacquescamier/JupyterProjects/dfw_sqlalchemy_talk/mysite'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))\n",
    "\n",
    "BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(\"session.py\")))\n",
    "\n",
    "BASE_DIR"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## As you can see the BASE_DIR get us to the project directory of `mysite` which also contains the SQLite database file `db.sqlite3` "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['db.sqlite3-journal', 'mysite', 'db.sqlite3', 'polls', 'manage.py']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.listdir(BASE_DIR)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## To get the SQLAlchemy SQLite file path protocol"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'sqlite:////Users/jacquescamier/JupyterProjects/dfw_sqlalchemy_talk/mysite/db.sqlite3'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sqlite_path = os.path.join(BASE_DIR, 'db.sqlite3')\n",
    "sqlite_path_str = f\"sqlite:///{sqlite_path}\"\n",
    "\n",
    "sqlite_path_str"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## As mentioned in my earlier tutorial:\n",
    "https://github.com/jcamier/dfw_sqlalchemy_talk/blob/master/intro_sqlalchemy.ipynb\n",
    "\n",
    "## We will need to create the engine object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy\n",
    "\n",
    "engine = sqlalchemy.create_engine(sqlite_path_str)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create a session object to use SQLAlchemy's ORM and querying the tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy.orm import sessionmaker\n",
    "\n",
    "Session = sessionmaker(bind=engine)\n",
    "session = Session()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now one approach is to rewrite the classes using SQLAlchemy's (SA) syntax of our models so we can query them later with the ORM but this would not be efficient"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SA has a great tool called reflection that allows us access the models (Tables) already created. We just need to make a connection between them and the `metadata` object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy.engine import reflection\n",
    "\n",
    "metadata = sqlalchemy.MetaData()\n",
    "\n",
    "metadata.reflect(bind=engine)\n",
    "\n",
    "insp = reflection.Inspector.from_engine(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## To create an object that SA can use by adding it to the `metadata` object, you would do something like this:\n",
    "metadata.tables[`table_name`]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create table reflection\n",
    "\n",
    "auth_user_table = metadata.tables['auth_user']\n",
    "polls_choice_table = metadata.tables['polls_choice']\n",
    "polls_question_table = metadata.tables['polls_question']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now we can see all the tables using this simple helper function I wrote"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['auth_group',\n",
       " 'auth_group_permissions',\n",
       " 'auth_permission',\n",
       " 'auth_user',\n",
       " 'auth_user_groups',\n",
       " 'auth_user_user_permissions',\n",
       " 'django_admin_log',\n",
       " 'django_content_type',\n",
       " 'django_migrations',\n",
       " 'django_session',\n",
       " 'polls_choice',\n",
       " 'polls_question',\n",
       " 'sqlite_sequence']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from typing import List\n",
    "\n",
    "def get_all_tables() -> List:\n",
    "    \"\"\"\n",
    "    `get_all_tables` helper function to get a list of all tables in the database\n",
    "    \n",
    "    :return list all table names: \n",
    "    \"\"\"\n",
    "    return insp.get_table_names()\n",
    "\n",
    "django_tables = get_all_tables()\n",
    "\n",
    "django_tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## And if you forgot the column names, here is another helper function I wrote. Honestly, in most case you would just go to the models.py to see them, but this is more to display the functionaliy of SA\n",
    "\n",
    "## Be sure to use the metadata reflection table name assigned, not Django's. For example, it would be `auth_user_table` from above not `auth_user` because we are using SA and not the built-in Django ORM"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['auth_user.id', 'auth_user.password', 'auth_user.last_login', 'auth_user.is_superuser', 'auth_user.username', 'auth_user.first_name', 'auth_user.email', 'auth_user.is_staff', 'auth_user.is_active', 'auth_user.date_joined', 'auth_user.last_name']\n"
     ]
    }
   ],
   "source": [
    "def print_table_meta_columns(table) -> None:\n",
    "    \"\"\"\n",
    "    `print_table_meta_columns helper function to stdout to terminal the column names of a table\n",
    "\n",
    "    :param table:\n",
    "    :return None:\n",
    "    \"\"\"\n",
    "\n",
    "    print(table._columns)\n",
    "    \n",
    "print_table_meta_columns(table=auth_user_table)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## At this point, we haven't really seen anything that would want us to switch to using SA's ORM vs Django's\n",
    "\n",
    "## Let's write some test data to our database so we can play around with the ORM's functionality"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "import datetime as dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'username': 'admin@example.com',\n",
       "  'password': 'test1234',\n",
       "  'email': 'admin@example.com',\n",
       "  'first_name': 'Admin',\n",
       "  'last_name': '',\n",
       "  'is_superuser': 1,\n",
       "  'is_staff': 1,\n",
       "  'is_active': 1,\n",
       "  'date_joined': datetime.datetime(2020, 4, 23, 21, 39, 41, 507769)},\n",
       " {'username': 'chris@example.com',\n",
       "  'password': 'test1234',\n",
       "  'email': 'chris@example.com',\n",
       "  'first_name': 'Chris',\n",
       "  'last_name': 'Smith',\n",
       "  'is_superuser': 0,\n",
       "  'is_staff': 1,\n",
       "  'is_active': 1,\n",
       "  'date_joined': datetime.datetime(2020, 4, 23, 21, 39, 41, 507829)},\n",
       " {'username': 'bill@example.com',\n",
       "  'password': 'test1234',\n",
       "  'email': 'bill@example.com',\n",
       "  'first_name': 'Bill',\n",
       "  'last_name': 'Jackson',\n",
       "  'is_superuser': 0,\n",
       "  'is_staff': 1,\n",
       "  'is_active': 1,\n",
       "  'date_joined': datetime.datetime(2020, 4, 23, 21, 39, 41, 507832)},\n",
       " {'username': 'sue@example.com',\n",
       "  'password': 'test1234',\n",
       "  'email': 'sue@example.com',\n",
       "  'first_name': 'Sue',\n",
       "  'last_name': 'Stein',\n",
       "  'is_superuser': 0,\n",
       "  'is_staff': 1,\n",
       "  'is_active': 1,\n",
       "  'date_joined': datetime.datetime(2020, 4, 23, 21, 39, 41, 507835)},\n",
       " {'username': 'nikhil@example.com',\n",
       "  'password': 'test1234',\n",
       "  'email': 'nikhil@example.com',\n",
       "  'first_name': 'Nikhil',\n",
       "  'last_name': 'Anwar',\n",
       "  'is_superuser': 0,\n",
       "  'is_staff': 1,\n",
       "  'is_active': 1,\n",
       "  'date_joined': datetime.datetime(2020, 4, 23, 21, 39, 41, 507847)}]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "django_users_file = os.path.join(CURRENT_DIR, 'django_users_data.csv')\n",
    "\n",
    "with open(django_users_file, mode='r') as users_file:\n",
    "    reader = csv.reader(users_file, delimiter=',')\n",
    "    headers = next(reader, None)\n",
    "    users_import_list = [{headers[0]: row[0], headers[1]: row[1], headers[2]: row[2], headers[3]: row[3],\n",
    "                          headers[4]: row[4], headers[5]: int(row[5]), headers[6]: int(row[6]),\n",
    "                          headers[7]: int(row[7]), \"date_joined\": dt.datetime.now()\n",
    "                         } for idx, row in enumerate(reader)]\n",
    "    \n",
    "\n",
    "users_import_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Insert rows to database\n",
    "session.execute(auth_user_table.insert(), users_import_list)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now we can do a SQL query of all users, in Django we would do:\n",
    "`User.objects.all()`\n",
    "## Notice I am using the `auth_user_table` that I assigned earlier which is part of SA's `metadata` object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(4,\n",
       "  'test1234',\n",
       "  None,\n",
       "  True,\n",
       "  'admin@example.com',\n",
       "  'Admin',\n",
       "  'admin@example.com',\n",
       "  True,\n",
       "  True,\n",
       "  datetime.datetime(2020, 4, 23, 21, 39, 41, 507769),\n",
       "  ''),\n",
       " (5,\n",
       "  'test1234',\n",
       "  None,\n",
       "  False,\n",
       "  'chris@example.com',\n",
       "  'Chris',\n",
       "  'chris@example.com',\n",
       "  True,\n",
       "  True,\n",
       "  datetime.datetime(2020, 4, 23, 21, 39, 41, 507829),\n",
       "  'Smith'),\n",
       " (6,\n",
       "  'test1234',\n",
       "  None,\n",
       "  False,\n",
       "  'bill@example.com',\n",
       "  'Bill',\n",
       "  'bill@example.com',\n",
       "  True,\n",
       "  True,\n",
       "  datetime.datetime(2020, 4, 23, 21, 39, 41, 507832),\n",
       "  'Jackson'),\n",
       " (7,\n",
       "  'test1234',\n",
       "  None,\n",
       "  False,\n",
       "  'sue@example.com',\n",
       "  'Sue',\n",
       "  'sue@example.com',\n",
       "  True,\n",
       "  True,\n",
       "  datetime.datetime(2020, 4, 23, 21, 39, 41, 507835),\n",
       "  'Stein'),\n",
       " (8,\n",
       "  'test1234',\n",
       "  None,\n",
       "  False,\n",
       "  'nikhil@example.com',\n",
       "  'Nikhil',\n",
       "  'nikhil@example.com',\n",
       "  True,\n",
       "  True,\n",
       "  datetime.datetime(2020, 4, 23, 21, 39, 41, 507847),\n",
       "  'Anwar')]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(auth_user_table).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's also insert questions to the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'question_text': \"What's new?\",\n",
       "  'pub_date': datetime.datetime(2020, 4, 25, 14, 4, 34, 110321)},\n",
       " {'question_text': 'What is your favorite programming language?',\n",
       "  'pub_date': datetime.datetime(2020, 4, 25, 14, 4, 34, 110329)},\n",
       " {'question_text': 'What is your favorite city?',\n",
       "  'pub_date': datetime.datetime(2020, 4, 25, 14, 4, 34, 110332)},\n",
       " {'question_text': 'What is your favorite meetup?',\n",
       "  'pub_date': datetime.datetime(2020, 4, 25, 14, 4, 34, 110334)},\n",
       " {'question_text': 'How are you doing today?',\n",
       "  'pub_date': datetime.datetime(2020, 4, 25, 14, 4, 34, 110346)}]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "questions_file = os.path.join(CURRENT_DIR, 'questions_data.csv')\n",
    "\n",
    "with open(questions_file, mode='r') as questions_file:\n",
    "    reader = csv.reader(questions_file, delimiter=',')\n",
    "    headers = next(reader, None)\n",
    "    questions_import_list = [{headers[0]: row[0], \"pub_date\": dt.datetime.now()\n",
    "                         } for idx, row in enumerate(reader)]\n",
    "    \n",
    "\n",
    "questions_import_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Insert questions to database\n",
    "session.execute(polls_question_table.insert(), questions_import_list)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## We can query this data similarly to above. In Django, this would be `Question.objects.all()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, \"What's new?\", datetime.datetime(2020, 4, 25, 14, 4, 34, 110321)),\n",
       " (2,\n",
       "  'What is your favorite programming language?',\n",
       "  datetime.datetime(2020, 4, 25, 14, 4, 34, 110329)),\n",
       " (3,\n",
       "  'What is your favorite city?',\n",
       "  datetime.datetime(2020, 4, 25, 14, 4, 34, 110332)),\n",
       " (4,\n",
       "  'What is your favorite meetup?',\n",
       "  datetime.datetime(2020, 4, 25, 14, 4, 34, 110334)),\n",
       " (5,\n",
       "  'How are you doing today?',\n",
       "  datetime.datetime(2020, 4, 25, 14, 4, 34, 110346))]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(polls_question_table).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's filter for the question with an id of 1. \n",
    "### Django: `Question.objects.filter(id=1)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, \"What's new?\", datetime.datetime(2020, 4, 25, 14, 4, 34, 110321))\n"
     ]
    }
   ],
   "source": [
    "query = session.query(polls_question_table).filter(polls_question_table.c.id==1)\n",
    "\n",
    "for q in query: print(q)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using SQL's  `where` clause with `startswith` in the `polls_question_table`\n",
    "### Django: `Question.objects.filter(question_text__startswith='What')`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, \"What's new?\", datetime.datetime(2020, 4, 25, 14, 4, 34, 110321))\n",
      "(2, 'What is your favorite programming language?', datetime.datetime(2020, 4, 25, 14, 4, 34, 110329))\n",
      "(3, 'What is your favorite city?', datetime.datetime(2020, 4, 25, 14, 4, 34, 110332))\n",
      "(4, 'What is your favorite meetup?', datetime.datetime(2020, 4, 25, 14, 4, 34, 110334))\n"
     ]
    }
   ],
   "source": [
    "query = session.query(polls_question_table).filter(polls_question_table.c.question_text.startswith('What'))\n",
    "\n",
    "for q in query: print(q)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## You can also use the `like` SQL syntax"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2, 'What is your favorite programming language?', datetime.datetime(2020, 4, 25, 14, 4, 34, 110329))\n",
      "(3, 'What is your favorite city?', datetime.datetime(2020, 4, 25, 14, 4, 34, 110332))\n",
      "(4, 'What is your favorite meetup?', datetime.datetime(2020, 4, 25, 14, 4, 34, 110334))\n"
     ]
    }
   ],
   "source": [
    "query = session.query(polls_question_table).filter(polls_question_table.c.question_text.like('%favorite%'))\n",
    "\n",
    "for q in query: print(q)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get the question that was published this year.\n",
    "\n",
    "### Django:\n",
    "\n",
    "from django.utils import timezone\n",
    "\n",
    "current_year = timezone.now().year\n",
    "\n",
    "Question.objects.get(pub_date__year=current_year)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, \"What's new?\", datetime.datetime(2020, 4, 25, 14, 4, 34, 110321))\n",
      "(2, 'What is your favorite programming language?', datetime.datetime(2020, 4, 25, 14, 4, 34, 110329))\n",
      "(3, 'What is your favorite city?', datetime.datetime(2020, 4, 25, 14, 4, 34, 110332))\n",
      "(4, 'What is your favorite meetup?', datetime.datetime(2020, 4, 25, 14, 4, 34, 110334))\n",
      "(5, 'How are you doing today?', datetime.datetime(2020, 4, 25, 14, 4, 34, 110346))\n"
     ]
    }
   ],
   "source": [
    "current_year = dt.datetime.now().year # Remember I aliased datetime to dt above\n",
    "\n",
    "query = session.query(polls_question_table).filter(polls_question_table.c.pub_date >= current_year)\n",
    "\n",
    "for q in query: print(q)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now, let's do our final SQL import i.e. insert"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'choice_text': 'Not much', 'votes': '0', 'question_id': '1'},\n",
       " {'choice_text': 'Trying to stay safe', 'votes': '0', 'question_id': '1'},\n",
       " {'choice_text': 'Bored', 'votes': '0', 'question_id': '1'},\n",
       " {'choice_text': 'Doing good', 'votes': '0', 'question_id': '1'},\n",
       " {'choice_text': 'Python', 'votes': '0', 'question_id': '2'},\n",
       " {'choice_text': 'Java', 'votes': '0', 'question_id': '2'},\n",
       " {'choice_text': 'JavaScript', 'votes': '0', 'question_id': '2'},\n",
       " {'choice_text': 'Ruby', 'votes': '0', 'question_id': '2'},\n",
       " {'choice_text': 'C/C++', 'votes': '0', 'question_id': '2'},\n",
       " {'choice_text': 'C#', 'votes': '0', 'question_id': '2'},\n",
       " {'choice_text': 'R', 'votes': '0', 'question_id': '2'}]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "choices_file = os.path.join(CURRENT_DIR, 'choice_data.csv')\n",
    "\n",
    "with open(choices_file, mode='r') as choice_file:\n",
    "    reader = csv.reader(choice_file, delimiter=',')\n",
    "    headers = next(reader, None)\n",
    "    choices_import_list = [{headers[0]: row[0], headers[1]: row[1], headers[2]: row[2]\n",
    "                         } for idx, row in enumerate(reader)]\n",
    "    \n",
    "\n",
    "choices_import_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Insert choices to database\n",
    "session.execute(polls_choice_table.insert(), choices_import_list)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Query the `polls_choice_table` to ensure the data was loaded"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Not much', 0, 1),\n",
       " (2, 'Trying to stay safe', 0, 1),\n",
       " (3, 'Bored', 0, 1),\n",
       " (4, 'Doing good', 0, 1),\n",
       " (5, 'Python', 0, 2),\n",
       " (6, 'Java', 0, 2),\n",
       " (7, 'JavaScript', 0, 2),\n",
       " (8, 'Ruby', 0, 2),\n",
       " (9, 'C/C++', 0, 2),\n",
       " (10, 'C#', 0, 2),\n",
       " (11, 'R', 0, 2)]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(polls_choice_table).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's add one more choice to question 1\n",
    "\n",
    "### Django\n",
    "\n",
    "q = Question.objects.get(pk=1)\n",
    "\n",
    "c = q.choice_set.create(choice_text='Just hacking again', votes=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.execute(polls_choice_table.insert(), {'choice_text': 'Attending a Python Meetup', 'votes': '0', 'question_id': '1'})\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Not much', 0, 1),\n",
       " (2, 'Trying to stay safe', 0, 1),\n",
       " (3, 'Bored', 0, 1),\n",
       " (4, 'Doing good', 0, 1),\n",
       " (5, 'Python', 0, 2),\n",
       " (6, 'Java', 0, 2),\n",
       " (7, 'JavaScript', 0, 2),\n",
       " (8, 'Ruby', 0, 2),\n",
       " (9, 'C/C++', 0, 2),\n",
       " (10, 'C#', 0, 2),\n",
       " (11, 'R', 0, 2),\n",
       " (12, 'Attending a Python Meetup', 0, 1)]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(polls_choice_table).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Delete a record\n",
    "\n",
    "### Django\n",
    "c = q.choice_set.filter(choice_text__startswith='Bored')\n",
    "\n",
    "c.delete()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c = session.query(polls_choice_table).filter(polls_choice_table.c.choice_text.startswith('Bored'))\n",
    "c.delete(synchronize_session=False) # deletions require a synchronization strategy\n",
    "# https://docs.sqlalchemy.org/en/13/orm/query.html?highlight=delete#sqlalchemy.orm.query.Query.delete"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Not much', 0, 1),\n",
       " (2, 'Trying to stay safe', 0, 1),\n",
       " (4, 'Doing good', 0, 1),\n",
       " (5, 'Python', 0, 2),\n",
       " (6, 'Java', 0, 2),\n",
       " (7, 'JavaScript', 0, 2),\n",
       " (8, 'Ruby', 0, 2),\n",
       " (9, 'C/C++', 0, 2),\n",
       " (10, 'C#', 0, 2),\n",
       " (11, 'R', 0, 2),\n",
       " (12, 'Attending a Python Meetup', 0, 1)]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(polls_choice_table).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now one of the key components of Django are views. If we can't use SA in these scenarios, then really there is no point to using it all. In fact, this situation is what led me to want to try to implement SQLAlchemey's ORM vs. Django's when I needed to write more complex queries for my views.\n",
    "\n",
    "## We will update our `polls/view.py` as such:"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "from django.http import HttpResponse, Http404\n",
    "from django.shortcuts import render\n",
    "\n",
    "from .models import Question\n",
    "from .session import auth_user_table, polls_choice_table, polls_question_table, session\n",
    "\n",
    "def index(request):\n",
    "    latest_question_list = session.query(polls_question_table).order_by(polls_question_table.c.pub_date.desc()) \\\n",
    "                               .all()[:5]\n",
    "    context = {'latest_question_list': latest_question_list}\n",
    "    return render(request, 'polls/index.html', context)\n",
    "\n",
    "\n",
    "def detail(request, question_id):\n",
    "    question = session.query(polls_question_table).filter(polls_question_table.c.id == question_id).first()\n",
    "    if not question:\n",
    "        raise Http404(\"Question does not exist\")\n",
    "    return render(request, 'polls/detail.html', {'question': question})\n",
    "\n",
    "\n",
    "def results(request, question_id):\n",
    "    response = \"You're looking at the results of question %s.\"\n",
    "    return HttpResponse(response % question_id)\n",
    "\n",
    "\n",
    "def vote(request, question_id):\n",
    "    return HttpResponse(\"You're voting on question %s.\" % question_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Notice the extra line for the import:\n",
    "### `from .session import auth_user_table, polls_choice_table, polls_question_table, session`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## This is our new session module that housed our SQLAlchemy objects. In particular, the `sesssion` object and our table reflections of the Django model classes into SA metadata objects: `auth_user_table`, `polls_choice_table`, `polls_question_table`. \n",
    "\n",
    "## I could have written this as two modules maybe with a session constructor and repository class that housed all my SA queries and metadata models but wanted to make this code base simple to follow along in one module."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## In Django: `latest_question_list = Question.objects.order_by('-pub_date')[:5]` is replaced with:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "latest_question_list = session.query(polls_question_table).order_by(polls_question_table.c.pub_date.desc()) \\\n",
    "                                     .all()[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(5,\n",
       "  'How are you doing today?',\n",
       "  datetime.datetime(2020, 4, 25, 14, 4, 34, 110346)),\n",
       " (4,\n",
       "  'What is your favorite meetup?',\n",
       "  datetime.datetime(2020, 4, 25, 14, 4, 34, 110334)),\n",
       " (3,\n",
       "  'What is your favorite city?',\n",
       "  datetime.datetime(2020, 4, 25, 14, 4, 34, 110332)),\n",
       " (2,\n",
       "  'What is your favorite programming language?',\n",
       "  datetime.datetime(2020, 4, 25, 14, 4, 34, 110329)),\n",
       " (1, \"What's new?\", datetime.datetime(2020, 4, 25, 14, 4, 34, 110321))]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "latest_question_list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## And in Django `question = Question.objects.get(pk=question_id)` is replaced with:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "question_id = 1 # This is just a placeholder variable. It will be dependent on what is in the url\n",
    "# http://127.0.0.1:8000/polls/1/\n",
    "\n",
    "question = session.query(polls_question_table).filter(polls_question_table.c.id==question_id).first()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1, \"What's new?\", datetime.datetime(2020, 4, 25, 14, 4, 34, 110321))"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "question"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## As you can see, we can use SQLAlchemy's ORM instead of Django's when we want to and switch back to Django's ORM in our views depending on the requirements. \n",
    "\n",
    "## We can get the best of both worlds! \n",
    "\n",
    "## In most cases, the simple queries you will use Django but when you get in very complicated joins combined with multiple `where`, `and` as well as `or` SQL clauses, you can use SA."
   ]
  },
  {
   "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
}