Mission 4 Creating and reading records.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Mission 4: Creating and reading records"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Prerequisites  \n",
    "To complete this exercise, you must have access to the `“little_lemon”` database. Establish a connection between Python and the database via the connector API and create a `“cursor”` object using the following code: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector as connector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Establish connection between Python and MySQL database via connector API\n",
    "connection=connector.connect(\n",
    "                             user=\"root\", \n",
    "                             password=\"\", \n",
    "                            )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create cursor object to communicate with entire MySQL database\n",
    "cursor = connection.cursor()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once, the connection is established, and you have a cursor object, select the database `“little_lemon”` and print the names of the tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Database is use is: little_lemon\n",
      "\n",
      "The existing tables in the little_lemon database are:\n",
      "('Bookings',)\n",
      "('MenuItems',)\n",
      "('Menus',)\n",
      "('Orders',)\n"
     ]
    }
   ],
   "source": [
    "# Setting little_lemon for use \n",
    "cursor.execute(\"use little_lemon\")\n",
    "\n",
    "# Confirming\n",
    "print(\"Database is use is:\", connection.database)\n",
    "print()\n",
    "print(\"The existing tables in the little_lemon database are:\")\n",
    "cursor.execute(\"SHOW TABLES\") \n",
    "# Print table names \n",
    "for table in cursor: \n",
    "    print(table)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercise\n",
    "\n",
    "Little Lemon restaurant has created a database and designed tables to keep records of key data. The list of tables that they’ve created are as follows: \n",
    "\n",
    "* A table called `Menu` that stores menu data, \n",
    "* A table called `MenuItems` that stores data on menu items, \n",
    "* A table called `Orders` that stores data on customer orders, \n",
    "* A table called `Bookings` that stores data on customer bookings. \n",
    "\n",
    "They now need to populate these tables with relevant data. They also need to read the data once the records have been inserted into the database.  "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Task 1:\n",
    "\n",
    "Insert data in all four tables in the `\"little_lemon\"` database using Python. Using `\"INSERT\"` queries to populate the tables with relevant data. \n",
    "\n",
    "Use the `\"SELECT\"` command to check the output and ensure that each `\"INSERT\"` query has been executed successfully. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Booking table data inserted  6 rows\n"
     ]
    }
   ],
   "source": [
    "# inserting data into Bookings table\n",
    "\n",
    "insert_query = \"\"\"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",
    "cursor.execute(insert_query)\n",
    "connection.commit()\n",
    "print (\"Booking table data inserted \", cursor.rowcount , \"rows\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Bookings table\n",
      "('BookingID', 'TableNo', 'GuestFirstName', 'GuestLastName', 'BookingSlot', 'EmployeeID')\n",
      "(1, 12, 'Anna', 'Iversen', datetime.timedelta(seconds=68400), 1)\n",
      "(2, 12, 'Joakim', 'Iversen', datetime.timedelta(seconds=68400), 1)\n",
      "(3, 19, 'Vanessa', 'McCarthy', datetime.timedelta(seconds=54000), 3)\n",
      "(4, 15, 'Marcos', 'Romero', datetime.timedelta(seconds=63000), 4)\n",
      "(5, 5, 'Hiroki', 'Yamane', datetime.timedelta(seconds=66600), 2)\n",
      "(6, 8, 'Diana', 'Pinto', datetime.timedelta(seconds=72000), 5)\n"
     ]
    }
   ],
   "source": [
    "#viewing the inserted data\n",
    "checking = \"\"\"SELECT * FROM Bookings;\"\"\"\n",
    "cursor.execute(checking)\n",
    "results = cursor.fetchall()\n",
    "cols = cursor.column_names\n",
    "print(\"Bookings table\")\n",
    "print(cols)\n",
    "for result in results:\n",
    "    print(result)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "inserted data into MenuItems table  17  rows\n"
     ]
    }
   ],
   "source": [
    "#inserting data into Menuitems table\n",
    "insert_query2 = \"\"\"\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",
    "cursor.execute(insert_query2)\n",
    "connection.commit()\n",
    "print(\"inserted data into MenuItems table \", cursor.rowcount, \" rows\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MenuItems table\n",
      "('ItemID', 'Name', 'Type', 'Price')\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"
     ]
    }
   ],
   "source": [
    "checking_2 = \"\"\"SELECT * FROM MenuItems\"\"\"\n",
    "cursor.execute(checking_2)\n",
    "results = cursor.fetchall()\n",
    "cols = cursor.column_names\n",
    "print(\"MenuItems table\")\n",
    "print(cols)\n",
    "for result in results:\n",
    "    print(result)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "inserted data into Menu table,  12  rows\n"
     ]
    }
   ],
   "source": [
    "#inserting Menu table\n",
    "insert_query3 = \"\"\"INSERT INTO Menus(MenuID, ItemID, Cuisine)\n",
    "VALUES (1, 1, 'Greek'), (1, 7, 'Greek'), (1, 10, 'Greek'), (1, 13, 'Greek'), (2, 3, 'Italian'), (2, 9, 'Italian'),\n",
    "(2, 12, 'Italian'), (2, 15, 'Italian'), (3, 5, 'Turkish'), (3, 17, 'Turkish'), (3, 11, 'Turkish'), (3, 16, 'Turkish');\"\"\"\n",
    "cursor.execute(insert_query3)\n",
    "connection.commit\n",
    "print(\"inserted data into Menu table, \", cursor.rowcount,\" rows\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Menus table\n",
      "('MenuID', 'ItemID', 'Cuisine')\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, 11, 'Turkish')\n",
      "(3, 16, 'Turkish')\n",
      "(3, 17, 'Turkish')\n"
     ]
    }
   ],
   "source": [
    "#checking data on Menus table\n",
    "checking_3 = \"\"\"SELECT * FROM Menus;\"\"\"\n",
    "cursor.execute(checking_3)\n",
    "results = cursor.fetchall()\n",
    "cols = cursor.column_names\n",
    "print(\"Menus table\")\n",
    "print(cols)\n",
    "for result in results:\n",
    "    print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "data inserted into Orders table,  5  rows\n"
     ]
    }
   ],
   "source": [
    "#inserting data into Orders table\n",
    "insert_query4 = \"\"\"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",
    "cursor.execute(insert_query4)\n",
    "connection.commit()\n",
    "print(\"data inserted into Orders table, \",cursor.rowcount,\" rows\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Orders table\n",
      "('OrderID', 'TableNo', 'MenuID', 'BookingID', 'BillAmount', 'Quantity')\n",
      "(1, 12, 1, 1, 86, 2)\n",
      "(2, 19, 2, 2, 37, 1)\n",
      "(3, 15, 2, 3, 37, 1)\n",
      "(4, 5, 3, 4, 40, 1)\n",
      "(5, 8, 1, 5, 43, 1)\n"
     ]
    }
   ],
   "source": [
    "#checking data or Orders table\n",
    "checking_4 = \"\"\"SELECT * FROM Orders;\"\"\"\n",
    "cursor.execute(checking_4)\n",
    "results = cursor.fetchall()\n",
    "cols = cursor.column_names\n",
    "print(\"Orders table\")\n",
    "print(cols)\n",
    "for result in results:\n",
    "    print(result)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Task 2:\n",
    "The restaurant requires the following data for each guest: \n",
    "* Guest first and last names  \n",
    "* The table number assigned to each guest.  \n",
    "  \n",
    "Read this data from the “Bookings” table using Python. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Bookings table:\n",
      "('GuestFirstName', 'GuestLastName', 'TableNo')\n",
      "('Anna', 'Iversen', 12)\n",
      "('Joakim', 'Iversen', 12)\n",
      "('Vanessa', 'McCarthy', 19)\n",
      "('Marcos', 'Romero', 15)\n",
      "('Hiroki', 'Yamane', 5)\n",
      "('Diana', 'Pinto', 8)\n"
     ]
    }
   ],
   "source": [
    "# retrieveing data from booking table\n",
    "retrieve_query = \"\"\"SELECT GuestFirstName, GuestLastName, TableNo FROM Bookings\"\"\"\n",
    "cursor.execute(retrieve_query)\n",
    "results = cursor.fetchall()\n",
    "columns = cursor.column_names\n",
    "print(\"\"\"Bookings table:\"\"\")\n",
    "print(columns)\n",
    "for result in results:\n",
    "    print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Task 3:\n",
    "Read all the records in the `“Menu”` table and retrieve only the first three from the `“cursor”`. \n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Options 1:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The first 3 rows in Menus table\n",
      "('MenuID', 'ItemID', 'Cuisine')\n",
      "(1, 1, 'Greek')\n",
      "(1, 7, 'Greek')\n",
      "(1, 10, 'Greek')\n"
     ]
    }
   ],
   "source": [
    "# retrieve the first 3 records from Menus table\n",
    "select_query = \"\"\"SELECT * FROM Menus\"\"\"\n",
    "cursor.execute(select_query)\n",
    "results = cursor.fetchmany(size=3)\n",
    "columns=cursor.column_names\n",
    "print(\"The first 3 rows in Menus table\")\n",
    "print(columns)\n",
    "for result in results:\n",
    "    print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After grabbing first three records, the cursor will be at the fourth record and we can call `fetchall()` to grab them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(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, 11, 'Turkish')\n",
      "(3, 16, 'Turkish')\n",
      "(3, 17, 'Turkish')\n"
     ]
    }
   ],
   "source": [
    "# getting the rest of the rows\n",
    "results= cursor.fetchall()\n",
    "for result in results:\n",
    "    print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Options 2:**\n",
    "\n",
    "This task can also be accomplished by setting `LIMIT 3` in the `SQL` query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "First 3 columns on the \"Menu\" table\n",
      "('MenuID', 'ItemID', 'Cuisine')\n",
      "(1, 1, 'Greek')\n",
      "(1, 7, 'Greek')\n",
      "(1, 10, 'Greek')\n"
     ]
    }
   ],
   "source": [
    "# method two of retrieveing the first three rows\n",
    "select_query2 = \"\"\"SELECT * FROM Menus LIMIT 3;\"\"\"\n",
    "cursor.execute(select_query2)\n",
    "results = cursor.fetchall()   \n",
    "columns = cursor.column_names\n",
    "print(\"\"\"First 3 columns on the \"Menu\" table\"\"\")\n",
    "print(columns)\n",
    "for result in results:\n",
    "    print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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": {
  "interpreter": {
   "hash": "31f2aee4e71d21fbe5cf8b01ff0e069b9275f58929596ceb00d14d90e3e16cd6"
  },
  "kernelspec": {
   "display_name": "Python 3.8.10 64-bit",
   "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}