{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "source": [
        "!pip install mysql-connector-python"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "aCjDPOSDumMW",
        "outputId": "18cd7907-d9ca-4db6-fe37-894cb7f929bd"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Requirement already satisfied: mysql-connector-python in /usr/local/lib/python3.12/dist-packages (26.7.0)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import mysql.connector"
      ],
      "metadata": {
        "id": "QVciyF4QutNR"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "!apt-get update\n",
        "!apt-get install -y mysql-server"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "DX23JlQrvh6c",
        "outputId": "2158f664-9fae-45c5-ef8c-9e05c8598dac"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\r0% [Working]\r            \rHit:1 https://cli.github.com/packages stable InRelease\n",
            "Hit:2 https://cloud.r-project.org/bin/linux/ubuntu jammy-cran40/ InRelease\n",
            "Hit:3 http://archive.ubuntu.com/ubuntu jammy InRelease\n",
            "Hit:4 http://security.ubuntu.com/ubuntu jammy-security InRelease\n",
            "Hit:5 https://r2u.stat.illinois.edu/ubuntu jammy InRelease\n",
            "Hit:6 http://archive.ubuntu.com/ubuntu jammy-updates InRelease\n",
            "Hit:7 http://archive.ubuntu.com/ubuntu jammy-backports InRelease\n",
            "Hit:8 https://ppa.launchpadcontent.net/deadsnakes/ppa/ubuntu jammy InRelease\n",
            "Hit:9 https://ppa.launchpadcontent.net/ubuntugis/ppa/ubuntu jammy InRelease\n",
            "Reading package lists... Done\n",
            "W: Skipping acquire of configured file 'main/source/Sources' as repository 'https://r2u.stat.illinois.edu/ubuntu jammy InRelease' does not seem to provide it (sources.list entry misspelt?)\n",
            "Reading package lists... Done\n",
            "Building dependency tree... Done\n",
            "Reading state information... Done\n",
            "mysql-server is already the newest version (8.0.46-0ubuntu0.22.04.3).\n",
            "0 upgraded, 0 newly installed, 0 to remove and 137 not upgraded.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!service mysql start"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "3eBAWRKvvk5z",
        "outputId": "55b5bc16-c8d6-4a6d-81f3-30e245c892ae"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * Starting MySQL database server mysqld\n",
            "   ...done.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [],
      "metadata": {
        "id": "2yRUrXQjv1NK"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install mysql-connector-python"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "jPVbpRExv15E",
        "outputId": "7c6a817b-1728-4ff0-d116-ba9c612f121e"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Requirement already satisfied: mysql-connector-python in /usr/local/lib/python3.12/dist-packages (26.7.0)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!service mysql start"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "pioXYWprwB-t",
        "outputId": "057d59bf-a6fb-4c5f-e90c-6fcf73abd2e5"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * Starting MySQL database server mysqld\n",
            "   ...done.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!mysql -u root -e \"ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root123';\"\n",
        "!mysql -u root -e \"FLUSH PRIVILEGES;\""
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ouNA44T5aLiq",
        "outputId": "2589b412-fa6a-4d8f-c513-337ab5ebc2ae"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)\n",
            "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import mysql.connector\n",
        "\n",
        "mycon = mysql.connector.connect(\n",
        "    host=\"localhost\",\n",
        "    user=\"root\",\n",
        "    password=\"root123\"\n",
        ")\n",
        "\n",
        "print(\"Connected Successfully!\")\n",
        "mycur = mycon.cursor()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "nypzya7JaRl1",
        "outputId": "0bbe3c20-5201-4ffe-dbff-04588274ca5d"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Connected Successfully!\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "mycur.execute(\"CREATE DATABASE IF NOT EXISTS BankDB\")\n",
        "mycur.execute(\"USE BankDB\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 159
        },
        "id": "VDH0EUeNwGfw",
        "outputId": "da4f28ab-5a43-42e7-a013-107ad52d5775"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "error",
          "ename": "NameError",
          "evalue": "name 'mycur' is not defined",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
            "\u001b[0;32m/tmp/ipykernel_604/208055328.py\u001b[0m in \u001b[0;36m<cell line: 0>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mmycur\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"CREATE DATABASE IF NOT EXISTS BankDB\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      2\u001b[0m \u001b[0mmycur\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"USE BankDB\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mNameError\u001b[0m: name 'mycur' is not defined"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "mycur.execute(\"\"\"\n",
        "CREATE TABLE IF NOT EXISTS Customers(\n",
        "    customer_id INT AUTO_INCREMENT PRIMARY KEY,\n",
        "    first_name VARCHAR(50),\n",
        "    last_name VARCHAR(50),\n",
        "    phone VARCHAR(15),\n",
        "    address VARCHAR(100)\n",
        ")\n",
        "\"\"\")\n",
        "\n",
        "mycur.execute(\"\"\"\n",
        "CREATE TABLE IF NOT EXISTS Accounts(\n",
        "    account_no BIGINT PRIMARY KEY,\n",
        "    customer_id INT,\n",
        "    account_type VARCHAR(20),\n",
        "    balance DECIMAL(10,2),\n",
        "    FOREIGN KEY(customer_id)\n",
        "    REFERENCES Customers(customer_id)\n",
        ")\n",
        "\"\"\")\n",
        "\n",
        "mycur.execute(\"\"\"\n",
        "CREATE TABLE IF NOT EXISTS BankTransactions(\n",
        "    transaction_id INT AUTO_INCREMENT PRIMARY KEY,\n",
        "    account_no BIGINT,\n",
        "    transaction_type VARCHAR(20),\n",
        "    amount DECIMAL(10,2),\n",
        "    transaction_date DATETIME,\n",
        "    FOREIGN KEY(account_no)\n",
        "    REFERENCES Accounts(account_no)\n",
        ")\n",
        "\"\"\")\n",
        "\n",
        "print(\"Tables Created Successfully\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 356
        },
        "id": "OXnNUwELwM08",
        "outputId": "02afce80-d788-46ec-a315-517a6c6df43b"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "error",
          "ename": "ProgrammingError",
          "evalue": "1046 (3D000): No database selected",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mProgrammingError\u001b[0m                          Traceback (most recent call last)",
            "\u001b[0;32m/tmp/ipykernel_4128/2418992575.py\u001b[0m in \u001b[0;36m<cell line: 0>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m mycur.execute(\"\"\"\n\u001b[0m\u001b[1;32m      2\u001b[0m CREATE TABLE IF NOT EXISTS Customers(\n\u001b[1;32m      3\u001b[0m     \u001b[0mcustomer_id\u001b[0m \u001b[0mINT\u001b[0m \u001b[0mAUTO_INCREMENT\u001b[0m \u001b[0mPRIMARY\u001b[0m \u001b[0mKEY\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m     \u001b[0mfirst_name\u001b[0m \u001b[0mVARCHAR\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;36m50\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m     \u001b[0mlast_name\u001b[0m \u001b[0mVARCHAR\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;36m50\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.12/dist-packages/mysql/connector/cursor.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, operation, params, map_results)\u001b[0m\n\u001b[1;32m    414\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    415\u001b[0m         self._handle_result(\n\u001b[0;32m--> 416\u001b[0;31m             self._connection.cmd_query(\n\u001b[0m\u001b[1;32m    417\u001b[0m                 \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_stmt_partition\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"mappable_stmt\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    418\u001b[0m                 \u001b[0mread_timeout\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_read_timeout\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.12/dist-packages/mysql/connector/opentelemetry/context_propagation.py\u001b[0m in \u001b[0;36mwrapper\u001b[0;34m(cnx, *args, **kwargs)\u001b[0m\n\u001b[1;32m    104\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    105\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 106\u001b[0;31m             \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mmethod\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcnx\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    107\u001b[0m         \u001b[0;32mfinally\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    108\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mtp_header\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.12/dist-packages/mysql/connector/_decorating.py\u001b[0m in \u001b[0;36mhandle_cnx_method\u001b[0;34m(cnx, *args, **kwargs)\u001b[0m\n\u001b[1;32m     87\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0merr\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mTimeoutError\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     88\u001b[0m                     \u001b[0mcnx\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mclose\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 89\u001b[0;31m                 \u001b[0;32mraise\u001b[0m \u001b[0merr\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     90\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     91\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mhandle_cnx_method\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.12/dist-packages/mysql/connector/_decorating.py\u001b[0m in \u001b[0;36mhandle_cnx_method\u001b[0;34m(cnx, *args, **kwargs)\u001b[0m\n\u001b[1;32m     83\u001b[0m         ) -> Any:\n\u001b[1;32m     84\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 85\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mcnx_method\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcnx\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     86\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0merr\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     87\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0merr\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mTimeoutError\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.12/dist-packages/mysql/connector/connection.py\u001b[0m in \u001b[0;36mcmd_query\u001b[0;34m(self, query, raw, buffered, raw_as_string, **kwargs)\u001b[0m\n\u001b[1;32m    982\u001b[0m             \u001b[0mwrite_timeout\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mkwargs\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"write_timeout\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    983\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 984\u001b[0;31m             result = self._handle_result(\n\u001b[0m\u001b[1;32m    985\u001b[0m                 self._send_cmd(\n\u001b[1;32m    986\u001b[0m                     \u001b[0mServerCmd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mQUERY\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.12/dist-packages/mysql/connector/_decorating.py\u001b[0m in \u001b[0;36mhandle_cnx_method\u001b[0;34m(cnx, *args, **kwargs)\u001b[0m\n\u001b[1;32m     87\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0merr\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mTimeoutError\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     88\u001b[0m                     \u001b[0mcnx\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mclose\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 89\u001b[0;31m                 \u001b[0;32mraise\u001b[0m \u001b[0merr\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     90\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     91\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mhandle_cnx_method\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.12/dist-packages/mysql/connector/_decorating.py\u001b[0m in \u001b[0;36mhandle_cnx_method\u001b[0;34m(cnx, *args, **kwargs)\u001b[0m\n\u001b[1;32m     83\u001b[0m         ) -> Any:\n\u001b[1;32m     84\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 85\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mcnx_method\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcnx\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     86\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0merr\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     87\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0merr\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mTimeoutError\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.12/dist-packages/mysql/connector/connection.py\u001b[0m in \u001b[0;36m_handle_result\u001b[0;34m(self, packet, read_timeout, write_timeout)\u001b[0m\n\u001b[1;32m    743\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_handle_eof\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpacket\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    744\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mpacket\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m4\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0mERR_STATUS\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 745\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mget_exception\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpacket\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    746\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    747\u001b[0m         \u001b[0;31m# We have a text result set\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mProgrammingError\u001b[0m: 1046 (3D000): No database selected"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "mycur.execute(\"SHOW TABLES\")\n",
        "\n",
        "for table in mycur.fetchall():\n",
        "    print(table[0])"
      ],
      "metadata": {
        "id": "UtRXdESQwVVH"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "mycur.execute(\"\"\"\n",
        "INSERT INTO Customers (first_name, last_name, phone, address)\n",
        "VALUES\n",
        "('John', 'Smith', '1234567890', 'New York'),\n",
        "('Emma', 'Johnson', '2345678901', 'Chicago'),\n",
        "('Michael', 'Brown', '3456789012', 'Los Angeles')\n",
        "\"\"\")\n",
        "\n",
        "mycur.execute(\"\"\"\n",
        "INSERT INTO Accounts (account_no, customer_id, account_type, balance)\n",
        "VALUES\n",
        "(10002, 1, 'Savings', 5000.00),\n",
        "(10003, 2, 'Current', 12000.00),\n",
        "(10004, 3, 'Savings', 8000.00)\n",
        "\"\"\")\n",
        "\n",
        "mycur.execute(\"\"\"\n",
        "INSERT INTO BankTransactions\n",
        "(account_no, transaction_type, amount, transaction_date)\n",
        "VALUES\n",
        "(10001, 'Deposit', 5000.00, NOW()),\n",
        "(10002, 'Deposit', 12000.00, NOW()),\n",
        "(10003, 'Deposit', 8000.00, NOW())\n",
        "\"\"\")\n",
        "\n",
        "mycon.commit()\n",
        "\n",
        "print(\"Sample Data Inserted Successfully\")"
      ],
      "metadata": {
        "id": "EKzypb6Uxdrf"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "mycur.execute(\"SELECT * FROM Customers\")\n",
        "print(\"Customers:\")\n",
        "for row in mycur.fetchall():\n",
        "    print(row)\n",
        "\n",
        "mycur.execute(\"SELECT * FROM Accounts\")\n",
        "print(\"\\nAccounts:\")\n",
        "for row in mycur.fetchall():\n",
        "    print(row)\n",
        "\n",
        "mycur.execute(\"SELECT * FROM BankTransactions\")\n",
        "print(\"\\nBank Transactions:\")\n",
        "for row in mycur.fetchall():\n",
        "    print(row)\n",
        "\n"
      ],
      "metadata": {
        "id": "YdTn_cOey-WS"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Create Account"
      ],
      "metadata": {
        "id": "PMu1HiEXMXJE"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def create_account():\n",
        "    account_no = int(input(\"Enter Account Number: \"))\n",
        "    customer_id = int(input(\"Enter Customer ID: \"))\n",
        "    account_type = input(\"Enter Account Type: \")\n",
        "    balance = float(input(\"Enter Opening Balance: \"))\n",
        "\n",
        "    sql = \"\"\"\n",
        "    INSERT INTO Accounts(account_no, customer_id, account_type, balance)\n",
        "    VALUES(%s, %s, %s, %s)\n",
        "    \"\"\"\n",
        "\n",
        "    values = (\n",
        "        account_no,\n",
        "        customer_id,\n",
        "        account_type,\n",
        "        balance\n",
        "    )\n",
        "\n",
        "    mycur.execute(sql, values)\n",
        "    mycon.commit()\n",
        "\n",
        "    print(\"Account Created Successfully\")"
      ],
      "metadata": {
        "id": "nzQQN6ezYOek"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "create_account()"
      ],
      "metadata": {
        "id": "kK6fKiIVYUPt"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Deposit Money"
      ],
      "metadata": {
        "id": "8hnUkZPKMeGX"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def deposit_money():\n",
        "\n",
        "    acc_no = int(input(\"Enter Account Number : \"))\n",
        "\n",
        "    qry = \"SELECT * FROM Accounts WHERE account_no=%s\"\n",
        "\n",
        "    mycur.execute(qry, (acc_no,))\n",
        "    result = mycur.fetchone()\n",
        "\n",
        "    if result:\n",
        "\n",
        "        amount = float(input(\"Enter Deposit Amount : \"))\n",
        "\n",
        "        # Update Balance\n",
        "        sql = \"\"\"\n",
        "        UPDATE Accounts\n",
        "        SET balance = balance + %s\n",
        "        WHERE account_no = %s\n",
        "        \"\"\"\n",
        "\n",
        "        mycur.execute(sql, (amount, acc_no))\n",
        "        mycon.commit()\n",
        "\n",
        "        # Store Transaction\n",
        "        mycur.execute(\"\"\"\n",
        "        INSERT INTO BankTransactions\n",
        "        (account_no, transaction_type, amount, transaction_date)\n",
        "        VALUES (%s, %s, %s, NOW())\n",
        "        \"\"\", (acc_no, \"Deposit\", amount))\n",
        "\n",
        "        mycon.commit()\n",
        "\n",
        "        print(\"Amount Deposited Successfully.\")\n",
        "\n",
        "    else:\n",
        "\n",
        "        print(\"Account Not Found.\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "GHpUTqrajn6R",
        "outputId": "e30079ff-a0b4-41d6-bd83-21f5dafdc7ef"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Enter Account Number: 1234\n",
            "\n",
            "Account Not Found.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Withdraw Money"
      ],
      "metadata": {
        "id": "4rAhjEPHLpxA"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "def withdraw_money():\n",
        "\n",
        "    acc_no = int(input(\"Enter Account Number : \"))\n",
        "\n",
        "    qry = \"SELECT balance FROM Accounts WHERE account_no = %s\"\n",
        "\n",
        "    mycur.execute(qry, (acc_no,))\n",
        "    result = mycur.fetchone()\n",
        "\n",
        "    if result:\n",
        "\n",
        "        balance = result[0]\n",
        "\n",
        "        amount = float(input(\"Enter Withdrawal Amount : \"))\n",
        "\n",
        "        if amount <= balance:\n",
        "\n",
        "            # Update Balance\n",
        "            sql = \"\"\"\n",
        "            UPDATE Accounts\n",
        "            SET balance = balance - %s\n",
        "            WHERE account_no = %s\n",
        "            \"\"\"\n",
        "\n",
        "            mycur.execute(sql, (amount, acc_no))\n",
        "            mycon.commit()\n",
        "\n",
        "            # Store Transaction\n",
        "            mycur.execute(\"\"\"\n",
        "            INSERT INTO BankTransactions\n",
        "            (account_no, transaction_type, amount, transaction_date)\n",
        "            VALUES (%s, %s, %s, NOW())\n",
        "            \"\"\", (acc_no, \"Withdraw\", amount))\n",
        "\n",
        "            mycon.commit()\n",
        "\n",
        "            print(\"Amount Withdrawn Successfully.\")\n",
        "\n",
        "        else:\n",
        "\n",
        "            print(\"Insufficient Balance.\")\n",
        "\n",
        "    else:\n",
        "\n",
        "        print(\"Account Not Found.\")"
      ],
      "metadata": {
        "id": "NNyeErR2kd_3"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}