mysql.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[![CraigDoesData][logo]][link]\n",
    "\n",
    "[logo]: ./img/logo.png\n",
    "[link]: https://www.craigdoesdata.de/\n",
    "\n",
    "\n",
    "# MySQL + Python for Data Analysts\n",
    "\n",
    "Using [MySQL Connector](https://dev.mysql.com/doc/connector-python/en/) and Python to implement a database on MySQL Server, and to create, read, update and delete data in that database.\n",
    "\n",
    "#### Project status - Complete\n",
    "\n",
    "\n",
    "## Introduction\n",
    "\n",
    "This notebook accompanies [this tutorial]() I wrote for [freeCodeCamp](https://www.freecodecamp.org/).\n",
    "\n",
    "The notebook takes the reader step-by-step through all the processes involved with using Python and the [MySQL Connector](https://dev.mysql.com/doc/connector-python/en/) to perform the standard [CRUD functions](https://stackify.com/what-are-crud-operations/) on a database running on [MySQL Server](https://dev.mysql.com/downloads/mysql/).\n",
    "\n",
    "This notebook uses code and data from [this repository](https://github.com/thecraigd/SQL_School_Tutorial/) which accompanies my [series of SQL Tutorials](https://towardsdatascience.com/tagged/sql-series) on [Towards Data Science](https://towardsdatascience.com/). If you are not familiar with relational databases and SQL, I recommend going through those tutorials first.\n",
    "\n",
    "We will be implementing the same code to build the below database for the International Language School, but this time doing it via Python using MySQL Connector.\n",
    "\n",
    "\n",
    "[![Entity Relationship Diagram for Database][erd]][link1]\n",
    "\n",
    "[erd]: ./img/ERD.png\n",
    "[link1]: https://towardsdatascience.com/designing-a-relational-database-and-creating-an-entity-relationship-diagram-89c1c19320b2\n",
    "\n",
    "-----------------\n",
    "\n",
    "\n",
    "### Methods used\n",
    "* Defining functions in Python\n",
    "* Database Implementation\n",
    "* Creating, Reading, Updating and Deleting data using SQL and Python\n",
    "\n",
    "\n",
    "### Technologies used\n",
    "* [MySQL Community Server](https://dev.mysql.com/downloads/mysql/)\n",
    "* [MySQL Python Connector](https://dev.mysql.com/doc/connector-python/en/)\n",
    "* [PopSQL](https://popsql.com/)\n",
    "* [Jupyter Notebook](https://jupyter.org/)\n",
    "* [pandas](https://pandas.pydata.org/)\n",
    "\n",
    "### Data sources\n",
    "The data used were created by myself for an earlier [project](https://github.com/thecraigd/SQL_School_Tutorial). \n",
    "Some participant names were created using [Fake Name Generator](https://www.fakenamegenerator.com/).\n",
    "\n",
    "\n",
    "\n",
    "---------------------\n",
    "\n",
    "\n",
    "## Table of Contents\n",
    "\n",
    "#### 1. Library Import\n",
    "* 1.1 Library Import\n",
    "\n",
    "#### 2. Connect to Server and Create Database\n",
    "* 2.1 Define Server Connection Function \n",
    "* 2.2 Create a New Database\n",
    "* 2.3 Modify Server Connection Function, Create Database Connection Function\n",
    "* 2.4 Define Query Execution Function\n",
    "\n",
    "#### 3. Creating Tables\n",
    "* 3.1 Create Teacher Table\n",
    "* 3.2 Create Remaining Tables\n",
    "* 3.3 Define Foreign Key Relationships\n",
    "\n",
    "#### 4. Populating Tables\n",
    "* 4.1 Populate Teacher Table\n",
    "* 4.2 Populate Remaining Tables\n",
    "\n",
    "#### 5. Reading Data\n",
    "* 5.1 Define Data Reading Function\n",
    "* 5.2 - Read Data from Database\n",
    "* 5.3 - Formatting Output into a List\n",
    "* 5.4 - Formatting Output into a List of Lists\n",
    "* 5.5 - Formatting Output into a pandas DataFrame\n",
    "\n",
    "#### 6. Updating Records\n",
    "* 6.1 Updating Client Address\n",
    "\n",
    "#### 7. Deleting Records\n",
    "* 7.1 Deleting a Course\n",
    "* 7.2 Restoring the Course\n",
    "\n",
    "#### 8. Creating Records from Lists\n",
    "* 8.1 Create Execute List Query Function\n",
    "* 8.2 Add New Teachers\n",
    "\n",
    "#### 9. Conclusion\n",
    "* 9.1 Conclusion\n",
    "\n",
    "--------------\n",
    "\n",
    "### 1. Import Libraries\n",
    "\n",
    "##### 1.1 - Import Libraries\n",
    "\n",
    "Our first step is to import [MySQL Connector](https://dev.mysql.com/doc/connector-python/en/) and [pandas](https://pandas.pydata.org/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector\n",
    "from mysql.connector import Error\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-------------------\n",
    "\n",
    "### 2. Connect to Server and Create Database\n",
    "\n",
    "##### 2.1 - Define Server Connection Function \n",
    "\n",
    "Next we want to define a function in python which connects to our MySQL Server (for info on how to set this up, see [my previous article](https://towardsdatascience.com/coding-and-implementing-a-relational-database-using-mysql-d9bc69be90f5). To do this we use the [mysql.connector.connect()](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysql-connector-connect.html) method.\n",
    "\n",
    "**N.B. Update the variable 'pw' with the root password for your MySQL Server! Otherwise the connection cannot be made.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MySQL Database connection successful\n"
     ]
    }
   ],
   "source": [
    "def create_server_connection(host_name, user_name, user_password):\n",
    "    connection = None\n",
    "    try:\n",
    "        connection = mysql.connector.connect(\n",
    "            host=host_name,\n",
    "            user=user_name,\n",
    "            passwd=user_password\n",
    "        )\n",
    "        print(\"MySQL Database connection successful\")\n",
    "    except Error as err:\n",
    "        print(f\"Error: '{err}'\")\n",
    "\n",
    "    return connection\n",
    "\n",
    "pw = \"******\" # IMPORTANT! Put your MySQL Terminal password here.\n",
    "db = \"school\" # This is the name of the database we will create in the next step - call it whatever you like.\n",
    "\n",
    "connection = create_server_connection(\"localhost\", \"root\", pw)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 2.2 - Create a New Database\n",
    "\n",
    "Now we define a function to create a new database on our server. Here we are using [cursor.execute()](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html) to execute a [CREATE DATABASE](https://dev.mysql.com/doc/refman/8.0/en/creating-database.html) SQL command."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Database created successfully\n"
     ]
    }
   ],
   "source": [
    "def create_database(connection, query):\n",
    "    cursor = connection.cursor()\n",
    "    try:\n",
    "        cursor.execute(query)\n",
    "        print(\"Database created successfully\")\n",
    "    except Error as err:\n",
    "        print(f\"Error: '{err}'\")\n",
    "\n",
    "create_database_query = \"CREATE DATABASE school\"\n",
    "create_database(connection, create_database_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 2.3 - Modify Server Connection Function, Create Database Connection Function\n",
    "\n",
    "Now that we've created a DB, let's modify our create_server_connection function to create a new function for connecting directly to that DB. This will prove more useful than just connecting to our server."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_db_connection(host_name, user_name, user_password, db_name):\n",
    "    connection = None\n",
    "    try:\n",
    "        connection = mysql.connector.connect(\n",
    "            host=host_name,\n",
    "            user=user_name,\n",
    "            passwd=user_password,\n",
    "            database=db_name\n",
    "        )\n",
    "        print(\"MySQL Database connection successful\")\n",
    "    except Error as err:\n",
    "        print(f\"Error: '{err}'\")\n",
    "\n",
    "    return connection"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 2.4 - Define Query Execution Function\n",
    "\n",
    "The final step of this stage is to create a function which will allow us to execute queries written in SQL. This is going to be extremely useful!\n",
    "\n",
    "Again, we use [cursor.execute()](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html) to execute our commands."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def execute_query(connection, query):\n",
    "    cursor = connection.cursor()\n",
    "    try:\n",
    "        cursor.execute(query)\n",
    "        connection.commit()\n",
    "        print(\"Query successful\")\n",
    "    except Error as err:\n",
    "        print(f\"Error: '{err}'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-------------------\n",
    "\n",
    "### 3. Creating Tables\n",
    "\n",
    "##### 3.1 - Create Teacher Table\n",
    "\n",
    "Now let's create our first table inside our DB, using our newly defined functions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MySQL Database connection successful\n",
      "Query successful\n"
     ]
    }
   ],
   "source": [
    "# Assign our SQL command to a python variable using triple quotes to create a multi-line string\n",
    "create_teacher_table = \"\"\"\n",
    "CREATE TABLE teacher (\n",
    "  teacher_id INT PRIMARY KEY,\n",
    "  first_name VARCHAR(40) NOT NULL,\n",
    "  last_name VARCHAR(40) NOT NULL,\n",
    "  language_1 VARCHAR(3) NOT NULL,\n",
    "  language_2 VARCHAR(3),\n",
    "  dob DATE,\n",
    "  tax_id INT UNIQUE,\n",
    "  phone_no VARCHAR(20)\n",
    "  );\n",
    " \"\"\"\n",
    "\n",
    "connection = create_db_connection(\"localhost\", \"root\", pw, db) # Connect to the Database\n",
    "execute_query(connection, create_teacher_table) # Execute our defined query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we check in our MySQL terminal using SHOW TABLES; we see the following:\n",
    "\n",
    "![MySQL Terminal Screen confirming table creation](./img/01.jpg)\n",
    "\n",
    "Success! The teacher table has been created.\n",
    "\n",
    "##### 3.2 - Create Remaining Tables\n",
    "\n",
    "Now let's create the rest of our tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MySQL Database connection successful\n",
      "Query successful\n",
      "Query successful\n",
      "Query successful\n"
     ]
    }
   ],
   "source": [
    "create_client_table = \"\"\"\n",
    "CREATE TABLE client (\n",
    "  client_id INT PRIMARY KEY,\n",
    "  client_name VARCHAR(40) NOT NULL,\n",
    "  address VARCHAR(60) NOT NULL,\n",
    "  industry VARCHAR(20)\n",
    ");\n",
    " \"\"\"\n",
    "\n",
    "create_participant_table = \"\"\"\n",
    "CREATE TABLE participant (\n",
    "  participant_id INT PRIMARY KEY,\n",
    "  first_name VARCHAR(40) NOT NULL,\n",
    "  last_name VARCHAR(40) NOT NULL,\n",
    "  phone_no VARCHAR(20),\n",
    "  client INT\n",
    ");\n",
    "\"\"\"\n",
    "\n",
    "create_course_table = \"\"\"\n",
    "CREATE TABLE course (\n",
    "  course_id INT PRIMARY KEY,\n",
    "  course_name VARCHAR(40) NOT NULL,\n",
    "  language VARCHAR(3) NOT NULL,\n",
    "  level VARCHAR(2),\n",
    "  course_length_weeks INT,\n",
    "  start_date DATE,\n",
    "  in_school BOOLEAN,\n",
    "  teacher INT,\n",
    "  client INT\n",
    ");\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "connection = create_db_connection(\"localhost\", \"root\", pw, db)\n",
    "execute_query(connection, create_client_table)\n",
    "execute_query(connection, create_participant_table)\n",
    "execute_query(connection, create_course_table)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 3.3 - Define Foreign Key Relationships\n",
    "\n",
    "Now altering the tables to create Foreign Key relationships (see the accompanying [SQL Tutorial Series](https://towardsdatascience.com/tagged/sql-series) on [Towards Data Science](https://towardsdatascience.com/) for the background on all of this), and creating our final table, takes_course"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MySQL Database connection successful\n",
      "Query successful\n",
      "Query successful\n",
      "Query successful\n",
      "Query successful\n"
     ]
    }
   ],
   "source": [
    "alter_participant = \"\"\"\n",
    "ALTER TABLE participant\n",
    "ADD FOREIGN KEY(client)\n",
    "REFERENCES client(client_id)\n",
    "ON DELETE SET NULL;\n",
    "\"\"\"\n",
    "\n",
    "alter_course = \"\"\"\n",
    "ALTER TABLE course\n",
    "ADD FOREIGN KEY(teacher)\n",
    "REFERENCES teacher(teacher_id)\n",
    "ON DELETE SET NULL;\n",
    "\"\"\"\n",
    "\n",
    "alter_course_again = \"\"\"\n",
    "ALTER TABLE course\n",
    "ADD FOREIGN KEY(client)\n",
    "REFERENCES client(client_id)\n",
    "ON DELETE SET NULL;\n",
    "\"\"\"\n",
    "\n",
    "create_takescourse_table = \"\"\"\n",
    "CREATE TABLE takes_course (\n",
    "  participant_id INT,\n",
    "  course_id INT,\n",
    "  PRIMARY KEY(participant_id, course_id),\n",
    "  FOREIGN KEY(participant_id) REFERENCES participant(participant_id) ON DELETE CASCADE, -- it makes no sense to keep this rtelation when a participant or course is no longer in the system, hence why CASCADE this time\n",
    "  FOREIGN KEY(course_id) REFERENCES course(course_id) ON DELETE CASCADE\n",
    ");\n",
    "\"\"\"\n",
    "\n",
    "connection = create_db_connection(\"localhost\", \"root\", pw, db)\n",
    "execute_query(connection, alter_participant)\n",
    "execute_query(connection, alter_course)\n",
    "execute_query(connection, alter_course_again)\n",
    "execute_query(connection, create_takescourse_table)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-----------------\n",
    "\n",
    "### 4. Populate Tables\n",
    "\n",
    "The next step is to populate our tables with data, starting with the teacher table\n",
    "\n",
    "##### 4.1 - Populate Teacher Table\n",
    "\n",
    "Here we again assign a multi-line string with our SQL command to a variable, and then call our create_db_connection and execute_query functions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MySQL Database connection successful\n",
      "Query successful\n"
     ]
    }
   ],
   "source": [
    "pop_teacher = \"\"\"\n",
    "INSERT INTO teacher VALUES\n",
    "(1,  'James', 'Smith', 'ENG', NULL, '1985-04-20', 12345, '+491774553676'),\n",
    "(2, 'Stefanie',  'Martin',  'FRA', NULL,  '1970-02-17', 23456, '+491234567890'), \n",
    "(3, 'Steve', 'Wang',  'MAN', 'ENG', '1990-11-12', 34567, '+447840921333'),\n",
    "(4, 'Friederike',  'Müller-Rossi', 'DEU', 'ITA', '1987-07-07',  45678, '+492345678901'),\n",
    "(5, 'Isobel', 'Ivanova', 'RUS', 'ENG', '1963-05-30',  56789, '+491772635467'),\n",
    "(6, 'Niamh', 'Murphy', 'ENG', 'IRI', '1995-09-08',  67890, '+491231231232');\n",
    "\"\"\"\n",
    "\n",
    "connection = create_db_connection(\"localhost\", \"root\", pw, db)\n",
    "execute_query(connection, pop_teacher)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a look in MySQL terminal:\n",
    "\n",
    "![MySQL Terminal Screen confirming table population](./img/02.jpg)\n",
    "\n",
    "Success! We can see the table has been properly filled with all of our data.\n",
    "\n",
    "##### 4.2 - Populate Remaining Tables\n",
    "\n",
    "Now, let's populate the remaining tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MySQL Database connection successful\n",
      "Query successful\n",
      "Query successful\n",
      "Query successful\n",
      "Query successful\n"
     ]
    }
   ],
   "source": [
    "pop_client = \"\"\"\n",
    "INSERT INTO client VALUES\n",
    "(101, 'Big Business Federation', '123 Falschungstraße, 10999 Berlin', 'NGO'),\n",
    "(102, 'eCommerce GmbH', '27 Ersatz Allee, 10317 Berlin', 'Retail'),\n",
    "(103, 'AutoMaker AG',  '20 Künstlichstraße, 10023 Berlin', 'Auto'),\n",
    "(104, 'Banko Bank',  '12 Betrugstraße, 12345 Berlin', 'Banking'),\n",
    "(105, 'WeMoveIt GmbH', '138 Arglistweg, 10065 Berlin', 'Logistics');\n",
    "\"\"\"\n",
    "\n",
    "pop_participant = \"\"\"\n",
    "INSERT INTO participant VALUES\n",
    "(101, 'Marina', 'Berg','491635558182', 101),\n",
    "(102, 'Andrea', 'Duerr', '49159555740', 101),\n",
    "(103, 'Philipp', 'Probst',  '49155555692', 102),\n",
    "(104, 'René',  'Brandt',  '4916355546',  102),\n",
    "(105, 'Susanne', 'Shuster', '49155555779', 102),\n",
    "(106, 'Christian', 'Schreiner', '49162555375', 101),\n",
    "(107, 'Harry', 'Kim', '49177555633', 101),\n",
    "(108, 'Jan', 'Nowak', '49151555824', 101),\n",
    "(109, 'Pablo', 'Garcia',  '49162555176', 101),\n",
    "(110, 'Melanie', 'Dreschler', '49151555527', 103),\n",
    "(111, 'Dieter', 'Durr',  '49178555311', 103),\n",
    "(112, 'Max', 'Mustermann', '49152555195', 104),\n",
    "(113, 'Maxine', 'Mustermann', '49177555355', 104),\n",
    "(114, 'Heiko', 'Fleischer', '49155555581', 105);\n",
    "\"\"\"\n",
    "\n",
    "pop_course = \"\"\"\n",
    "INSERT INTO course VALUES\n",
    "(12, 'English for Logistics', 'ENG', 'A1', 10, '2020-02-01', TRUE,  1, 105),\n",
    "(13, 'Beginner English', 'ENG', 'A2', 40, '2019-11-12',  FALSE, 6, 101),\n",
    "(14, 'Intermediate English', 'ENG', 'B2', 40, '2019-11-12', FALSE, 6, 101),\n",
    "(15, 'Advanced English', 'ENG', 'C1', 40, '2019-11-12', FALSE, 6, 101),\n",
    "(16, 'Mandarin für Autoindustrie', 'MAN', 'B1', 15, '2020-01-15', TRUE, 3, 103),\n",
    "(17, 'Français intermédiaire', 'FRA', 'B1',  18, '2020-04-03', FALSE, 2, 101),\n",
    "(18, 'Deutsch für Anfänger', 'DEU', 'A2', 8, '2020-02-14', TRUE, 4, 102),\n",
    "(19, 'Intermediate English', 'ENG', 'B2', 10, '2020-03-29', FALSE, 1, 104),\n",
    "(20, 'Fortgeschrittenes Russisch', 'RUS', 'C1',  4, '2020-04-08',  FALSE, 5, 103);\n",
    "\"\"\"\n",
    "\n",
    "pop_takescourse = \"\"\"\n",
    "INSERT INTO takes_course VALUES\n",
    "(101, 15),\n",
    "(101, 17),\n",
    "(102, 17),\n",
    "(103, 18),\n",
    "(104, 18),\n",
    "(105, 18),\n",
    "(106, 13),\n",
    "(107, 13),\n",
    "(108, 13),\n",
    "(109, 14),\n",
    "(109, 15),\n",
    "(110, 16),\n",
    "(110, 20),\n",
    "(111, 16),\n",
    "(114, 12),\n",
    "(112, 19),\n",
    "(113, 19);\n",
    "\"\"\"\n",
    "\n",
    "connection = create_db_connection(\"localhost\", \"root\", pw, db)\n",
    "execute_query(connection, pop_client)\n",
    "execute_query(connection, pop_participant)\n",
    "execute_query(connection, pop_course)\n",
    "execute_query(connection, pop_takescourse)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "--------------\n",
    "\n",
    "### 5. Reading Data\n",
    "\n",
    "##### 5.1 - Define Data Reading Function\n",
    "\n",
    "Now that we have populated our tables, it's time to start creating read queries. To do this, we will need a new function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def read_query(connection, query):\n",
    "    cursor = connection.cursor()\n",
    "    result = None\n",
    "    try:\n",
    "        cursor.execute(query)\n",
    "        result = cursor.fetchall()\n",
    "        return result\n",
    "    except Error as err:\n",
    "        print(f\"Error: '{err}'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 5.2 - Read Data from Database\n",
    "\n",
    "Let's try this with a simple query to begin with."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MySQL Database connection successful\n",
      "(1, 'James', 'Smith', 'ENG', None, datetime.date(1985, 4, 20), 12345, '+491774553676')\n",
      "(2, 'Stefanie', 'Martin', 'FRA', None, datetime.date(1970, 2, 17), 23456, '+491234567890')\n",
      "(3, 'Steve', 'Wang', 'MAN', 'ENG', datetime.date(1990, 11, 12), 34567, '+447840921333')\n",
      "(4, 'Friederike', 'Müller-Rossi', 'DEU', 'ITA', datetime.date(1987, 7, 7), 45678, '+492345678901')\n",
      "(5, 'Isobel', 'Ivanova', 'RUS', 'ENG', datetime.date(1963, 5, 30), 56789, '+491772635467')\n",
      "(6, 'Niamh', 'Murphy', 'ENG', 'IRI', datetime.date(1995, 9, 8), 67890, '+491231231232')\n"
     ]
    }
   ],
   "source": [
    "q1 = \"\"\"\n",
    "SELECT *\n",
    "FROM teacher;\n",
    "\"\"\"\n",
    "\n",
    "connection = create_db_connection(\"localhost\", \"root\", pw, db)\n",
    "results = read_query(connection, q1)\n",
    "\n",
    "for result in results:\n",
    "  print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And here are some more queries to try."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MySQL Database connection successful\n",
      "(13, 'Beginner English', 'ENG', 'Big Business Federation', '123 Falschungstraße, 10999 Berlin')\n",
      "(14, 'Intermediate English', 'ENG', 'Big Business Federation', '123 Falschungstraße, 10999 Berlin')\n",
      "(15, 'Advanced English', 'ENG', 'Big Business Federation', '123 Falschungstraße, 10999 Berlin')\n",
      "(17, 'Français intermédiaire', 'FRA', 'Big Business Federation', '123 Falschungstraße, 10999 Berlin')\n",
      "(19, 'Intermediate English', 'ENG', 'Banko Bank', '12 Betrugstraße, 12345 Berlin')\n",
      "(20, 'Fortgeschrittenes Russisch', 'RUS', 'AutoMaker AG', '20 Künstlichstraße, 10023 Berlin')\n"
     ]
    }
   ],
   "source": [
    "q2 = \"\"\"\n",
    "SELECT last_name, dob\n",
    "FROM teacher;\n",
    "\"\"\"\n",
    "\n",
    "q3 = \"\"\"\n",
    "SELECT *\n",
    "FROM course\n",
    "WHERE language = 'ENG'\n",
    "ORDER BY start_date DESC;\n",
    "\"\"\"\n",
    "\n",
    "q4 = \"\"\"\n",
    "SELECT first_name, last_name, phone_no\n",
    "FROM teacher\n",
    "WHERE dob < '1990-01-01';\n",
    "\"\"\"\n",
    "\n",
    "q5 = \"\"\"\n",
    "SELECT course.course_id, course.course_name, course.language, client.client_name, client.address\n",
    "FROM course\n",
    "JOIN client\n",
    "ON course.client = client.client_id\n",
    "WHERE course.in_school = FALSE;\n",
    "\"\"\"\n",
    "\n",
    "connection = create_db_connection(\"localhost\", \"root\", pw, db)\n",
    "results = read_query(connection, q5)\n",
    "\n",
    "for result in results:\n",
    "  print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 5.3 - Formatting Output into a List\n",
    "\n",
    "Now we can assign the results to a list, to use further in our python applications or scripts.\n",
    "\n",
    "The following code returns the results of our query as a list of tuples."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(13, 'Beginner English', 'ENG', 'Big Business Federation', '123 Falschungstraße, 10999 Berlin'), (14, 'Intermediate English', 'ENG', 'Big Business Federation', '123 Falschungstraße, 10999 Berlin'), (15, 'Advanced English', 'ENG', 'Big Business Federation', '123 Falschungstraße, 10999 Berlin'), (17, 'Français intermédiaire', 'FRA', 'Big Business Federation', '123 Falschungstraße, 10999 Berlin'), (19, 'Intermediate English', 'ENG', 'Banko Bank', '12 Betrugstraße, 12345 Berlin'), (20, 'Fortgeschrittenes Russisch', 'RUS', 'AutoMaker AG', '20 Künstlichstraße, 10023 Berlin')]\n"
     ]
    }
   ],
   "source": [
    "#Initialise empty list\n",
    "from_db = []\n",
    "\n",
    "# Loop over the results and append them into our list, different styles\n",
    "\n",
    "# Returns a list of tuples\n",
    "for result in results:\n",
    "  result = result\n",
    "  from_db.append(result)\n",
    "    \n",
    "print(from_db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 5.4 - Formatting Output into a List of Lists\n",
    "\n",
    "If we want to, we can make this return a list of lists instead, like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[13, 'Beginner English', 'ENG', 'Big Business Federation', '123 Falschungstraße, 10999 Berlin'], [14, 'Intermediate English', 'ENG', 'Big Business Federation', '123 Falschungstraße, 10999 Berlin'], [15, 'Advanced English', 'ENG', 'Big Business Federation', '123 Falschungstraße, 10999 Berlin'], [17, 'Français intermédiaire', 'FRA', 'Big Business Federation', '123 Falschungstraße, 10999 Berlin'], [19, 'Intermediate English', 'ENG', 'Banko Bank', '12 Betrugstraße, 12345 Berlin'], [20, 'Fortgeschrittenes Russisch', 'RUS', 'AutoMaker AG', '20 Künstlichstraße, 10023 Berlin']]\n"
     ]
    }
   ],
   "source": [
    "# Returns a list of lists\n",
    "from_db = []\n",
    "\n",
    "for result in results:\n",
    "  result = list(result)\n",
    "  from_db.append(result)\n",
    "    \n",
    "print(from_db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 5.5 - Formatting Output into a pandas [DataFrame](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.html)\n",
    "\n",
    "With a little more work (creating a list with our column names), we can create a pandas DataFrame like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>course_id</th>\n",
       "      <th>course_name</th>\n",
       "      <th>language</th>\n",
       "      <th>client_name</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13</td>\n",
       "      <td>Beginner English</td>\n",
       "      <td>ENG</td>\n",
       "      <td>Big Business Federation</td>\n",
       "      <td>123 Falschungstraße, 10999 Berlin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14</td>\n",
       "      <td>Intermediate English</td>\n",
       "      <td>ENG</td>\n",
       "      <td>Big Business Federation</td>\n",
       "      <td>123 Falschungstraße, 10999 Berlin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>15</td>\n",
       "      <td>Advanced English</td>\n",
       "      <td>ENG</td>\n",
       "      <td>Big Business Federation</td>\n",
       "      <td>123 Falschungstraße, 10999 Berlin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17</td>\n",
       "      <td>Français intermédiaire</td>\n",
       "      <td>FRA</td>\n",
       "      <td>Big Business Federation</td>\n",
       "      <td>123 Falschungstraße, 10999 Berlin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>19</td>\n",
       "      <td>Intermediate English</td>\n",
       "      <td>ENG</td>\n",
       "      <td>Banko Bank</td>\n",
       "      <td>12 Betrugstraße, 12345 Berlin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>20</td>\n",
       "      <td>Fortgeschrittenes Russisch</td>\n",
       "      <td>RUS</td>\n",
       "      <td>AutoMaker AG</td>\n",
       "      <td>20 Künstlichstraße, 10023 Berlin</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   course_id                 course_name language              client_name  \\\n",
       "0         13            Beginner English      ENG  Big Business Federation   \n",
       "1         14        Intermediate English      ENG  Big Business Federation   \n",
       "2         15            Advanced English      ENG  Big Business Federation   \n",
       "3         17      Français intermédiaire      FRA  Big Business Federation   \n",
       "4         19        Intermediate English      ENG               Banko Bank   \n",
       "5         20  Fortgeschrittenes Russisch      RUS             AutoMaker AG   \n",
       "\n",
       "                             address  \n",
       "0  123 Falschungstraße, 10999 Berlin  \n",
       "1  123 Falschungstraße, 10999 Berlin  \n",
       "2  123 Falschungstraße, 10999 Berlin  \n",
       "3  123 Falschungstraße, 10999 Berlin  \n",
       "4      12 Betrugstraße, 12345 Berlin  \n",
       "5   20 Künstlichstraße, 10023 Berlin  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Returns a list of lists and then creates a pandas DataFrame\n",
    "from_db = []\n",
    "\n",
    "for result in results:\n",
    "  result = list(result)\n",
    "  from_db.append(result)\n",
    "\n",
    "\n",
    "columns = [\"course_id\", \"course_name\", \"language\", \"client_name\", \"address\"]\n",
    "df = pd.DataFrame(from_db, columns=columns)\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. Updating Records\n",
    "\n",
    "Sometimes we will need to update our Database. We can do this very easily using our execute_query function alongside the SQL [UPDATE](https://dev.mysql.com/doc/refman/8.0/en/update.html) statement.\n",
    "\n",
    "##### 6.1 - Updating Client Address\n",
    "\n",
    "The School receives notification that the Big Business Federation has moved office, and now they are located at 23 Fingiertweg, 14534 Berlin. We can change that in our database like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MySQL Database connection successful\n",
      "Query successful\n"
     ]
    }
   ],
   "source": [
    "update = \"\"\"\n",
    "UPDATE client \n",
    "SET address = '23 Fingiertweg, 14534 Berlin' \n",
    "WHERE client_id = 101;\n",
    "\"\"\"\n",
    "\n",
    "connection = create_db_connection(\"localhost\", \"root\", pw, db)\n",
    "execute_query(connection, update)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's see if that worked."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MySQL Database connection successful\n",
      "(101, 'Big Business Federation', '23 Fingiertweg, 14534 Berlin', 'NGO')\n"
     ]
    }
   ],
   "source": [
    "q1 = \"\"\"\n",
    "SELECT *\n",
    "FROM client\n",
    "WHERE client_id = 101;\n",
    "\"\"\"\n",
    "\n",
    "connection = create_db_connection(\"localhost\", \"root\", pw, db)\n",
    "results = read_query(connection, q1)\n",
    "\n",
    "for result in results:\n",
    "  print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Excellent!\n",
    "\n",
    "Notice that as well as using \"WHERE client_id = 101\" in the UPDATE query, we could also have used \"WHERE client_name = 'Big Business Federation'\" or \"WHERE address = '123 Falschungstraße, 10999 Berlin'\" (or even \"WHERE address LIKE '%Falschung%'\"). The important thing is that the WHERE clause allows us to uniquely identify the record we want to update. \n",
    "\n",
    "Running the query without a WHERE clause would update all address records in the table, which is very much not what we wanted to do.\n",
    "\n",
    "-----------------\n",
    "\n",
    "### 7. Deleting Records\n",
    "\n",
    "##### 7.1 - Deleting a Course\n",
    "\n",
    "We can also use our execute_query function to delete records, by using DELETE FROM.\n",
    "\n",
    "Let's try this with our course table. First let's remind ourselves of the courses contained in the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MySQL Database connection successful\n",
      "(12, 'English for Logistics', 'ENG', 'A1', 10, datetime.date(2020, 2, 1), 1, 1, 105)\n",
      "(13, 'Beginner English', 'ENG', 'A2', 40, datetime.date(2019, 11, 12), 0, 6, 101)\n",
      "(14, 'Intermediate English', 'ENG', 'B2', 40, datetime.date(2019, 11, 12), 0, 6, 101)\n",
      "(15, 'Advanced English', 'ENG', 'C1', 40, datetime.date(2019, 11, 12), 0, 6, 101)\n",
      "(16, 'Mandarin für Autoindustrie', 'MAN', 'B1', 15, datetime.date(2020, 1, 15), 1, 3, 103)\n",
      "(17, 'Français intermédiaire', 'FRA', 'B1', 18, datetime.date(2020, 4, 3), 0, 2, 101)\n",
      "(18, 'Deutsch für Anfänger', 'DEU', 'A2', 8, datetime.date(2020, 2, 14), 1, 4, 102)\n",
      "(19, 'Intermediate English', 'ENG', 'B2', 10, datetime.date(2020, 3, 29), 0, 1, 104)\n",
      "(20, 'Fortgeschrittenes Russisch', 'RUS', 'C1', 4, datetime.date(2020, 4, 8), 0, 5, 103)\n"
     ]
    }
   ],
   "source": [
    "q1 = \"\"\"\n",
    "SELECT *\n",
    "FROM course;\n",
    "\"\"\"\n",
    "\n",
    "connection = create_db_connection(\"localhost\", \"root\", pw, db)\n",
    "results = read_query(connection, q1)\n",
    "\n",
    "from_db = []\n",
    "\n",
    "for result in results:\n",
    "  print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's delete the course with course_id 20 - 'Fortgeschrittenes Russisch'. For this we will use the [DELETE](https://dev.mysql.com/doc/refman/8.0/en/delete.html) SQL command."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MySQL Database connection successful\n",
      "Query successful\n"
     ]
    }
   ],
   "source": [
    "delete_course = \"\"\"\n",
    "DELETE FROM course WHERE course_id = 20;\n",
    "\"\"\"\n",
    "\n",
    "connection = create_db_connection(\"localhost\", \"root\", pw, db)\n",
    "execute_query(connection, delete_course)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's confirm that the course is gone."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MySQL Database connection successful\n",
      "(12, 'English for Logistics', 'ENG', 'A1', 10, datetime.date(2020, 2, 1), 1, 1, 105)\n",
      "(13, 'Beginner English', 'ENG', 'A2', 40, datetime.date(2019, 11, 12), 0, 6, 101)\n",
      "(14, 'Intermediate English', 'ENG', 'B2', 40, datetime.date(2019, 11, 12), 0, 6, 101)\n",
      "(15, 'Advanced English', 'ENG', 'C1', 40, datetime.date(2019, 11, 12), 0, 6, 101)\n",
      "(16, 'Mandarin für Autoindustrie', 'MAN', 'B1', 15, datetime.date(2020, 1, 15), 1, 3, 103)\n",
      "(17, 'Français intermédiaire', 'FRA', 'B1', 18, datetime.date(2020, 4, 3), 0, 2, 101)\n",
      "(18, 'Deutsch für Anfänger', 'DEU', 'A2', 8, datetime.date(2020, 2, 14), 1, 4, 102)\n",
      "(19, 'Intermediate English', 'ENG', 'B2', 10, datetime.date(2020, 3, 29), 0, 1, 104)\n"
     ]
    }
   ],
   "source": [
    "q1 = \"\"\"\n",
    "SELECT *\n",
    "FROM course;\n",
    "\"\"\"\n",
    "\n",
    "connection = create_db_connection(\"localhost\", \"root\", pw, db)\n",
    "results = read_query(connection, q1)\n",
    "\n",
    "from_db = []\n",
    "\n",
    "for result in results:\n",
    "  print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It's gone! Good work.\n",
    "\n",
    "##### 7.2 - Restoring the Course\n",
    "\n",
    "Let's put that course back - it's a perfectly good course."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MySQL Database connection successful\n",
      "Query successful\n",
      "MySQL Database connection successful\n",
      "(12, 'English for Logistics', 'ENG', 'A1', 10, datetime.date(2020, 2, 1), 1, 1, 105)\n",
      "(13, 'Beginner English', 'ENG', 'A2', 40, datetime.date(2019, 11, 12), 0, 6, 101)\n",
      "(14, 'Intermediate English', 'ENG', 'B2', 40, datetime.date(2019, 11, 12), 0, 6, 101)\n",
      "(15, 'Advanced English', 'ENG', 'C1', 40, datetime.date(2019, 11, 12), 0, 6, 101)\n",
      "(16, 'Mandarin für Autoindustrie', 'MAN', 'B1', 15, datetime.date(2020, 1, 15), 1, 3, 103)\n",
      "(17, 'Français intermédiaire', 'FRA', 'B1', 18, datetime.date(2020, 4, 3), 0, 2, 101)\n",
      "(18, 'Deutsch für Anfänger', 'DEU', 'A2', 8, datetime.date(2020, 2, 14), 1, 4, 102)\n",
      "(19, 'Intermediate English', 'ENG', 'B2', 10, datetime.date(2020, 3, 29), 0, 1, 104)\n",
      "(20, 'Fortgeschrittenes Russisch', 'RUS', 'C1', 4, datetime.date(2020, 4, 8), 0, 5, 103)\n"
     ]
    }
   ],
   "source": [
    "restore_russian = \"\"\"\n",
    "INSERT INTO course VALUES\n",
    "(20, 'Fortgeschrittenes Russisch', 'RUS', 'C1',  4, '2020-04-08',  FALSE, 5, 103);\n",
    "\"\"\"\n",
    "\n",
    "connection = create_db_connection(\"localhost\", \"root\", pw, db)\n",
    "execute_query(connection, restore_russian)\n",
    "\n",
    "\n",
    "q1 = \"\"\"\n",
    "SELECT *\n",
    "FROM course;\n",
    "\"\"\"\n",
    "\n",
    "connection = create_db_connection(\"localhost\", \"root\", pw, db)\n",
    "results = read_query(connection, q1)\n",
    "\n",
    "from_db = []\n",
    "\n",
    "for result in results:\n",
    "  print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Excellent.\n",
    "\n",
    "This also works with DROP TABLE if we want to get rid of a whole table at once. We won't do that in this tutorial, but feel free to try it yourself. In this notebook we can always create and populate the table again - in a real production environment we will need to be much more careful with the DELETE and DROP operations. \n",
    "\n",
    "And always remember to back up our database!\n",
    "\n",
    "----------------\n",
    "\n",
    "### 8. Creating Records from Lists\n",
    "\n",
    "We saw in Section 4 that we can use the SQL [INSERT](https://dev.mysql.com/doc/refman/8.0/en/insert.html) command in our execute_query function to insert records into our tables.\n",
    "\n",
    "MySQL Connector also gives us a way to do this in a more 'pythonic' fashion, using a list of tuples as our input, where each tuple contains the data we wish to insert into our table. This is extremely useful for updating our database with data which may have been generated by an application we have written in Python, such as logs of user activity on a social media app, for example.\n",
    "\n",
    "To do this, we will use the [executemany()](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-executemany.html) method, instead of the simpler [execute()](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html) method we have been using thus far.\n",
    "\n",
    "This method is also more secure if our database is open to our users at any point, as it helps to prevent against [SQL Injection](https://en.wikipedia.org/wiki/SQL_injection) attacks, which can damage or even destroy our whole database.\n",
    "\n",
    "##### 8.1 - Create Execute List Query Function\n",
    "\n",
    "To see how we can do this, let's add a couple of new teachers to our teacher table.\n",
    "\n",
    "Fist let's modify our execute_query function to use [executemany()](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-executemany.html) and to accept one more argument."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "def execute_list_query(connection, sql, val):\n",
    "    cursor = connection.cursor()\n",
    "    try:\n",
    "        cursor.executemany(sql, val)\n",
    "        connection.commit()\n",
    "        print(\"Query successful\")\n",
    "    except Error as err:\n",
    "        print(f\"Error: '{err}'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 8.2 - Add New Teachers\n",
    "\n",
    "Now let's create a list containing the data for our new teachers (each stored within a [tuple](https://www.w3schools.com/python/python_tuples.asp)), and the SQL command to perform our action.\n",
    "\n",
    "Notice that the SQL command requires a '%s' placeholder for each of the columns we wish to act upon, so in this case we need 8 for the 8 columns we wish to add values for."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MySQL Database connection successful\n",
      "Query successful\n"
     ]
    }
   ],
   "source": [
    "sql = '''\n",
    "    INSERT INTO teacher (teacher_id, first_name, last_name, language_1, language_2, dob, tax_id, phone_no) \n",
    "    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)\n",
    "    '''\n",
    "val = [\n",
    "    (7, 'Hank', 'Dodson', 'ENG', None, '1991-12-23', 11111, '+491772345678'), \n",
    "    (8, 'Sue', 'Perkins', 'MAN', 'ENG', '1976-02-02', 22222, '+491443456432')\n",
    "]\n",
    "\n",
    "\n",
    "connection = create_db_connection(\"localhost\", \"root\", pw, db)\n",
    "execute_list_query(connection, sql, val)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MySQL Database connection successful\n",
      "(1, 'James', 'Smith', 'ENG', None, datetime.date(1985, 4, 20), 12345, '+491774553676')\n",
      "(2, 'Stefanie', 'Martin', 'FRA', None, datetime.date(1970, 2, 17), 23456, '+491234567890')\n",
      "(3, 'Steve', 'Wang', 'MAN', 'ENG', datetime.date(1990, 11, 12), 34567, '+447840921333')\n",
      "(4, 'Friederike', 'Müller-Rossi', 'DEU', 'ITA', datetime.date(1987, 7, 7), 45678, '+492345678901')\n",
      "(5, 'Isobel', 'Ivanova', 'RUS', 'ENG', datetime.date(1963, 5, 30), 56789, '+491772635467')\n",
      "(6, 'Niamh', 'Murphy', 'ENG', 'IRI', datetime.date(1995, 9, 8), 67890, '+491231231232')\n",
      "(7, 'Hank', 'Dodson', 'ENG', None, datetime.date(1991, 12, 23), 11111, '+491772345678')\n",
      "(8, 'Sue', 'Perkins', 'MAN', 'ENG', datetime.date(1976, 2, 2), 22222, '+491443456432')\n"
     ]
    }
   ],
   "source": [
    "q1 = \"\"\"\n",
    "SELECT *\n",
    "FROM teacher;\n",
    "\"\"\"\n",
    "\n",
    "connection = create_db_connection(\"localhost\", \"root\", pw, db)\n",
    "results = read_query(connection, q1)\n",
    "\n",
    "from_db = []\n",
    "\n",
    "for result in results:\n",
    "  print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Welcome to the ILS, Hank and Sue!\n",
    "\n",
    "This method can allow us to create new records in our database (or read, update or delete existing records) using a python list as our input. It is difficult to overstate how useful this can be when we are working with Python and SQL together.\n",
    "\n",
    "--------------------\n",
    "\n",
    "### 9. Conclusion\n",
    "\n",
    "##### 9.1 - Conclusion\n",
    "\n",
    "We have covered a lot of ground in this tutorial. From using Python and MySQL Connector to create an entirely new database in MySQL Server, creating tables, defining their relationships to one another and populating them with data. We have covered how to [Create, Read, Update and Delete](https://en.wikipedia.org/wiki/Create,_read,_update_and_delete) data in our database.\n",
    "\n",
    "We have looked at how to extract data from existing databases and load them into pandas DataFrames, ready for analysis and further work taking advantage of all the possibilities offered by the [PyData stack](https://www.pluralsight.com/guides/a-lap-around-the-pydata-stack). Going the other direction, we have also learned how to take data generated by our Python scripts and applications, and write those into a database where they can be safely stored for later retrieval.\n",
    "\n",
    "I hope it is clear just how powerful each of these programming languages can be for Data Analysts. Using them together makes them even stronger.\n",
    "\n",
    "----------------------------\n",
    "\n",
    "[![CraigDoesData][logo]][link]\n",
    "\n",
    "[logo]: ./img/logo.png\n",
    "[link]: https://www.craigdoesdata.de/"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}