little_lemon_client.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# install mysql-connector\n",
    "%pip install mysql-connector-python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "# libs\n",
    "import mysql.connector as connector\n",
    "from mysql.connector import errorcode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# connection\n",
    "def get_connection() -> connector.connection_cext.CMySQLConnection:\n",
    "    try:\n",
    "        connection = connector.connect(\n",
    "            user=\"root\",\n",
    "            password=\"\",\n",
    "            database=\"LittleLemonDB\",\n",
    "            host=\"localhost\",\n",
    "            port=3306\n",
    "            )\n",
    "        return connection\n",
    "    except connector.Error as e:\n",
    "        if e.errno == errorcode.ER_ACCESS_DENIED_ERROR:\n",
    "            print(\"connection user or password are incorrect\")\n",
    "        elif e.errno == errorcode.ER_BAD_DB_ERROR:\n",
    "            print(\"database does not exist\")\n",
    "        else:\n",
    "            print(\"Error code: \", e.errno)\n",
    "            print(\"Error message: \", e.msg)\n",
    "\n",
    "# mysql statements execution\n",
    "def mysql_results(query: str, type='query', args=[]) -> None:\n",
    "    connection = get_connection()\n",
    "    cursor = connection.cursor()\n",
    "    if type == 'procedure':\n",
    "        cursor.callproc(query, args)\n",
    "        [print(*i[0]) for i in [res.fetchall() for res in cursor.stored_results()]]\n",
    "    else:\n",
    "        cursor.execute(query)\n",
    "        [print(*i) for i in cursor.fetchall()]\n",
    "    connection.commit()\n",
    "    cursor.close()\n",
    "    connection.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "addresses\n",
      "bookings\n",
      "categories\n",
      "cuisines\n",
      "customers\n",
      "frequencyappearanceinorders\n",
      "menu\n",
      "orderitems\n",
      "orders\n",
      "ordersdeliverystatus\n",
      "ordersview\n",
      "roles\n",
      "staff\n"
     ]
    }
   ],
   "source": [
    "# show tables\n",
    "query = 'SHOW tables;'\n",
    "mysql_results(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Arthur Lopez arthurlopez@gmail.com 987895623 49.48\n",
      "Lewis Fisher lewisfisher@gamil.com 987951535 55.80\n"
     ]
    }
   ],
   "source": [
    "# select customers details from orders with total cost greater than 40\n",
    "query = '''\n",
    "select concat(Customers.firstName, ' ', Customers.lastName) as 'Full Name', Customers.email, Customers.phone, totalCost from Orders\n",
    "inner join Customers on Orders.customerId = Customers.customerId where totalCost > 40;\n",
    "'''\n",
    "mysql_results(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get Maximum Quantity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5\n"
     ]
    }
   ],
   "source": [
    "query = 'GetMaxQuantity'\n",
    "mysql_results(query, 'procedure')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Manage Booking"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table 3 is already booked - booking cancelled\n"
     ]
    }
   ],
   "source": [
    "args = [\"2024-05-05\", \"12:30:00\", 3, 3]\n",
    "query = 'ManageBooking'\n",
    "mysql_results(query, 'procedure', args)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Booking confirmed\n"
     ]
    }
   ],
   "source": [
    "args = [\"2024-05-08\", \"13:30:00\", 2, 5]\n",
    "mysql_results(query, 'procedure', args)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add Booking"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Booking confirmed\n"
     ]
    }
   ],
   "source": [
    "args = [\"2024-05-09\", \"13:30:00\", 4, 4]\n",
    "mysql_results(query, type='procedure', args=args)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Update Booking"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Updated Booking\n"
     ]
    }
   ],
   "source": [
    "args = [1, \"2024-05-05\", \"12:30:00\", 4]\n",
    "query = \"UpdateBooking\"\n",
    "mysql_results(query, 'procedure', args)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table 4 is already booked\n"
     ]
    }
   ],
   "source": [
    "args = [2, \"2024-05-05\", \"12:30:00\", 4]\n",
    "mysql_results(query, 'procedure', args)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cancel Booking"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Booking 11 cancelled\n"
     ]
    }
   ],
   "source": [
    "args = [11]\n",
    "query = 'CancelBooking'\n",
    "mysql_results(query, 'procedure', args)"
   ]
  }
 ],
 "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.11.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}