Section 03 Filtering Data/06 MySQL AND operator.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b5cf1741-b0da-46e0-8bbf-ee5e8372044e",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## **[MySQL AND operator](https://www.mysqltutorial.org/mysql-and/)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "72b82d36-82fb-4008-b685-7b0200700b5b",
   "metadata": {},
   "source": [
    "Use the MySQL AND operator to combine multiple Boolean expressions to filter data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "42ff6792-9141-431e-8c6e-5438f9d99abc",
   "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": "fa37bcc0-c1ea-4cfc-9a28-040465973f70",
   "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": "204cb6d7-e057-4964-a588-aa6fd72d67f2",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Introduction to MySQL AND operator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "8dee7102-0778-48c0-9a11-63ae38645b89",
   "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 and 1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   1 and 1\n",
       "0        1"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql('select 1 and 1;', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "fd0984cd-e23a-4a02-b83c-157a2a430b03",
   "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 AND 0</th>\n",
       "      <th>0 AND 1</th>\n",
       "      <th>0 AND 0</th>\n",
       "      <th>0 AND NULL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   1 AND 0  0 AND 1  0 AND 0  0 AND NULL\n",
       "0        0        0        0           0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql('''\n",
    "SELECT 1 AND 0, 0 AND 1, 0 AND 0, 0 AND NULL''', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5be268d7-1ff9-471e-9634-2e8d2af3aa2c",
   "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 AND NULL</th>\n",
       "      <th>NULL AND NULL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  1 AND NULL NULL AND NULL\n",
       "0       None          None"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql('''\n",
    "SELECT 1 AND NULL, NULL AND NULL''', engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c9e8b47-ad05-49e9-b1e3-d5669f65ce59",
   "metadata": {},
   "source": [
    "- Short-circuit evaluation: the expression `1 = 0` returns 0, MySQL can determine the result of the whole expression."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e91e1945-46ac-4de5-8c4e-e5044915af9f",
   "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 = 0 AND 1 / 0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   1 = 0 AND 1 / 0\n",
       "0                0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql('SELECT 1 = 0 AND 1 / 0;', engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e830555a-435f-448d-a50c-7f0b60ea6ee5",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL AND operator examples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "840f9642-292a-4db0-b222-8fea3a53f2a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT customerName, country, state\n",
    "FROM customers\n",
    "WHERE country = 'USA' AND state = 'CA';\n",
    "'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2bb823f4-f35e-4084-8726-1ccbe726bc76",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('customers', engine)\n",
    "    .query(\"country == 'USA' and state == 'CA'\")\n",
    "    [['customerName', 'country', 'state']]\n",
    "    .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "253015b5-46a1-4efa-b9d6-8796d1569fb4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "6cd8b2e6-e0ba-480c-915e-d5e653aca8c1",
   "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>customerName</th>\n",
       "      <th>country</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mini Gifts Distributors Ltd.</td>\n",
       "      <td>USA</td>\n",
       "      <td>CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mini Wheels Co.</td>\n",
       "      <td>USA</td>\n",
       "      <td>CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Technics Stores Inc.</td>\n",
       "      <td>USA</td>\n",
       "      <td>CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Toys4GrownUps.com</td>\n",
       "      <td>USA</td>\n",
       "      <td>CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Boards &amp; Toys Co.</td>\n",
       "      <td>USA</td>\n",
       "      <td>CA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   customerName country state\n",
       "0  Mini Gifts Distributors Ltd.     USA    CA\n",
       "1               Mini Wheels Co.     USA    CA\n",
       "2          Technics Stores Inc.     USA    CA\n",
       "3             Toys4GrownUps.com     USA    CA\n",
       "4             Boards & Toys Co.     USA    CA"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "03d13363-0a9d-4fbf-bff9-652959e6c456",
   "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>customerName</th>\n",
       "      <th>country</th>\n",
       "      <th>state</th>\n",
       "      <th>creditLimit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mini Gifts Distributors Ltd.</td>\n",
       "      <td>USA</td>\n",
       "      <td>CA</td>\n",
       "      <td>210500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Collectable Mini Designs Co.</td>\n",
       "      <td>USA</td>\n",
       "      <td>CA</td>\n",
       "      <td>105000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Corporate Gift Ideas Co.</td>\n",
       "      <td>USA</td>\n",
       "      <td>CA</td>\n",
       "      <td>105000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   customerName country state  creditLimit\n",
       "0  Mini Gifts Distributors Ltd.     USA    CA     210500.0\n",
       "1  Collectable Mini Designs Co.     USA    CA     105000.0\n",
       "2      Corporate Gift Ideas Co.     USA    CA     105000.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string = '''\n",
    "SELECT customerName, country, state, creditLimit\n",
    "FROM customers\n",
    "WHERE country = 'USA' AND state = 'CA' AND creditLimit > 100000;\n",
    "'''\n",
    "\n",
    "pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "336066f0-d660-4fea-a541-106d88e6c19a",
   "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>customerName</th>\n",
       "      <th>country</th>\n",
       "      <th>state</th>\n",
       "      <th>creditLimit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mini Gifts Distributors Ltd.</td>\n",
       "      <td>USA</td>\n",
       "      <td>CA</td>\n",
       "      <td>210500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Collectable Mini Designs Co.</td>\n",
       "      <td>USA</td>\n",
       "      <td>CA</td>\n",
       "      <td>105000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Corporate Gift Ideas Co.</td>\n",
       "      <td>USA</td>\n",
       "      <td>CA</td>\n",
       "      <td>105000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   customerName country state  creditLimit\n",
       "0  Mini Gifts Distributors Ltd.     USA    CA     210500.0\n",
       "1  Collectable Mini Designs Co.     USA    CA     105000.0\n",
       "2      Corporate Gift Ideas Co.     USA    CA     105000.0"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(pd.read_sql_table('customers', engine)\n",
    " .query(\"country == 'USA' and state == 'CA' and creditLimit > 100000\")\n",
    " [['customerName', 'country', 'state', 'creditLimit']]\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
}