Python MySQL Database Connection using MySQL Connector.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "bcf25a69",
   "metadata": {},
   "source": [
    "# Python MySQL Database Connection using MySQL Connector\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc431379",
   "metadata": {},
   "source": [
    "## Learning Agenda of this Notebook\n",
    "- How to connect MySQL database in Python\n",
    " >- Arguments required to connect\n",
    "- Create MySQL table from Python\n",
    "- Python MySQL CRUD Operations"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca614337",
   "metadata": {},
   "source": [
    "## In Python, We can use the following modules to communicate with MySQL.\n",
    "\n",
    "- MySQL Connector Python\n",
    "- PyMySQL\n",
    "- MySQLDB\n",
    "- MySqlClient\n",
    "- OurSQL\n",
    "\n",
    "\n",
    "In this lecture, our focuse on the MySQL Connector Python module. All examples are created using MySQL Connector Python.\n",
    "\n",
    "#### Advantages and benefits of MySQL Connector Python: –\n",
    "\n",
    "- MySQL Connector Python is written in pure Python, and it is self-sufficient to execute database queries through Python.\n",
    "- It is an official Oracle-supported driver to work with MySQL and Python.\n",
    "- It is Python 3 compatible, actively maintained."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05521f5b",
   "metadata": {},
   "source": [
    "## How to Connect to MySQL Database in Python\n",
    "\n",
    "- Step 1: Install MySQL connector module\n",
    " >- pip install mysql-connector-python\n",
    "\n",
    "- Step 2: Import MySQL connector module\n",
    " >- Import using a `import mysql.connector` statement so you can use this module’s methods to communicate with the MySQL database.\n",
    "\n",
    "- Step 3: Use the connect() method\n",
    " >- Use the `connect()` method of the MySQL Connector class with the required arguments to connect MySQL. It would return a `MySQLConnection` object if the connection established successfully.\n",
    " \n",
    "- Step 4: Use the cursor() method\n",
    " >- Use the `cursor()` method of a MySQLConnection object to create a cursor object to perform various SQL operations.\n",
    "\n",
    "- Step 5: Use the execute() method\n",
    " >- The `execute()` methods run the SQL query and return the result.\n",
    "\n",
    "- Step 6: Extract result using fetchall()\n",
    " >- Use `cursor.fetchall()` or `fetchone()` or `fetchmany()` to read query result.\n",
    " \n",
    "- Step 7: Close cursor and connection objects\n",
    " >- use `cursor.clsoe()` and `connection.clsoe()` method to close open connections after your work completes."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b0153a3",
   "metadata": {},
   "source": [
    "### Arguments required to connect\n",
    "You need to know the following detail of the MySQL server to perform the connection from Python.\n",
    "\n",
    "- Username : The username that you use to work with MySQL Server. The default username for the MySQL database is a `root`.\n",
    "- Password : Password is given by the user at the time of installing the MySQL server. If you are using root then you won’t need the password.\n",
    "- Host name\t: The server name or Ip address on which MySQL is running. if you are running on localhost, then you can use localhost or its IP 127.0.0.0\n",
    "- Database name\t: The name of the database to which you want to connect and perform the operations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f974efbc",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "a89960b7",
   "metadata": {},
   "source": [
    "## Example to connect to MySQL Database in Python\n",
    "![](images/pythonMysql)\n",
    "\n",
    "\n",
    "### Points to remember\n",
    "\n",
    "- Catch exceptions that may occur during this process by importing the Error class from the MySQL connector module using a `from mysql.connector import Error` statement. Error class is useful to debug when we failed to connect to MySQL. For example, ACCESS DENIED ERROR when the username or password is wrong.\n",
    "- The `connect()` method can throw a `Database error` exception if one of the required parameters is wrong. For example, if you provide a database name that is not present in MySQL.\n",
    "- The `is_connected()` is the method of the `MySQLConnection` class through which we can verify is our Python application connected to MySQL.\n",
    "- At last, we are closing the MySQL database connection using a `close()` method of MySQLConnection class."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a10b295d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0ec16ce1",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "313c22c9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connected to MySQL Server Version :  8.0.29\n",
      "Your are connected to database:  ('Electronics',)\n",
      "MySQL Connection is closed!\n"
     ]
    }
   ],
   "source": [
    "# first step import mysql.connector\n",
    "from mysql.connector import connect, Error\n",
    "try:\n",
    "# second step create connection object\n",
    "    connection = connect(user='root', host='localhost', database='Electronics', password='Pucit12345#')\n",
    "    if connection.is_connected():\n",
    "        db_Info = connection.get_server_info()\n",
    "        print(\"Connected to MySQL Server Version : \", db_Info)\n",
    "#         third step create cursor object\n",
    "        cursor = connection.cursor()\n",
    "#     fourth step call execute function of cursor object to execute mysql queries\n",
    "        cursor.execute(\"select database();\")\n",
    "        record = cursor.fetchone()\n",
    "#         fifth step call the fetch functions of cursor object to display/ show records \n",
    "        print(\"Your are connected to database: \", record)\n",
    "except Error as e:\n",
    "    print(\"Error while connection to MySQL is \", e)\n",
    "finally:\n",
    "    if connection.is_connected():\n",
    "#         close all the connections \n",
    "        cursor.close()\n",
    "        connection.close()\n",
    "        print(\"MySQL Connection is closed!\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f88dc922",
   "metadata": {},
   "source": [
    "## Create MySQL table from Python\n",
    "- Now we know how to connect to a MySQL server from Python.\n",
    "- Let’s create table `Laptop` under the `Electronics` database.\n",
    "<img src=\"images/Screenshot from 2022-07-04 18-36-19.png\" height=600px width=600px> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "70bf56ff",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Laptop table created successfully!\n",
      "MySQL connection is closed!\n"
     ]
    }
   ],
   "source": [
    "# Fisrt step : import mysql connector\n",
    "from mysql.connector import connect, Error\n",
    "try:\n",
    "#     Second step : create connection to the database using connect function of mysql.connector\n",
    "    connection = connect(host='localhost', user='root', database='Electronics', password='Pucit12345#')\n",
    "    mysql_query = \"\"\"\n",
    "    create table Laptop(\n",
    "    Id int(11) NOT NULL,\n",
    "    Name varchar(250) NOT NULL,\n",
    "    Price float NOT NULL,\n",
    "    Purchase_date Date NOT NULL,\n",
    "    PRIMARY KEY(Id))\n",
    "    \"\"\"\n",
    "    if connection.is_connected():\n",
    "#         Third step: create cursor object using connection object\n",
    "        cursor = connection.cursor()\n",
    "#         Fourth step : execute all the mysql queries using execute/executemany functions of cursor object\n",
    "        result = cursor.execute(mysql_query)\n",
    "        print(\"Laptop table created successfully!\")\n",
    "except Error as e:\n",
    "    print(\"Error while creating table is : \", e)\n",
    "finally:\n",
    "    if connection.is_connected():\n",
    "#         Fifth step : close cursor and connection objects\n",
    "        cursor.close()\n",
    "        connection.close()\n",
    "        print(\"MySQL connection is closed!\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5090b59c",
   "metadata": {},
   "source": [
    "## Python MySQL CRUD Operation\n",
    "<img src=\"images/1_2eBdh0vLZjUyCDF6x1EqvQ.png\" height= 600px width= 600px>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c794e87f",
   "metadata": {},
   "source": [
    "## Insert rows into MySQL table from Python: \n",
    "- We can nsert a single and multiple rows into the MySQL table. \n",
    "- Also, we learn how to use Python variables in the parameterized query to insert dynamic data into a table.\n",
    "<img src=\"images/python_insert_into_mysql_table.webp\">\n",
    "\n",
    "- I have created a table ‘Laptop’ in the MySQL server to insert records in it. See its column structure in the image.\n",
    "<img src=\"images/empty_mysql_laptop_table.png\" >\n",
    "\n",
    "## Insert a Single Row into MySQL table from Python\n",
    "There are following steps to Insert Into MySQL table from Python\n",
    "\n",
    "- Connect to MySQL from Python\n",
    " >- Connect to MySQL database from Python using MySQL Connector module\n",
    "\n",
    "- Define a SQL Insert query\n",
    " >- Next, prepare a SQL INSERT query to insert a row into a table. in the insert query, we mention column names and their values to insert in a table. For example, `INSERT INTO mysql_table (column1, column2, …) VALUES (value1, value2, …);`\n",
    "\n",
    "- Get Cursor Object from Connection\n",
    " >- Next, use a `connection.cursor()` method to create a cursor object. This method creates a new `MySQLCursor` object.\n",
    "\n",
    "- Execute the insert query using `execute()` method\n",
    " >- Execute the insert query using the `cursor.execute()` method. This method executes the operation stored in the Insert query.\n",
    "\n",
    "- Commit your changes\n",
    " >- After the successful execution of a query make changes persistent into a database using the `commit()` of a connection class.\n",
    "\n",
    "- Get the number of rows affected\n",
    " >- After a successful insert operation, use a `cursor.rowcount` method to get the number of rows affected. The count depends on how many rows you are Inserting.\n",
    "\n",
    "- Verify result using the SQL SELECT query\n",
    " >- Execute a `MySQL select query from Python` to see the new changes.\n",
    "\n",
    "- Close the cursor object and database connection object\n",
    " >- use `cursor.clsoe()` and `connection.clsoe()` method to close open connections after your work completes."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a4b1422",
   "metadata": {},
   "source": [
    "### Let’ s see the program now"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "844535c7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 Record inserted successfully into Laptop table\n",
      "MySQL connection is closed\n"
     ]
    }
   ],
   "source": [
    "from mysql.connector import connect, Error\n",
    "\n",
    "try:\n",
    "    connection = connect(host='localhost',\n",
    "                                         database='Electronics',\n",
    "                                         user='root',\n",
    "                                         password='Pucit12345#')\n",
    "\n",
    "    mySql_insert_query = \"\"\"\n",
    "                        INSERT INTO Laptop (Id, Name, Price, Purchase_date) \n",
    "                           VALUES(1, 'Lenovo ThinkPad P71', 6459, '2019-08-14') \n",
    "                           \"\"\"\n",
    "\n",
    "    cursor = connection.cursor()\n",
    "    cursor.execute(mySql_insert_query)\n",
    "    connection.commit()\n",
    "    print(cursor.rowcount, \"Record inserted successfully into Laptop table\")\n",
    "    cursor.close()\n",
    "\n",
    "except Error as error:\n",
    "    print(\"Failed to insert record into Laptop table {}\".format(error))\n",
    "\n",
    "finally:\n",
    "    if connection.is_connected():\n",
    "        connection.close()\n",
    "        print(\"MySQL connection is closed\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f5e725c",
   "metadata": {},
   "source": [
    "![](images/mysql_table_after_inserting_first_row_from_python.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c474293",
   "metadata": {},
   "source": [
    "## Use Python Variables in a MySQL Insert Query\n",
    "- Sometimes you need to insert a Python variable value into a table’s column. For example, in the user signup form user enter his/her details. You can take those values in Python variables and insert them into a table.\n",
    " >- We can insert Python variables into the table using the prepared statement and parameterized query.\n",
    " >- Using a parameterized query, we can pass Python variables as a query parameter in which placeholders (%s) used for parameters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b074237b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Record inserted successfully into Laptop Table!\n",
      "MySQL connection is closed successfully!\n",
      "Record inserted successfully into Laptop Table!\n",
      "MySQL connection is closed successfully!\n"
     ]
    }
   ],
   "source": [
    "from mysql.connector import *\n",
    "def insert_values_into_table(Id, Name, Price, Purchase_date):\n",
    "    try:\n",
    "        connection = connect(host='localhost', user='root', password='Pucit12345#', database='Electronics')\n",
    "#     write mysql query\n",
    "        mysql_insert_query = \"\"\"\n",
    "        INSERT INTO Laptop(Id, Name, Price , Purchase_date) values(%s, %s, %s, %s)\n",
    "        \"\"\"\n",
    "        mysql_query = (Id, Name, Price, Purchase_date)\n",
    "        \n",
    "        if connection.is_connected():\n",
    "            cursor = connection.cursor()\n",
    "            cursor.execute(mysql_insert_query, mysql_query)\n",
    "            connection.commit()\n",
    "            print(\"Record inserted successfully into Laptop Table!\")\n",
    "    except Error as e:\n",
    "        print(\"Error while inserting record into table!\")\n",
    "    \n",
    "    finally:\n",
    "        if connection.is_connected():\n",
    "            cursor.close()\n",
    "            connection.close()\n",
    "            print(\"MySQL connection is closed successfully!\")\n",
    "def main():\n",
    "    insert_values_into_table(2, 'Area 51M', 6999, '2019-04-14')\n",
    "    insert_values_into_table(3, 'MacBook Pro', 2499, '2019-06-20')\n",
    "if __name__ == '__main__':\n",
    "    main()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e38b779f",
   "metadata": {},
   "source": [
    "![](images/mysql_laptop_table_after_inserting_python_varibles.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d68f1b69",
   "metadata": {},
   "source": [
    "## Insert multiple rows into MySQL table using the cursor’s executemany()\n",
    "- In the previous example, we have used execute() method of cursor object to insert a single record.\n",
    "- What if you want to insert multiple rows into a table in a single insert query from the Python application. Use the cursor’s `executemany()` function to insert multiple records into a table.\n",
    "\n",
    "#### Syntax of the executemany() method.\n",
    "```\n",
    "cursor.executemany(operation, seq_of_params)\n",
    "```\n",
    "\n",
    "- Note:\n",
    " >- This method executes Insert `operation` against all parameter sequences in the sequence `seq_of_params` argument.\n",
    " >- You need to include lists of tuples in the `seq_of_params` argument along with the insert query.\n",
    " >- Each tuple inside the list contains a single row that you want to insert. So you can add as many rows in the list and pass a list to a `cursor.executemany()` function along with the insert query.\n",
    " >- Each tuple is enclosed within parentheses and separated by commas."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78fa5ea4",
   "metadata": {},
   "source": [
    "### Example to INSERT multiple rows into a MySQL table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "dcdccdf7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3 Record inserted successfully into Laptop table\n",
      "MySQL connection is closed\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "\n",
    "try:\n",
    "    connection = mysql.connector.connect(host='localhost',\n",
    "                                         database='Electronics',\n",
    "                                         user='root',\n",
    "                                         password='Pucit12345#')\n",
    "\n",
    "    mySql_insert_query = \"\"\"INSERT INTO Laptop (Id, Name, Price, Purchase_date) \n",
    "                           VALUES (%s, %s, %s, %s) \"\"\"\n",
    "\n",
    "    records_to_insert = [(4, 'HP Pavilion Power', 1999, '2019-01-11'),\n",
    "                         (5, 'MSI WS75 9TL-496', 5799, '2019-02-27'),\n",
    "                         (6, 'Microsoft Surface', 2330, '2019-07-23')]\n",
    "\n",
    "    cursor = connection.cursor()\n",
    "    cursor.executemany(mySql_insert_query, records_to_insert)\n",
    "    connection.commit()\n",
    "    print(cursor.rowcount, \"Record inserted successfully into Laptop table\")\n",
    "\n",
    "except mysql.connector.Error as error:\n",
    "    print(\"Failed to insert record into MySQL table {}\".format(error))\n",
    "\n",
    "finally:\n",
    "    if connection.is_connected():\n",
    "        cursor.close()\n",
    "        connection.close()\n",
    "        print(\"MySQL connection is closed\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24276f4e",
   "metadata": {},
   "source": [
    "![](images/mysql_laptop_table_after_inserting_multiple_rows.png)\n",
    "- Using `cursor.executemany(sql_insert_query, records_to_insert)` we are inserting multiple rows (from a List) into the table.\n",
    "- Using the `cursor.rowcount` we can find the number of records inserted."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "15c22ee5",
   "metadata": {},
   "source": [
    "### Insert timestamp and DateTime into a MySQL table using Python\n",
    "We have a date column in a MySQL table. Let’s see how to prepare an insert query to add DateTime into a table from Python."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0cb16fa",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "31b83072",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Date Record inserted successfully\n",
      "MySQL connection is closed\n"
     ]
    }
   ],
   "source": [
    "from datetime import datetime\n",
    "\n",
    "import mysql.connector\n",
    "\n",
    "try:\n",
    "    connection = mysql.connector.connect(host='localhost',\n",
    "                                         database='Electronics',\n",
    "                                         user='root',\n",
    "                                         password='Pucit12345#')\n",
    "\n",
    "    mySql_insert_query = \"\"\"INSERT INTO Laptop (Id, Name, Price, Purchase_date) \n",
    "                            VALUES (%s, %s, %s, %s) \"\"\"\n",
    "\n",
    "    cursor = connection.cursor()\n",
    "    current_Date = datetime.now()\n",
    "    # convert date in the format you want\n",
    "    formatted_date = current_Date.strftime('%Y-%m-%d %H:%M:%S')\n",
    "    insert_tuple = (7, 'Acer Predator Triton', 2435, current_Date)\n",
    "\n",
    "    result = cursor.execute(mySql_insert_query, insert_tuple)\n",
    "    connection.commit()\n",
    "    print(\"Date Record inserted successfully\")\n",
    "\n",
    "except mysql.connector.Error as error:\n",
    "    connection.rollback()\n",
    "    print(\"Failed to insert into MySQL table {}\".format(error))\n",
    "\n",
    "finally:\n",
    "    if connection.is_connected():\n",
    "        cursor.close()\n",
    "        connection.close()\n",
    "        print(\"MySQL connection is closed\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b5859a2",
   "metadata": {},
   "source": [
    "## Python Select from MySQL Table\n",
    "We will learn the following MySQL SELECT operations from Python using a ‘MySQL Connector Python’ module.\n",
    "- Execute the SELECT query and process the result set returned by the query in Python.\n",
    "- Use Python `variables in a where clause` of a SELECT query to pass dynamic values.\n",
    "- Use `fetchall()`, `fetchmany()`, and `fetchone()` methods of a cursor class to fetch all or limited rows from a table.\n",
    "\n",
    "For this task, we are using a `Laptop` table present in my MySQL server.\n",
    "![](images/mysql_laptop_table_with_data.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "08d133de",
   "metadata": {},
   "source": [
    "### Steps to fetch rows from a MySQL database table\n",
    "- Connect to MySQL from Python\n",
    " >- To connect to MySQL database from Python using MySQL Connector module\n",
    "\n",
    "- Define a SQL SELECT Query\n",
    " >- Next, prepare a SQL SELECT query to fetch rows from a table. You can select all or limited rows based on your requirement. If the where condition is used, then it decides the number of rows to fetch.\n",
    "For example, `SELECT col1, col2,…colnN FROM MySQL_table WHERE id = 10;`. This will return row number 10.\n",
    "\n",
    "- Get Cursor Object from Connection\n",
    " >- Next, use a `connection.cursor()` method to create a cursor object. This method creates a new `MySQLCursor` object.\n",
    "\n",
    "- Execute the SELECT query using execute() method\n",
    " >- Execute the select query using the `cursor.execute()` method.\n",
    "\n",
    "- Extract all rows from a result\n",
    " >- After successfully executing a Select operation, Use the `fetchall()` method of a cursor object to get all rows from a query result. it returns a list of rows.\n",
    "\n",
    "- Iterate each row\n",
    " >- Iterate a row list using a for loop and access each row individually (Access each row’s column data using a column name or index number.)\n",
    "\n",
    "- Close the cursor object and database connection object\n",
    " >- use `cursor.clsoe()` and `connection.clsoe()` method to close open connections after your work completes.\n",
    "![](images/python_select_from_mysql_table.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea8fc84c",
   "metadata": {},
   "source": [
    "### Example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "4c2ff9d5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of rows in table are :  7\n",
      "Printing each row of Table!\n",
      "(1, 'Lenovo ThinkPad P71', 6459.0, datetime.date(2019, 8, 14))\n",
      "(2, 'Area 51M', 6999.0, datetime.date(2019, 4, 14))\n",
      "(3, 'MacBook Pro', 2499.0, datetime.date(2019, 6, 20))\n",
      "(4, 'HP Pavilion Power', 1999.0, datetime.date(2019, 1, 11))\n",
      "(5, 'MSI WS75 9TL-496', 5799.0, datetime.date(2019, 2, 27))\n",
      "(6, 'Microsoft Surface', 2330.0, datetime.date(2019, 7, 23))\n",
      "(7, 'Acer Predator Triton', 2435.0, datetime.date(2022, 7, 16))\n",
      "MySQL Connection is closed!\n"
     ]
    }
   ],
   "source": [
    "# First Step\n",
    "from mysql.connector import connect, Error\n",
    "try:\n",
    "#     Second  step : create connection\n",
    "    connection = connect(host='localhost', user='root', password='Pucit12345#', database='Electronics')\n",
    "#     Third Step : Define query\n",
    "    select_query = \"SELECT * FROM Laptop\"\n",
    "    if connection.is_connected():\n",
    "        cursor = connection.cursor()\n",
    "#         execute select query\n",
    "        cursor.execute(select_query)\n",
    "#         get all the records of table\n",
    "        records = cursor.fetchall()\n",
    "        print(\"Total number of rows in table are : \", cursor.rowcount)\n",
    "        print(\"Printing each row of Table!\")\n",
    "        for row in records:\n",
    "            print(row)\n",
    "except Error as e:\n",
    "    print(\"Error while select data from table: \", e)\n",
    "finally:\n",
    "    if connection.is_connected():\n",
    "        cursor.close()\n",
    "        connection.close()\n",
    "        print(\"MySQL Connection is closed!\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "865739b9",
   "metadata": {},
   "source": [
    "- Use the following methods to fetch data returned by a cursor.execute()\n",
    " >- cursor.fetchall() to fetch all rows\n",
    " >- cursor.fetchone() to fetch a single row\n",
    " >- cursor.fetchmany(SIZE) to fetch limited rows"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e465ab3",
   "metadata": {},
   "source": [
    "### Use Python variables as parameters in MySQL Select Query\n",
    "- We often need to pass variables to `SQL select query` in where clause to check some conditions. Let’s say the application wants to fetch laptop price by giving any laptop id at runtime. To handle such a requirement, we need to use a parameterized query.\n",
    "\n",
    "- A `parameterized query` is a query in which placeholders (%s) are used for parameters and the parameter values supplied at execution time.\n",
    "```\n",
    "cursor.execute(\"SELECT Price FROM Laptop WHERE id = s%\", variable)\n",
    "``` "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "cd320a84",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter ID to get laptop details : 7\n",
      "Record of select laptop is : \n",
      "ID :  7\n",
      "Name :  Acer Predator Triton\n",
      "Price :  2435.0\n",
      "Purchased Date :  2022-07-16\n",
      "MySQL connection is closed successfully!\n"
     ]
    }
   ],
   "source": [
    "# First step\n",
    "from mysql.connector import connect, Error\n",
    "def get_Laptop_details(ID):\n",
    "    try:\n",
    "#         Second Step\n",
    "        connection = connect(host='localhost', user='root', password='Pucit12345#', database='Electronics')\n",
    "#         Third step\n",
    "        select_query = \"SELECT * FROM Laptop where Id = %s\"\n",
    "        value = (ID,)\n",
    "        if connection.is_connected():\n",
    "#             Fourth step\n",
    "            cursor = connection.cursor()\n",
    "#             fifth step\n",
    "            cursor.execute(select_query, value)\n",
    "#             sixth step\n",
    "            record = cursor.fetchall()\n",
    "            print(\"Record of select laptop is : \")\n",
    "#             print(\"Price : \",record[0][0])\n",
    "            for row in record:\n",
    "                print(\"ID : \", row[0])\n",
    "                print(\"Name : \", row[1])\n",
    "                print(\"Price : \", row[2])\n",
    "                print(\"Purchased Date : \", row[3])\n",
    "                \n",
    "    except Error as e:\n",
    "        print(\"Error while selecting the record is : \", e)\n",
    "    finally:\n",
    "        if connection.is_connected():\n",
    "            cursor.close()\n",
    "            connection.close()\n",
    "            print(\"MySQL connection is closed successfully!\")\n",
    "def main():\n",
    "    ID = int(input(\"Enter ID to get laptop details : \"))\n",
    "    get_Laptop_details(ID)\n",
    "if __name__ ==\"__main__\":\n",
    "    main()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d15576f",
   "metadata": {},
   "source": [
    "### Select limited rows from MySQL table using fetchmany and fetchone\n",
    "- In some situations, fetching all the rows from a table is a time-consuming task if a table contains thousands of rows.\n",
    "- If we fetch all rows, we need more space and processing time. So it is essentials to use the `fetchmany()` method of cursor class to `fetch fewer rows`.\n",
    "\n",
    "#### Syntax of the cursor’s fetchmany()\n",
    "```\n",
    "rows = cursor.fetchmany(size=row_size)\n",
    "```\n",
    " >- Cursor’s `fetchmany()` methods return the number of rows specified by size argument, defaults value of size argument is one. For example, if the specified size is 5, then it returns five rows.\n",
    " >- If a table contains a row lesser than the specified size, then fewer rows are returned.\n",
    " \n",
    "#### Syntax of fetchone()\n",
    "```\n",
    "row = cursor.fetchone()\n",
    "```\n",
    " >- This method fetches the next set of rows of a query result and returns a list of tuples. If no more rows are available, it returns an empty list.\n",
    " >- This method returns a `single record` or None if no more rows are available.\n",
    " >- The `fetchone()` method is internally used by a `fetchall() and fetchmany()` to fetch rows.\n",
    " \n",
    "- Note: If you are getting MySQL Unread result error set buffered=True in connection like connection.cursor(buffered=True)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9e67485",
   "metadata": {},
   "source": [
    "### Python Fetch MySQL row using the column names\n",
    "- You can also retrieve result using columns names instead of id. For example, you would like to do something like this.\n",
    "```\n",
    "records = cursor.fetchall()\n",
    "for row in records:\n",
    "    val1 = row[\"columnName1\"], )\n",
    "    val2 = row[\"columnName2\"])\n",
    "    val3 = row[\"columnName3\"])\n",
    "```\n",
    "\n",
    "- If you try to fetch data using column name directly, you will receive a TypeError: `tuple indices must be integers or slices, not str.`\n",
    "- To select records from my MySQL table using a column name, we only need to change the cursor creation. Replace the standard cursor creation with the following code, and you are ready to fetch records from my MySQL table using a column name.\n",
    "```\n",
    "cursor = connection.cursor(dictionary=True)\n",
    "```\n",
    "\n",
    "- Note :\n",
    " >- Why set dictionary=True? because MySQLCursorDict creates a cursor that returns rows as dictionaries so we can access using column name (here column name is the key of the dictionary)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "a7f15f88",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Fetching each row using column name\n",
      "Name :  Lenovo ThinkPad P71  &  Price :  6459.0\n",
      "Name :  Area 51M  &  Price :  6999.0\n",
      "Name :  MacBook Pro  &  Price :  2499.0\n",
      "Name :  HP Pavilion Power  &  Price :  1999.0\n",
      "Name :  MSI WS75 9TL-496  &  Price :  5799.0\n",
      "Name :  Microsoft Surface  &  Price :  2330.0\n",
      "Name :  Acer Predator Triton  &  Price :  2435.0\n",
      "MySQL connection is closed\n"
     ]
    }
   ],
   "source": [
    "# First step\n",
    "from mysql.connector import connect, Error\n",
    "try:\n",
    "#     Second step\n",
    "    connection = mysql.connector.connect(host='localhost',\n",
    "                                         database='Electronics',\n",
    "                                         user='root',\n",
    "                                         password='Pucit12345#')\n",
    "#     Third Step\n",
    "    sql_select_Query = \"select * from Laptop\"\n",
    "    # MySQLCursorDict creates a cursor that returns rows as dictionaries\n",
    "    cursor = connection.cursor(dictionary=True, buffered= True)\n",
    "#     Fourth step\n",
    "    cursor.execute(sql_select_Query)\n",
    "    records = cursor.fetchall()\n",
    "    \n",
    "    print(\"Fetching each row using column name\")\n",
    "    for row in records:\n",
    "#         Id = row['Id']\n",
    "        name = row[\"Name\"]\n",
    "        price = row[\"Price\"]\n",
    "#         purchase_date = row[\"Purchase_date\"]\n",
    "        print(\"Name : \", name,\" & \", \"Price : \",price )\n",
    "\n",
    "except Error as e:\n",
    "    print(\"Error reading data from MySQL table\", e)\n",
    "finally:\n",
    "    if connection.is_connected():\n",
    "        connection.close()\n",
    "        cursor.close()\n",
    "        print(\"MySQL connection is closed\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97cdc42c",
   "metadata": {},
   "source": [
    "### Select MySQL column value into a Python Variable\n",
    "- Let’s see how to execute the following SELECT SQL Query and store the table’s column value into a Python variable for further processing.\n",
    "```\n",
    "cursor.execute(\"SELECT Price FROM Laptop WHERE id = 21\")\n",
    "print (cursor.fetchone() )\n",
    "```       \n",
    "- You can get the output like          \n",
    "```\n",
    "{u'Price': u'7000'}\n",
    "```\n",
    "- Let’s see how to extract just the column value (7000) and save it in a variable to do some calculations so you can give a 10% discount on it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "000c590d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(6999.0,)\n",
      "Laptop price is :  6999.0\n",
      "MySQL connection is closed\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "\n",
    "try:\n",
    "    connection = mysql.connector.connect(host='localhost',\n",
    "                                         database='Electronics',\n",
    "                                         user='root',\n",
    "                                         password='Pucit12345#')\n",
    "\n",
    "    sql_Query = \"select price from Laptop where Id =%s\"\n",
    "    Id = (2,)\n",
    "    cursor = connection.cursor()\n",
    "    cursor.execute(sql_Query, Id)\n",
    "    record = cursor.fetchone()\n",
    "    print(record)\n",
    "    # selecting column value into variable\n",
    "    price = float(record[0])\n",
    "    print(\"Laptop price is : \", price)\n",
    "\n",
    "except mysql.connector.Error as error:\n",
    "    print(\"Failed to get record from database: {}\".format(error))\n",
    "\n",
    "finally:\n",
    "    if connection.is_connected():\n",
    "        cursor.close()\n",
    "        connection.close()\n",
    "        print(\"MySQL connection is closed\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9a6df4b",
   "metadata": {},
   "source": [
    "- Let’s understand this example:\n",
    "\n",
    " >- You can see in the above example after fetching a row using a cursor.fetchone() we converted column value to appropriate type using `value = float(record[0]).`\n",
    " >- We retrieved the 0th index value from the result set"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d0326aef",
   "metadata": {},
   "source": [
    "## Python Update MySQL Table\n",
    "We will learn the following MySQL UPDATE operations from Python using a ‘MySQL Connector’ module.\n",
    "\n",
    "- Update single and multiple rows, single and multiple columns\n",
    "- Use a Python variable in a parameterized query to update table rows.\n",
    "- Also, Update a column with date-time and timestamp values\n",
    "- The role of `commit` and `rollback` in the update operation.\n",
    "\n",
    "I am using a Laptop table present in my MySQL server.\n",
    "![](images/mysql_laptop_table_with_data.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "909b7260",
   "metadata": {},
   "source": [
    "### Example to Update a row of MySQL Table\n",
    "\n",
    "How to Update MySQL Table in Python\n",
    "\n",
    "- Connect to MySQL from Python\n",
    " >- Refer to Python MySQL database connection to connect to MySQL database from Python using MySQL Connector module\n",
    "\n",
    "- Prepare a SQL Update Query\n",
    " >- Prepare an update statement query with data to update. FOr example, `UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;`\n",
    "\n",
    "- Execute the UPDATE query, using cursor.execute()\n",
    " >- Execute the UPDATE query using `cursor.execute()` method. This method execute the operation stored in the UPDATE query.\n",
    "\n",
    "- Commit your changes\n",
    " >- Make modification persistent into a database using the `commit()` of a connection class.\n",
    "\n",
    "- Extract the number of rows affected\n",
    " >- After a successful update operation, use a `cursor.rowcount` method to get the number of rows affected. The count depends on how many rows you are updating.\n",
    "\n",
    "- Verify result using the SQL SELECT query\n",
    " >- Execute a MySQL select query from Python to see the new changes.\n",
    "\n",
    "- Close the cursor object and database connection object\n",
    " >- use `cursor.clsoe()` and `connection.clsoe()` method to close open connections after your work completes.\n",
    " \n",
    "![](images/python_update_mysql_table.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cecfc805",
   "metadata": {},
   "source": [
    "#### Example:\n",
    "In this program, we are updating a Laptop table by changing the price column of the first row."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "5d84da9d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Before updating a record \n",
      "(1, 'Lenovo ThinkPad P71', 6459.0, datetime.date(2019, 8, 14))\n",
      "Row affected are :  1\n",
      "Record Updated successfully \n",
      "After updating record \n",
      "(1, 'Lenovo ThinkPad P71', 7000.0, datetime.date(2019, 8, 14))\n",
      "MySQL connection is closed\n"
     ]
    }
   ],
   "source": [
    "from mysql.connector import connect, Error\n",
    "\n",
    "try:\n",
    "    connection = connect(host='localhost',\n",
    "                                         database='Electronics',\n",
    "                                         user='root',\n",
    "                                         password='Pucit12345#')\n",
    "    cursor = connection.cursor()\n",
    "\n",
    "    print(\"Before updating a record \")\n",
    "    sql_select_query = \"\"\"select * from Laptop where Id = 1\"\"\"\n",
    "    cursor.execute(sql_select_query)\n",
    "    record = cursor.fetchone()\n",
    "    print(record)\n",
    "\n",
    "    # Update single record now\n",
    "    sql_update_query = \"\"\"Update Laptop set Price = 7000 where Id = 1\"\"\"\n",
    "    cursor.execute(sql_update_query)\n",
    "    connection.commit()\n",
    "    print(\"Row affected are : \", cursor.rowcount)\n",
    "    print(\"Record Updated successfully \")\n",
    "    print(\"After updating record \")\n",
    "    cursor.execute(sql_select_query)\n",
    "    record = cursor.fetchone()\n",
    "    print(record)\n",
    "\n",
    "except Error as error:\n",
    "    print(\"Failed to update table record: {}\".format(error))\n",
    "finally:\n",
    "    if connection.is_connected():\n",
    "        connection.close()\n",
    "        print(\"MySQL connection is closed\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5710a7e6",
   "metadata": {},
   "source": [
    "#### MySQL Laptop table after updating record\n",
    "![](images/mysql_laptop_table_after_updating_record.png)\n",
    "\n",
    "#### Note:\n",
    "- Don’t forget to close the cursor and database connection objects and Catch any SQL exceptions that may occur during this process\n",
    "- We also used `commit()` and `rollback()` method. commit() to make changes persistent in the database and rollback() revert the changes if any database error occurred."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c990bc14",
   "metadata": {},
   "source": [
    "### Use a Python variable in MySQL Update query\n",
    "- Sometimes we need input from the user, such as when users update their password or any other details through User Interface. Or when you want to update details dynamically by passing Python variables into a query. Such as setting column value using the variable.\n",
    "\n",
    "- It is always best practice to use parameterized query and prepared statement, i.e., placeholders ( %s ) inside any SQL statements that contain input from users. This helps us prevent SQL injection and other SQL issues.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "fc53e0e7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Record before updating!\n",
      "(1, 'Lenovo ThinkPad P71', 7000.0, datetime.date(2019, 8, 14))\n",
      "(2, 'Area 51M', 6999.0, datetime.date(2019, 4, 14))\n",
      "(3, 'MacBook Pro', 2499.0, datetime.date(2019, 6, 20))\n",
      "(4, 'HP Pavilion Power', 1999.0, datetime.date(2019, 1, 11))\n",
      "(5, 'MSI WS75 9TL-496', 5799.0, datetime.date(2019, 2, 27))\n",
      "(6, 'Microsoft Surface', 7500.0, datetime.date(2019, 7, 23))\n",
      "(7, 'Acer Predator Triton', 2435.0, datetime.date(2022, 7, 16))\n",
      "Records updated successfully!\n",
      "\n",
      "\n",
      "Records after updation!\n",
      "(1, 'Lenovo ThinkPad P71', 7000.0, datetime.date(2019, 8, 14))\n",
      "(2, 'Area 51M', 6999.0, datetime.date(2019, 4, 14))\n",
      "(3, 'MacBook Pro', 2499.0, datetime.date(2019, 6, 20))\n",
      "(4, 'HP Pavilion Power', 1999.0, datetime.date(2019, 1, 11))\n",
      "(5, 'MSI WS75 9TL-496', 6600.0, datetime.date(2019, 2, 27))\n",
      "(6, 'Microsoft Surface', 7500.0, datetime.date(2019, 7, 23))\n",
      "(7, 'Acer Predator Triton', 2435.0, datetime.date(2022, 7, 16))\n"
     ]
    }
   ],
   "source": [
    "from mysql.connector import connect, Error\n",
    "def update_Laptop_table(PRICE, ID):\n",
    "    try:\n",
    "        connection = connect(host='localhost', user='root', password='Pucit12345#', database='Electronics')        \n",
    "        cursor = connection.cursor()\n",
    "        print(\"Record before updating!\")\n",
    "        select_query = \"SELECT * FROM Laptop\"\n",
    "        cursor.execute(select_query)\n",
    "        records = cursor.fetchall()\n",
    "        for row in records:\n",
    "            print(row)\n",
    "            \n",
    "#         update data of the table\n",
    "        update_query = \"UPDATE Laptop set Price = %s WHERE Id = %s\"\n",
    "        input_query = (PRICE, ID)\n",
    "        cursor.execute(update_query, input_query)\n",
    "        connection.commit()\n",
    "        print(\"Records updated successfully!\")\n",
    "        \n",
    "        print(\"\\n\\nRecords after updation!\")\n",
    "        cursor.execute(select_query)\n",
    "        records = cursor.fetchall()\n",
    "        for row in records:\n",
    "            print(row)\n",
    "    except Error as e:\n",
    "        print(\"Error while updating records of the table!\", e)\n",
    "    finally:\n",
    "        if connection:\n",
    "            cursor.close()\n",
    "            connection.close()\n",
    "        \n",
    "def main():\n",
    "#     update_Laptop_table(7500, 6)\n",
    "    update_Laptop_table(6600, 5)\n",
    "    \n",
    "main()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c524db79",
   "metadata": {},
   "source": [
    "#### Note:\n",
    "- We used the `prepared statement` to accept user input using a placeholder, i.e., we put two placeholders in the update query, one for the `Price` column and the other is for the `id` column.\n",
    "- Next, we added those two columns value in the tuple format in sequential order and passed SQL update query and input tuple to the `cursor.execute()` method. Remember tuple contains user data in the sequential order of placeholders.\n",
    "- In the end, we are committing our changes to the database using the `connection.commit()`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd132db4",
   "metadata": {},
   "source": [
    "### Update Multiple Rows of MySQL Table using Python\n",
    "- It is possible to update multiple rows in a single SQL Query. You can also call it a bulk update. Use the `cursor.executemany()` method of cursor object to update multiple rows of a table.\n",
    "\n",
    "#### The syntax of the executemany() method\n",
    "```\n",
    "cursor.executemany(operation, seq_of_params)\n",
    "```\n",
    "- This method executes operation against all parameter sequences in the sequence seq_of_params argument."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "cbbf3890",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Record before updating!\n",
      "(1, 'Lenovo ThinkPad P71', 7000.0, datetime.date(2019, 8, 14))\n",
      "(2, 'Area 51M', 6999.0, datetime.date(2019, 4, 14))\n",
      "(3, 'MacBook Pro', 2499.0, datetime.date(2019, 6, 20))\n",
      "(4, 'HP Pavilion Power', 1999.0, datetime.date(2019, 1, 11))\n",
      "(5, 'MSI WS75 9TL-496', 6600.0, datetime.date(2019, 2, 27))\n",
      "(6, 'Microsoft Surface', 7500.0, datetime.date(2019, 7, 23))\n",
      "(7, 'Acer Predator Triton', 2435.0, datetime.date(2022, 7, 16))\n",
      "Records updated successfully!\n",
      "2 Records of a laptop table updated successfully\n",
      "\n",
      "\n",
      "Records after updation!\n",
      "(1, 'Lenovo ThinkPad P71', 7000.0, datetime.date(2019, 8, 14))\n",
      "(2, 'Area 51M', 6999.0, datetime.date(2019, 4, 14))\n",
      "(3, 'MacBook Pro', 2499.0, datetime.date(2019, 6, 20))\n",
      "(4, 'HP Pavilion Power', 2750.0, datetime.date(2019, 1, 11))\n",
      "(5, 'MSI WS75 9TL-496', 6600.0, datetime.date(2019, 2, 27))\n",
      "(6, 'Microsoft Surface', 7500.0, datetime.date(2019, 7, 23))\n",
      "(7, 'Acer Predator Triton', 3000.0, datetime.date(2022, 7, 16))\n"
     ]
    }
   ],
   "source": [
    "from mysql.connector import connect, Error\n",
    "def update_Laptop_table():\n",
    "    try:\n",
    "        connection = connect(host='localhost', user='root', password='Pucit12345#', database='Electronics')        \n",
    "        cursor = connection.cursor()\n",
    "        print(\"Record before updating!\")\n",
    "        select_query = \"SELECT * FROM Laptop\"\n",
    "        cursor.execute(select_query)\n",
    "        records = cursor.fetchall()\n",
    "        for row in records:\n",
    "            print(row)\n",
    "            \n",
    "#         update data of the table\n",
    "        update_query = \"UPDATE Laptop set Price = %s WHERE Id = %s\"\n",
    "        records_to_update = [(3000, 7), (2750, 4)]\n",
    "        cursor.executemany(update_query, records_to_update)\n",
    "        connection.commit()\n",
    "        print(\"Records updated successfully!\")\n",
    "        print(cursor.rowcount, \"Records of a laptop table updated successfully\")\n",
    "        \n",
    "        print(\"\\n\\nRecords after updation!\")\n",
    "        cursor.execute(select_query)\n",
    "        records = cursor.fetchall()\n",
    "        for row in records:\n",
    "            print(row)\n",
    "    except Error as e:\n",
    "        print(\"Error while updating records of the table!\", e)\n",
    "    finally:\n",
    "        if connection:\n",
    "            cursor.close()\n",
    "            connection.close()\n",
    "        \n",
    "def main():\n",
    "    update_Laptop_table()    \n",
    "main()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "251d1556",
   "metadata": {},
   "source": [
    "#### Note:\n",
    "- We defined a SQL update query with two placeholders `(“Price” and “Id” column)`. and prepared a list of records to be updated. This List contains a tuple for each row. Here we created two tuples, so we are updated two rows.\n",
    "- We used the `cursor.executemany()` method to update multiple rows of a database table.\n",
    "- Using the `cursor.rowcount` we can find how many rows are updated successfully."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "347b3687",
   "metadata": {},
   "source": [
    "### Python update multiple Columns of MySQL table\n",
    "- We can also update multiple columns of the MySQL table in a single query. Use parameterized query using a placeholder to update multiple columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "4ab8d499",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Record before updating!\n",
      "(1, 'Lenovo ThinkPad P71', 7000.0, datetime.date(2019, 8, 14))\n",
      "(2, 'Area 51M', 6999.0, datetime.date(2019, 4, 14))\n",
      "(3, 'MacBook Pro', 2499.0, datetime.date(2019, 6, 20))\n",
      "(4, 'HP Pavilion Power', 2750.0, datetime.date(2019, 1, 11))\n",
      "(5, 'MSI WS75 9TL-496', 6600.0, datetime.date(2019, 2, 27))\n",
      "(6, 'Microsoft Surface', 7500.0, datetime.date(2019, 7, 23))\n",
      "(7, 'Acer Predator Triton', 3000.0, datetime.date(2022, 7, 16))\n",
      "Records updated successfully!\n",
      "1 Records of a laptop table updated successfully\n",
      "\n",
      "\n",
      "Records after updation!\n",
      "(1, 'Lenovo ThinkPad P71', 7000.0, datetime.date(2019, 8, 14))\n",
      "(2, 'Area 51M', 6999.0, datetime.date(2019, 4, 14))\n",
      "(3, 'MacBook Pro', 2499.0, datetime.date(2019, 6, 20))\n",
      "(4, 'HP Pavilion', 2200.0, datetime.date(2019, 1, 11))\n",
      "(5, 'MSI WS75 9TL-496', 6600.0, datetime.date(2019, 2, 27))\n",
      "(6, 'Microsoft Surface', 7500.0, datetime.date(2019, 7, 23))\n",
      "(7, 'Acer Predator Triton', 3000.0, datetime.date(2022, 7, 16))\n"
     ]
    }
   ],
   "source": [
    "from mysql.connector import connect, Error\n",
    "def update_Laptop_table():\n",
    "    try:\n",
    "        connection = connect(host='localhost', user='root', password='Pucit12345#', database='Electronics')        \n",
    "        cursor = connection.cursor()\n",
    "        print(\"Record before updating!\")\n",
    "        select_query = \"SELECT * FROM Laptop\"\n",
    "        cursor.execute(select_query)\n",
    "        records = cursor.fetchall()\n",
    "        for row in records:\n",
    "            print(row)\n",
    "            \n",
    "#         update data of the table\n",
    "        sql_update_query = \"\"\"Update Laptop set Name = %s, Price = %s where Id = %s\"\"\"\n",
    "        name = \"HP Pavilion\"\n",
    "        price = 2200\n",
    "        ID = 4\n",
    "        Input = (name, price, ID)\n",
    "        cursor.execute(sql_update_query, Input)\n",
    "        connection.commit()\n",
    "        print(\"Records updated successfully!\")\n",
    "        print(cursor.rowcount, \"Records of a laptop table updated successfully\")\n",
    "        \n",
    "        print(\"\\n\\nRecords after updation!\")\n",
    "        cursor.execute(select_query)\n",
    "        records = cursor.fetchall()\n",
    "        for row in records:\n",
    "            print(row)\n",
    "    except Error as e:\n",
    "        print(\"Error while updating records of the table!\", e)\n",
    "    finally:\n",
    "        if connection:\n",
    "            cursor.close()\n",
    "            connection.close()\n",
    "        \n",
    "def main():\n",
    "    update_Laptop_table()    \n",
    "main()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a42a718d",
   "metadata": {},
   "source": [
    "### Update Datetime and timestamp column of a MySQL table from Python\n",
    "- Suppose you have a date column in a MySQL table and you want to update a `datetime.datetime()` object into this column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6a3cbeab",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "ae5edc13",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Record before updating!\n",
      "(1, 'Lenovo ThinkPad P71', 7000.0, datetime.date(2019, 8, 14))\n",
      "(2, 'Area 51M', 6999.0, datetime.date(2019, 4, 14))\n",
      "(3, 'MacBook Pro', 2499.0, datetime.date(2022, 7, 18))\n",
      "(4, 'HP Pavilion', 2200.0, datetime.date(2019, 1, 11))\n",
      "(5, 'MSI WS75 9TL-496', 6600.0, datetime.date(2019, 2, 27))\n",
      "(6, 'Microsoft Surface', 7500.0, datetime.date(2019, 7, 23))\n",
      "(7, 'Acer Predator Triton', 3000.0, datetime.date(2022, 7, 16))\n",
      "Records updated successfully!\n",
      "1 Records of a laptop table updated successfully\n",
      "\n",
      "\n",
      "Records after updation!\n",
      "(1, 'Lenovo ThinkPad P71', 7000.0, datetime.date(2019, 8, 14))\n",
      "(2, 'Area 51M', 6999.0, datetime.date(2019, 4, 14))\n",
      "(3, 'MacBook Pro', 2499.0, datetime.date(2022, 7, 18))\n",
      "(4, 'HP Pavilion', 2200.0, datetime.date(2019, 1, 11))\n",
      "(5, 'MSI WS75 9TL-496', 6600.0, datetime.date(2022, 7, 18))\n",
      "(6, 'Microsoft Surface', 7500.0, datetime.date(2019, 7, 23))\n",
      "(7, 'Acer Predator Triton', 3000.0, datetime.date(2022, 7, 16))\n"
     ]
    }
   ],
   "source": [
    "from mysql.connector import connect, Error\n",
    "import datetime\n",
    "def update_Laptop_table(ID):\n",
    "    try:\n",
    "        connection = connect(host='localhost', user='root', password='Pucit12345#', database='Electronics')        \n",
    "        cursor = connection.cursor()\n",
    "        print(\"Record before updating!\")\n",
    "        select_query = \"SELECT * FROM Laptop\"\n",
    "        cursor.execute(select_query)\n",
    "        records = cursor.fetchall()\n",
    "        for row in records:\n",
    "            print(row)\n",
    "            \n",
    "#         update data of the table\n",
    "        sql_update_query = \"\"\"Update Laptop set  Purchase_date = %s where Id = %s\"\"\"\n",
    "        current_Date = datetime.datetime.now()\n",
    "        formatted_date = current_Date.strftime('%Y-%m-%d %H:%M:%S')\n",
    "        Input = (formatted_date,ID)\n",
    "        cursor.execute(sql_update_query, Input)\n",
    "        connection.commit()\n",
    "        print(\"Records updated successfully!\")\n",
    "        print(cursor.rowcount, \"Records of a laptop table updated successfully\")\n",
    "        \n",
    "        print(\"\\n\\nRecords after updation!\")\n",
    "        cursor.execute(select_query)\n",
    "        records = cursor.fetchall()\n",
    "        for row in records:\n",
    "            print(row)\n",
    "    except Error as e:\n",
    "        print(\"Error while updating records of the table!\", e)\n",
    "    finally:\n",
    "        if connection:\n",
    "            cursor.close()\n",
    "            connection.close()\n",
    "        \n",
    "def main():\n",
    "    update_Laptop_table(5)    \n",
    "main()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "767112ba",
   "metadata": {},
   "source": [
    "## Python Delete data from MySQL Table\n",
    "\n",
    "- Now, we will discuss how to execute the SQL DELETE query from Python to delete data from a MySQL database table.After this, we will able to delete single, multiple, and all rows, as well as delete a single column and multiple columns from the MySQL table using Python\n",
    " >- Python Example to Delete a Single Row from a MySQL table\n",
    " >- Use Python Variable in a SQL query to delete data from the table\n",
    " >- Python Delete Multiple Rows from a MySQL Table\n",
    " >- Delete All rows from a table in Python\n",
    " >- Delete MySQL Table and Database from Python\n",
    " >- Delete MySQL Table column from Python\n",
    "\n",
    "I am using a Laptop table present in my MySQL server.\n",
    "![](images/mysql_laptop_table_with_data.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff3ea2b5",
   "metadata": {},
   "source": [
    "### Python Example to Delete a Single Row from a MySQL table\n",
    "\n",
    "- Connect to MySQL from Python\n",
    " >- Refer to Python MySQL database connection to connect to MySQL database from Python using `MySQL Connector module`.\n",
    "\n",
    "- Define a SQL Delete Query\n",
    " >- Next, prepare a SQL delete query to delete a row from a table. Delete query contains the row to be deleted based on a condition placed in where clause of a query.\n",
    "`For example, DELETE FROM MySQL_table WHERE id=10;`\n",
    "\n",
    "- Get Cursor Object from Connection\n",
    " >- Next, use a `connection.cursor()` method to create a cursor object. This method creates a new `MySQLCursor` object.\n",
    "\n",
    "- Execute the delete query using execute() method\n",
    " >- Execute the delete query using the `cursor.execute()` method. This method executes the operation stored in the delete query. After a successful delete operation, the `execute()` method returns us the number of rows affected.\n",
    "\n",
    "- Commit your changes\n",
    " >- After successfully executing a delete operation, make changes persistent into a database using the `commit()` of a connection class.\n",
    "\n",
    "- Get the number of rows affected\n",
    " >- Use a `cursor.rowcount` method to get the number of rows affected. The count depends on how many rows you are deleting. You can also Execute a MySQL select query from Python to Verify the result.\n",
    "\n",
    "- Close the cursor object and database connection object\n",
    " >- use `cursor.clsoe()` and `connection.clsoe()` method to close open connections after your work completes.\n",
    " \n",
    "![](images/python_delete_from_mysql_table.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "3b9bcbb9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Laptop table before deleting a row\n",
      "(7, 'Acer Predator Triton', 3000.0, datetime.date(2022, 7, 16))\n",
      "number of rows deleted 1\n",
      "Record Deleted successfully \n",
      "MySQL connection is closed\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "\n",
    "try:\n",
    "    connection = mysql.connector.connect(host='localhost',\n",
    "                                         database='Electronics',\n",
    "                                         user='root',\n",
    "                                        password='Pucit12345#')\n",
    "    cursor = connection.cursor()\n",
    "    print(\"Laptop table before deleting a row\")\n",
    "    sql_select_query = \"\"\"select * from Laptop where Id = 7\"\"\"\n",
    "    cursor.execute(sql_select_query)\n",
    "    record = cursor.fetchone()\n",
    "    print(record)\n",
    "\n",
    "    # Delete a record\n",
    "    sql_Delete_query = \"\"\"Delete from Laptop where Id = 7\"\"\"\n",
    "    cursor.execute(sql_Delete_query)\n",
    "    connection.commit()\n",
    "    print('number of rows deleted', cursor.rowcount)\n",
    "\n",
    "    # Verify using select query (optional)\n",
    "    cursor.execute(sql_select_query)\n",
    "    records = cursor.fetchall()\n",
    "    if len(records) == 0:\n",
    "        print(\"Record Deleted successfully \")\n",
    "\n",
    "except mysql.connector.Error as error:\n",
    "    print(\"Failed to delete record from table: {}\".format(error))\n",
    "finally:\n",
    "    if connection.is_connected():\n",
    "        cursor.close()\n",
    "        connection.close()\n",
    "        print(\"MySQL connection is closed\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd37ab51",
   "metadata": {},
   "source": [
    "![](images/mysql_laptop_table_after_deleting_record.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "082f0f99",
   "metadata": {},
   "source": [
    "### Use Python Variable in a SQL query to delete data from the table\n",
    "- Sometimes we need input from the user, for example, when the user is deleting their data from the web portal or any other details through User Interface. in such a scenario, it is always best practice to use a parameterized query.\n",
    "\n",
    "- A `prepared statement` or parameterized query uses `placeholders (%s)` inside any SQL statements that contain input from users. i.e., Using a parameterized query, we can pass Python variables as a query parameter in which `placeholders (%s)` used for parameters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "6dea56fa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Laptop table before deleting a row\n",
      "(4, 'HP Pavilion', 2200.0, datetime.date(2019, 1, 11))\n",
      "number of rows deleted 1\n",
      "Record Deleted successfully \n",
      "(1, 'Lenovo ThinkPad P71', 7000.0, datetime.date(2019, 8, 14))\n",
      "(2, 'Area 51M', 6999.0, datetime.date(2019, 4, 14))\n",
      "(3, 'MacBook Pro', 2499.0, datetime.date(2022, 7, 18))\n",
      "(5, 'MSI WS75 9TL-496', 6600.0, datetime.date(2022, 7, 18))\n",
      "(6, 'Microsoft Surface', 7500.0, datetime.date(2019, 7, 23))\n",
      "MySQL connection is closed\n"
     ]
    }
   ],
   "source": [
    "def delete_record(ID):\n",
    "    try:\n",
    "        connection = mysql.connector.connect(host='localhost',\n",
    "                                         database='Electronics',\n",
    "                                         user='root',\n",
    "                                        password='Pucit12345#')\n",
    "        cursor = connection.cursor()\n",
    "        print(\"Laptop table before deleting a row\")\n",
    "        sql_select_query = \"\"\"select * from Laptop where Id = %s\"\"\"\n",
    "        Input = (ID,)\n",
    "        cursor.execute(sql_select_query, Input)\n",
    "        record = cursor.fetchall()\n",
    "        for i in record:\n",
    "            print(i)\n",
    "    # Delete a record\n",
    "        sql_Delete_query = \"\"\"Delete from Laptop where Id = %s\"\"\"\n",
    "        cursor.execute(sql_Delete_query, (ID,))\n",
    "        connection.commit()\n",
    "        print('number of rows deleted', cursor.rowcount)\n",
    "        print(\"Record Deleted successfully \")\n",
    "\n",
    "\n",
    "    # Verify using select query (optional)\n",
    "        cursor.execute(\"SELECT * FROM Laptop\")\n",
    "        records = cursor.fetchall()\n",
    "        for row in records:\n",
    "            print(row)\n",
    "\n",
    "    except mysql.connector.Error as error:\n",
    "        print(\"Failed to delete record from table: {}\".format(error))\n",
    "    finally:\n",
    "        if connection.is_connected():\n",
    "            cursor.close()\n",
    "            connection.close()\n",
    "            print(\"MySQL connection is closed\")\n",
    "def main():\n",
    "    delete_record(4)\n",
    "main()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1f1317e",
   "metadata": {},
   "source": [
    "### Python Delete Multiple Rows from a MySQL Table\n",
    "- Sometimes we need to delete an N-number of rows that match a specific condition. For example, you want to delete employee data from the employee table who left the organization. \n",
    "- You can delete multiple rows from the MySQL table using a single delete SQL Query in Python."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "8c92abfe",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Laptop table before deleting a row\n",
      "(1, 'Lenovo ThinkPad P71', 7000.0, datetime.date(2019, 8, 14))\n",
      "(2, 'Area 51M', 6999.0, datetime.date(2019, 4, 14))\n",
      "(3, 'MacBook Pro', 2499.0, datetime.date(2022, 7, 18))\n",
      "(5, 'MSI WS75 9TL-496', 6600.0, datetime.date(2022, 7, 18))\n",
      "(6, 'Microsoft Surface', 7500.0, datetime.date(2019, 7, 23))\n",
      "number of rows deleted 2\n",
      "Record Deleted successfully \n",
      "(1, 'Lenovo ThinkPad P71', 7000.0, datetime.date(2019, 8, 14))\n",
      "(2, 'Area 51M', 6999.0, datetime.date(2019, 4, 14))\n",
      "(3, 'MacBook Pro', 2499.0, datetime.date(2022, 7, 18))\n",
      "MySQL connection is closed\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "def delete_record():\n",
    "    try:\n",
    "        connection = mysql.connector.connect(host='localhost',\n",
    "                                         database='Electronics',\n",
    "                                         user='root',\n",
    "                                        password='Pucit12345#')\n",
    "        cursor = connection.cursor()\n",
    "        print(\"Laptop table before deleting a row\")\n",
    "        sql_select_query = \"\"\"select * from Laptop\"\"\"\n",
    "        cursor.execute(sql_select_query)\n",
    "        record = cursor.fetchall()\n",
    "        for i in record:\n",
    "            print(i)\n",
    "    # Delete a record\n",
    "        sql_Delete_query = \"\"\"Delete from Laptop where Id = %s\"\"\"\n",
    "        Input = [(6,),(5,)]\n",
    "        cursor.executemany(sql_Delete_query, Input)\n",
    "        connection.commit()\n",
    "        print('number of rows deleted', cursor.rowcount)\n",
    "        print(\"Record Deleted successfully \")\n",
    "\n",
    "\n",
    "    # Verify using select query (optional)\n",
    "        cursor.execute(\"SELECT * FROM Laptop\")\n",
    "        records = cursor.fetchall()\n",
    "        for row in records:\n",
    "            print(row)\n",
    "\n",
    "    except mysql.connector.Error as error:\n",
    "        print(\"Failed to delete record from table: {}\".format(error))\n",
    "    finally:\n",
    "        if connection.is_connected():\n",
    "            cursor.close()\n",
    "            connection.close()\n",
    "            print(\"MySQL connection is closed\")\n",
    "def main():\n",
    "    delete_record()\n",
    "main()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a8ec32b",
   "metadata": {},
   "source": [
    "### Delete MySQL Table column from Python\n",
    "- Use alter table drop column command to delete a column from a MySQL table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "7031e79a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Laptop table before deleting a column\n",
      "(1, 'Lenovo ThinkPad P71', 7000.0, datetime.date(2019, 8, 14))\n",
      "(2, 'Area 51M', 6999.0, datetime.date(2019, 4, 14))\n",
      "(3, 'MacBook Pro', 2499.0, datetime.date(2022, 7, 18))\n",
      "Record Deleted successfully \n",
      "(1, 'Lenovo ThinkPad P71', 7000.0)\n",
      "(2, 'Area 51M', 6999.0)\n",
      "(3, 'MacBook Pro', 2499.0)\n",
      "MySQL connection is closed\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "def delete_record():\n",
    "    try:\n",
    "        connection = mysql.connector.connect(host='localhost',\n",
    "                                         database='Electronics',\n",
    "                                         user='root',\n",
    "                                        password='Pucit12345#')\n",
    "        cursor = connection.cursor()\n",
    "        print(\"Laptop table before deleting a column\")\n",
    "        sql_select_query = \"\"\"select * from Laptop\"\"\"\n",
    "        cursor.execute(sql_select_query)\n",
    "        record = cursor.fetchall()\n",
    "        for i in record:\n",
    "            print(i)\n",
    "    # Delete a record\n",
    "        sql_Delete_query = \"\"\"ALTER TABLE Laptop DROP COLUMN Purchase_date\"\"\"\n",
    "        cursor.execute(sql_Delete_query)\n",
    "        connection.commit()\n",
    "        print(\"Record Deleted successfully \")\n",
    "\n",
    "\n",
    "    # Verify using select query (optional)\n",
    "        cursor.execute(\"SELECT * FROM Laptop\")\n",
    "        records = cursor.fetchall()\n",
    "        for row in records:\n",
    "            print(row)\n",
    "\n",
    "    except mysql.connector.Error as error:\n",
    "        print(\"Failed to delete record from table: {}\".format(error))\n",
    "    finally:\n",
    "        if connection.is_connected():\n",
    "            cursor.close()\n",
    "            connection.close()\n",
    "            print(\"MySQL connection is closed\")\n",
    "def main():\n",
    "    delete_record()\n",
    "main()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3fcddebe",
   "metadata": {},
   "source": [
    "### HomeWork:\n",
    "- Delete MySQL Table and Database from Python.\n",
    "- Delete All rows from a table in Python\n",
    " >- Note : using truncate, drop and delete commands of table.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "8d048ac1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Laptop table before deleting a column\n",
      "(1, 'Lenovo ThinkPad P71', 7000.0)\n",
      "(2, 'Area 51M', 6999.0)\n",
      "(3, 'MacBook Pro', 2499.0)\n",
      "Record Deleted successfully \n",
      "MySQL connection is closed\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "def delete_record():\n",
    "    try:\n",
    "        connection = mysql.connector.connect(host='localhost',\n",
    "                                         database='Electronics',\n",
    "                                         user='root',\n",
    "                                        password='Pucit12345#')\n",
    "        cursor = connection.cursor()\n",
    "        print(\"Laptop table before deleting a column\")\n",
    "        sql_select_query = \"\"\"select * from Laptop\"\"\"\n",
    "        cursor.execute(sql_select_query)\n",
    "        record = cursor.fetchall()\n",
    "        for i in record:\n",
    "            print(i)\n",
    "    # Delete a record\n",
    "        sql_Delete_query = \"\"\"Delete from Laptop\"\"\"\n",
    "        cursor.execute(sql_Delete_query)\n",
    "        connection.commit()\n",
    "        print(\"Record Deleted successfully \")\n",
    "\n",
    "\n",
    "    # Verify using select query (optional)\n",
    "        cursor.execute(\"SELECT * FROM Laptop\")\n",
    "        records = cursor.fetchall()\n",
    "        for row in records:\n",
    "            print(row)\n",
    "\n",
    "    except mysql.connector.Error as error:\n",
    "        print(\"Failed to delete record from table: {}\".format(error))\n",
    "    finally:\n",
    "        if connection.is_connected():\n",
    "            cursor.close()\n",
    "            connection.close()\n",
    "            print(\"MySQL connection is closed\")\n",
    "def main():\n",
    "    delete_record()\n",
    "main()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "4fd81f5b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Laptop table before deleting a column\n",
      "Record Deleted successfully \n",
      "Failed to delete record from table: 1146 (42S02): Table 'Electronics.Laptop' doesn't exist\n",
      "MySQL connection is closed\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "def delete_record():\n",
    "    try:\n",
    "        connection = mysql.connector.connect(host='localhost',\n",
    "                                         database='Electronics',\n",
    "                                         user='root',\n",
    "                                        password='Pucit12345#')\n",
    "        cursor = connection.cursor()\n",
    "        print(\"Laptop table before deleting a column\")\n",
    "        sql_select_query = \"\"\"select * from Laptop\"\"\"\n",
    "        cursor.execute(sql_select_query)\n",
    "        record = cursor.fetchall()\n",
    "        for i in record:\n",
    "            print(i)\n",
    "    # Delete a record\n",
    "        sql_Delete_query = \"\"\"DROP TABLE Laptop\"\"\"\n",
    "        cursor.execute(sql_Delete_query)\n",
    "        connection.commit()\n",
    "        print(\"Record Deleted successfully \")\n",
    "\n",
    "\n",
    "    # Verify using select query (optional)\n",
    "        cursor.execute(\"SELECT * FROM Laptop\")\n",
    "        records = cursor.fetchall()\n",
    "        for row in records:\n",
    "            print(row)\n",
    "\n",
    "    except mysql.connector.Error as error:\n",
    "        print(\"Failed to delete record from table: {}\".format(error))\n",
    "    finally:\n",
    "        if connection.is_connected():\n",
    "            cursor.close()\n",
    "            connection.close()\n",
    "            print(\"MySQL connection is closed\")\n",
    "def main():\n",
    "    delete_record()\n",
    "main()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a35f2ec",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d84ed611",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d760c3a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "3672a54e",
   "metadata": {},
   "source": [
    "# Python Database Programming Exercise\n",
    "- This Database exercise Project will help us to learn database programming skills quickly. In this exercise, we will perform database CRUD(create, read, update, delete) operations using Python.\n",
    "- This Python database programming exercise includes\n",
    " >- Now it has 5 exercise questions, which simulate the real-time queries, and each question contains a specific skill you need to learn. When you complete the exercise, you get more familiar with database operations in Python.\n",
    "\n",
    "### Hospital Information System\n",
    "- In this exercise, We are implementing the `Hospital Information System`. In this exercise, I have created two tables, Hospital and Doctor. You need to create those two tables on your database server before starting the exercise.\n",
    "- Please prepare data for your project using following query.\n",
    "\n",
    "- create database\n",
    "\n",
    "```\n",
    "CREATE database python_db;            \n",
    "```\n",
    "- create hospital table\n",
    "\n",
    "```  \n",
    "CREATE TABLE Hospital (                \n",
    "    Hospital_Id INT UNSIGNED NOT NULL,                \n",
    "    Hospital_Name TEXT NOT NULL,                   \n",
    "    Bed_Count INT,               \n",
    "    PRIMARY KEY (Hospital_Id)            \n",
    ");                    \n",
    " \n",
    "INSERT INTO Hospital (Hospital_Id, Hospital_Name, Bed_Count)                \n",
    "VALUES               \n",
    "('1', 'Mayo Clinic', 200),               \n",
    "('2', 'Cleveland Clinic', 400),                    \n",
    "('3', 'Johns Hopkins', 1000),                \n",
    "('4', 'UCLA Medical Center', 1500);                \n",
    "```\n",
    "             \n",
    "- CREATE Doctoe Table             \n",
    "          \n",
    "```\n",
    "CREATE TABLE Doctor(                  \n",
    "    Doctor_Id INT UNSIGNED NOT NULL,                   \n",
    "    Doctor_Name TEXT NOT NULL,                  \n",
    "    Hospital_Id INT NOT NULL,                  \n",
    "    Joining_Date DATE NOT NULL,                  \n",
    "    Speciality TEXT NULL,                   \n",
    "    Salary INT NULL,                  \n",
    "    Experience INT NULL,                  \n",
    "    PRIMARY KEY (Doctor_Id)           \n",
    ");                 \n",
    "          \n",
    "INSERT INTO Doctor (Doctor_Id, Doctor_Name, Hospital_Id, Joining_Date, Speciality, Salary, Experience)     \n",
    "VALUES       \n",
    "('101', 'David', '1', '2005-2-10', 'Pediatric', '40000', NULL),             \n",
    "('102', 'Michael', '1', '2018-07-23', 'Oncologist', '20000', NULL),                  \n",
    "('103', 'Susan', '2', '2016-05-19', 'Garnacologist', '25000', NULL),                 \n",
    "('104', 'Robert', '2', '2017-12-28', 'Pediatric ', '28000', NULL),                   \n",
    "('105', 'Linda', '3', '2004-06-04', 'Garnacologist', '42000', NULL),                \n",
    "('106', 'William', '3', '2012-09-11', 'Dermatologist', '30000', NULL),             \n",
    "('107', 'Richard', '4', '2014-08-21', 'Garnacologist', '32000', NULL),             \n",
    "('108', 'Karen', '4', '2011-10-17', 'Radiologist', '30000', NULL);             \n",
    "```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e3fe719",
   "metadata": {},
   "source": [
    "### Doctor Table\n",
    "![](images/doctor_table.png)\n",
    "\n",
    "### Hospital Table\n",
    "![](images/hospital_table.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06d31c22",
   "metadata": {},
   "source": [
    "### Exercise 1: Connect to your database server and print its version\n",
    "##### Note:\n",
    " >- Write SQL query to get the database server version.\n",
    " >- Connect to the database and use `cursor.execute()` to execute this query.\n",
    " >- Next, use `cursor.fetchone()` to fetch the record.\n",
    " \n",
    "#### Solution:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "0160410b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Question no 01: Database version \n",
      "We are connected to MySQL version :  ('8.0.29',)\n"
     ]
    }
   ],
   "source": [
    "from mysql.connector import connect, Error\n",
    "\n",
    "# this function create connection with database\n",
    "def get_Connection():\n",
    "    connection = connect(host='localhost', user='root', database='python_db', password='Pucit12345#')\n",
    "    return connection\n",
    "\n",
    "# this function gets connection and will close it\n",
    "def close_connection(connection):\n",
    "    if connection:\n",
    "        connection.close()\n",
    "        \n",
    "# this function returns our database version        \n",
    "def read_database_version():\n",
    "    try:\n",
    "        connection = get_Connection()\n",
    "        cursor = connection.cursor()\n",
    "        cursor.execute(\"select version();\")\n",
    "        db_version = cursor.fetchone()\n",
    "        print(\"We are connected to MySQL version : \", db_version)\n",
    "        close_connection(connection)\n",
    "    except Error as e:\n",
    "        print(\"Error while getting database version : \", e)\n",
    "print(\"Question no 01: Database version \")\n",
    "read_database_version()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "336cee92",
   "metadata": {},
   "source": [
    "### Question 2: Fetch Hospital and Doctor Information using hospital Id and doctor Id \n",
    "- Implement the functionality to read the details of a given doctor from the doctor table and Hospital from the hospital table. i.e., read records from Hospital and Doctor Table as per given hospital Id and Doctor Id.\n",
    "\n",
    "#### Hint\n",
    "- Connect to `python_db` and use `cursor.execute()` to execute the parameterized query.\n",
    "- Next, use `cursor.fetchall()` to fetch the record.\n",
    "- Next, iterate record/resultSet to print all column values\n",
    "#### Expected Output:\n",
    "![](images/output.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "b3813c5e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "            1.Enter to get hospital record\n",
      "\n",
      "            2.Enter to get dcotor record\n",
      "\n",
      "            3.Enter to exit program\n",
      "\n",
      "            1\n",
      "Enter Hospital's ID to get Hospital's details : 2\n",
      "Printing hospital records ! \n",
      "Hospital Id :  2\n",
      "Hospital Name :  Cleveland Clinic\n",
      "Bed Count :  400\n",
      "\n",
      "            1.Enter to get hospital record\n",
      "\n",
      "            2.Enter to get dcotor record\n",
      "\n",
      "            3.Enter to exit program\n",
      "\n",
      "            2\n",
      "Enter Doctor's ID to get Doctor's details : 102\n",
      "Printing Doctor records ! \n",
      "Doctor Id: 102\n",
      "Doctor Name: Michael\n",
      "Hospital Id: 1\n",
      "Joining Date: 2018-07-23\n",
      "Specialty: Oncologist\n",
      "Salary: 20000\n",
      "Experience: 3\n",
      "\n",
      "            1.Enter to get hospital record\n",
      "\n",
      "            2.Enter to get dcotor record\n",
      "\n",
      "            3.Enter to exit program\n",
      "\n",
      "            103\n",
      "\n",
      "            1.Enter to get hospital record\n",
      "\n",
      "            2.Enter to get dcotor record\n",
      "\n",
      "            3.Enter to exit program\n",
      "\n",
      "            2\n",
      "Enter Doctor's ID to get Doctor's details : 103\n",
      "Printing Doctor records ! \n",
      "Doctor Id: 103\n",
      "Doctor Name: Susan\n",
      "Hospital Id: 2\n",
      "Joining Date: 2016-05-19\n",
      "Specialty: Garnacologist\n",
      "Salary: 25000\n",
      "Experience: None\n",
      "\n",
      "            1.Enter to get hospital record\n",
      "\n",
      "            2.Enter to get dcotor record\n",
      "\n",
      "            3.Enter to exit program\n",
      "\n",
      "            3\n"
     ]
    }
   ],
   "source": [
    "from mysql.connector import connect, Error\n",
    "def get_connection():\n",
    "    connection = connect(host='localhost', user='root', database='python_db', password='Pucit12345#')\n",
    "    return connection\n",
    "def close_connection(connection):\n",
    "    if connection:\n",
    "        connection.close()\n",
    "# function is used to get hospital records\n",
    "def get_Hospital_Record(Hospital_id):\n",
    "    try:\n",
    "        connection = get_connection()\n",
    "        cursor = connection.cursor()\n",
    "        mysql_query = \"SELECT * FROM  Hospital WHERE Hospital_Id = %s \"\n",
    "        cursor.execute(mysql_query, (Hospital_id,))\n",
    "        records = cursor.fetchall()\n",
    "        print(\"Printing hospital records ! \")\n",
    "        for row in records:\n",
    "            print(\"Hospital Id : \", row[0])\n",
    "            print(\"Hospital Name : \", row[1])\n",
    "            print(\"Bed Count : \", row[2])\n",
    "        close_connection(connection)\n",
    "    except Error as e:\n",
    "        print(\"Error while getting hospital records !\", e)\n",
    "        \n",
    "# function is used to get doctor records\n",
    "def get_Doctor_Record(Doctor_Id):\n",
    "    try:\n",
    "        connection = connect(host='localhost', user='root', database='python_db', password='Pucit12345#')\n",
    "        cursor = connection.cursor()\n",
    "        mysql_query = \"SELECT * FROM Doctor WHERE Doctor_Id = %s\"\n",
    "        cursor.execute(mysql_query, (Doctor_Id,))\n",
    "        records = cursor.fetchall()\n",
    "        print(\"Printing Doctor records ! \")\n",
    "        for row in records:\n",
    "            print(\"Doctor Id:\", row[0])\n",
    "            print(\"Doctor Name:\", row[1])\n",
    "            print(\"Hospital Id:\", row[2])\n",
    "            print(\"Joining Date:\", row[3])\n",
    "            print(\"Specialty:\", row[4])\n",
    "            print(\"Salary:\", row[5])\n",
    "            print(\"Experience:\", row[6])\n",
    "        close_connection(connection)\n",
    "    except Error as e:\n",
    "        print(\"Error while getting doctor records ! \", e)\n",
    "def main():\n",
    "    while True:\n",
    "        try:\n",
    "            choice = int(input(\"\"\"\n",
    "            1.Enter to get hospital record\\n\n",
    "            2.Enter to get dcotor record\\n\n",
    "            3.Enter to exit program\\n\n",
    "            \"\"\"))\n",
    "            if choice==1:\n",
    "                ID = int(input(\"Enter Hospital's ID to get Hospital's details : \"))\n",
    "                get_Hospital_Record(ID)\n",
    "            if choice==2:\n",
    "                ID = int(input(\"Enter Doctor's ID to get Doctor's details : \"))\n",
    "                get_Doctor_Record(ID)\n",
    "            if choice==3:\n",
    "                break\n",
    "        except:\n",
    "            print(\"Invalid Input!\")\n",
    "            \n",
    "if __name__ == \"__main__\":\n",
    "    main()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51a03dff",
   "metadata": {},
   "source": [
    "## Exercise 3: Get the list Of doctors as per the given specialty and salary\n",
    "#### Note:\n",
    "- Fetch all doctors whose salary higher than the input amount and specialty is the same as the input specialty.\n",
    "\n",
    "#### Given:    \n",
    "```\n",
    "get_specialist_doctors_list(\"Garnacologist\", 30000)\n",
    "\n",
    "```\n",
    "#### Hint\n",
    "- Define the parameterized select query to fetch data from the table as per the given specialty and salary.\n",
    "- Next, use the cursor.execute() to execute the query.\n",
    "- Next, get all records using cursor.fetchall()\n",
    "- Iterate those records and print each row.\n",
    "\n",
    "#### Expected Output:\n",
    "![](images/output1.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "23358c58",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter doctor's speciality to get records : Garnacologist\n",
      "Enter doctor's salary to get records : 30000\n",
      "Printing Doctor records ! \n",
      "Doctor Id: 105\n",
      "Doctor Name: Linda\n",
      "Hospital Id: 3\n",
      "Joining Date: 2004-06-04\n",
      "Specialty: Garnacologist\n",
      "Salary: 42000\n",
      "Experience: None\n",
      "\n",
      "\n",
      "\n",
      "Doctor Id: 107\n",
      "Doctor Name: Richard\n",
      "Hospital Id: 4\n",
      "Joining Date: 2014-08-21\n",
      "Specialty: Garnacologist\n",
      "Salary: 32000\n",
      "Experience: None\n",
      "\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "from mysql.connector import connect, Error\n",
    "def get_connection():\n",
    "    connection = connect(host='localhost', user='root', database='python_db', password='Pucit12345#')\n",
    "    return connection\n",
    "def close_connection(connection):\n",
    "    if connection:\n",
    "        connection.close()\n",
    "def get_specialist_doctors_list(speciality, salary):\n",
    "    connection = get_connection()\n",
    "    cursor = connection.cursor()\n",
    "    mysql_query = \"\"\"\n",
    "    SELECT * FROM Doctor WHERE (Speciality = %s) and (Salary > %s)\n",
    "    \"\"\"\n",
    "    cursor.execute(mysql_query, (speciality, salary))\n",
    "    records = cursor.fetchall()\n",
    "    print(\"Printing Doctor records ! \")\n",
    "    for row in records:\n",
    "        print(\"Doctor Id:\", row[0])\n",
    "        print(\"Doctor Name:\", row[1])\n",
    "        print(\"Hospital Id:\", row[2])\n",
    "        print(\"Joining Date:\", row[3])\n",
    "        print(\"Specialty:\", row[4])\n",
    "        print(\"Salary:\", row[5])\n",
    "        print(\"Experience:\", row[6])\n",
    "        print(\"\\n\\n\")\n",
    "    close_connection(connection)\n",
    "def main():\n",
    "    speciality = input(\"Enter doctor's speciality to get records : \")\n",
    "    salary = int(input(\"Enter doctor's salary to get records : \"))\n",
    "    get_specialist_doctors_list(speciality, salary)\n",
    "main()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b4d4296",
   "metadata": {},
   "source": [
    "### Exercise 4: Get a list of doctors from a given hospital\n",
    "#### Note: \n",
    "- Implement the functionality to fetch all the doctors as per the given Hospital Id. You must display the hospital name of a doctor.\n",
    "\n",
    "#### Given:\n",
    "```\n",
    "def get_doctors(hospital_id):\n",
    "    #Fetch All doctors within given Hospital\n",
    "\n",
    "get_doctors(2)\n",
    "```\n",
    "\n",
    "#### Hint:\n",
    "\n",
    "- Define the parameterized select query to get the hospital name as per the given hospital id.\n",
    "- Next, use the cursor.execute() to execute this query and store the hospital name in a variable.\n",
    "- Define the parameterized select query to fetch all doctors from the doctor table as per the given hospital id.\n",
    "- Next, use the `cursor.execute()` to execute the query.\n",
    "- Next, get all records using `cursor.fetchall()`.\n",
    "- Iterate those records and print each column. Also, display the hospital name we fetched in the first query in each doctor’s entry"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "c8110f1a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter Hospital's Id to get all the doctors : 3\n",
      "Printing Doctors of  Johns Hopkins Hospital\n",
      "Doctor Id :  105\n",
      "Doctor Name :  Linda\n",
      "Hospital Id :  3\n",
      "Hospital Name :  Johns Hopkins\n",
      "Joining Date :  2004-06-04\n",
      "Speciality :  Garnacologist\n",
      "Salary:  42000\n",
      "Experience :  None\n",
      "\n",
      "\n",
      "\n",
      "Doctor Id :  106\n",
      "Doctor Name :  William\n",
      "Hospital Id :  3\n",
      "Hospital Name :  Johns Hopkins\n",
      "Joining Date :  2012-09-11\n",
      "Speciality :  Dermatologist\n",
      "Salary:  30000\n",
      "Experience :  None\n",
      "\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "from mysql.connector import connect, Error\n",
    "def get_connection():\n",
    "    connection = connect(host='localhost', user='root', password='Pucit12345#', database='python_db')\n",
    "    return connection\n",
    "\n",
    "def close_connection(connection):\n",
    "    if connection:\n",
    "        connection.close()\n",
    "    \n",
    "def get_Hospital_Name(Hospital_Id):\n",
    "    try:\n",
    "        connection = get_connection()\n",
    "        cursor = connection.cursor()\n",
    "        mysql_query = \"SELECT * FROM Hospital WHERE Hospital_Id = %s\"\n",
    "        cursor.execute(mysql_query, (Hospital_Id,))\n",
    "        records = cursor.fetchone()\n",
    "        name = records[1]\n",
    "        return name\n",
    "#         print(records)\n",
    "#         for i in records:\n",
    "#             print(i)\n",
    "        close_connection(connection)\n",
    "    except Error as e:\n",
    "        print(\"Error while getting Hospital name is \", e)\n",
    "\n",
    "def get_Doctors(Hospital_Id):\n",
    "    try:\n",
    "        hospital_Name = get_Hospital_Name(Hospital_Id)\n",
    "        connection = get_connection()\n",
    "        cursor = connection.cursor()\n",
    "        mysql_query = \"SELECT * FROM Doctor WHERE Hospital_Id = %s\"\n",
    "        cursor.execute(mysql_query, (Hospital_Id,))\n",
    "        records = cursor.fetchall()\n",
    "        print(\"Printing Doctors of \", hospital_Name, 'Hospital')\n",
    "        for row in records:\n",
    "            print(\"Doctor Id : \", row[0])\n",
    "            print(\"Doctor Name : \", row[1])\n",
    "            print(\"Hospital Id : \", row[2])\n",
    "            print(\"Hospital Name : \", hospital_Name)\n",
    "            print(\"Joining Date : \", row[3])\n",
    "            print(\"Speciality : \", row[4])\n",
    "            print(\"Salary: \", row[5])\n",
    "            print(\"Experience : \", row[6])\n",
    "            print(\"\\n\\n\")\n",
    "    except Error as e:\n",
    "        print(\"Error while getting doctors  details : \", e)\n",
    "            \n",
    "def main():\n",
    "    ID = int(input(\"Enter Hospital's Id to get all the doctors : \"))\n",
    "    get_Doctors(ID)\n",
    "main()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "264cc96e",
   "metadata": {},
   "source": [
    "### Question 5: Update doctor experience in years\n",
    "- The value of the experience column for each doctor is null. Implement the functionality to update the experience of a given doctor in years.\n",
    "\n",
    "#### Given:\n",
    "```\n",
    "def update_doctor_experience(doctor_id):\n",
    "    # Update Doctor Experience in Years\n",
    "\n",
    "update_doctor_experience(101)\n",
    "```\n",
    "#### Hint\n",
    "- The doctor table has the joining date for each doctor.\n",
    "- Get a given doctor’s `joining date`.\n",
    "- To get a difference in a year, we can calculate the difference between `today’s date` and `joining-date` in years.\n",
    "- After calculating the difference in a year, you can execute the update table query to update the experience of a given doctor.\n",
    "\n",
    "![](images/output3.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "f415b8e6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[2019, 10, 20]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tup = ('2019-10-20',)\n",
    "a = list(tup)[0].split('-')\n",
    "b = [int(i) for i in a]\n",
    "b\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "376a08f1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Question 5: Calculate and Update experience of all doctors  \n",
      "\n",
      "Doctor Id: 108  Experience updated to  10  years\n"
     ]
    }
   ],
   "source": [
    "from mysql.connector import connect, Error\n",
    "from datetime import date\n",
    "def get_connection():\n",
    "    connection = connect(host='localhost', user='root', database='python_db', password='Pucit12345#')\n",
    "    return connection\n",
    "\n",
    "def close_connection(connection):\n",
    "    if connection:\n",
    "        connection.close()\n",
    "        \n",
    "def get_doctor_Experience(joiningDate):\n",
    "    today = date.today()\n",
    "    str_List = str(list(joiningDate)[0]).split(\"-\")\n",
    "    list1 = [int(i) for i in str_List]\n",
    "    joining_Date = date(list1[0], list1[1], list1[2])\n",
    "    experience = today.year - joining_Date.year - ((today.month, today.day) < (joining_Date.month, joining_Date.day))\n",
    "    return experience\n",
    "\n",
    "\n",
    "def update_doctor_experienc(Doctor_Id):\n",
    "    try:\n",
    "                # Get joining date\n",
    "        connection = get_connection()\n",
    "        cursor = connection.cursor()\n",
    "        select_query = \"\"\"select Joining_Date from Doctor where Doctor_Id = %s\"\"\"\n",
    "        cursor.execute(select_query, (Doctor_Id,))\n",
    "        joining_date = cursor.fetchone()\n",
    "        \n",
    "#         get doctor experience\n",
    "        experience = get_doctor_Experience(joining_date)\n",
    "        # Update doctor's Experience now\n",
    "        connection = get_connection()\n",
    "        cursor = connection.cursor()\n",
    "        sql_select_query = \"\"\"update Doctor set Experience = %s where Doctor_Id =%s\"\"\"\n",
    "        cursor.execute(sql_select_query, (experience, Doctor_Id))\n",
    "        connection.commit()\n",
    "        print(\"Doctor Id:\", Doctor_Id, \" Experience updated to \", experience, \" years\")\n",
    "        close_connection(connection)\n",
    "\n",
    "    except Error as e:\n",
    "        print(\"Error while getting doctor's data\", e)\n",
    "\n",
    "print(\"Question 5: Calculate and Update experience of all doctors  \\n\")\n",
    "update_doctor_experienc(108)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97a9f694",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4095a8ed",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55dd1624",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}