Mission 3 Working with cursors.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Mission 3: Working with cursors"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise \n",
    "\n",
    "Little Lemon needs to perform some basic tasks on its databases such as setting up the database and checking the names of the tables in the database. For this purpose, they have established a connection with the MySQL database using Python. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Task 1\n",
    "\n",
    "Little Lemon is restructuring its database and they are interested to know what existing tables are in the database. You need to help them retrieve the names of all the existing tables in their database. \n",
    "\n",
    "To access the names of the existing tables in the Little Lemon database, set the database `little_lemon` in use. Then, create a cursor object and execute `SHOW TABLES` to retrieve the names of the tables in the database. Fetch all the names in a variable and use the `for` loop to print the output.    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Bookings',)\n",
      "('MenuItems',)\n",
      "('Menus',)\n",
      "('Orders',)\n"
     ]
    }
   ],
   "source": [
    "# find existing tables\n",
    "cursor = connection.cursor()\n",
    "cursor.execute(\"use little_lemon\")\n",
    "cursor.execute(\"show tables\")\n",
    "results = cursor.fetchall()\n",
    "for table in results:\n",
    "    print(table)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Task 2\n",
    "\n",
    "Creating the cursor is an important step to communicate with the entire MySQL database using Python.  \n",
    "\n",
    "Run a test between the standard and the buffered cursor to check what type of cursor will work for the situation given below: \n",
    "\n",
    "* Create a cursor \n",
    "* Execute `USE little_lemon` \n",
    "* Execute `SELECT * FROM Bookings` \n",
    "* Execute `SELECT * FROM Orders` "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Now using litte_lemon db\n",
      "All Bookings retrieved\n"
     ]
    },
    {
     "ename": "InternalError",
     "evalue": "Unread result found",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mInternalError\u001b[0m                             Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[7], line 7\u001b[0m\n\u001b[1;32m      5\u001b[0m cursor\u001b[38;5;241m.\u001b[39mexecute (\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mselect * from Bookings;\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[1;32m      6\u001b[0m \u001b[38;5;28mprint\u001b[39m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mAll Bookings retrieved\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m----> 7\u001b[0m \u001b[43mcursor\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m \u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mselect * from Orders;\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\n\u001b[1;32m      8\u001b[0m \u001b[38;5;28mprint\u001b[39m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mAll Orders retrieved\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "File \u001b[0;32m~/.local/lib/python3.8/site-packages/mysql/connector/cursor_cext.py:303\u001b[0m, in \u001b[0;36mCMySQLCursor.execute\u001b[0;34m(self, operation, params, multi)\u001b[0m\n\u001b[1;32m    301\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m (ProgrammingError, \u001b[38;5;167;01mReferenceError\u001b[39;00m) \u001b[38;5;28;01mas\u001b[39;00m err:\n\u001b[1;32m    302\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m ProgrammingError(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mCursor is not connected\u001b[39m\u001b[38;5;124m\"\u001b[39m, \u001b[38;5;241m2055\u001b[39m) \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01merr\u001b[39;00m\n\u001b[0;32m--> 303\u001b[0m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_cnx\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mhandle_unread_result\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    305\u001b[0m stmt \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[1;32m    306\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mreset()\n",
      "File \u001b[0;32m~/.local/lib/python3.8/site-packages/mysql/connector/connection_cext.py:921\u001b[0m, in \u001b[0;36mCMySQLConnection.handle_unread_result\u001b[0;34m(self, prepared)\u001b[0m\n\u001b[1;32m    919\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mconsume_results()\n\u001b[1;32m    920\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m unread_result:\n\u001b[0;32m--> 921\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m InternalError(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mUnread result found\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "\u001b[0;31mInternalError\u001b[0m: Unread result found"
     ]
    }
   ],
   "source": [
    "# using standard cursor\n",
    "cursor = connection.cursor()\n",
    "cursor.execute (\"use little_lemon;\")\n",
    "print(\"Now using litte_lemon db\")\n",
    "cursor.execute (\"select * from Bookings;\")\n",
    "print(\"All Bookings retrieved\")\n",
    "cursor.execute (\"select * from Orders;\")\n",
    "print(\"All Orders retrieved\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "ename": "InternalError",
     "evalue": "Unread result found",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mInternalError\u001b[0m                             Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[8], line 2\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[38;5;66;03m#using buffered cursor\u001b[39;00m\n\u001b[0;32m----> 2\u001b[0m buffered_cursor \u001b[38;5;241m=\u001b[39m \u001b[43mconnection\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcursor\u001b[49m\u001b[43m(\u001b[49m\u001b[43mbuffered\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43m \u001b[49m\u001b[38;5;28;43;01mTrue\u001b[39;49;00m\u001b[43m)\u001b[49m\n\u001b[1;32m      3\u001b[0m buffered_cursor\u001b[38;5;241m.\u001b[39mexecute (\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124muse little_lemon;\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[1;32m      4\u001b[0m \u001b[38;5;28mprint\u001b[39m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mlittle_lemon db now in use\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "File \u001b[0;32m~/.local/lib/python3.8/site-packages/mysql/connector/connection_cext.py:673\u001b[0m, in \u001b[0;36mCMySQLConnection.cursor\u001b[0;34m(self, buffered, raw, prepared, cursor_class, dictionary, named_tuple)\u001b[0m\n\u001b[1;32m    636\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mcursor\u001b[39m(\n\u001b[1;32m    637\u001b[0m     \u001b[38;5;28mself\u001b[39m,\n\u001b[1;32m    638\u001b[0m     buffered: Optional[\u001b[38;5;28mbool\u001b[39m] \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mNone\u001b[39;00m,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m    643\u001b[0m     named_tuple: Optional[\u001b[38;5;28mbool\u001b[39m] \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mNone\u001b[39;00m,\n\u001b[1;32m    644\u001b[0m ) \u001b[38;5;241m-\u001b[39m\u001b[38;5;241m>\u001b[39m CMySQLCursor:\n\u001b[1;32m    645\u001b[0m     \u001b[38;5;124;03m\"\"\"Instantiates and returns a cursor using C Extension\u001b[39;00m\n\u001b[1;32m    646\u001b[0m \n\u001b[1;32m    647\u001b[0m \u001b[38;5;124;03m    By default, CMySQLCursor is returned. Depending on the options\u001b[39;00m\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m    671\u001b[0m \u001b[38;5;124;03m    :rtype: CMySQLCursor or subclass\u001b[39;00m\n\u001b[1;32m    672\u001b[0m \u001b[38;5;124;03m    \"\"\"\u001b[39;00m\n\u001b[0;32m--> 673\u001b[0m     \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mhandle_unread_result\u001b[49m\u001b[43m(\u001b[49m\u001b[43mprepared\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m    674\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mis_connected():\n\u001b[1;32m    675\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m OperationalError(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mMySQL Connection not available.\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "File \u001b[0;32m~/.local/lib/python3.8/site-packages/mysql/connector/connection_cext.py:921\u001b[0m, in \u001b[0;36mCMySQLConnection.handle_unread_result\u001b[0;34m(self, prepared)\u001b[0m\n\u001b[1;32m    919\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mconsume_results()\n\u001b[1;32m    920\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m unread_result:\n\u001b[0;32m--> 921\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m InternalError(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mUnread result found\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "\u001b[0;31mInternalError\u001b[0m: Unread result found"
     ]
    }
   ],
   "source": [
    "#using buffered cursor\n",
    "buffered_cursor = connection.cursor(buffered = True)\n",
    "buffered_cursor.execute (\"use little_lemon;\")\n",
    "print(\"little_lemon db now in use\")\n",
    "buffered_cursor.execute (\"select * from Bookings;\")\n",
    "print(\"Booking records are ready\")\n",
    "buffered_cursor.execute (\"select * from Orders;\")\n",
    "print(\"Orders record are ready\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Task 3\n",
    "Little Lemon will have multiple databases soon. They need to plan for a scalable solution.  This information can be tracked in a Python dictionary. A dictionary cursor will be helpful as it returns a dictionary object.  \n",
    "\n",
    "Create a cursor with argument `[dictionary = True]` and retrieve the names of all the tables in the form of a dictionary object where the name of the tables is a value, and the database name is a key.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'Tables_in_little_lemon': 'Bookings'}\n",
      "{'Tables_in_little_lemon': 'MenuItems'}\n",
      "{'Tables_in_little_lemon': 'Menus'}\n",
      "{'Tables_in_little_lemon': 'Orders'}\n"
     ]
    }
   ],
   "source": [
    "#using a dictionary cursor\n",
    "dic_cursor=connection.cursor(dictionary=True)\n",
    "dic_cursor.execute(\"use little_lemon\")\n",
    "dic_cursor.execute(\"show tables;\")\n",
    "results = dic_cursor.fetchall()\n",
    "for table in results:\n",
    "    print(table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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
}