Section 03 Filtering Data/11 MySQL LIKE operator.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "d07ac89f-3251-442e-8fab-fee9b7c3ffca",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## **[MySQL LIKE](https://www.mysqltutorial.org/mysql-like/)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e67890e-c17c-4673-add3-47f1d141351f",
   "metadata": {},
   "source": [
    "Use the MySQL LIKE operator to query data based on a specified pattern."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8093ad6d-071d-4080-9f51-917306b1e387",
   "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": "774965f5-63f8-4756-8f44-12efb2218e0c",
   "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": "bf2a2666-f55d-4e41-8fdc-bea5527158c2",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Introduction to MySQL LIKE operator"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "718a816e-27a1-4a1a-bfcf-7f2c36f2632f",
   "metadata": {},
   "source": [
    "- The syntax of the `LIKE` operator: **_expression `LIKE` pattern `ESCAPE` escape_character_**\n",
    "- **_pattern_** includes `%` and `_` wildcards:\n",
    "\n",
    "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;`%` wildcard matches any string of zeor or more characters.\n",
    "\n",
    "&ensp;&ensp;&ensp;&ensp;&ensp;&ensp;&ensp;&ensp;`_` wildcard matches any single character\n",
    "\n",
    "- When the **_pattern_** contains the wildcard character and you want to treat it as a regular character, you can use the `ESCAPE` clause"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36817614-8c28-46fc-b63a-bd1790eb99e9",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "### MySQL LIKE operator examples"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ea0f190-31c0-44f5-b903-0b095aef7ea2",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### A) Using MySQL LIKE operator with the percentage (`%`) wildcard"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb87cf0f-9ea9-4beb-8cdb-3f30edf4743c",
   "metadata": {},
   "source": [
    "- Find employees whose first names start with the letter `a`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ad2b1be0-e318-46a4-8627-46284f4b9089",
   "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>employeeNumber</th>\n",
       "      <th>lastName</th>\n",
       "      <th>firstName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1143</td>\n",
       "      <td>Bow</td>\n",
       "      <td>Anthony</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1611</td>\n",
       "      <td>Fixter</td>\n",
       "      <td>Andy</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employeeNumber lastName firstName\n",
       "0            1143      Bow   Anthony\n",
       "1            1611   Fixter      Andy"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string = '''\n",
    "SELECT employeeNumber, lastName, firstName\n",
    "FROM employees\n",
    "WHERE firstName LIKE 'a%';\n",
    "'''\n",
    "\n",
    "pd.read_sql(text(string), engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b3143e41-0e9a-4434-83e5-459af9937e0d",
   "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>employeeNumber</th>\n",
       "      <th>lastName</th>\n",
       "      <th>firstName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1143</td>\n",
       "      <td>Bow</td>\n",
       "      <td>Anthony</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1611</td>\n",
       "      <td>Fixter</td>\n",
       "      <td>Andy</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employeeNumber lastName firstName\n",
       "0            1143      Bow   Anthony\n",
       "1            1611   Fixter      Andy"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "char = ('a', 'A')\n",
    "(pd.read_sql_table('employees', engine)\n",
    " # .query(\"firstName.str.upper().str.startswith('A')\")\n",
    " .query(\"firstName.str.startswith(@char)\")\n",
    " [['employeeNumber', 'lastName', 'firstName']]\n",
    " .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6cd153ef-9f3c-4116-8a7a-aafed041f262",
   "metadata": {},
   "source": [
    "- Find employees whose last names end with the literal string `on` e.g., `Patterson`, `Thomson`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "235ca093-f5f2-4771-98ff-6f94beda4a65",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT employeeNumber, lastName, firstName\n",
    "FROM employees\n",
    "WHERE lastName LIKE '%on';\n",
    "'''\n",
    "\n",
    "df1 = pd.read_sql(text(string), engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "43a875a2-1391-4532-9012-fc4098715823",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('employees', engine)\n",
    "    .query(\"lastName.str.endswith('on')\")\n",
    "    [['employeeNumber', 'lastName', 'firstName']] \n",
    "    .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "42c4c6a6-fef2-4c2c-9ef2-3c15ff8758a9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "98f11a79-91cb-4ef4-a3b9-1f61f7ddaf82",
   "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>employeeNumber</th>\n",
       "      <th>lastName</th>\n",
       "      <th>firstName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1056</td>\n",
       "      <td>Patterson</td>\n",
       "      <td>Mary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1088</td>\n",
       "      <td>Patterson</td>\n",
       "      <td>William</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1166</td>\n",
       "      <td>Thompson</td>\n",
       "      <td>Leslie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1216</td>\n",
       "      <td>Patterson</td>\n",
       "      <td>Steve</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employeeNumber   lastName firstName\n",
       "0            1056  Patterson      Mary\n",
       "1            1088  Patterson   William\n",
       "2            1166   Thompson    Leslie\n",
       "3            1216  Patterson     Steve"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7cb59b7e-39a9-45b1-ab49-aa560cbb8147",
   "metadata": {},
   "source": [
    "- Find all employees whose last names contain the substring `on`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "1a5d24b2-b746-4235-b50c-4fc6266f63af",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT employeeNumber, lastName, firstName\n",
    "FROM employees\n",
    "WHERE lastName LIKE '%on%'\n",
    "'''\n",
    "\n",
    "df1 = pd.read_sql(text(string), engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "41a7bc68-93d5-4498-9eab-f2917b4431ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('employees', engine)\n",
    "    .query(\"lastName.str.contains('on')\")\n",
    "    [['employeeNumber', 'lastName', 'firstName']]\n",
    "    .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "c1543a66-21fc-4a4f-8fe0-c21938e733f2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "02d13f51-02b5-4aaa-807e-ae83898176eb",
   "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>employeeNumber</th>\n",
       "      <th>lastName</th>\n",
       "      <th>firstName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1056</td>\n",
       "      <td>Patterson</td>\n",
       "      <td>Mary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1088</td>\n",
       "      <td>Patterson</td>\n",
       "      <td>William</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1102</td>\n",
       "      <td>Bondur</td>\n",
       "      <td>Gerard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1166</td>\n",
       "      <td>Thompson</td>\n",
       "      <td>Leslie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1216</td>\n",
       "      <td>Patterson</td>\n",
       "      <td>Steve</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1337</td>\n",
       "      <td>Bondur</td>\n",
       "      <td>Loui</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1504</td>\n",
       "      <td>Jones</td>\n",
       "      <td>Barry</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employeeNumber   lastName firstName\n",
       "0            1056  Patterson      Mary\n",
       "1            1088  Patterson   William\n",
       "2            1102     Bondur    Gerard\n",
       "3            1166   Thompson    Leslie\n",
       "4            1216  Patterson     Steve\n",
       "5            1337     Bondur      Loui\n",
       "6            1504      Jones     Barry"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec1c2293-c18c-47df-bf75-7013149a257f",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### B) Using MySQL LIKE operator with the underscore (`_`) wildcard"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31035aa4-5944-43bf-a2e8-930ca2ea3b3b",
   "metadata": {},
   "source": [
    "- Find employees whose first names start with `T`, end with `m`, contain any single character between"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "e4b031b2-3f47-4041-84e4-6b039643fae8",
   "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>employeeNumber</th>\n",
       "      <th>lastName</th>\n",
       "      <th>firstName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1619</td>\n",
       "      <td>King</td>\n",
       "      <td>Tom</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employeeNumber lastName firstName\n",
       "0            1619     King       Tom"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string = '''\n",
    "SELECT employeeNumber, lastName, firstName\n",
    "FROM employees\n",
    "WHERE firstName LIKE 'T_m';\n",
    "'''\n",
    "\n",
    "pd.read_sql(text(string), engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "894a4153-e36e-41ff-b3fe-058f1048fbeb",
   "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>employeeNumber</th>\n",
       "      <th>lastName</th>\n",
       "      <th>firstName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1619</td>\n",
       "      <td>King</td>\n",
       "      <td>Tom</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employeeNumber lastName firstName\n",
       "0            1619     King       Tom"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(pd.read_sql_table('employees', engine)\n",
    " .query(\"firstName.str.contains('T.m')\")\n",
    " [['employeeNumber', 'lastName', 'firstName']]\n",
    " .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05570f2d-7381-4142-a428-1b9e402e8317",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### C) Using MySQL NOT LIKE operator"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64d68090-9af1-4db3-bb67-66c4c1266393",
   "metadata": {},
   "source": [
    "- Search for employees whose last names don't start with the letter `B`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "ee923d72-9436-4f90-8baf-bce66e082eb2",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT employeeNumber, lastName, firstName\n",
    "FROM employees\n",
    "WHERE lastName NOT LIKE 'b%'\n",
    "'''\n",
    "\n",
    "df1 = pd.read_sql(text(string), engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "00ec4f30-5ecb-4645-8252-bd597bdef51e",
   "metadata": {},
   "outputs": [],
   "source": [
    "char = ('b', 'B')\n",
    "df2 = (\n",
    "    pd.read_sql_table('employees', engine)\n",
    "    .query(\"~lastName.str.startswith(@char)\")\n",
    "    [['employeeNumber', 'lastName', 'firstName']]\n",
    "    .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "aa53f3cf-8c12-44a4-962b-c8b11d5bd421",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "d27bb275-57a2-4c97-840a-6e056c1135b8",
   "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>employeeNumber</th>\n",
       "      <th>lastName</th>\n",
       "      <th>firstName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1002</td>\n",
       "      <td>Murphy</td>\n",
       "      <td>Diane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1056</td>\n",
       "      <td>Patterson</td>\n",
       "      <td>Mary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1076</td>\n",
       "      <td>Firrelli</td>\n",
       "      <td>Jeff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1088</td>\n",
       "      <td>Patterson</td>\n",
       "      <td>William</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1165</td>\n",
       "      <td>Jennings</td>\n",
       "      <td>Leslie</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employeeNumber   lastName firstName\n",
       "0            1002     Murphy     Diane\n",
       "1            1056  Patterson      Mary\n",
       "2            1076   Firrelli      Jeff\n",
       "3            1088  Patterson   William\n",
       "4            1165   Jennings    Leslie"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "997f98b0-eb0d-49de-8400-2f107f48bf4b",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL LIKE operator with the ESCAPE clause"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7c264f5-8291-4b0d-b4ca-1f61f4b2a87d",
   "metadata": {},
   "source": [
    "- Find products whose product codes contain the string `_20`:\n",
    "`'%\\_20%'` is the same as `'%$_20%' ESCAPE '$'`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "e52f91c2-09de-4028-b3ea-c1b3e4340607",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT productCode, productName\n",
    "FROM products\n",
    "WHERE \n",
    "    # productCode LIKE '%\\_20%'\n",
    "    productCode LIKE '%$_20%' ESCAPE '$'\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(text(string), engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "f19fa542-3830-437e-97ad-5e0f2009abdc",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('products', engine)\n",
    "    .query(\"productCode.str.contains('_20')\")\n",
    "    [['productCode', 'productName']]\n",
    "    .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "73bb77da-9484-403e-ab69-0b2b6595ac7a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "080466a0-cad0-48f3-bece-eaed4b2a0b72",
   "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>productCode</th>\n",
       "      <th>productName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S10_2016</td>\n",
       "      <td>1996 Moto Guzzi 1100i</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S24_2000</td>\n",
       "      <td>1960 BSA Gold Star DBD34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S24_2011</td>\n",
       "      <td>18th century schooner</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S24_2022</td>\n",
       "      <td>1938 Cadillac V-16 Presidential Limousine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S700_2047</td>\n",
       "      <td>HMS Bounty</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  productCode                                productName\n",
       "0    S10_2016                      1996 Moto Guzzi 1100i\n",
       "1    S24_2000                   1960 BSA Gold Star DBD34\n",
       "2    S24_2011                      18th century schooner\n",
       "3    S24_2022  1938 Cadillac V-16 Presidential Limousine\n",
       "4   S700_2047                                 HMS Bounty"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  }
 ],
 "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
}