Section 03 Filtering Data/09 MySQL NOT IN operator.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "bc2b9f58-2021-4ad5-bc57-b8b10811c171",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## **[MySQL NOT IN operator](https://www.mysqltutorial.org/mysql-basics/mysql-not-in/)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "975a3bca-6e77-4058-ba9e-055ec589f947",
   "metadata": {},
   "source": [
    "Use the MySQL NOT IN operator to check if a value is not in a list of values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "92cc31df-34c7-4bd6-907e-78ac7bb8a6e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.image as mpimg\n",
    "from IPython.display import Image, SVG\n",
    "\n",
    "from sqlalchemy_utils import database_exists, create_database\n",
    "from sqlalchemy import create_engine, inspect, MetaData, text\n",
    "from sqlalchemy_schemadisplay import create_schema_graph\n",
    "import pymysql\n",
    "\n",
    "pd.set_option(\n",
    "    'display.max_columns', None,\n",
    "    'expand_frame_repr', True,\n",
    "    # 'max_rows', 10, \n",
    "    'display.max_colwidth', None,\n",
    "    'display.max_rows', 10,\n",
    "    # 'precision', 2,\n",
    "    # 'width', 45\n",
    ")\n",
    "\n",
    "pd.set_option('display.width', 65)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "a14dafec-344f-41f6-9683-50648d72e704",
   "metadata": {},
   "outputs": [],
   "source": [
    "connect_args={'ssl':{'fake_flag_to_enable_tls': True}}\n",
    "\n",
    "engine = create_engine('mysql+pymysql://namlq:abc123@localhost/classicmodels',\n",
    "                       connect_args=connect_args, echo=False\n",
    "                         )\n",
    "inspector = inspect(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "864f3312-8615-4248-930a-8d5ce283e23e",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Introduction to the MySQL NOT IN operator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "576abfc0-56e3-4575-bbbf-f313a51ba7e0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>1 NOT IN (1,2,3)</th>\n",
       "      <th>0 NOT IN (1,2,3)</th>\n",
       "      <th>NULL NOT IN (1,2,3)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   1 NOT IN (1,2,3)  0 NOT IN (1,2,3) NULL NOT IN (1,2,3)\n",
       "0                 0                 1                None"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string = '''\n",
    "SELECT\n",
    "    1 NOT IN (1,2,3),\n",
    "    0 NOT IN (1,2,3),\n",
    "    NULL NOT IN (1,2,3)\n",
    ";'''\n",
    "\n",
    "pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00adf4c5-d074-4656-bb5a-31b5c3c12f97",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL NOT IN examples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c675a323-37bf-435c-8234-02cf92c394c9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>officeCode</th>\n",
       "      <th>city</th>\n",
       "      <th>phone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7</td>\n",
       "      <td>London</td>\n",
       "      <td>+44 20 7877 2041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>Sydney</td>\n",
       "      <td>+61 2 9264 2451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>Tokyo</td>\n",
       "      <td>+81 33 224 5000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  officeCode    city             phone\n",
       "0          7  London  +44 20 7877 2041\n",
       "1          6  Sydney   +61 2 9264 2451\n",
       "2          5   Tokyo   +81 33 224 5000"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string = '''\n",
    "SELECT officeCode, city, phone\n",
    "FROM offices\n",
    "WHERE country NOT IN ('USA', 'France')\n",
    "ORDER BY city\n",
    ";'''\n",
    "\n",
    "pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "292aa7fb-c4e0-45ad-bcff-160f5bde529d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>officeCode</th>\n",
       "      <th>city</th>\n",
       "      <th>phone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7</td>\n",
       "      <td>London</td>\n",
       "      <td>+44 20 7877 2041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>Sydney</td>\n",
       "      <td>+61 2 9264 2451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>Tokyo</td>\n",
       "      <td>+81 33 224 5000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  officeCode    city             phone\n",
       "0          7  London  +44 20 7877 2041\n",
       "1          6  Sydney   +61 2 9264 2451\n",
       "2          5   Tokyo   +81 33 224 5000"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(pd.read_sql_table('offices', engine)\n",
    " .query(\"country not in ['USA', 'France']\")\n",
    " [['officeCode', 'city', 'phone']]\n",
    " .sort_values(by='city')\n",
    " .reset_index(drop=True)\n",
    ")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.15"
  },
  "toc-autonumbering": true,
  "toc-showmarkdowntxt": false,
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 5
}