Section 09 Modifying Data in MySQL/34 MySQL INSERT INTO SELECT.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cb57dbc2-ea7a-43ef-a7de-67efe055fe4f",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## **[MySQL INSERT INTO SELECT](https://www.mysqltutorial.org/mysql-insert-into-select/)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d48b121-b175-4dbc-9268-17cd5c583d52",
   "metadata": {},
   "source": [
    "Use the MySQL INSERT INTO SELECT statement to insert data into a table, where data comes from the result of a SELECT statement."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "57887971-de78-452f-be87-9f65f0f7a7b8",
   "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",
    "    'display.max_colwidth', None,\n",
    "    'display.max_rows', 10,\n",
    ")\n",
    "\n",
    "pd.set_option('display.width', 65)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "94e2fb21-3593-4cb9-9919-2d4e7e4544e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# connect to the classicmodels database\n",
    "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": "1ac96c98-5cf5-46cb-98fb-ace59c0ed2f5",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL INSERT INTO SELECT Overview"
   ]
  },
  {
   "cell_type": "raw",
   "id": "5edd632f-d34e-408e-ab81-e25530ee4acc",
   "metadata": {},
   "source": [
    "INSERT INTO table_name(c1,c2,...)\n",
    "VALUES(v1,v2,..);"
   ]
  },
  {
   "cell_type": "raw",
   "id": "f4c166d2-ca2c-45ed-8e8b-a82cbdc2d60e",
   "metadata": {},
   "source": [
    "INSERT INTO table_name(column_list)\n",
    "SELECT \n",
    "   select_list \n",
    "FROM \n",
    "   another_table\n",
    "WHERE\n",
    "   condition;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef76eabf-5aa8-447e-a1bc-05e7597201c1",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL INSERT INTO SELECT example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4b0acdb7-25ed-4720-9062-e0af8846c47d",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_30222/4061040428.py:17: RemovedIn20Warning: Deprecated API features detected! These feature(s) are not compatible with SQLAlchemy 2.0. To prevent incompatible upgrades prior to updating applications, ensure requirements files are pinned to \"sqlalchemy<2.0\". Set environment variable SQLALCHEMY_WARN_20=1 to show all deprecation warnings.  Set environment variable SQLALCHEMY_SILENCE_UBER_WARNING=1 to silence this message. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)\n",
      "  engine.execute('DROP TABLE IF EXISTS suppliers')\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f24ec30b9a0>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a new table called suppliers:\n",
    "string = '''\n",
    "CREATE TABLE suppliers (\n",
    "    supplierNumber INT AUTO_INCREMENT,\n",
    "    supplierName VARCHAR(50) NOT NULL,\n",
    "    phone VARCHAR(50),\n",
    "    addressLine1 VARCHAR(50),\n",
    "    addressLine2 VARCHAR(50),\n",
    "    city VARCHAR(50),\n",
    "    state VARCHAR(50),\n",
    "    postalCode VARCHAR(50),\n",
    "    country VARCHAR(50),\n",
    "    customerNumber INT,\n",
    "    PRIMARY KEY (supplierNumber)\n",
    ");'''\n",
    "\n",
    "engine.execute('DROP TABLE IF EXISTS suppliers')\n",
    "engine.execute(string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f89df55b-92e8-4201-83ea-421b919da87a",
   "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>customerNumber</th>\n",
       "      <th>customerName</th>\n",
       "      <th>phone</th>\n",
       "      <th>addressLine1</th>\n",
       "      <th>addressLine2</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>postalCode</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>124</td>\n",
       "      <td>Mini Gifts Distributors Ltd.</td>\n",
       "      <td>4155551450</td>\n",
       "      <td>5677 Strong St.</td>\n",
       "      <td>None</td>\n",
       "      <td>San Rafael</td>\n",
       "      <td>CA</td>\n",
       "      <td>97562</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>129</td>\n",
       "      <td>Mini Wheels Co.</td>\n",
       "      <td>6505555787</td>\n",
       "      <td>5557 North Pendale Street</td>\n",
       "      <td>None</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>CA</td>\n",
       "      <td>94217</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>161</td>\n",
       "      <td>Technics Stores Inc.</td>\n",
       "      <td>6505556809</td>\n",
       "      <td>9408 Furth Circle</td>\n",
       "      <td>None</td>\n",
       "      <td>Burlingame</td>\n",
       "      <td>CA</td>\n",
       "      <td>94217</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerNumber                  customerName       phone  \\\n",
       "0             124  Mini Gifts Distributors Ltd.  4155551450   \n",
       "1             129               Mini Wheels Co.  6505555787   \n",
       "2             161          Technics Stores Inc.  6505556809   \n",
       "\n",
       "                addressLine1 addressLine2           city state  \\\n",
       "0            5677 Strong St.         None     San Rafael    CA   \n",
       "1  5557 North Pendale Street         None  San Francisco    CA   \n",
       "2          9408 Furth Circle         None     Burlingame    CA   \n",
       "\n",
       "  postalCode country  \n",
       "0      97562     USA  \n",
       "1      94217     USA  \n",
       "2      94217     USA  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# find all customers who locate in California, USA\n",
    "string = '''\n",
    "SELECT\n",
    "    customerNumber, customerName, phone, \n",
    "    addressLine1, addressLine2, city,\n",
    "    state, postalCode, country\n",
    "FROM\n",
    "    customers\n",
    "WHERE\n",
    "    country = 'USA' AND state = 'CA'\n",
    ";'''\n",
    "\n",
    "pd.read_sql(string, engine).head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "077256a6-752a-412e-9230-bb0e95c74b52",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f24769d6730>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# insert customers who locate in California USA\n",
    "# from  the cusotmers table into the suppliers table\n",
    "string = '''\n",
    "INSERT INTO suppliers(\n",
    "    supplierName, phone, addressLine1, \n",
    "    addressLine2, city, state, postalCode,\n",
    "    country, customerNumber)\n",
    "SELECT\n",
    "    customerName, phone, addressLine1,\n",
    "    addressLine2, city, state, postalCode,\n",
    "    country, customerNumber\n",
    "FROM \n",
    "    customers\n",
    "WHERE\n",
    "    country = 'USA' AND state = 'CA'\n",
    ";'''\n",
    "\n",
    "engine.execute(string)\n",
    "\n",
    "df1 = pd.read_sql('SELECT * FROM suppliers', engine)\n",
    "\n",
    "engine.execute('DROP TABLE IF EXISTS suppliers')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "805b6116-5e98-4a03-aab6-8bc61af9fd59",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.DataFrame(\n",
    "    data=None,\n",
    "    columns=['supplierNumber', 'supplierName', 'phone', 'addressLine1',\n",
    "             'addressLine2', 'city', 'state', 'postalCode',\n",
    "             'country', 'customerNumber']\n",
    ")\n",
    "\n",
    "cus_usa_ca = (\n",
    "    pd.read_sql_table('customers', engine)\n",
    "    .query(\"country == 'USA' and state == 'CA'\")\n",
    "    .assign(supplierNumber = lambda df: range(1, len(df)+1),\n",
    "            supplierName = lambda df: df.customerName.str[:50],\n",
    "            phone = lambda df: df.phone.str[:50],\n",
    "            addressLine1 = lambda df: df.addressLine1.str[:50],\n",
    "            addressLine2 = lambda df: df.addressLine2.str[:50],\n",
    "            city = lambda df: df.city.str[:50],\n",
    "            state = lambda df: df.state.str[:50],\n",
    "            postalCode = lambda df: df.postalCode.str[:50],\n",
    "            country = lambda df: df.country.str[:50],\n",
    "            customerNumber = lambda df: df.customerNumber.astype(int)\n",
    "           )\n",
    "    [['supplierNumber', 'supplierName', 'phone', 'addressLine1',\n",
    "      'addressLine2', 'city', 'state', 'postalCode',\n",
    "      'country', 'customerNumber']]\n",
    "    .reset_index(drop=True))\n",
    "\n",
    "df2 = pd.concat([df2, cus_usa_ca])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "1c349d14-ba5e-45d7-a552-d5de7766b39e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "f9476ab2-42c4-489f-b681-8e05dcd1c3ef",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "supplierNumber     0\n",
       "supplierName       0\n",
       "phone              0\n",
       "addressLine1       0\n",
       "addressLine2      11\n",
       "city               0\n",
       "state              0\n",
       "postalCode         0\n",
       "country            0\n",
       "customerNumber     0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1 != df2).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "9099abd6-8df2-464f-9b4b-d058e22a9cb2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.addressLine2.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "342d348c-b1c8-4624-a10e-c3675527db46",
   "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>supplierNumber</th>\n",
       "      <th>supplierName</th>\n",
       "      <th>phone</th>\n",
       "      <th>addressLine1</th>\n",
       "      <th>addressLine2</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>postalCode</th>\n",
       "      <th>country</th>\n",
       "      <th>customerNumber</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Mini Gifts Distributors Ltd.</td>\n",
       "      <td>4155551450</td>\n",
       "      <td>5677 Strong St.</td>\n",
       "      <td>None</td>\n",
       "      <td>San Rafael</td>\n",
       "      <td>CA</td>\n",
       "      <td>97562</td>\n",
       "      <td>USA</td>\n",
       "      <td>124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Mini Wheels Co.</td>\n",
       "      <td>6505555787</td>\n",
       "      <td>5557 North Pendale Street</td>\n",
       "      <td>None</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>CA</td>\n",
       "      <td>94217</td>\n",
       "      <td>USA</td>\n",
       "      <td>129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Technics Stores Inc.</td>\n",
       "      <td>6505556809</td>\n",
       "      <td>9408 Furth Circle</td>\n",
       "      <td>None</td>\n",
       "      <td>Burlingame</td>\n",
       "      <td>CA</td>\n",
       "      <td>94217</td>\n",
       "      <td>USA</td>\n",
       "      <td>161</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   supplierNumber                  supplierName       phone  \\\n",
       "0               1  Mini Gifts Distributors Ltd.  4155551450   \n",
       "1               2               Mini Wheels Co.  6505555787   \n",
       "2               3          Technics Stores Inc.  6505556809   \n",
       "\n",
       "                addressLine1 addressLine2           city state  \\\n",
       "0            5677 Strong St.         None     San Rafael    CA   \n",
       "1  5557 North Pendale Street         None  San Francisco    CA   \n",
       "2          9408 Furth Circle         None     Burlingame    CA   \n",
       "\n",
       "  postalCode country  customerNumber  \n",
       "0      97562     USA             124  \n",
       "1      94217     USA             129  \n",
       "2      94217     USA             161  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6049c00-ce94-4bbd-8d5e-41b9e2919d51",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Using SELECT statement in the VALUES list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "5be2e0bb-8e8e-4569-88ef-8da57a569b55",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f24769f5cd0>"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a new table called stats\n",
    "string = '''\n",
    "CREATE TABLE stats (\n",
    "    totalProduct INT,\n",
    "    totalCustomer INT,\n",
    "    totalOrder INT\n",
    ");'''\n",
    "\n",
    "engine.execute('DROP TABLE IF EXISTS stats')\n",
    "engine.execute(string)\n",
    "\n",
    "# insert some data into the table\n",
    "string = '''\n",
    "INSERT INTO stats(totalProduct, totalCustomer, totalOrder)\n",
    "VALUES (\n",
    "    (SELECT COUNT(*) FROM products),\n",
    "    (SELECT COUNT(*) FROM customers),\n",
    "    (SELECT COUNT(*) FROM orders)\n",
    ");'''\n",
    "\n",
    "engine.execute(string)\n",
    "\n",
    "df1 = pd.read_sql('SELECT * FROM stats', engine)\n",
    "\n",
    "# drop the job done table\n",
    "engine.execute('DROP TABLE IF EXISTS stats')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "bee44b06-e607-4379-9adb-e0e3558d3260",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.DataFrame(\n",
    "    columns=['totalProduct', 'totalCustomer', 'totalOrder'])\n",
    "\n",
    "row = {'totalProduct': [len(pd.read_sql_table('products', engine))],\n",
    "       'totalCustomer': [len(pd.read_sql_table('customers', engine))],\n",
    "       'totalOrder': [len(pd.read_sql_table('orders', engine))]}\n",
    "\n",
    "df2 = pd.concat([df2, pd.DataFrame.from_dict(row)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "58462f24-8602-4061-95e1-43789b1a1ba4",
   "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>totalProduct</th>\n",
       "      <th>totalCustomer</th>\n",
       "      <th>totalOrder</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>110</td>\n",
       "      <td>122</td>\n",
       "      <td>326</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   totalProduct  totalCustomer  totalOrder\n",
       "0           110            122         326"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "67dda686-107b-4a9a-b53c-10af93a550c1",
   "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>totalProduct</th>\n",
       "      <th>totalCustomer</th>\n",
       "      <th>totalOrder</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>110</td>\n",
       "      <td>122</td>\n",
       "      <td>326</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  totalProduct totalCustomer totalOrder\n",
       "0          110           122        326"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  }
 ],
 "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
}