{
 "metadata": {
  "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.7.6-final"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Common Usage"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connect to DB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql mysql+mysqlconnector://root:root@localhost:3306"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Database Management"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n6 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('information_schema',),\n ('mysql',),\n ('performance_schema',),\n ('sakila',),\n ('sys',),\n ('world',)]",
      "text/html": "<table>\n    <tr>\n        <th>Database</th>\n    </tr>\n    <tr>\n        <td>information_schema</td>\n    </tr>\n    <tr>\n        <td>mysql</td>\n    </tr>\n    <tr>\n        <td>performance_schema</td>\n    </tr>\n    <tr>\n        <td>sakila</td>\n    </tr>\n    <tr>\n        <td>sys</td>\n    </tr>\n    <tr>\n        <td>world</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "source": [
    "%%sql\n",
    "SHOW databases;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n6 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('%', 'admin', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0, 'caching_sha2_password', '$A$005$0s\\x1a\\x12%|\\x0f<&t./k:}p P{tpIir.lyQtoJbD1IqQv1wz3joWKjUWZmmz2e8Prn7fW.', 'N', datetime.datetime(2020, 5, 27, 14, 41, 35), None, 'N', 'N', 'N', None, None, None, None),\n ('localhost', 'mysql.infoschema', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, 'caching_sha2_password', '$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED', 'N', datetime.datetime(2020, 5, 27, 14, 41, 32), None, 'Y', 'N', 'N', None, None, None, None),\n ('localhost', 'mysql.session', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, 'caching_sha2_password', '$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED', 'N', datetime.datetime(2020, 5, 27, 14, 41, 32), None, 'Y', 'N', 'N', None, None, None, None),\n ('localhost', 'mysql.sys', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, 'caching_sha2_password', '$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED', 'N', datetime.datetime(2020, 5, 27, 14, 41, 32), None, 'Y', 'N', 'N', None, None, None, None),\n ('localhost', 'root', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0, 'caching_sha2_password', '$A$005$5EQPBjv|}zQ3X\\x0e\\x02\\nV{ciITiWzr5sNajlp8MOUWtzZp7Kpk0H0HYxbfUTtjrD15C', 'N', datetime.datetime(2020, 5, 27, 14, 41, 35), None, 'N', 'Y', 'Y', None, None, None, None),\n ('localhost', 'user', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, 'caching_sha2_password', '$A$005$Y\\x19R_6%\\x08By\\npV+8\\x03t<04kJ62nrI5BXhSWdoj9Yg6j2jxkWqmy2xq43o3PkKoiug1', 'N', datetime.datetime(2020, 8, 25, 0, 32, 28), None, 'N', 'N', 'N', None, None, None, None)]",
      "text/html": "<table>\n    <tr>\n        <th>Host</th>\n        <th>User</th>\n        <th>Select_priv</th>\n        <th>Insert_priv</th>\n        <th>Update_priv</th>\n        <th>Delete_priv</th>\n        <th>Create_priv</th>\n        <th>Drop_priv</th>\n        <th>Reload_priv</th>\n        <th>Shutdown_priv</th>\n        <th>Process_priv</th>\n        <th>File_priv</th>\n        <th>Grant_priv</th>\n        <th>References_priv</th>\n        <th>Index_priv</th>\n        <th>Alter_priv</th>\n        <th>Show_db_priv</th>\n        <th>Super_priv</th>\n        <th>Create_tmp_table_priv</th>\n        <th>Lock_tables_priv</th>\n        <th>Execute_priv</th>\n        <th>Repl_slave_priv</th>\n        <th>Repl_client_priv</th>\n        <th>Create_view_priv</th>\n        <th>Show_view_priv</th>\n        <th>Create_routine_priv</th>\n        <th>Alter_routine_priv</th>\n        <th>Create_user_priv</th>\n        <th>Event_priv</th>\n        <th>Trigger_priv</th>\n        <th>Create_tablespace_priv</th>\n        <th>ssl_type</th>\n        <th>ssl_cipher</th>\n        <th>x509_issuer</th>\n        <th>x509_subject</th>\n        <th>max_questions</th>\n        <th>max_updates</th>\n        <th>max_connections</th>\n        <th>max_user_connections</th>\n        <th>plugin</th>\n        <th>authentication_string</th>\n        <th>password_expired</th>\n        <th>password_last_changed</th>\n        <th>password_lifetime</th>\n        <th>account_locked</th>\n        <th>Create_role_priv</th>\n        <th>Drop_role_priv</th>\n        <th>Password_reuse_history</th>\n        <th>Password_reuse_time</th>\n        <th>Password_require_current</th>\n        <th>User_attributes</th>\n    </tr>\n    <tr>\n        <td>%</td>\n        <td>admin</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td></td>\n        <td></td>\n        <td></td>\n        <td></td>\n        <td>0</td>\n        <td>0</td>\n        <td>0</td>\n        <td>0</td>\n        <td>caching_sha2_password</td>\n        <td>$A$005$0s\u001a\u0012%|\u000f&lt;&amp;t./k:}p P{tpIir.lyQtoJbD1IqQv1wz3joWKjUWZmmz2e8Prn7fW.</td>\n        <td>N</td>\n        <td>2020-05-27 14:41:35</td>\n        <td>None</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>None</td>\n        <td>None</td>\n        <td>None</td>\n        <td>None</td>\n    </tr>\n    <tr>\n        <td>localhost</td>\n        <td>mysql.infoschema</td>\n        <td>Y</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td></td>\n        <td></td>\n        <td></td>\n        <td></td>\n        <td>0</td>\n        <td>0</td>\n        <td>0</td>\n        <td>0</td>\n        <td>caching_sha2_password</td>\n        <td>$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED</td>\n        <td>N</td>\n        <td>2020-05-27 14:41:32</td>\n        <td>None</td>\n        <td>Y</td>\n        <td>N</td>\n        <td>N</td>\n        <td>None</td>\n        <td>None</td>\n        <td>None</td>\n        <td>None</td>\n    </tr>\n    <tr>\n        <td>localhost</td>\n        <td>mysql.session</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>Y</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>Y</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td></td>\n        <td></td>\n        <td></td>\n        <td></td>\n        <td>0</td>\n        <td>0</td>\n        <td>0</td>\n        <td>0</td>\n        <td>caching_sha2_password</td>\n        <td>$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED</td>\n        <td>N</td>\n        <td>2020-05-27 14:41:32</td>\n        <td>None</td>\n        <td>Y</td>\n        <td>N</td>\n        <td>N</td>\n        <td>None</td>\n        <td>None</td>\n        <td>None</td>\n        <td>None</td>\n    </tr>\n    <tr>\n        <td>localhost</td>\n        <td>mysql.sys</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td></td>\n        <td></td>\n        <td></td>\n        <td></td>\n        <td>0</td>\n        <td>0</td>\n        <td>0</td>\n        <td>0</td>\n        <td>caching_sha2_password</td>\n        <td>$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED</td>\n        <td>N</td>\n        <td>2020-05-27 14:41:32</td>\n        <td>None</td>\n        <td>Y</td>\n        <td>N</td>\n        <td>N</td>\n        <td>None</td>\n        <td>None</td>\n        <td>None</td>\n        <td>None</td>\n    </tr>\n    <tr>\n        <td>localhost</td>\n        <td>root</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td></td>\n        <td></td>\n        <td></td>\n        <td></td>\n        <td>0</td>\n        <td>0</td>\n        <td>0</td>\n        <td>0</td>\n        <td>caching_sha2_password</td>\n        <td>$A$005$5EQPBjv|}zQ3X\u000e\u0002<br>V{ciITiWzr5sNajlp8MOUWtzZp7Kpk0H0HYxbfUTtjrD15C</td>\n        <td>N</td>\n        <td>2020-05-27 14:41:35</td>\n        <td>None</td>\n        <td>N</td>\n        <td>Y</td>\n        <td>Y</td>\n        <td>None</td>\n        <td>None</td>\n        <td>None</td>\n        <td>None</td>\n    </tr>\n    <tr>\n        <td>localhost</td>\n        <td>user</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td></td>\n        <td></td>\n        <td></td>\n        <td></td>\n        <td>0</td>\n        <td>0</td>\n        <td>0</td>\n        <td>0</td>\n        <td>caching_sha2_password</td>\n        <td>$A$005$Y\u0019R_6%\bBy<br>pV+8\u0003t&lt;04kJ62nrI5BXhSWdoj9Yg6j2jxkWqmy2xq43o3PkKoiug1</td>\n        <td>N</td>\n        <td>2020-08-25 00:32:28</td>\n        <td>None</td>\n        <td>N</td>\n        <td>N</td>\n        <td>N</td>\n        <td>None</td>\n        <td>None</td>\n        <td>None</td>\n        <td>None</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 6
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * from mysql.user;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n1 rows affected.\n1 rows affected.\n8 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('information_schema',),\n ('mysql',),\n ('performance_schema',),\n ('sakila',),\n ('sys',),\n ('testdb',),\n ('testschema',),\n ('world',)]",
      "text/html": "<table>\n    <tr>\n        <th>Database</th>\n    </tr>\n    <tr>\n        <td>information_schema</td>\n    </tr>\n    <tr>\n        <td>mysql</td>\n    </tr>\n    <tr>\n        <td>performance_schema</td>\n    </tr>\n    <tr>\n        <td>sakila</td>\n    </tr>\n    <tr>\n        <td>sys</td>\n    </tr>\n    <tr>\n        <td>testdb</td>\n    </tr>\n    <tr>\n        <td>testschema</td>\n    </tr>\n    <tr>\n        <td>world</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 7
    }
   ],
   "source": [
    "# create database/schema\n",
    "%%sql\n",
    "CREATE SCHEMA testSchema;\n",
    "CREATE DATABASE if not exists testDB;\n",
    "SHOW databases;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n0 rows affected.\n0 rows affected.\n7 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('information_schema',),\n ('mysql',),\n ('performance_schema',),\n ('sakila',),\n ('sys',),\n ('testdb',),\n ('world',)]",
      "text/html": "<table>\n    <tr>\n        <th>Database</th>\n    </tr>\n    <tr>\n        <td>information_schema</td>\n    </tr>\n    <tr>\n        <td>mysql</td>\n    </tr>\n    <tr>\n        <td>performance_schema</td>\n    </tr>\n    <tr>\n        <td>sakila</td>\n    </tr>\n    <tr>\n        <td>sys</td>\n    </tr>\n    <tr>\n        <td>testdb</td>\n    </tr>\n    <tr>\n        <td>world</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 8
    }
   ],
   "source": [
    "# drop database/schema\n",
    "%%sql\n",
    "DROP SCHEMA IF EXISTS testschema;\n",
    "DROP DATABASE IF EXISTS test;\n",
    "SHOW databases;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n0 rows affected.\n * mysql+mysqlconnector://root:***@localhost:3306\n0 rows affected.\n * mysql+mysqlconnector://root:***@localhost:3306\n0 rows affected.\n * mysql+mysqlconnector://root:***@localhost:3306\n2 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORAR ... (77 characters truncated) ...  VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `admin`@`localhost`',),\n ('GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROU ... (196 characters truncated) ... ION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `admin`@`localhost`',)]",
      "text/html": "<table>\n    <tr>\n        <th>Grants for admin@localhost</th>\n    </tr>\n    <tr>\n        <td>GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `admin`@`localhost`</td>\n    </tr>\n    <tr>\n        <td>GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `admin`@`localhost`</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 13
    }
   ],
   "source": [
    "# create privileged user\n",
    "%sql CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin';\n",
    "%sql GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';\n",
    "%sql FLUSH PRIVILEGES;\n",
    "%sql SHOW GRANTS FOR admin@localhost;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n0 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[]"
     },
     "metadata": {},
     "execution_count": 16
    }
   ],
   "source": [
    "# remove all privileges\n",
    "%sql REVOKE ALL PRIVILEGES, GRANT OPTION FROM user@localhost;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n2 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORAR ... (77 characters truncated) ...  VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `admin`@`localhost`',),\n ('GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROU ... (196 characters truncated) ... ION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `admin`@`localhost`',)]",
      "text/html": "<table>\n    <tr>\n        <th>Grants for admin@localhost</th>\n    </tr>\n    <tr>\n        <td>GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `admin`@`localhost`</td>\n    </tr>\n    <tr>\n        <td>GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `admin`@`localhost`</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 17
    }
   ],
   "source": [
    "%sql SHOW GRANTS FOR admin@localhost;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n0 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[]"
     },
     "metadata": {},
     "execution_count": 18
    }
   ],
   "source": [
    "# Drop User\n",
    "%%sql\n",
    "DROP USER 'admin'@'localhost';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n0 rows affected.\n * mysql+mysqlconnector://root:***@localhost:3306\n1 rows affected.\n * mysql+mysqlconnector://root:***@localhost:3306\n0 rows affected.\n * mysql+mysqlconnector://root:***@localhost:3306\n2 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('GRANT USAGE ON *.* TO `user`@`localhost`',),\n ('GRANT ALL PRIVILEGES ON `world`.* TO `user`@`localhost`',)]",
      "text/html": "<table>\n    <tr>\n        <th>Grants for user@localhost</th>\n    </tr>\n    <tr>\n        <td>GRANT USAGE ON *.* TO `user`@`localhost`</td>\n    </tr>\n    <tr>\n        <td>GRANT ALL PRIVILEGES ON `world`.* TO `user`@`localhost`</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 20
    }
   ],
   "source": [
    "# Grant Specific Privileges\n",
    "%sql CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';\n",
    "%sql SHOW GRANTS FOR user@localhost;\n",
    "%sql GRANT ALL ON world.* TO 'user'@'localhost';\n",
    "%sql\n",
    " SHOW GRANTS FOR user@localhost;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n0 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[]"
     },
     "metadata": {},
     "execution_count": 21
    }
   ],
   "source": [
    "%sql DROP USER 'user'@'localhost';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n0 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[]"
     },
     "metadata": {},
     "execution_count": 22
    }
   ],
   "source": [
    "%%sql\n",
    "USE world;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n5 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('city',), ('country',), ('countrylanguage',), ('person',), ('student',)]",
      "text/html": "<table>\n    <tr>\n        <th>Tables_in_world</th>\n    </tr>\n    <tr>\n        <td>city</td>\n    </tr>\n    <tr>\n        <td>country</td>\n    </tr>\n    <tr>\n        <td>countrylanguage</td>\n    </tr>\n    <tr>\n        <td>person</td>\n    </tr>\n    <tr>\n        <td>student</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 23
    }
   ],
   "source": [
    "# list tables in the chosen database\n",
    "%%sql\n",
    "SHOW TABLES;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n1 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('city', 'InnoDB', 10, 'Dynamic', 4188, 97, 409600, 0, 131072, 0, 4080, datetime.datetime(2020, 5, 27, 14, 42, 6), None, None, 'latin1_swedish_ci', None, '', '')]",
      "text/html": "<table>\n    <tr>\n        <th>Name</th>\n        <th>Engine</th>\n        <th>Version</th>\n        <th>Row_format</th>\n        <th>Rows</th>\n        <th>Avg_row_length</th>\n        <th>Data_length</th>\n        <th>Max_data_length</th>\n        <th>Index_length</th>\n        <th>Data_free</th>\n        <th>Auto_increment</th>\n        <th>Create_time</th>\n        <th>Update_time</th>\n        <th>Check_time</th>\n        <th>Collation</th>\n        <th>Checksum</th>\n        <th>Create_options</th>\n        <th>Comment</th>\n    </tr>\n    <tr>\n        <td>city</td>\n        <td>InnoDB</td>\n        <td>10</td>\n        <td>Dynamic</td>\n        <td>4188</td>\n        <td>97</td>\n        <td>409600</td>\n        <td>0</td>\n        <td>131072</td>\n        <td>0</td>\n        <td>4080</td>\n        <td>2020-05-27 14:42:06</td>\n        <td>None</td>\n        <td>None</td>\n        <td>latin1_swedish_ci</td>\n        <td>None</td>\n        <td></td>\n        <td></td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 25
    }
   ],
   "source": [
    "# View Store Engine\n",
    "%sql SHOW TABLE STATUS LIKE 'city';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n9 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('MEMORY', 'YES', 'Hash based, stored in memory, useful for temporary tables', 'NO', 'NO', 'NO'),\n ('MRG_MYISAM', 'YES', 'Collection of identical MyISAM tables', 'NO', 'NO', 'NO'),\n ('CSV', 'YES', 'CSV storage engine', 'NO', 'NO', 'NO'),\n ('FEDERATED', 'NO', 'Federated MySQL storage engine', None, None, None),\n ('PERFORMANCE_SCHEMA', 'YES', 'Performance Schema', 'NO', 'NO', 'NO'),\n ('MyISAM', 'YES', 'MyISAM storage engine', 'NO', 'NO', 'NO'),\n ('InnoDB', 'DEFAULT', 'Supports transactions, row-level locking, and foreign keys', 'YES', 'YES', 'YES'),\n ('BLACKHOLE', 'YES', '/dev/null storage engine (anything you write to it disappears)', 'NO', 'NO', 'NO'),\n ('ARCHIVE', 'YES', 'Archive storage engine', 'NO', 'NO', 'NO')]",
      "text/html": "<table>\n    <tr>\n        <th>Engine</th>\n        <th>Support</th>\n        <th>Comment</th>\n        <th>Transactions</th>\n        <th>XA</th>\n        <th>Savepoints</th>\n    </tr>\n    <tr>\n        <td>MEMORY</td>\n        <td>YES</td>\n        <td>Hash based, stored in memory, useful for temporary tables</td>\n        <td>NO</td>\n        <td>NO</td>\n        <td>NO</td>\n    </tr>\n    <tr>\n        <td>MRG_MYISAM</td>\n        <td>YES</td>\n        <td>Collection of identical MyISAM tables</td>\n        <td>NO</td>\n        <td>NO</td>\n        <td>NO</td>\n    </tr>\n    <tr>\n        <td>CSV</td>\n        <td>YES</td>\n        <td>CSV storage engine</td>\n        <td>NO</td>\n        <td>NO</td>\n        <td>NO</td>\n    </tr>\n    <tr>\n        <td>FEDERATED</td>\n        <td>NO</td>\n        <td>Federated MySQL storage engine</td>\n        <td>None</td>\n        <td>None</td>\n        <td>None</td>\n    </tr>\n    <tr>\n        <td>PERFORMANCE_SCHEMA</td>\n        <td>YES</td>\n        <td>Performance Schema</td>\n        <td>NO</td>\n        <td>NO</td>\n        <td>NO</td>\n    </tr>\n    <tr>\n        <td>MyISAM</td>\n        <td>YES</td>\n        <td>MyISAM storage engine</td>\n        <td>NO</td>\n        <td>NO</td>\n        <td>NO</td>\n    </tr>\n    <tr>\n        <td>InnoDB</td>\n        <td>DEFAULT</td>\n        <td>Supports transactions, row-level locking, and foreign keys</td>\n        <td>YES</td>\n        <td>YES</td>\n        <td>YES</td>\n    </tr>\n    <tr>\n        <td>BLACKHOLE</td>\n        <td>YES</td>\n        <td>/dev/null storage engine (anything you write to it disappears)</td>\n        <td>NO</td>\n        <td>NO</td>\n        <td>NO</td>\n    </tr>\n    <tr>\n        <td>ARCHIVE</td>\n        <td>YES</td>\n        <td>Archive storage engine</td>\n        <td>NO</td>\n        <td>NO</td>\n        <td>NO</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 26
    }
   ],
   "source": [
    "%%sql\n",
    "SHOW ENGINES;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n"
    },
    {
     "output_type": "error",
     "ename": "DatabaseError",
     "evalue": "(mysql.connector.errors.DatabaseError) 3776 (HY000): Cannot change table's storage engine because the table participates in a foreign key constraint.\n[SQL: ALTER TABLE city ENGINE = 'myisam';]\n(Background on this error at: http://sqlalche.me/e/4xp6)",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mMySQLInterfaceError\u001b[0m                       Traceback (most recent call last)",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\mysql\\connector\\connection_cext.py\u001b[0m in \u001b[0;36mcmd_query\u001b[1;34m(self, query, raw, buffered, raw_as_string)\u001b[0m\n\u001b[0;32m    488\u001b[0m                                \u001b[0mraw\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mraw\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mbuffered\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mbuffered\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 489\u001b[1;33m                                raw_as_string=raw_as_string)\n\u001b[0m\u001b[0;32m    490\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mMySQLInterfaceError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mMySQLInterfaceError\u001b[0m: Cannot change table's storage engine because the table participates in a foreign key constraint.",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[1;31mDatabaseError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[1;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[0;32m   1245\u001b[0m                     self.dialect.do_execute(\n\u001b[1;32m-> 1246\u001b[1;33m                         \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1247\u001b[0m                     )\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[1;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[0;32m    587\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 588\u001b[1;33m         \u001b[0mcursor\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    589\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\mysql\\connector\\cursor_cext.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, operation, params, multi)\u001b[0m\n\u001b[0;32m    265\u001b[0m                                          \u001b[0mbuffered\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_buffered\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 266\u001b[1;33m                                          raw_as_string=self._raw_as_string)\n\u001b[0m\u001b[0;32m    267\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mMySQLInterfaceError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\mysql\\connector\\connection_cext.py\u001b[0m in \u001b[0;36mcmd_query\u001b[1;34m(self, query, raw, buffered, raw_as_string)\u001b[0m\n\u001b[0;32m    491\u001b[0m             raise errors.get_mysql_exception(exc.errno, msg=exc.msg,\n\u001b[1;32m--> 492\u001b[1;33m                                              sqlstate=exc.sqlstate)\n\u001b[0m\u001b[0;32m    493\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mAttributeError\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mDatabaseError\u001b[0m: 3776 (HY000): Cannot change table's storage engine because the table participates in a foreign key constraint.",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[1;31mDatabaseError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-27-eb56b23bf540>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mget_ipython\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrun_cell_magic\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'sql'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m''\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m\"ALTER TABLE city ENGINE = 'myisam';\\n\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py\u001b[0m in \u001b[0;36mrun_cell_magic\u001b[1;34m(self, magic_name, line, cell)\u001b[0m\n\u001b[0;32m   2360\u001b[0m             \u001b[1;32mwith\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mbuiltin_trap\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2361\u001b[0m                 \u001b[0margs\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m(\u001b[0m\u001b[0mmagic_arg_s\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcell\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2362\u001b[1;33m                 \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mfn\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2363\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2364\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m<C:\\ProgramData\\Anaconda3\\lib\\site-packages\\decorator.py:decorator-gen-128>\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, line, cell, local_ns)\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\IPython\\core\\magic.py\u001b[0m in \u001b[0;36m<lambda>\u001b[1;34m(f, *a, **k)\u001b[0m\n\u001b[0;32m    185\u001b[0m     \u001b[1;31m# but it's overkill for just that one bit of state.\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    186\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mmagic_deco\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marg\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 187\u001b[1;33m         \u001b[0mcall\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mlambda\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[0ma\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mk\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0ma\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mk\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    188\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    189\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mcallable\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marg\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m<C:\\ProgramData\\Anaconda3\\lib\\site-packages\\decorator.py:decorator-gen-127>\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, line, cell, local_ns)\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\IPython\\core\\magic.py\u001b[0m in \u001b[0;36m<lambda>\u001b[1;34m(f, *a, **k)\u001b[0m\n\u001b[0;32m    185\u001b[0m     \u001b[1;31m# but it's overkill for just that one bit of state.\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    186\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mmagic_deco\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marg\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 187\u001b[1;33m         \u001b[0mcall\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mlambda\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[0ma\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mk\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0ma\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mk\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    188\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    189\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mcallable\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marg\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sql\\magic.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, line, cell, local_ns)\u001b[0m\n\u001b[0;32m    215\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    216\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 217\u001b[1;33m             \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0msql\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrun\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrun\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparsed\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"sql\"\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0muser_ns\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    218\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    219\u001b[0m             if (\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sql\\run.py\u001b[0m in \u001b[0;36mrun\u001b[1;34m(conn, sql, config, user_namespace)\u001b[0m\n\u001b[0;32m    365\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    366\u001b[0m                 \u001b[0mtxt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0msqlalchemy\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msql\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtext\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 367\u001b[1;33m                 \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mconn\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msession\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mtxt\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0muser_namespace\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    368\u001b[0m             \u001b[0m_commit\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mconn\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mconfig\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mconfig\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    369\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mresult\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0mconfig\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfeedback\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, object_, *multiparams, **params)\u001b[0m\n\u001b[0;32m    980\u001b[0m             \u001b[1;32mraise\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mObjectNotExecutableError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mobject_\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    981\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 982\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mmeth\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    983\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    984\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_execute_function\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\sql\\elements.py\u001b[0m in \u001b[0;36m_execute_on_connection\u001b[1;34m(self, connection, multiparams, params)\u001b[0m\n\u001b[0;32m    291\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_execute_on_connection\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mconnection\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    292\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msupports_execution\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 293\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mconnection\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_execute_clauseelement\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    294\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    295\u001b[0m             \u001b[1;32mraise\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mObjectNotExecutableError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_execute_clauseelement\u001b[1;34m(self, elem, multiparams, params)\u001b[0m\n\u001b[0;32m   1099\u001b[0m             \u001b[0mdistilled_params\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1100\u001b[0m             \u001b[0mcompiled_sql\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1101\u001b[1;33m             \u001b[0mdistilled_params\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1102\u001b[0m         )\n\u001b[0;32m   1103\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_has_events\u001b[0m \u001b[1;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_has_events\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[1;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[0;32m   1248\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1249\u001b[0m             self._handle_dbapi_exception(\n\u001b[1;32m-> 1250\u001b[1;33m                 \u001b[0me\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1251\u001b[0m             )\n\u001b[0;32m   1252\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[1;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[0;32m   1474\u001b[0m                 \u001b[0mutil\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mraise_from_cause\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mnewraise\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1475\u001b[0m             \u001b[1;32melif\u001b[0m \u001b[0mshould_wrap\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1476\u001b[1;33m                 \u001b[0mutil\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mraise_from_cause\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msqlalchemy_exception\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1477\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1478\u001b[0m                 \u001b[0mutil\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mreraise\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\util\\compat.py\u001b[0m in \u001b[0;36mraise_from_cause\u001b[1;34m(exception, exc_info)\u001b[0m\n\u001b[0;32m    396\u001b[0m     \u001b[0mexc_type\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mexc_value\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mexc_tb\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    397\u001b[0m     \u001b[0mcause\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mexc_value\u001b[0m \u001b[1;32mif\u001b[0m \u001b[0mexc_value\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mexception\u001b[0m \u001b[1;32melse\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 398\u001b[1;33m     \u001b[0mreraise\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mtype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mexception\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mexception\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mtb\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mexc_tb\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcause\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mcause\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    399\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    400\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\util\\compat.py\u001b[0m in \u001b[0;36mreraise\u001b[1;34m(tp, value, tb, cause)\u001b[0m\n\u001b[0;32m    150\u001b[0m             \u001b[0mvalue\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__cause__\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mcause\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    151\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mvalue\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__traceback__\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mtb\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 152\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mvalue\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mwith_traceback\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mtb\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    153\u001b[0m         \u001b[1;32mraise\u001b[0m \u001b[0mvalue\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    154\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[1;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[0;32m   1244\u001b[0m                 \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1245\u001b[0m                     self.dialect.do_execute(\n\u001b[1;32m-> 1246\u001b[1;33m                         \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1247\u001b[0m                     )\n\u001b[0;32m   1248\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[1;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[0;32m    586\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    587\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 588\u001b[1;33m         \u001b[0mcursor\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    589\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    590\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mdo_execute_no_params\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\mysql\\connector\\cursor_cext.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, operation, params, multi)\u001b[0m\n\u001b[0;32m    264\u001b[0m             result = self._cnx.cmd_query(stmt, raw=self._raw,\n\u001b[0;32m    265\u001b[0m                                          \u001b[0mbuffered\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_buffered\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 266\u001b[1;33m                                          raw_as_string=self._raw_as_string)\n\u001b[0m\u001b[0;32m    267\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mMySQLInterfaceError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    268\u001b[0m             raise errors.get_mysql_exception(msg=exc.msg, errno=exc.errno,\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\mysql\\connector\\connection_cext.py\u001b[0m in \u001b[0;36mcmd_query\u001b[1;34m(self, query, raw, buffered, raw_as_string)\u001b[0m\n\u001b[0;32m    490\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mMySQLInterfaceError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    491\u001b[0m             raise errors.get_mysql_exception(exc.errno, msg=exc.msg,\n\u001b[1;32m--> 492\u001b[1;33m                                              sqlstate=exc.sqlstate)\n\u001b[0m\u001b[0;32m    493\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mAttributeError\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    494\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_unix_socket\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mDatabaseError\u001b[0m: (mysql.connector.errors.DatabaseError) 3776 (HY000): Cannot change table's storage engine because the table participates in a foreign key constraint.\n[SQL: ALTER TABLE city ENGINE = 'myisam';]\n(Background on this error at: http://sqlalche.me/e/4xp6)"
     ]
    }
   ],
   "source": [
    "## Fail to change to another engine because there is foreigh key which InnoDB does but the new one doesn't.\n",
    "%%sql\n",
    "ALTER TABLE city ENGINE = 'myisam';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n5 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('ID', 'int', 'NO', 'PRI', None, 'auto_increment'),\n ('Name', 'char(35)', 'NO', '', '', ''),\n ('CountryCode', 'char(3)', 'NO', 'MUL', '', ''),\n ('District', 'char(20)', 'NO', '', '', ''),\n ('Population', 'int', 'NO', '', '0', '')]",
      "text/html": "<table>\n    <tr>\n        <th>Field</th>\n        <th>Type</th>\n        <th>Null</th>\n        <th>Key</th>\n        <th>Default</th>\n        <th>Extra</th>\n    </tr>\n    <tr>\n        <td>ID</td>\n        <td>int</td>\n        <td>NO</td>\n        <td>PRI</td>\n        <td>None</td>\n        <td>auto_increment</td>\n    </tr>\n    <tr>\n        <td>Name</td>\n        <td>char(35)</td>\n        <td>NO</td>\n        <td></td>\n        <td></td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>CountryCode</td>\n        <td>char(3)</td>\n        <td>NO</td>\n        <td>MUL</td>\n        <td></td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>District</td>\n        <td>char(20)</td>\n        <td>NO</td>\n        <td></td>\n        <td></td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>Population</td>\n        <td>int</td>\n        <td>NO</td>\n        <td></td>\n        <td>0</td>\n        <td></td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 29
    }
   ],
   "source": [
    "%%sql\n",
    "DESC city;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n15 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('Code', 'char(3)', 'NO', 'PRI', '', ''),\n ('Name', 'char(52)', 'NO', '', '', ''),\n ('Continent', \"enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America')\", 'NO', '', 'Asia', ''),\n ('Region', 'char(26)', 'NO', '', '', ''),\n ('SurfaceArea', 'float(10,2)', 'NO', '', '0.00', ''),\n ('IndepYear', 'smallint', 'YES', '', None, ''),\n ('Population', 'int', 'NO', '', '0', ''),\n ('LifeExpectancy', 'float(3,1)', 'YES', '', None, ''),\n ('GNP', 'float(10,2)', 'YES', '', None, ''),\n ('GNPOld', 'float(10,2)', 'YES', '', None, ''),\n ('LocalName', 'char(45)', 'NO', '', '', ''),\n ('GovernmentForm', 'char(45)', 'NO', '', '', ''),\n ('HeadOfState', 'char(60)', 'YES', '', None, ''),\n ('Capital', 'int', 'YES', '', None, ''),\n ('Code2', 'char(2)', 'NO', '', '', '')]",
      "text/html": "<table>\n    <tr>\n        <th>Field</th>\n        <th>Type</th>\n        <th>Null</th>\n        <th>Key</th>\n        <th>Default</th>\n        <th>Extra</th>\n    </tr>\n    <tr>\n        <td>Code</td>\n        <td>char(3)</td>\n        <td>NO</td>\n        <td>PRI</td>\n        <td></td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>Name</td>\n        <td>char(52)</td>\n        <td>NO</td>\n        <td></td>\n        <td></td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>Continent</td>\n        <td>enum(&#x27;Asia&#x27;,&#x27;Europe&#x27;,&#x27;North America&#x27;,&#x27;Africa&#x27;,&#x27;Oceania&#x27;,&#x27;Antarctica&#x27;,&#x27;South America&#x27;)</td>\n        <td>NO</td>\n        <td></td>\n        <td>Asia</td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>Region</td>\n        <td>char(26)</td>\n        <td>NO</td>\n        <td></td>\n        <td></td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>SurfaceArea</td>\n        <td>float(10,2)</td>\n        <td>NO</td>\n        <td></td>\n        <td>0.00</td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>IndepYear</td>\n        <td>smallint</td>\n        <td>YES</td>\n        <td></td>\n        <td>None</td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>Population</td>\n        <td>int</td>\n        <td>NO</td>\n        <td></td>\n        <td>0</td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>LifeExpectancy</td>\n        <td>float(3,1)</td>\n        <td>YES</td>\n        <td></td>\n        <td>None</td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>GNP</td>\n        <td>float(10,2)</td>\n        <td>YES</td>\n        <td></td>\n        <td>None</td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>GNPOld</td>\n        <td>float(10,2)</td>\n        <td>YES</td>\n        <td></td>\n        <td>None</td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>LocalName</td>\n        <td>char(45)</td>\n        <td>NO</td>\n        <td></td>\n        <td></td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>GovernmentForm</td>\n        <td>char(45)</td>\n        <td>NO</td>\n        <td></td>\n        <td></td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>HeadOfState</td>\n        <td>char(60)</td>\n        <td>YES</td>\n        <td></td>\n        <td>None</td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>Capital</td>\n        <td>int</td>\n        <td>YES</td>\n        <td></td>\n        <td>None</td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>Code2</td>\n        <td>char(2)</td>\n        <td>NO</td>\n        <td></td>\n        <td></td>\n        <td></td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 30
    }
   ],
   "source": [
    "%%sql\n",
    "DESCRIBE country;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n4 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('CountryCode', 'char(3)', 'NO', 'PRI', '', ''),\n ('Language', 'char(30)', 'NO', 'PRI', '', ''),\n ('IsOfficial', \"enum('T','F')\", 'NO', '', 'F', ''),\n ('Percentage', 'float(4,1)', 'NO', '', '0.0', '')]",
      "text/html": "<table>\n    <tr>\n        <th>Field</th>\n        <th>Type</th>\n        <th>Null</th>\n        <th>Key</th>\n        <th>Default</th>\n        <th>Extra</th>\n    </tr>\n    <tr>\n        <td>CountryCode</td>\n        <td>char(3)</td>\n        <td>NO</td>\n        <td>PRI</td>\n        <td></td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>Language</td>\n        <td>char(30)</td>\n        <td>NO</td>\n        <td>PRI</td>\n        <td></td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>IsOfficial</td>\n        <td>enum(&#x27;T&#x27;,&#x27;F&#x27;)</td>\n        <td>NO</td>\n        <td></td>\n        <td>F</td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>Percentage</td>\n        <td>float(4,1)</td>\n        <td>NO</td>\n        <td></td>\n        <td>0.0</td>\n        <td></td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 31
    }
   ],
   "source": [
    "%%sql\n",
    "DESCRIBE countrylanguage;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n1 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('city', \"CREATE TABLE `city` (\\n  `ID` int NOT NULL AUTO_INCREMENT,\\n  `Name` char(35) NOT NULL DEFAULT '',\\n  `CountryCode` char(3) NOT NULL DEFAULT '',\\n  ` ... (139 characters truncated) ... `),\\n  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)\\n) ENGINE=InnoDB AUTO_INCREMENT=4083 DEFAULT CHARSET=latin1\")]",
      "text/html": "<table>\n    <tr>\n        <th>Table</th>\n        <th>Create Table</th>\n    </tr>\n    <tr>\n        <td>city</td>\n        <td>CREATE TABLE `city` (<br>  `ID` int NOT NULL AUTO_INCREMENT,<br>  `Name` char(35) NOT NULL DEFAULT &#x27;&#x27;,<br>  `CountryCode` char(3) NOT NULL DEFAULT &#x27;&#x27;,<br>  `District` char(20) NOT NULL DEFAULT &#x27;&#x27;,<br>  `Population` int NOT NULL DEFAULT &#x27;0&#x27;,<br>  PRIMARY KEY (`ID`),<br>  KEY `CountryCode` (`CountryCode`),<br>  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)<br>) ENGINE=InnoDB AUTO_INCREMENT=4083 DEFAULT CHARSET=latin1</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 32
    }
   ],
   "source": [
    "%%sql\n",
    "SHOW CREATE TABLE city;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CRUD"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Select"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n5 rows affected.\n5 rows affected.\n5 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('ABW', 'Dutch', 'T', 5.3),\n ('ABW', 'English', 'F', 9.5),\n ('ABW', 'Papiamento', 'F', 76.7),\n ('ABW', 'Spanish', 'F', 7.4),\n ('AFG', 'Balochi', 'F', 0.9)]",
      "text/html": "<table>\n    <tr>\n        <th>CountryCode</th>\n        <th>Language</th>\n        <th>IsOfficial</th>\n        <th>Percentage</th>\n    </tr>\n    <tr>\n        <td>ABW</td>\n        <td>Dutch</td>\n        <td>T</td>\n        <td>5.3</td>\n    </tr>\n    <tr>\n        <td>ABW</td>\n        <td>English</td>\n        <td>F</td>\n        <td>9.5</td>\n    </tr>\n    <tr>\n        <td>ABW</td>\n        <td>Papiamento</td>\n        <td>F</td>\n        <td>76.7</td>\n    </tr>\n    <tr>\n        <td>ABW</td>\n        <td>Spanish</td>\n        <td>F</td>\n        <td>7.4</td>\n    </tr>\n    <tr>\n        <td>AFG</td>\n        <td>Balochi</td>\n        <td>F</td>\n        <td>0.9</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 33
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM city LIMIT 5;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n5 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('ABW', 'Aruba', 'North America', 'Caribbean', 193.0, None, 103000, 78.4, 828.0, 793.0, 'Aruba', 'Nonmetropolitan Territory of The Netherlands', 'Beatrix', 129, 'AW'),\n ('AFG', 'Afghanistan', 'Asia', 'Southern and Central Asia', 652090.0, 1919, 22720000, 45.9, 5976.0, None, 'Afganistan/Afqanestan', 'Islamic Emirate', 'Mohammad Omar', 1, 'AF'),\n ('AGO', 'Angola', 'Africa', 'Central Africa', 1246700.0, 1975, 12878000, 38.3, 6648.0, 7984.0, 'Angola', 'Republic', 'José Eduardo dos Santos', 56, 'AO'),\n ('AIA', 'Anguilla', 'North America', 'Caribbean', 96.0, None, 8000, 76.1, 63.2, None, 'Anguilla', 'Dependent Territory of the UK', 'Elisabeth II', 62, 'AI'),\n ('ALB', 'Albania', 'Europe', 'Southern Europe', 28748.0, 1912, 3401200, 71.6, 3205.0, 2500.0, 'Shqipëria', 'Republic', 'Rexhep Mejdani', 34, 'AL')]",
      "text/html": "<table>\n    <tr>\n        <th>Code</th>\n        <th>Name</th>\n        <th>Continent</th>\n        <th>Region</th>\n        <th>SurfaceArea</th>\n        <th>IndepYear</th>\n        <th>Population</th>\n        <th>LifeExpectancy</th>\n        <th>GNP</th>\n        <th>GNPOld</th>\n        <th>LocalName</th>\n        <th>GovernmentForm</th>\n        <th>HeadOfState</th>\n        <th>Capital</th>\n        <th>Code2</th>\n    </tr>\n    <tr>\n        <td>ABW</td>\n        <td>Aruba</td>\n        <td>North America</td>\n        <td>Caribbean</td>\n        <td>193.0</td>\n        <td>None</td>\n        <td>103000</td>\n        <td>78.4</td>\n        <td>828.0</td>\n        <td>793.0</td>\n        <td>Aruba</td>\n        <td>Nonmetropolitan Territory of The Netherlands</td>\n        <td>Beatrix</td>\n        <td>129</td>\n        <td>AW</td>\n    </tr>\n    <tr>\n        <td>AFG</td>\n        <td>Afghanistan</td>\n        <td>Asia</td>\n        <td>Southern and Central Asia</td>\n        <td>652090.0</td>\n        <td>1919</td>\n        <td>22720000</td>\n        <td>45.9</td>\n        <td>5976.0</td>\n        <td>None</td>\n        <td>Afganistan/Afqanestan</td>\n        <td>Islamic Emirate</td>\n        <td>Mohammad Omar</td>\n        <td>1</td>\n        <td>AF</td>\n    </tr>\n    <tr>\n        <td>AGO</td>\n        <td>Angola</td>\n        <td>Africa</td>\n        <td>Central Africa</td>\n        <td>1246700.0</td>\n        <td>1975</td>\n        <td>12878000</td>\n        <td>38.3</td>\n        <td>6648.0</td>\n        <td>7984.0</td>\n        <td>Angola</td>\n        <td>Republic</td>\n        <td>José Eduardo dos Santos</td>\n        <td>56</td>\n        <td>AO</td>\n    </tr>\n    <tr>\n        <td>AIA</td>\n        <td>Anguilla</td>\n        <td>North America</td>\n        <td>Caribbean</td>\n        <td>96.0</td>\n        <td>None</td>\n        <td>8000</td>\n        <td>76.1</td>\n        <td>63.2</td>\n        <td>None</td>\n        <td>Anguilla</td>\n        <td>Dependent Territory of the UK</td>\n        <td>Elisabeth II</td>\n        <td>62</td>\n        <td>AI</td>\n    </tr>\n    <tr>\n        <td>ALB</td>\n        <td>Albania</td>\n        <td>Europe</td>\n        <td>Southern Europe</td>\n        <td>28748.0</td>\n        <td>1912</td>\n        <td>3401200</td>\n        <td>71.6</td>\n        <td>3205.0</td>\n        <td>2500.0</td>\n        <td>Shqipëria</td>\n        <td>Republic</td>\n        <td>Rexhep Mejdani</td>\n        <td>34</td>\n        <td>AL</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 34
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM country LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n5 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('ABW', 'Dutch', 'T', 5.3),\n ('ABW', 'English', 'F', 9.5),\n ('ABW', 'Papiamento', 'F', 76.7),\n ('ABW', 'Spanish', 'F', 7.4),\n ('AFG', 'Balochi', 'F', 0.9)]",
      "text/html": "<table>\n    <tr>\n        <th>CountryCode</th>\n        <th>Language</th>\n        <th>IsOfficial</th>\n        <th>Percentage</th>\n    </tr>\n    <tr>\n        <td>ABW</td>\n        <td>Dutch</td>\n        <td>T</td>\n        <td>5.3</td>\n    </tr>\n    <tr>\n        <td>ABW</td>\n        <td>English</td>\n        <td>F</td>\n        <td>9.5</td>\n    </tr>\n    <tr>\n        <td>ABW</td>\n        <td>Papiamento</td>\n        <td>F</td>\n        <td>76.7</td>\n    </tr>\n    <tr>\n        <td>ABW</td>\n        <td>Spanish</td>\n        <td>F</td>\n        <td>7.4</td>\n    </tr>\n    <tr>\n        <td>AFG</td>\n        <td>Balochi</td>\n        <td>F</td>\n        <td>0.9</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 35
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM countrylanguage LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Country who has the largest percentage of population speaking english"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n1 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('Bermuda',)]",
      "text/html": "<table>\n    <tr>\n        <th>name</th>\n    </tr>\n    <tr>\n        <td>Bermuda</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 86
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT c.name FROM country AS c INNER JOIN\n",
    "(SELECT CountryCode\n",
    "      FROM countrylanguage\n",
    "      WHERE Percentage = (SELECT max(Percentage) AS Percentage FROM countrylanguage WHERE Language = 'English')\n",
    "        AND language = 'English') as cl ON c.Code=cl.CountryCode;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Percentage of chinese speaking population in the world population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n1 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('19.61%',)]",
      "text/html": "<table>\n    <tr>\n        <th>ratio</th>\n    </tr>\n    <tr>\n        <td>19.61%</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 45
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT concat(round((SELECT sum(population.population)\n",
    "                     FROM (SELECT pbp.population * pbp.percentage / 100 AS population\n",
    "                           FROM (SELECT c.population, cl.percentage\n",
    "                                 FROM country AS c\n",
    "                                          INNER JOIN (SELECT * FROM countrylanguage WHERE language = 'chinese') AS cl\n",
    "                                                     ON c.code = cl.countrycode) AS pbp) AS population) /\n",
    "                    (SELECT sum(population) FROM country) * 100, 2), '%') AS ratio;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n1 rows affected.\n1 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[(4084, 'shenzhen', 'chn', 'unknown', 10000000)]",
      "text/html": "<table>\n    <tr>\n        <th>ID</th>\n        <th>Name</th>\n        <th>CountryCode</th>\n        <th>District</th>\n        <th>Population</th>\n    </tr>\n    <tr>\n        <td>4084</td>\n        <td>shenzhen</td>\n        <td>chn</td>\n        <td>unknown</td>\n        <td>10000000</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 91
    }
   ],
   "source": [
    "%%sql\n",
    "INSERT INTO city (name, countrycode, district, population)\n",
    "VALUES ('shenzhen', 'chn', 'unknown', 10000000);\n",
    "\n",
    "SELECT * FROM city\n",
    "ORDER BY id DESC LIMIT 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Create Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n0 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[]"
     },
     "metadata": {},
     "execution_count": 100
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE TABLE person\n",
    "(\n",
    "    id                int NOT NULL AUTO_INCREMENT,\n",
    "    firstname         varchar(30),\n",
    "    lastname          varchar(30),\n",
    "    home_country_code char(3),\n",
    "    CONSTRAINT pk_id PRIMARY KEY (id)\n",
    ") ENGINE = innodb;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n0 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[]"
     },
     "metadata": {},
     "execution_count": 101
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE TABLE student\n",
    "(\n",
    "    id       int NOT NULL AUTO_INCREMENT,\n",
    "    personid int NOT NULL,\n",
    "    CONSTRAINT pk_id PRIMARY KEY (id),\n",
    "    CONSTRAINT fk_person FOREIGN KEY (personid) REFERENCES person (id)\n",
    "        ON UPDATE CASCADE\n",
    "        ON DELETE CASCADE\n",
    ") ENGINE = innodb;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n2 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('id', 'int', 'NO', 'PRI', None, 'auto_increment'),\n ('personid', 'int', 'NO', 'MUL', None, '')]",
      "text/html": "<table>\n    <tr>\n        <th>Field</th>\n        <th>Type</th>\n        <th>Null</th>\n        <th>Key</th>\n        <th>Default</th>\n        <th>Extra</th>\n    </tr>\n    <tr>\n        <td>id</td>\n        <td>int</td>\n        <td>NO</td>\n        <td>PRI</td>\n        <td>None</td>\n        <td>auto_increment</td>\n    </tr>\n    <tr>\n        <td>personid</td>\n        <td>int</td>\n        <td>NO</td>\n        <td>MUL</td>\n        <td>None</td>\n        <td></td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 102
    }
   ],
   "source": [
    "%%sql\n",
    "DESC student;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n0 rows affected.\n5 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('id', 'int', 'NO', 'PRI', None, 'auto_increment'),\n ('personid', 'int', 'NO', 'MUL', None, ''),\n ('school', 'varchar(50)', 'YES', '', None, ''),\n ('grade', 'int unsigned', 'YES', '', None, ''),\n ('class', 'int unsigned', 'YES', '', None, '')]",
      "text/html": "<table>\n    <tr>\n        <th>Field</th>\n        <th>Type</th>\n        <th>Null</th>\n        <th>Key</th>\n        <th>Default</th>\n        <th>Extra</th>\n    </tr>\n    <tr>\n        <td>id</td>\n        <td>int</td>\n        <td>NO</td>\n        <td>PRI</td>\n        <td>None</td>\n        <td>auto_increment</td>\n    </tr>\n    <tr>\n        <td>personid</td>\n        <td>int</td>\n        <td>NO</td>\n        <td>MUL</td>\n        <td>None</td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>school</td>\n        <td>varchar(50)</td>\n        <td>YES</td>\n        <td></td>\n        <td>None</td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>grade</td>\n        <td>int unsigned</td>\n        <td>YES</td>\n        <td></td>\n        <td>None</td>\n        <td></td>\n    </tr>\n    <tr>\n        <td>class</td>\n        <td>int unsigned</td>\n        <td>YES</td>\n        <td></td>\n        <td>None</td>\n        <td></td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 103
    }
   ],
   "source": [
    "%%sql\n",
    "ALTER TABLE student\n",
    "    ADD COLUMN school varchar(50),\n",
    "    ADD COLUMN grade  int UNSIGNED,\n",
    "    ADD COLUMN class  int UNSIGNED;\n",
    "\n",
    "DESC student;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next: Should fail because of foreign key constraint, the personID DNE in person table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n"
    },
    {
     "output_type": "error",
     "ename": "IntegrityError",
     "evalue": "(mysql.connector.errors.IntegrityError) 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`world`.`student`, CONSTRAINT `fk_person` FOREIGN KEY (`personid`) REFERENCES `person` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)\n[SQL: INSERT INTO student (personid, school) VALUES (10, 'bcs');]\n(Background on this error at: http://sqlalche.me/e/gkpj)",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mMySQLInterfaceError\u001b[0m                       Traceback (most recent call last)",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\mysql\\connector\\connection_cext.py\u001b[0m in \u001b[0;36mcmd_query\u001b[1;34m(self, query, raw, buffered, raw_as_string)\u001b[0m\n\u001b[0;32m    488\u001b[0m                                \u001b[0mraw\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mraw\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mbuffered\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mbuffered\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 489\u001b[1;33m                                raw_as_string=raw_as_string)\n\u001b[0m\u001b[0;32m    490\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mMySQLInterfaceError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mMySQLInterfaceError\u001b[0m: Cannot add or update a child row: a foreign key constraint fails (`world`.`student`, CONSTRAINT `fk_person` FOREIGN KEY (`personid`) REFERENCES `person` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[1;31mIntegrityError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[1;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[0;32m   1245\u001b[0m                     self.dialect.do_execute(\n\u001b[1;32m-> 1246\u001b[1;33m                         \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1247\u001b[0m                     )\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[1;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[0;32m    587\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 588\u001b[1;33m         \u001b[0mcursor\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    589\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\mysql\\connector\\cursor_cext.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, operation, params, multi)\u001b[0m\n\u001b[0;32m    265\u001b[0m                                          \u001b[0mbuffered\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_buffered\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 266\u001b[1;33m                                          raw_as_string=self._raw_as_string)\n\u001b[0m\u001b[0;32m    267\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mMySQLInterfaceError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\mysql\\connector\\connection_cext.py\u001b[0m in \u001b[0;36mcmd_query\u001b[1;34m(self, query, raw, buffered, raw_as_string)\u001b[0m\n\u001b[0;32m    491\u001b[0m             raise errors.get_mysql_exception(exc.errno, msg=exc.msg,\n\u001b[1;32m--> 492\u001b[1;33m                                              sqlstate=exc.sqlstate)\n\u001b[0m\u001b[0;32m    493\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mAttributeError\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mIntegrityError\u001b[0m: 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`world`.`student`, CONSTRAINT `fk_person` FOREIGN KEY (`personid`) REFERENCES `person` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[1;31mIntegrityError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-104-5476ef76ad7b>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mget_ipython\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrun_cell_magic\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'sql'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m''\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m\"INSERT INTO student (personid, school) VALUES (10, 'bcs');\\n\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py\u001b[0m in \u001b[0;36mrun_cell_magic\u001b[1;34m(self, magic_name, line, cell)\u001b[0m\n\u001b[0;32m   2360\u001b[0m             \u001b[1;32mwith\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mbuiltin_trap\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2361\u001b[0m                 \u001b[0margs\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m(\u001b[0m\u001b[0mmagic_arg_s\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcell\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2362\u001b[1;33m                 \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mfn\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2363\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2364\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m<C:\\ProgramData\\Anaconda3\\lib\\site-packages\\decorator.py:decorator-gen-128>\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, line, cell, local_ns)\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\IPython\\core\\magic.py\u001b[0m in \u001b[0;36m<lambda>\u001b[1;34m(f, *a, **k)\u001b[0m\n\u001b[0;32m    185\u001b[0m     \u001b[1;31m# but it's overkill for just that one bit of state.\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    186\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mmagic_deco\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marg\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 187\u001b[1;33m         \u001b[0mcall\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mlambda\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[0ma\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mk\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0ma\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mk\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    188\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    189\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mcallable\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marg\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m<C:\\ProgramData\\Anaconda3\\lib\\site-packages\\decorator.py:decorator-gen-127>\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, line, cell, local_ns)\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\IPython\\core\\magic.py\u001b[0m in \u001b[0;36m<lambda>\u001b[1;34m(f, *a, **k)\u001b[0m\n\u001b[0;32m    185\u001b[0m     \u001b[1;31m# but it's overkill for just that one bit of state.\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    186\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mmagic_deco\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marg\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 187\u001b[1;33m         \u001b[0mcall\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mlambda\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[0ma\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mk\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0ma\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mk\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    188\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    189\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mcallable\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marg\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sql\\magic.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, line, cell, local_ns)\u001b[0m\n\u001b[0;32m    215\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    216\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 217\u001b[1;33m             \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0msql\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrun\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mrun\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparsed\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"sql\"\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0muser_ns\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    218\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    219\u001b[0m             if (\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sql\\run.py\u001b[0m in \u001b[0;36mrun\u001b[1;34m(conn, sql, config, user_namespace)\u001b[0m\n\u001b[0;32m    365\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    366\u001b[0m                 \u001b[0mtxt\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0msqlalchemy\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msql\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtext\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 367\u001b[1;33m                 \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mconn\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msession\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mtxt\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0muser_namespace\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    368\u001b[0m             \u001b[0m_commit\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mconn\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mconfig\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mconfig\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    369\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mresult\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0mconfig\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfeedback\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, object_, *multiparams, **params)\u001b[0m\n\u001b[0;32m    980\u001b[0m             \u001b[1;32mraise\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mObjectNotExecutableError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mobject_\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    981\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 982\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mmeth\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    983\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    984\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_execute_function\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\sql\\elements.py\u001b[0m in \u001b[0;36m_execute_on_connection\u001b[1;34m(self, connection, multiparams, params)\u001b[0m\n\u001b[0;32m    291\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_execute_on_connection\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mconnection\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    292\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msupports_execution\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 293\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mconnection\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_execute_clauseelement\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    294\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    295\u001b[0m             \u001b[1;32mraise\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mObjectNotExecutableError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_execute_clauseelement\u001b[1;34m(self, elem, multiparams, params)\u001b[0m\n\u001b[0;32m   1099\u001b[0m             \u001b[0mdistilled_params\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1100\u001b[0m             \u001b[0mcompiled_sql\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1101\u001b[1;33m             \u001b[0mdistilled_params\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1102\u001b[0m         )\n\u001b[0;32m   1103\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_has_events\u001b[0m \u001b[1;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_has_events\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[1;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[0;32m   1248\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1249\u001b[0m             self._handle_dbapi_exception(\n\u001b[1;32m-> 1250\u001b[1;33m                 \u001b[0me\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1251\u001b[0m             )\n\u001b[0;32m   1252\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[1;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[0;32m   1474\u001b[0m                 \u001b[0mutil\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mraise_from_cause\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mnewraise\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1475\u001b[0m             \u001b[1;32melif\u001b[0m \u001b[0mshould_wrap\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1476\u001b[1;33m                 \u001b[0mutil\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mraise_from_cause\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msqlalchemy_exception\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1477\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1478\u001b[0m                 \u001b[0mutil\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mreraise\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\util\\compat.py\u001b[0m in \u001b[0;36mraise_from_cause\u001b[1;34m(exception, exc_info)\u001b[0m\n\u001b[0;32m    396\u001b[0m     \u001b[0mexc_type\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mexc_value\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mexc_tb\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    397\u001b[0m     \u001b[0mcause\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mexc_value\u001b[0m \u001b[1;32mif\u001b[0m \u001b[0mexc_value\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mexception\u001b[0m \u001b[1;32melse\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 398\u001b[1;33m     \u001b[0mreraise\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mtype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mexception\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mexception\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mtb\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mexc_tb\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcause\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mcause\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    399\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    400\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\util\\compat.py\u001b[0m in \u001b[0;36mreraise\u001b[1;34m(tp, value, tb, cause)\u001b[0m\n\u001b[0;32m    150\u001b[0m             \u001b[0mvalue\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__cause__\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mcause\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    151\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mvalue\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__traceback__\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mtb\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 152\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mvalue\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mwith_traceback\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mtb\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    153\u001b[0m         \u001b[1;32mraise\u001b[0m \u001b[0mvalue\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    154\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_execute_context\u001b[1;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[0;32m   1244\u001b[0m                 \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1245\u001b[0m                     self.dialect.do_execute(\n\u001b[1;32m-> 1246\u001b[1;33m                         \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1247\u001b[0m                     )\n\u001b[0;32m   1248\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\sqlalchemy\\engine\\default.py\u001b[0m in \u001b[0;36mdo_execute\u001b[1;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[0;32m    586\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    587\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 588\u001b[1;33m         \u001b[0mcursor\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    589\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    590\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mdo_execute_no_params\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\mysql\\connector\\cursor_cext.py\u001b[0m in \u001b[0;36mexecute\u001b[1;34m(self, operation, params, multi)\u001b[0m\n\u001b[0;32m    264\u001b[0m             result = self._cnx.cmd_query(stmt, raw=self._raw,\n\u001b[0;32m    265\u001b[0m                                          \u001b[0mbuffered\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_buffered\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 266\u001b[1;33m                                          raw_as_string=self._raw_as_string)\n\u001b[0m\u001b[0;32m    267\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mMySQLInterfaceError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    268\u001b[0m             raise errors.get_mysql_exception(msg=exc.msg, errno=exc.errno,\n",
      "\u001b[1;32mC:\\ProgramData\\Anaconda3\\lib\\site-packages\\mysql\\connector\\connection_cext.py\u001b[0m in \u001b[0;36mcmd_query\u001b[1;34m(self, query, raw, buffered, raw_as_string)\u001b[0m\n\u001b[0;32m    490\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mMySQLInterfaceError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0mexc\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    491\u001b[0m             raise errors.get_mysql_exception(exc.errno, msg=exc.msg,\n\u001b[1;32m--> 492\u001b[1;33m                                              sqlstate=exc.sqlstate)\n\u001b[0m\u001b[0;32m    493\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mAttributeError\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    494\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_unix_socket\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mIntegrityError\u001b[0m: (mysql.connector.errors.IntegrityError) 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`world`.`student`, CONSTRAINT `fk_person` FOREIGN KEY (`personid`) REFERENCES `person` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)\n[SQL: INSERT INTO student (personid, school) VALUES (10, 'bcs');]\n(Background on this error at: http://sqlalche.me/e/gkpj)"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "INSERT INTO student (personid, school) VALUES (10, 'bcs');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n1 rows affected.\n1 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[]"
     },
     "metadata": {},
     "execution_count": 105
    }
   ],
   "source": [
    "%%sql\n",
    "INSERT INTO person (firstname, lastname)\n",
    "VALUES ('first', 'last');\n",
    "INSERT INTO person (id, firstname, lastname)\n",
    "VALUES (100, 'one', 'two');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n2 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[(1, 'first', 'last', None), (100, 'one', 'two', None)]",
      "text/html": "<table>\n    <tr>\n        <th>id</th>\n        <th>firstname</th>\n        <th>lastname</th>\n        <th>home_country_code</th>\n    </tr>\n    <tr>\n        <td>1</td>\n        <td>first</td>\n        <td>last</td>\n        <td>None</td>\n    </tr>\n    <tr>\n        <td>100</td>\n        <td>one</td>\n        <td>two</td>\n        <td>None</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 106
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM person;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n1 rows affected.\n1 rows affected.\n2 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[(2, 1, 'bcs', None, None), (3, 100, 'uoft', None, None)]",
      "text/html": "<table>\n    <tr>\n        <th>id</th>\n        <th>personid</th>\n        <th>school</th>\n        <th>grade</th>\n        <th>class</th>\n    </tr>\n    <tr>\n        <td>2</td>\n        <td>1</td>\n        <td>bcs</td>\n        <td>None</td>\n        <td>None</td>\n    </tr>\n    <tr>\n        <td>3</td>\n        <td>100</td>\n        <td>uoft</td>\n        <td>None</td>\n        <td>None</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 107
    }
   ],
   "source": [
    "%%sql\n",
    "INSERT INTO student (personid, school)\n",
    "VALUES ((SELECT id FROM person LIMIT 1), 'bcs');\n",
    "\n",
    "INSERT INTO student (personid, school)\n",
    "VALUES (100, 'uoft');\n",
    "\n",
    "SELECT * FROM student;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Update"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n1 rows affected.\n2 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[(1, 'first', 'last', None), (99, 'three', 'four', None)]",
      "text/html": "<table>\n    <tr>\n        <th>id</th>\n        <th>firstname</th>\n        <th>lastname</th>\n        <th>home_country_code</th>\n    </tr>\n    <tr>\n        <td>1</td>\n        <td>first</td>\n        <td>last</td>\n        <td>None</td>\n    </tr>\n    <tr>\n        <td>99</td>\n        <td>three</td>\n        <td>four</td>\n        <td>None</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 108
    }
   ],
   "source": [
    "%%sql\n",
    "UPDATE person\n",
    "SET id=99,\n",
    "    firstname='three',\n",
    "    lastname='four'\n",
    "WHERE id = 100;\n",
    "\n",
    "SELECT * FROM person;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cascading update student's personID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n2 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[(2, 1, 'bcs', None, None), (3, 99, 'uoft', None, None)]",
      "text/html": "<table>\n    <tr>\n        <th>id</th>\n        <th>personid</th>\n        <th>school</th>\n        <th>grade</th>\n        <th>class</th>\n    </tr>\n    <tr>\n        <td>2</td>\n        <td>1</td>\n        <td>bcs</td>\n        <td>None</td>\n        <td>None</td>\n    </tr>\n    <tr>\n        <td>3</td>\n        <td>99</td>\n        <td>uoft</td>\n        <td>None</td>\n        <td>None</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 109
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM student;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cascading delete student with person id=99"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n1 rows affected.\n1 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[(2, 1, 'bcs', None, None)]",
      "text/html": "<table>\n    <tr>\n        <th>id</th>\n        <th>personid</th>\n        <th>school</th>\n        <th>grade</th>\n        <th>class</th>\n    </tr>\n    <tr>\n        <td>2</td>\n        <td>1</td>\n        <td>bcs</td>\n        <td>None</td>\n        <td>None</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 110
    }
   ],
   "source": [
    "%%sql\n",
    "DELETE FROM person WHERE id = 99;\n",
    "SELECT * FROM student;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Delete (Clean Up)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n1 rows affected.\n0 rows affected.\n0 rows affected.\n0 rows affected.\n1 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[]"
     },
     "metadata": {},
     "execution_count": 111
    }
   ],
   "source": [
    "%%sql\n",
    "DELETE FROM person;\n",
    "DELETE FROM student;\n",
    "DROP TABLE IF EXISTS student;\n",
    "DROP TABLE IF EXISTS person;\n",
    "DELETE FROM city WHERE Name='shenzhen';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "* mysql+mysqlconnector://root:***@localhost:3306\n3 rows affected.\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[('city',), ('country',), ('countrylanguage',)]",
      "text/html": "<table>\n    <tr>\n        <th>Tables_in_world</th>\n    </tr>\n    <tr>\n        <td>city</td>\n    </tr>\n    <tr>\n        <td>country</td>\n    </tr>\n    <tr>\n        <td>countrylanguage</td>\n    </tr>\n</table>"
     },
     "metadata": {},
     "execution_count": 112
    }
   ],
   "source": [
    "%%sql\n",
    "SHOW TABLES;"
   ]
  }
 ]
}