Section 09 Modifying Data in MySQL/33 MySQL INSERT Multiple Rows.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c8f765ff-898f-4ef7-b874-02d07322e04e",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## **[MySQL INSERT Multiple Rows](https://www.mysqltutorial.org/mysql-insert-multiple-rows/)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "505888bc-d376-42a6-88ec-7728368a914d",
   "metadata": {},
   "source": [
    "Use a single MySQL INSERT statement to insert multiple rows into a table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f2dfbf19-eae5-4ac8-835d-ce4880034ea3",
   "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": "fbd89b3b-ede6-472f-8975-2c327bfacddf",
   "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": "a7fe424a-aca9-4489-93ed-9ce2e94c04d8",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL INSERT multiple rows statement"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c89a6bd4-653e-4a3a-9ee2-4a2b815f4b0b",
   "metadata": {},
   "source": [
    "syntax:"
   ]
  },
  {
   "cell_type": "raw",
   "id": "5ebe54de-0196-477b-8257-f368b55c41ab",
   "metadata": {},
   "source": [
    "INSERT INTO table_name (column_list)\n",
    "VALUES\n",
    "\t(value_list_1),\n",
    "\t(value_list_2),\n",
    "\t...\n",
    "\t(value_list_n);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1378b2d1-1b19-4dc1-83cd-0d4a7c813970",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL INSERT multiple rows limit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "cf0dfe5a-7882-449d-bbbb-e508bb03ac1b",
   "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>Variable_name</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>max_allowed_packet</td>\n",
       "      <td>67108864</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Variable_name     Value\n",
       "0  max_allowed_packet  67108864"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql(\"SHOW VARIABLES LIKE 'max_allowed_packet'\", engine)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "e0d3eb7b-cdb7-4adc-a9be-7845d65bc089",
   "metadata": {},
   "source": [
    "# not working\n",
    "engine.execute('SET GLOBAL max_allowed_packet=4567')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "33de6ee8-d41d-4d12-a593-ae1097f20436",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL INSERT multiple rows example"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88a58747-8a7f-4276-a613-35c6f5e141e6",
   "metadata": {},
   "source": [
    "Create a new table projects for the demonstration:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f068c938-9e08-4f8e-bfe9-ef37a852778a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7fa5ca43f580>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.execute('DROP TABLE IF EXISTS projects')\n",
    "\n",
    "string = '''\n",
    "CREATE TABLE projects(\n",
    "    project_id INT AUTO_INCREMENT,\n",
    "    name VARCHAR(100) NOT NULL,\n",
    "    start_date DATE,\n",
    "    end_date DATE,\n",
    "    PRIMARY KEY (project_id)\n",
    ");'''\n",
    "\n",
    "engine.execute(string)\n",
    "\n",
    "# insert some data into the table\n",
    "string = '''\n",
    "INSERT INTO projects\n",
    "    (name, start_date, end_date)\n",
    "VALUES\n",
    "    ('AI for Marketing', '2019-08-01', '2019-12-31'),\n",
    "    ('ML for Sales', '2019-05-15', '2019-11-20')\n",
    ";'''\n",
    "\n",
    "engine.execute(string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "462e134e-0a4a-4868-86e6-3be60916c5f8",
   "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>project_id</th>\n",
       "      <th>name</th>\n",
       "      <th>start_date</th>\n",
       "      <th>end_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>AI for Marketing</td>\n",
       "      <td>2019-08-01</td>\n",
       "      <td>2019-12-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>ML for Sales</td>\n",
       "      <td>2019-05-15</td>\n",
       "      <td>2019-11-20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   project_id              name  start_date    end_date\n",
       "0           1  AI for Marketing  2019-08-01  2019-12-31\n",
       "1           2      ML for Sales  2019-05-15  2019-11-20"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# verify the inserts\n",
    "df1 = pd.read_sql('SELECT * FROM projects', engine)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f3543e1c-9ca9-4a27-9576-2c6173142c58",
   "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>project_id</th>\n",
       "      <th>name</th>\n",
       "      <th>start_date</th>\n",
       "      <th>end_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>AI for Marketing</td>\n",
       "      <td>2019-08-01</td>\n",
       "      <td>2019-12-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>ML for Sales</td>\n",
       "      <td>2019-05-15</td>\n",
       "      <td>2019-11-20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   project_id              name start_date   end_date\n",
       "0           1  AI for Marketing 2019-08-01 2019-12-31\n",
       "1           2      ML for Sales 2019-05-15 2019-11-20"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def change_type(df):\n",
    "    df = df.assign(\n",
    "        project_id = range(1, len(df)+1),\n",
    "        name = df.name.astype('string').str[:100],\n",
    "        start_date = df.start_date.astype('datetime64[ns]'),\n",
    "        end_date = df.end_date.astype('datetime64[ns]'))\n",
    "    return df\n",
    "\n",
    "df2 = pd.DataFrame(\n",
    "    columns=['project_id', 'name', 'start_date', 'end_date'])\n",
    "\n",
    "row  = {'name': ['AI for Marketing', 'ML for Sales'],\n",
    "        'start_date': ['2019-08-01', '2019-05-15'],\n",
    "        'end_date': ['2019-12-31', '2019-11-20']}\n",
    "\n",
    "df2 = (pd.concat([df2, pd.DataFrame.from_dict(row)])\n",
    "       .pipe(change_type))\n",
    "\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "ca0ccf60-b9cc-42f9-bef0-c3a0add15ca6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7fa5ca43f700>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.execute('DROP TABLE IF EXISTS projects')"
   ]
  }
 ],
 "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
}