Python_Mysql.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Python-Mysql.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "authorship_tag": "ABX9TyOJ93Hd0AiBEVTDCjw+YvTV",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    },
    "accelerator": "TPU"
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/subithou/Python/blob/main/Python_Mysql.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "[Home](https://github.com/subithou/Python) / Python-Mysql\n",
        "# **Python MySQL**\n",
        "----------------\n",
        "\n",
        "\n",
        "*   Python can be used in database applications.\n",
        "*   One of the most popular databases is MySQL\n",
        "\n",
        "###**Install MySQL Driver**\n",
        "\n",
        "* Python needs a MySQL driver to access the MySQL database.\n",
        "* In this tutorial we will use the driver \"MySQL Connector\".\n",
        "* We recommend that you use PIP to install \"MySQL Connector\".\n",
        "* PIP is most likely already installed in your Python environment.\n",
        "\n",
        "*Navigate your command line to the location of PIP, and type the following*\n",
        "\n",
        "**C:\\Users\\Your Name\\AppData\\Local\\Programs\\Python\\Python36-32\\Scripts>python -m pip install mysql-connector-python**\n",
        "\n",
        "###**Test MySQL Connector**\n",
        " To test if the installation was successful, or if you already have \"MySQL Connector\" installed, create a Python page with the following content\n",
        "\n",
        " page name : demo_mysql_test.py"
      ],
      "metadata": {
        "id": "B1z2JqgEQIK6"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import mysql.connector"
      ],
      "metadata": {
        "id": "5-1hoOdkRylg"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "If the above code was executed with no errors, \"MySQL Connector\" is installed and ready to be used."
      ],
      "metadata": {
        "id": "9qR5m1NFR3VH"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "###**Create Connection**\n",
        "* Start by creating a connection to the database.\n",
        "\n",
        "* Use the username and password from your MySQL database"
      ],
      "metadata": {
        "id": "4udkq-q-R6RZ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\"\n",
        ")\n",
        "\n",
        "print(mydb)"
      ],
      "metadata": {
        "id": "ODpQGluwSRqj"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "##**Creating a Database**\n",
        "To create a database in MySQL, use the \"CREATE DATABASE\" statement"
      ],
      "metadata": {
        "id": "fCbEgV9_SYIW"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# create a database named \"mydatabase\":\n",
        "\n",
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\"\n",
        ")\n",
        "\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "mycursor.execute(\"CREATE DATABASE mydatabase\")"
      ],
      "metadata": {
        "id": "BKKJSoQjSggi"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###**Check if Database Exists**\n",
        "You can check if a database exist by listing all databases in your system by using the \"SHOW DATABASES\" statement"
      ],
      "metadata": {
        "id": "aHWoYgvbSwOf"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Return a list of your system's databases\n",
        "\n",
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\"\n",
        ")\n",
        "\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "mycursor.execute(\"SHOW DATABASES\")\n",
        "\n",
        "for x in mycursor:\n",
        "  print(x)"
      ],
      "metadata": {
        "id": "hWWFzEFZS4fp"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Or Try connecting to the database \"mydatabase\"\n",
        "\n",
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\",\n",
        "  database=\"mydatabase\"\n",
        ")\n",
        "\n",
        "# If the database does not exist, you will get an error."
      ],
      "metadata": {
        "id": "GtXAVgx-S_O9"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###**Creating a Table**\n",
        "* To create a table in MySQL, use the \"CREATE TABLE\" statement.\n",
        "\n",
        "* Make sure you define the name of the database when you create the connection"
      ],
      "metadata": {
        "id": "vumSzbRLTQkV"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Create a table named \"customers\":\n",
        "\n",
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\",\n",
        "  database=\"mydatabase\"\n",
        ")\n",
        "\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "mycursor.execute(\"CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))\")"
      ],
      "metadata": {
        "id": "Kyb3Oh_yTU4e"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###**Check if Table Exists**\n",
        "You can check if a table exist by listing all tables in your database with the \"SHOW TABLES\" statement"
      ],
      "metadata": {
        "id": "ha_JSWh3TcaP"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Return a list of your system's databases:\n",
        "\n",
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\",\n",
        "  database=\"mydatabase\"\n",
        ")\n",
        "\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "mycursor.execute(\"SHOW TABLES\")\n",
        "\n",
        "for x in mycursor:\n",
        "  print(x)"
      ],
      "metadata": {
        "id": "VvASjoNWTfdr"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###**Primary Key**\n",
        "* When creating a table, you should also create a column with a unique key for each record.\n",
        "\n",
        "* This can be done by defining a PRIMARY KEY.\n",
        "\n",
        "* We use the statement \"INT AUTO_INCREMENT PRIMARY KEY\" which will insert a unique number for each record. Starting at 1, and increased by one for each record"
      ],
      "metadata": {
        "id": "yRVef98eTltB"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Create primary key when creating the table:\n",
        "\n",
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\",\n",
        "  database=\"mydatabase\"\n",
        ")\n",
        "\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "mycursor.execute(\"CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))\")"
      ],
      "metadata": {
        "id": "D4_baEepTxHx"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "If the table already exists, use the ALTER TABLE keyword"
      ],
      "metadata": {
        "id": "TdnU3XnlTy8l"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Create primary key on an existing table:\n",
        "\n",
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\",\n",
        "  database=\"mydatabase\"\n",
        ")\n",
        "\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "mycursor.execute(\"ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY\")"
      ],
      "metadata": {
        "id": "EqHiYtPeT8va"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###**Insert Into Table**\n",
        "* To fill a table in MySQL, use the \"INSERT INTO\" statement."
      ],
      "metadata": {
        "id": "8ffT6f-tUED9"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Insert a record in the \"customers\" table:\n",
        "\n",
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\",\n",
        "  database=\"mydatabase\"\n",
        ")\n",
        "\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "sql = \"INSERT INTO customers (name, address) VALUES (%s, %s)\"\n",
        "val = (\"John\", \"Highway 21\")\n",
        "mycursor.execute(sql, val)\n",
        "\n",
        "mydb.commit()\n",
        "\n",
        "print(mycursor.rowcount, \"record inserted.\")"
      ],
      "metadata": {
        "id": "AYeeQ9bLUNOf"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###**Insert Multiple Rows**\n",
        "* To insert multiple rows into a table, use the executemany() method.\n",
        "\n",
        "* The second parameter of the executemany() method is a list of tuples, containing the data you want to insert"
      ],
      "metadata": {
        "id": "DYQRcwQcURMP"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Fill the \"customers\" table with data:\n",
        "\n",
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\",\n",
        "  database=\"mydatabase\"\n",
        ")\n",
        "\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "sql = \"INSERT INTO customers (name, address) VALUES (%s, %s)\"\n",
        "val = [\n",
        "  ('Peter', 'Lowstreet 4'),\n",
        "  ('Amy', 'Apple st 652'),\n",
        "  ('Hannah', 'Mountain 21'),\n",
        "  ('Michael', 'Valley 345'),\n",
        "  ('Sandy', 'Ocean blvd 2'),\n",
        "  ('Betty', 'Green Grass 1'),\n",
        "  ('Richard', 'Sky st 331'),\n",
        "  ('Susan', 'One way 98'),\n",
        "  ('Vicky', 'Yellow Garden 2'),\n",
        "  ('Ben', 'Park Lane 38'),\n",
        "  ('William', 'Central st 954'),\n",
        "  ('Chuck', 'Main Road 989'),\n",
        "  ('Viola', 'Sideway 1633')\n",
        "]\n",
        "\n",
        "mycursor.executemany(sql, val)\n",
        "\n",
        "mydb.commit()\n",
        "\n",
        "print(mycursor.rowcount, \"was inserted.\")"
      ],
      "metadata": {
        "id": "WFUC84_RUdKN"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###**Get Inserted ID**\n",
        "* You can get the id of the row you just inserted by asking the cursor object.\n",
        "\n",
        "**Note**: If you insert more than one row, the id of the last inserted row is returned."
      ],
      "metadata": {
        "id": "27y2Bd_HUiBN"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Insert one row, and return the ID:\n",
        "\n",
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\",\n",
        "  database=\"mydatabase\"\n",
        ")\n",
        "\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "sql = \"INSERT INTO customers (name, address) VALUES (%s, %s)\"\n",
        "val = (\"Michelle\", \"Blue Village\")\n",
        "mycursor.execute(sql, val)\n",
        "\n",
        "mydb.commit()\n",
        "\n",
        "print(\"1 record inserted, ID:\", mycursor.lastrowid)"
      ],
      "metadata": {
        "id": "CiilBKrIUqYR"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###**Select From a Table**\n",
        "To select from a table in MySQL, use the \"SELECT\" statement.\n",
        "\n",
        "**Note:** We use the fetchall() method, which fetches all rows from the last executed statement"
      ],
      "metadata": {
        "id": "_kgeHaKAUuMm"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Select all records from the \"customers\" table, and display the result:\n",
        "\n",
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\",\n",
        "  database=\"mydatabase\"\n",
        ")\n",
        "\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "mycursor.execute(\"SELECT * FROM customers\")\n",
        "\n",
        "myresult = mycursor.fetchall()\n",
        "\n",
        "for x in myresult:\n",
        "  print(x)"
      ],
      "metadata": {
        "id": "ojz590NVU0TV"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###**Selecting Columns**\n",
        "To select only some of the columns in a table, use the \"SELECT\" statement followed by the column name(s)"
      ],
      "metadata": {
        "id": "oRvrxXrHU-LO"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Select only the name and address columns:\n",
        "\n",
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\",\n",
        "  database=\"mydatabase\"\n",
        ")\n",
        "\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "mycursor.execute(\"SELECT name, address FROM customers\")\n",
        "\n",
        "myresult = mycursor.fetchall()\n",
        "\n",
        "for x in myresult:\n",
        "  print(x)"
      ],
      "metadata": {
        "id": "OotvfHr2VDWT"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###**Using the fetchone() Method**\n",
        "* If you are only interested in one row, you can use the fetchone() method.\n",
        "\n",
        "* The fetchone() method will return the first row of the result"
      ],
      "metadata": {
        "id": "N60YjZQtVF23"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Fetch only one row:\n",
        "\n",
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\",\n",
        "  database=\"mydatabase\"\n",
        ")\n",
        "\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "mycursor.execute(\"SELECT * FROM customers\")\n",
        "\n",
        "myresult = mycursor.fetchone()\n",
        "\n",
        "print(myresult)"
      ],
      "metadata": {
        "id": "5cxA-LJsVNv1"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###**Python MySQL Where**\n",
        "When selecting records from a table, you can filter the selection by using the \"WHERE\" statement"
      ],
      "metadata": {
        "id": "ga8rovNmVWKA"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Select record(s) where the address is \"Park Lane 38\": result:\n",
        "\n",
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\",\n",
        "  database=\"mydatabase\"\n",
        ")\n",
        "\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "sql = \"SELECT * FROM customers WHERE address ='Park Lane 38'\"\n",
        "\n",
        "mycursor.execute(sql)\n",
        "\n",
        "myresult = mycursor.fetchall()\n",
        "\n",
        "for x in myresult:\n",
        "  print(x)"
      ],
      "metadata": {
        "id": "Q5DudI7iVev7"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###**Python MySQL Order By**\n",
        "\n",
        "####**Sort the Result**\n",
        "* Use the ORDER BY statement to sort the result in ascending or descending order.\n",
        "\n",
        "* The ORDER BY keyword sorts the result ascending by default. To sort the result in descending order, use the DESC keyword."
      ],
      "metadata": {
        "id": "WBSZ3JFuiFOt"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Sort the result alphabetically by name: result:\n",
        "\n",
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\",\n",
        "  database=\"mydatabase\"\n",
        ")\n",
        "\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "sql = \"SELECT * FROM customers ORDER BY name\"\n",
        "\n",
        "mycursor.execute(sql)\n",
        "\n",
        "myresult = mycursor.fetchall()\n",
        "\n",
        "for x in myresult:\n",
        "  print(x)"
      ],
      "metadata": {
        "id": "QEjeyzYeiWID"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "####**ORDER BY DESC**\n",
        "Use the DESC keyword to sort the result in a descending order."
      ],
      "metadata": {
        "id": "qPPMgVEhideI"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Sort the result reverse alphabetically by name:\n",
        "\n",
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\",\n",
        "  database=\"mydatabase\"\n",
        ")\n",
        "\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "sql = \"SELECT * FROM customers ORDER BY name DESC\"\n",
        "\n",
        "mycursor.execute(sql)\n",
        "\n",
        "myresult = mycursor.fetchall()\n",
        "\n",
        "for x in myresult:\n",
        "  print(x)"
      ],
      "metadata": {
        "id": "xjOHGtbpioFN"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###**Python MySQL Delete From By**\n",
        "####**Delete Record**\n",
        "You can delete records from an existing table by using the \"DELETE FROM\" statement.\n",
        "\n",
        "**Important!:** Notice the statement: mydb.commit(). It is required to make the changes, otherwise no changes are made to the table.\n",
        "\n",
        "**Notice the WHERE clause in the DELETE syntax**: The WHERE clause specifies which record(s) that should be deleted. If you omit the WHERE clause, all records will be deleted!\n",
        "\n"
      ],
      "metadata": {
        "id": "yiYZWhw3iuIe"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Delete any record where the address is \"Mountain 21\":\n",
        "\n",
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\",\n",
        "  database=\"mydatabase\"\n",
        ")\n",
        "\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "sql = \"DELETE FROM customers WHERE address = 'Mountain 21'\"\n",
        "\n",
        "mycursor.execute(sql)\n",
        "\n",
        "mydb.commit()\n",
        "\n",
        "print(mycursor.rowcount, \"record(s) deleted\")"
      ],
      "metadata": {
        "id": "rLiQkYwei5lB"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###**Python MySQL Drop Table**\n",
        "####**Delete a Table**\n",
        "You can delete an existing table by using the \"DROP TABLE\" statement"
      ],
      "metadata": {
        "id": "KfD-qMmJjSOE"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Delete the table \"customers\":\n",
        "\n",
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\",\n",
        "  database=\"mydatabase\"\n",
        ")\n",
        "\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "sql = \"DROP TABLE customers\"\n",
        "\n",
        "mycursor.execute(sql)"
      ],
      "metadata": {
        "id": "6hhX9lHNjfzc"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "####**Drop Only if Exist**\n",
        "If the the table you want to delete is already deleted, or for any other reason does not exist, you can use the IF EXISTS keyword to avoid getting an error."
      ],
      "metadata": {
        "id": "HA4aglEQjkR-"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Delete the table \"customers\" if it exists\n",
        "\n",
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\",\n",
        "  database=\"mydatabase\"\n",
        ")\n",
        "\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "sql = \"DROP TABLE IF EXISTS customers\"\n",
        "\n",
        "mycursor.execute(sql)"
      ],
      "metadata": {
        "id": "A4vUsQnvjwvz"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###**Python MySQL Update Table**\n",
        "####**Update Table**\n",
        "You can update existing records in a table by using the \"UPDATE\" statement.\n",
        "\n",
        "**Important!:** Notice the statement: mydb.commit(). It is required to make the changes, otherwise no changes are made to the table.\n",
        "\n",
        "**Notice the WHERE clause in the UPDATE syntax:** The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!"
      ],
      "metadata": {
        "id": "6hhIBiqej2Ld"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Overwrite the address column from \"Valley 345\" to \"Canyon 123\":\n",
        "\n",
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\",\n",
        "  database=\"mydatabase\"\n",
        ")\n",
        "\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "sql = \"UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'\"\n",
        "\n",
        "mycursor.execute(sql)\n",
        "\n",
        "mydb.commit()\n",
        "\n",
        "print(mycursor.rowcount, \"record(s) affected\")"
      ],
      "metadata": {
        "id": "pDmgZS1skPIV"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "###**Python MySQL Limit**\n",
        "####**Limit the Result**\n",
        "You can limit the number of records returned from the query, by using the \"LIMIT\" statement"
      ],
      "metadata": {
        "id": "A-VNUEDgkSL7"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Select the 5 first records in the \"customers\" table:\n",
        "\n",
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\",\n",
        "  database=\"mydatabase\"\n",
        ")\n",
        "\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "mycursor.execute(\"SELECT * FROM customers LIMIT 5\")\n",
        "\n",
        "myresult = mycursor.fetchall()\n",
        "\n",
        "for x in myresult:\n",
        "  print(x)"
      ],
      "metadata": {
        "id": "B6RSxZKsky47"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "####**Start From Another Position**\n",
        "If you want to return five records, starting from the third record, you can use the \"OFFSET\" keyword"
      ],
      "metadata": {
        "id": "6w5kwc7Vk3Zj"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Start from position 3, and return 5 records:\n",
        "\n",
        "import mysql.connector\n",
        "\n",
        "mydb = mysql.connector.connect(\n",
        "  host=\"localhost\",\n",
        "  user=\"yourusername\",\n",
        "  password=\"yourpassword\",\n",
        "  database=\"mydatabase\"\n",
        ")\n",
        "\n",
        "mycursor = mydb.cursor()\n",
        "\n",
        "mycursor.execute(\"SELECT * FROM customers LIMIT 5 OFFSET 2\")\n",
        "\n",
        "myresult = mycursor.fetchall()\n",
        "\n",
        "for x in myresult:\n",
        "  print(x)"
      ],
      "metadata": {
        "id": "yAKxBee5k-Hw"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}