little_lemon.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Little Lemon is a family-owned Mediterranean restaurant. They are developing a Python-based application that needs to connect with the MySQL database so that the booking, menu and orders data can be stored in the respective tables.**\n",
    "\n",
    "**The restaurant owner wants to use the stored data to make data-driven decisions to increase their revenue. Establishing a database is one of their key objectives.**\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Establishing a Connection :**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importing MySQL Connector/Python \n",
    "import mysql.connector as connector\n",
    "from mysql.connector import Error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    connection=connector.connect(user=\"root\",password=\"\")\n",
    "except Error as er:\n",
    "    print(er.msg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Creating a Cursor :**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cursor is created to communicate with the MySQL using Python.\n"
     ]
    }
   ],
   "source": [
    "cursor = connection.cursor()\n",
    "print(\"Cursor is created to communicate with the MySQL using Python.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Creating the Database and Setting it for use :**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The database 'little_lemon_db' is created.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"CREATE DATABASE little_lemon_db\")\n",
    "print(\"The database 'little_lemon_db' is created.\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The database little_lemon_db is set for use.\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"USE little_lemon_db\")\n",
    "connection.database\n",
    "print(\"The database little_lemon_db is set for use.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Creating Tables :**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "MenuItems table :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MenuItems table is created.\n"
     ]
    }
   ],
   "source": [
    "#MenuItems table\n",
    "create_menuitem_table = \"\"\"CREATE TABLE MenuItems(\n",
    "ItemID INT AUTO_INCREMENT,\n",
    "Name VARCHAR(200),\n",
    "Type VARCHAR(100),\n",
    "Price INT,\n",
    "PRIMARY KEY(ItemID)\n",
    ");\"\"\"\n",
    "\n",
    "# Create MenuItems table\n",
    "cursor.execute(create_menuitem_table)\n",
    "print(\"MenuItems table is created.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Menu Table :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Menu table is created.\n"
     ]
    }
   ],
   "source": [
    "create_menu_table = \"\"\"CREATE TABLE Menus(\n",
    "MenuID INT,\n",
    "ItemID INT,\n",
    "Cuisine VARCHAR(100),\n",
    "PRIMARY KEY(MenuID,ItemID)\n",
    ");\"\"\"\n",
    "\n",
    "# Create Menu table\n",
    "cursor.execute(create_menu_table)\n",
    "print(\"Menu table is created.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Bookings table :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Bookings table is created.\n"
     ]
    }
   ],
   "source": [
    "create_booking_table = \"\"\"CREATE TABLE Bookings(\n",
    "BookingID INT AUTO_INCREMENT,\n",
    "TableNo INT,\n",
    "GuestFirstName VARCHAR(100) NOT NULL,\n",
    "GuestLastName VARCHAR(100) NOT NULL,\n",
    "BookingSlot TIME NOT NULL,\n",
    "EmployeeID INT,\n",
    "PRIMARY KEY(BookingID)\n",
    ");\"\"\"\n",
    "\n",
    "# Create Bookings table\n",
    "cursor.execute(create_booking_table)\n",
    "print(\"Bookings table is created.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Orders table :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Orders table is created.\n"
     ]
    }
   ],
   "source": [
    "create_orders_table = \"\"\"CREATE TABLE Orders(\n",
    "OrderID INT,\n",
    "TableNo INT,\n",
    "MenuID INT,\n",
    "BookingID INT,\n",
    "BillAmount INT,\n",
    "Quantity INT,\n",
    "PRIMARY KEY(OrderID,TableNo)\n",
    ");\"\"\"\n",
    "\n",
    "# Create Orders table\n",
    "cursor.execute(create_orders_table)\n",
    "print(\"Orders table is created.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Employees table :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Employees table is created.\n"
     ]
    }
   ],
   "source": [
    "create_employees_table = \"\"\"CREATE TABLE Employees(\n",
    "EmployeeID INT AUTO_INCREMENT PRIMARY KEY,\n",
    "Name VARCHAR(100),\n",
    "Role VARCHAR(100),\n",
    "Address VARCHAR(200),\n",
    "Contact_Number INT,\n",
    "Email VARCHAR(100),\n",
    "Annual_Salary VARCHAR(100)\n",
    ");\"\"\"\n",
    "\n",
    "# Create Employees table\n",
    "cursor.execute(create_employees_table)\n",
    "print(\"Employees table is created.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Inserting data :**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "#*******************************************************#\n",
    "# Insert query to populate \"MenuItems\" table:\n",
    "#*******************************************************#\n",
    "insert_menuitems=\"\"\"\n",
    "INSERT INTO MenuItems (ItemID, Name, Type, Price)\n",
    "VALUES\n",
    "(1, 'Olives','Starters',5),\n",
    "(2, 'Flatbread','Starters', 5),\n",
    "(3, 'Minestrone', 'Starters', 8),\n",
    "(4, 'Tomato bread','Starters', 8),\n",
    "(5, 'Falafel', 'Starters', 7),\n",
    "(6, 'Hummus', 'Starters', 5),\n",
    "(7, 'Greek salad', 'Main Courses', 15),\n",
    "(8, 'Bean soup', 'Main Courses', 12),\n",
    "(9, 'Pizza', 'Main Courses', 15),\n",
    "(10, 'Greek yoghurt','Desserts', 7),\n",
    "(11, 'Ice cream', 'Desserts', 6),\n",
    "(12, 'Cheesecake', 'Desserts', 4),\n",
    "(13, 'Athens White wine', 'Drinks', 25),\n",
    "(14, 'Corfu Red Wine', 'Drinks', 30),\n",
    "(15, 'Turkish Coffee', 'Drinks', 10),\n",
    "(16, 'Turkish Coffee', 'Drinks', 10),\n",
    "(17, 'Kabasa', 'Main Courses', 17);\"\"\"\n",
    "\n",
    "#*******************************************************#\n",
    "# Insert query to populate \"Menu\" table:\n",
    "#*******************************************************#\n",
    "insert_menu=\"\"\"\n",
    "INSERT INTO Menus (MenuID,ItemID,Cuisine)\n",
    "VALUES\n",
    "(1, 1, 'Greek'),\n",
    "(1, 7, 'Greek'),\n",
    "(1, 10, 'Greek'),\n",
    "(1, 13, 'Greek'),\n",
    "(2, 3, 'Italian'),\n",
    "(2, 9, 'Italian'),\n",
    "(2, 12, 'Italian'),\n",
    "(2, 15, 'Italian'),\n",
    "(3, 5, 'Turkish'),\n",
    "(3, 17, 'Turkish'),\n",
    "(3, 11, 'Turkish'),\n",
    "(3, 16, 'Turkish');\"\"\"\n",
    "\n",
    "#*******************************************************#\n",
    "# Insert query to populate \"Bookings\" table:\n",
    "#*******************************************************#\n",
    "insert_bookings=\"\"\"\n",
    "INSERT INTO Bookings (BookingID, TableNo, GuestFirstName, \n",
    "GuestLastName, BookingSlot, EmployeeID)\n",
    "VALUES\n",
    "(1, 12, 'Anna','Iversen','19:00:00',1),\n",
    "(2, 12, 'Joakim', 'Iversen', '19:00:00', 1),\n",
    "(3, 19, 'Vanessa', 'McCarthy', '15:00:00', 3),\n",
    "(4, 15, 'Marcos', 'Romero', '17:30:00', 4),\n",
    "(5, 5, 'Hiroki', 'Yamane', '18:30:00', 2),\n",
    "(6, 8, 'Diana', 'Pinto', '20:00:00', 5);\"\"\"\n",
    "\n",
    "#*******************************************************#\n",
    "# Insert query to populate \"Orders\" table:\n",
    "#*******************************************************#\n",
    "insert_orders=\"\"\"\n",
    "INSERT INTO Orders (OrderID, TableNo, MenuID, BookingID, Quantity, BillAmount)\n",
    "VALUES\n",
    "(1, 12, 1, 1, 2, 86),\n",
    "(2, 19, 2, 2, 1, 37),\n",
    "(3, 15, 2, 3, 1, 37),\n",
    "(4, 5, 3, 4, 1, 40),\n",
    "(5, 8, 1, 5, 1, 43);\"\"\"\n",
    "\n",
    "#*******************************************************#\n",
    "# Insert query to populate \"Employees\" table:\n",
    "#*******************************************************#\n",
    "insert_employees = \"\"\"\n",
    "INSERT INTO employees (EmployeeID, Name, Role, Address, Contact_Number, Email, Annual_Salary)\n",
    "VALUES\n",
    "(01,'Mario Gollini','Manager','724, Parsley Lane, Old Town, Chicago, IL',351258074,'Mario.g@littlelemon.com','$70,000'),\n",
    "(02,'Adrian Gollini','Assistant Manager','334, Dill Square, Lincoln Park, Chicago, IL',351474048,'Adrian.g@littlelemon.com','$65,000'),\n",
    "(03,'Giorgos Dioudis','Head Chef','879 Sage Street, West Loop, Chicago, IL',351970582,'Giorgos.d@littlelemon.com','$50,000'),\n",
    "(04,'Fatma Kaya','Assistant Chef','132  Bay Lane, Chicago, IL',351963569,'Fatma.k@littlelemon.com','$45,000'),\n",
    "(05,'Elena Salvai','Head Waiter','989 Thyme Square, EdgeWater, Chicago, IL',351074198,'Elena.s@littlelemon.com','$40,000'),\n",
    "(06,'John Millar','Receptionist','245 Dill Square, Lincoln Park, Chicago, IL',351584508,'John.m@littlelemon.com','$35,000');\"\"\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Inserting data in MenuItems table.\n",
      "Total number of rows in MenuItem table: 17\n",
      "\n",
      "Inserting data in Menu table.\n",
      "Total number of rows in Menu table: 12\n",
      "\n",
      "Inserting data in Bookings table.\n",
      "Total number of rows in Bookings table: 6\n",
      "\n",
      "Inserting data in Orders table.\n",
      "Total number of rows in Orders table: 5\n",
      "\n",
      "Inserting data in Employees table.\n",
      "Total number of rows in Employees table: 6\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Populate MenuItems table\n",
    "print(\"Inserting data in MenuItems table.\")\n",
    "cursor.execute(insert_menuitems)\n",
    "print(\"Total number of rows in MenuItem table: {}\\n\".format(cursor.rowcount))\n",
    "connection.commit()\n",
    "\n",
    "# Populate Menu table\n",
    "cursor.execute(insert_menu)\n",
    "print(\"Inserting data in Menu table.\")\n",
    "print(\"Total number of rows in Menu table: {}\\n\".format(cursor.rowcount))\n",
    "connection.commit()\n",
    "\n",
    "# Populate Bookings table\n",
    "print(\"Inserting data in Bookings table.\")\n",
    "cursor.execute(insert_bookings)\n",
    "print(\"Total number of rows in Bookings table: {}\\n\".format(cursor.rowcount))\n",
    "connection.commit()\n",
    "\n",
    "# Populate Orders table\n",
    "print(\"Inserting data in Orders table.\")\n",
    "cursor.execute(insert_orders)\n",
    "print(\"Total number of rows in Orders table: {}\\n\".format(cursor.rowcount))\n",
    "connection.commit()\n",
    "\n",
    "# Populate Employees table\n",
    "print(\"Inserting data in Employees table.\")\n",
    "cursor.execute(insert_employees)\n",
    "print(\"Total number of rows in Employees table: {}\\n\".format(cursor.rowcount))\n",
    "connection.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Implement and Query Stored Procedures**\n",
    "\n",
    "Create a pool of connections and get a connection from the pool to implement the stored procedures to complete the following tasks:\n",
    "\n",
    "1. Establish a connection by importing MySQLConnectionPool and creating a pool with two connections.\n",
    "\n",
    "2. Create and call a stored procedure named PeakHours that identifies the peak, or busiest hour, for the restaurant based on the number of bookings.\n",
    "\n",
    "3. Create and call a stored procedure named GuestStatus that outputs status of each guest’s order based on which employee is assigned to the order.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Establishing and Creating a Pool with 2 Connections :**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import MySQLConnectionPool class\n",
    "from mysql.connector.pooling import MySQLConnectionPool\n",
    "\n",
    "# Import Error class\n",
    "from mysql.connector import Error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The connection pool is created with name: pool_a\n",
      "The pool size is: 2\n"
     ]
    }
   ],
   "source": [
    "dbconfig={\"database\":\"little_lemon_db\", \"user\":\"root\", \"password\":\"\"}\n",
    "try:\n",
    "    pool = MySQLConnectionPool(pool_name=\"pool_a\", pool_size=2, host='localhost', **dbconfig)\n",
    "    print(\"The connection pool is created with name:\", pool.pool_name)\n",
    "    print(\"The pool size is:\", pool.pool_size)\n",
    "except Error as err:\n",
    "    print(\"Error Code:\", err.errno)\n",
    "    print(\"Error Message:\", err.msg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Obtaining a connection from pool_a and creating a cursor object to communicate with the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Getting a connection from the pool.\n",
      "'connection' object is created with a connection from the pool\n"
     ]
    }
   ],
   "source": [
    "print(\"Getting a connection from the pool.\")\n",
    "\n",
    "connection = pool.get_connection()\n",
    "if connection.is_connected():\n",
    "    cursor = connection.cursor()\n",
    "\n",
    "print(\"'connection' object is created with a connection from the pool\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Implementing a Stored Procedure called PeakHours**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Creating PROCEDURE query for PeakHours"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "create_peakhours_proc = \"\"\"\n",
    "CREATE PROCEDURE PeakHours()\n",
    "BEGIN\n",
    "SELECT HOUR(BookingSlot) AS booking_hour, COUNT(BookingID) AS number_of_bookings\n",
    "FROM Bookings\n",
    "GROUP BY booking_hour\n",
    "ORDER BY number_of_bookings DESC;\n",
    "END\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Running the stored procedure query by invoking execute module on the cursor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(create_peakhours_proc)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Invoke callproc to call the stored procedure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "()"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.callproc('PeakHours')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fetching the results in a variable called dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "results = next(cursor.stored_results())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = results.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Extracting the names of the columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "for column_id in cursor.stored_results():\n",
    "    cols = [column[0] for column in column_id.description]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Printing the names of the columns and the sorted data using for Loop"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['booking_hour', 'number_of_bookings']\n",
      "(19, 2)\n",
      "(15, 1)\n",
      "(17, 1)\n",
      "(18, 1)\n",
      "(20, 1)\n"
     ]
    }
   ],
   "source": [
    "print(cols)\n",
    "for data in dataset:\n",
    "    print(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Implementing a stored procedure GuestStatus**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "create_gueststatus_proc = \"\"\"\n",
    "CREATE PROCEDURE GuestStatus()\n",
    "BEGIN\n",
    "SELECT CONCAT(GuestFirstName, ' ', GuestLastName) AS GuestFullName,\n",
    "CASE\n",
    "WHEN Role in ('Manager','Assistant Manager') THEN 'Ready to pay'\n",
    "WHEN Role in ('Head Chef') THEN 'Ready to serve'\n",
    "WHEN Role in ('Assistant Chef') THEN 'Preparing Order'\n",
    "WHEN Role in ('Head Waiter') THEN 'Order served'\n",
    "ELSE \"Pending\"\n",
    "END AS Status\n",
    "FROM Bookings\n",
    "LEFT JOIN Employees\n",
    "ON Bookings.EmployeeID = Employees.EmployeeID;\n",
    "END\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(create_gueststatus_proc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "()"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.callproc('GuestStatus')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fetch the results in a variable called dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "results = next(cursor.stored_results())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = results.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Extract the names of the columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "for column_id in cursor.stored_results():\n",
    "    cols = [column[0] for column in column_id.description]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Printing the names of the columns and the sorted data using for Loop"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['GuestFullName', 'Status']\n",
      "('Anna Iversen', 'Ready to pay')\n",
      "('Joakim Iversen', 'Ready to pay')\n",
      "('Vanessa McCarthy', 'Ready to serve')\n",
      "('Marcos Romero', 'Preparing Order')\n",
      "('Hiroki Yamane', 'Ready to pay')\n",
      "('Diana Pinto', 'Order served')\n"
     ]
    }
   ],
   "source": [
    "print(cols)\n",
    "for data in dataset:\n",
    "    print(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Closing the connection to return it back to the pool"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Little Lemon Analysis and Sales Report :**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Establishing and Creating a Pool with 2 Connections :\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import MySQLConnectionPool class\n",
    "from mysql.connector.pooling import MySQLConnectionPool\n",
    "\n",
    "# Import Error class\n",
    "from mysql.connector import Error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The connection pool is created with name: pool_b\n",
      "The pool size is: 2\n"
     ]
    }
   ],
   "source": [
    "dbconfig={\"database\":\"little_lemon_db\", \"user\":\"root\", \"password\":\"\"}\n",
    "try:\n",
    "    pool = MySQLConnectionPool(pool_name=\"pool_b\", pool_size=2, host='localhost', **dbconfig)\n",
    "    print(\"The connection pool is created with name:\", pool.pool_name)\n",
    "    print(\"The pool size is:\", pool.pool_size)\n",
    "except Error as err:\n",
    "    print(\"Error Code:\", err.errno)\n",
    "    print(\"Error Message:\", err.msg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Three guests are trying to book dinner slots simultaneously. Get the connections from pool_b and insert the following data in the Bookings table:\n",
    "\n",
    "TIP: You need to add a connection to connect the third guest.\n",
    "\n",
    "Guest 1:\n",
    "\n",
    "Table Number: 8,\n",
    "First Name: Anees,\n",
    "Last Name: Java,\n",
    "Booking Time: 18:00,\n",
    "EmployeeID: 6\n",
    "\n",
    "Guest 2:\n",
    "\n",
    "Table Number: 5,\n",
    "First Name: Bald,\n",
    "Last Name: Vin,\n",
    "Booking Time: 19:00,\n",
    "EmployeeID: 6\n",
    "\n",
    "\n",
    "Guest 3:\n",
    "\n",
    "Table Number: 12,\n",
    "First Name: Jay,\n",
    "Last Name: Kon,\n",
    "Booking Time: 19:30, \n",
    "EmployeeID: 6\n",
    "\n",
    "Return all the connections back to the pool. \n",
    "\n",
    "\n",
    "TIP: The pool size is two. However, you have three connected users. You can only return two connections. Returning a third connection will raise a PoolError. Use try-except to print the error message.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A new booking is added in the \"Bookings\" table.\n",
      "A new booking is added in the \"Bookings\" table.\n",
      "Adding new connection in the pool.\n",
      "A new connection is added in the pool.\n",
      "\n",
      "'connection3' is added in the pool.\n",
      "A new booking is added in the \"Bookings\" table.\n",
      "Connection is returned to the pool\n",
      "Connection is returned to the pool\n",
      "\n",
      "Connection can't be returned to the pool\n",
      "Error Code: -1\n",
      "Error message: Failed adding connection; queue is full\n"
     ]
    }
   ],
   "source": [
    "# Connect the first guest.\n",
    "connection1 = pool.get_connection()\n",
    "cursor1=connection1.cursor()\n",
    "\n",
    "booking1=\"\"\"INSERT INTO Bookings \n",
    "(TableNo, GuestFirstName, GuestLastName, BookingSlot, EmployeeID)\n",
    "VALUES\n",
    "(8,'Anees','Java','18:00:00',6);\"\"\"\n",
    "\n",
    "cursor1.execute(booking1)\n",
    "connection1.commit()\n",
    "print(\"\"\"A new booking is added in the \"Bookings\" table.\"\"\")\n",
    "\n",
    "# Connect the second guest.\n",
    "connection2 = pool.get_connection()\n",
    "cursor2=connection2.cursor()\n",
    "\n",
    "booking2=\"\"\"INSERT INTO Bookings \n",
    "(TableNo, GuestFirstName, GuestLastName, BookingSlot, EmployeeID)\n",
    "VALUES\n",
    "(5, 'Bald','Vin','19:00:00',6);\"\"\"\n",
    "\n",
    "cursor2.execute(booking2)\n",
    "connection2.commit()\n",
    "print(\"\"\"A new booking is added in the \"Bookings\" table.\"\"\")\n",
    "\n",
    "# Adding a new connection to connect the third user.\n",
    "\n",
    "import mysql.connector as connector\n",
    "try:\n",
    "    connection3 = pool.get_connection()\n",
    "    print(\"The guest is connected\")\n",
    "except:\n",
    "    print(\"Adding new connection in the pool.\")\n",
    "        \n",
    "    # Create a connection\n",
    "    connection=connector.connect(user=\"root\",password=\"\")\n",
    "    \n",
    "    # Add the connection into the pool\n",
    "    pool.add_connection(cnx=connection)\n",
    "    print(\"A new connection is added in the pool.\\n\")\n",
    "        \n",
    "    connection3 = pool.get_connection()\n",
    "    print(\"'connection3' is added in the pool.\")\n",
    "    \n",
    "# Connect the third guest \n",
    "cursor3=connection3.cursor()\n",
    "\n",
    "booking3=\"\"\"INSERT INTO Bookings \n",
    "(TableNo, GuestFirstName, GuestLastName, BookingSlot, EmployeeID)\n",
    "VALUES\n",
    "(12, 'Jay','Kon','19:30:00',6);\"\"\"\n",
    "\n",
    "cursor3.execute(booking3)\n",
    "connection3.commit()\n",
    "print(\"\"\"A new booking is added in the \"Bookings\" table.\"\"\")\n",
    "\n",
    "# You can only return two connections back to the pool as the pool_size=2.\n",
    "# Closing all connections and using try-except to print the pool error if the pool is already full.\n",
    "\n",
    "from mysql.connector import Error\n",
    "for connection in [connection1, connection2, connection3]:\n",
    "    try:\n",
    "        connection.close()\n",
    "        print(\"Connection is returned to the pool\")\n",
    "    except Error as err:\n",
    "        print(\"\\nConnection can't be returned to the pool\")\n",
    "        print(\"Error Code:\", err.errno)\n",
    "        print(\"Error message:\", err.msg)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Create a report containing the following information:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Getting a connection from the pool.\n",
      "The object \"connection\" is created with a connection link from the pool_a\n",
      "Creating a cursor object to communicate with the database.\n",
      "The cursor object \"cursor\" is created.\n"
     ]
    }
   ],
   "source": [
    "# But First getting a connection from pool_a and creating a cursor object to communicate with the database. \n",
    "print(\"Getting a connection from the pool.\")\n",
    "connection = pool.get_connection()\n",
    "\n",
    "print(\"\"\"The object \"connection\" is created with a connection link from the pool_a\"\"\")\n",
    "print(\"\"\"Creating a cursor object to communicate with the database.\"\"\")\n",
    "\n",
    "cursor=connection.cursor()\n",
    "print(\"\"\"The cursor object \"cursor\" is created.\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. ) The name and EmployeeID of the Little Lemon manager"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Name', 'EmployeeID')\n",
      "('Mario Gollini', 1)\n"
     ]
    }
   ],
   "source": [
    "report_stmt_1 = \"\"\"\n",
    "SELECT Name, EmployeeID \n",
    "FROM Employees\n",
    "WHERE Role = 'Manager';\n",
    "\"\"\"\n",
    "\n",
    "cursor.execute(report_stmt_1)\n",
    "\n",
    "results=cursor.fetchall()\n",
    "\n",
    "columns=cursor.column_names\n",
    "\n",
    "print(columns)\n",
    "for result in results:\n",
    "    print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. ) The name and role of the employee who receives the highest salary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Name', 'Role')\n",
      "('Mario Gollini', 'Manager')\n"
     ]
    }
   ],
   "source": [
    "report_stmt_2 = \"\"\"\n",
    "SELECT Name, Role\n",
    "FROM Employees\n",
    "WHERE Annual_Salary = (SELECT MAX(Annual_Salary) FROM Employees);\n",
    "\"\"\"\n",
    "\n",
    "cursor.execute(report_stmt_2)\n",
    "\n",
    "results=cursor.fetchall()\n",
    "\n",
    "columns=cursor.column_names\n",
    "\n",
    "print(columns)\n",
    "for result in results:\n",
    "    print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3. ) The number of guests booked between 18:00 and 20:00."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('number_of_guests',)\n",
      "(7,)\n"
     ]
    }
   ],
   "source": [
    "report_stmt_3 = \"\"\"\n",
    "SELECT COUNT(BookingID) AS number_of_guests\n",
    "FROM Bookings\n",
    "WHERE BookingSlot BETWEEN '18:00:00' AND '20:00:00';\n",
    "\"\"\"\n",
    "\n",
    "cursor.execute(report_stmt_3)\n",
    "\n",
    "results=cursor.fetchall()\n",
    "\n",
    "columns=cursor.column_names\n",
    "\n",
    "print(columns)\n",
    "for result in results:\n",
    "    print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4. ) The full name and BookingID of all guests waiting to be seated with the receptionist in sorted order with respect to their BookingSlot."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The following guests are waiting to be seated:\n",
      "('GuestFullName', 'BookingID')\n",
      "('Jay Kon', 9)\n",
      "('Bald Vin', 8)\n",
      "('Anees Java', 7)\n"
     ]
    }
   ],
   "source": [
    "report_stmt_4 = \"\"\"\n",
    "SELECT CONCAT(Bookings.GuestFirstName, \" \", Bookings.GuestLastName) AS GuestFullName, Bookings.BookingID\n",
    "FROM Bookings\n",
    "LEFT JOIN Employees \n",
    "ON Employees.EmployeeID = Bookings.EmployeeID\n",
    "WHERE Employees.Role = \"Receptionist\"\n",
    "ORDER BY Bookings.BookingSlot DESC;\n",
    "\"\"\"\n",
    "\n",
    "print(\"The following guests are waiting to be seated:\")\n",
    "\n",
    "cursor.execute(report_stmt_4)\n",
    "\n",
    "results=cursor.fetchall()\n",
    "\n",
    "columns=cursor.column_names\n",
    "\n",
    "print(columns)\n",
    "for result in results:\n",
    "    print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Create a stored procedure named BasicSalesReport that returns the following statistics:**\n",
    "\n",
    "Total sales\n",
    "\n",
    "Average sale\n",
    "\n",
    "Minimum bill paid\n",
    "\n",
    "Maximum bill paid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "create_basicsalesreport_proc = \"\"\"\n",
    "CREATE PROCEDURE BasicSalesReport()\n",
    "BEGIN\n",
    "SELECT SUM(BillAmount) AS Total_sales,\n",
    "AVG(BillAmount) AS Average_sale,\n",
    "MIN(BillAmount) AS Min_bill_paid,\n",
    "MAX(BillAmount) AS Max_bill_paid\n",
    "FROM Orders;\n",
    "END\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(create_basicsalesreport_proc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "()"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.callproc('BasicSalesReport')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "results = next(cursor.stored_results())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = results.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "for column_id in cursor.stored_results():\n",
    "    cols = [column[0] for column in column_id.description]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\t Total_sales : 243\n",
      "\t Average_sale : 48.6000\n",
      "\t Min_bill_paid : 37\n",
      "\t Max_bill_paid : 86\n"
     ]
    }
   ],
   "source": [
    "for data in dataset:\n",
    "    print('\\t', cols[0], ':', data[0])\n",
    "    print('\\t', cols[1], ':', data[1])\n",
    "    print('\\t', cols[2], ':', data[2])\n",
    "    print('\\t', cols[3], ':', data[3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Little Lemon needs to display the next three upcoming bookings from the Bookings table on the kitchen screen to notify their chefs which orders are due next. To complete this task, carry out the following steps:**\n",
    "\n",
    "1. Get a connection from the pool.\n",
    "\n",
    "2. Create a buffered cursor.\n",
    "\n",
    "3. Combine the data from the Bookings and the Employee tables. Sort the retrieved records in ascending order. Then display the information of the first three guests. \n",
    "\n",
    "4. Returned the connection back to the pool.\n",
    "\n",
    "\n",
    "**The output should be as follows:**\n",
    "\n",
    "[BookingSlot]\n",
    "\n",
    "[Guest_name]\n",
    "\n",
    "[Assigned to: Employee Name [Employee Role]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = pool.get_connection()\n",
    "cursor = connection.cursor(buffered=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "upcoming_bookings_stmt = \"\"\"\n",
    "SELECT Bookings.BookingSlot,\n",
    "CONCAT(Bookings.GuestFirstName,\" \", Bookings.GuestLastName) AS Guest_Name,\n",
    "Employees.Name AS Emp_Name,\n",
    "Employees.Role AS Emp_Role\n",
    "FROM Bookings\n",
    "INNER JOIN Employees\n",
    "ON Bookings.EmployeeID = Employees.EmployeeID\n",
    "ORDER BY Bookings.BookingSlot;\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(upcoming_bookings_stmt)\n",
    "results = cursor.fetchmany(size=3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "BookingSlot : 15:00:00\n",
      "\tGuest_name : Vanessa McCarthy\n",
      "\tAssigned to : Giorgos Dioudis [Head Chef]\n",
      "\n",
      "BookingSlot : 17:30:00\n",
      "\tGuest_name : Marcos Romero\n",
      "\tAssigned to : Fatma Kaya [Assistant Chef]\n",
      "\n",
      "BookingSlot : 18:00:00\n",
      "\tGuest_name : Anees Java\n",
      "\tAssigned to : John Millar [Receptionist]\n"
     ]
    }
   ],
   "source": [
    "for result in results:\n",
    "    print(\"\\nBookingSlot :\", result[0])\n",
    "    print(\"\\tGuest_name :\", result[1])\n",
    "    print(\"\\tAssigned to :\", result[2],\"[{}]\".format(result[3]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The cursor is closed.\n",
      "MySQL connection is closed.\n"
     ]
    }
   ],
   "source": [
    "if connection.is_connected():\n",
    "    cursor.close()\n",
    "    print(\"The cursor is closed.\")\n",
    "    connection.close()\n",
    "    print(\"MySQL connection is closed.\")\n",
    "else:\n",
    "    print(\"Connection is already closed\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.10.9"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}