Python_MySQL_P1.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyOR0tI3L4pBZ+841BZ1AGyL",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/Animeshcoder/MySQL-Python/blob/main/Python_MySQL_P1.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### **Introduction**\n",
        "\n",
        "This project is a Python script that uses PyMySQL to connect to a MySQL server, create a new database named medicine, and create a new table named medicines within that database. The medicines table has several columns, including Name, Description, Expiry_date, Number_of_tablets, and Price_per_tablet, which can be used to store information about different medicines. This script demonstrates how to use PyMySQL to interact with a MySQL server and execute SQL statements to create a new database and table."
      ],
      "metadata": {
        "id": "hio0hIqGIsbE"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### **Steps Involved**\n",
        "\n",
        "**Connect to MySQL server**: The code starts by importing the pymysql module and using its connect function to create a connection to a MySQL server running on the local machine (localhost). The connect function takes several arguments, including the host, user, and password of the MySQL server. The resulting connection object represents the connection to the MySQL server.\n",
        "\n",
        "**Create cursor:** Next, the code creates a cursor object by calling the cursor method of the connection object. A cursor is used to execute SQL statements on the MySQL server.\n",
        "\n",
        "**Create database:** The code then uses the cursor to execute an SQL statement that creates a new database named medicine on the MySQL server. This is done by calling the execute method of the cursor object with an SQL CREATE DATABASE statement as its argument.\n",
        "\n",
        "**Select database:** After creating the new database, the code uses the cursor to execute another SQL statement that selects the medicine database as the default database for subsequent SQL statements. This is done by calling the execute method of the cursor object with an SQL USE statement as its argument.\n",
        "\n",
        "**Create table:** The code then uses the cursor to execute an SQL statement that creates a new table named medicines within the selected medicine database. This table has several columns, including Name, Description, Expiry_date, Number_of_tablets, and Price_per_tablet. This is done by calling the execute method of the cursor object with an SQL CREATE TABLE statement as its argument.\n",
        "\n",
        "**Close cursor and connection:** Finally, after creating the new table, the code closes the cursor and connection objects by calling their respective close methods. This releases any resources held by these objects and terminates the connection to the MySQL server."
      ],
      "metadata": {
        "id": "QGtZruZmJTm8"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "DwfzHLVOIDGi"
      },
      "outputs": [],
      "source": [
        "import pymysql\n",
        "\n",
        "# create a connection to the MySQL server\n",
        "connection = pymysql.connect(host=\"yourhost\", user=\"youruser\", password=\"yourpassword\")\n",
        "\n",
        "# create a cursor object to execute SQL statements\n",
        "cursor = connection.cursor()\n",
        "\n",
        "# create a new database\n",
        "cursor.execute(\"CREATE DATABASE medicine\")\n",
        "\n",
        "# select the new database\n",
        "cursor.execute(\"USE medicine\")\n",
        "\n",
        "# create a new table with columns\n",
        "cursor.execute(\"\"\"\n",
        "    CREATE TABLE  medicines(\n",
        "        Name VARCHAR(2000),\n",
        "        Description VARCHAR(2550),\n",
        "        Expiry_date VARCHAR(255),\n",
        "        Number_of_tablets VARCHAR(25),\n",
        "        Price_per_tablet VARCHAR(255)\n",
        "    )\n",
        "\"\"\")\n",
        "\n",
        "# close the cursor and connection\n",
        "cursor.close()\n",
        "connection.close()\n"
      ]
    }
  ]
}