Database.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a89f8c2b",
   "metadata": {},
   "source": [
    "# Mysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "08f04603",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: mysql-connector-python in c:\\programdata\\anaconda3\\lib\\site-packages (8.0.26)\n",
      "Requirement already satisfied: protobuf>=3.0.0 in c:\\programdata\\anaconda3\\lib\\site-packages (from mysql-connector-python) (3.17.3)\n",
      "Requirement already satisfied: six>=1.9 in c:\\programdata\\anaconda3\\lib\\site-packages (from protobuf>=3.0.0->mysql-connector-python) (1.15.0)\n"
     ]
    }
   ],
   "source": [
    "!pip install mysql-connector-python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f1daa8c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector as conn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "32c5492c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<mysql.connector.connection.MySQLConnection at 0x2286add4370>"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db = conn.connect(\n",
    "    host=\"localhost\", #127.0.0.1\n",
    "    user=\"root\",\n",
    "    password=\"\"\n",
    ")\n",
    "\n",
    "db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1b1a3cbc",
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    db = conn.connect(\n",
    "    host=\"localhost\",\n",
    "    user=\"root\",\n",
    "    password=\"\")\n",
    "except Exception as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "434ed3e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a database\n",
    "def database_connection(host='localhost', user='root', password='', database=''):\n",
    "    try:\n",
    "        db = conn.connect(\n",
    "            host=host,\n",
    "            user=user,\n",
    "            password=password,\n",
    "            database=database)\n",
    "\n",
    "        return db\n",
    "    except Exception as e:\n",
    "        print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ea9a19f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_database(db_name):\n",
    "    try:\n",
    "        # search the db\n",
    "        db = database_connection()\n",
    "        # sql query\n",
    "        query = \"CREATE DATABASE \"+db_name\n",
    "        cursor = db.cursor()\n",
    "        cursor.execute(query)\n",
    "        cursor.close()\n",
    "        db.close()\n",
    "    except Exception as e:\n",
    "        print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "ba6c5ce0",
   "metadata": {},
   "outputs": [],
   "source": [
    "create_database(\"employee\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "fdf38354",
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_table(db_name, table_name):\n",
    "    try:\n",
    "        db = database_connection(host='localhost', user='root', password='', database=db_name)\n",
    "        # sql query\n",
    "        query = \"CREATE TABLE {}(id integer primary key auto_increment, name varchar(255), dept varchar(255))\".format(table_name)\n",
    "        cursor = db.cursor()\n",
    "        cursor.execute(query)\n",
    "        db.commit()\n",
    "        cursor.close()\n",
    "        db.close()\n",
    "    except Exception as e:\n",
    "        print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "fabd2907",
   "metadata": {},
   "outputs": [],
   "source": [
    "create_table('employee', 'emp_info')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "96151420",
   "metadata": {},
   "outputs": [],
   "source": [
    "def delete_table(db_name, table_name):\n",
    "    try:\n",
    "        db = database_connection(host='localhost', user='root', password='', database=db_name)\n",
    "        # sql query\n",
    "        query = \"DROP TABLE if exists \"+table_name\n",
    "        cursor = db.cursor()\n",
    "        cursor.execute(query)\n",
    "        db.commit()\n",
    "        cursor.close()\n",
    "        db.close()\n",
    "    except Exception as e:\n",
    "        print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "907651cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "delete_table('employee', 'emp_info')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "7c766eca",
   "metadata": {},
   "outputs": [],
   "source": [
    "def insert_value(db_name, table_name, emp_id, name, dept):\n",
    "    try:\n",
    "        db = database_connection(host='localhost', user='root', password='', database=db_name)\n",
    "        # sql query\n",
    "        query = \"INSERT INTO {}(id, name, dept) VALUES(%s, %s, %s)\".format(table_name)\n",
    "        val = (emp_id, name, dept)\n",
    "        cursor = db.cursor()\n",
    "        cursor.execute(query, val)\n",
    "        db.commit()\n",
    "        cursor.close()\n",
    "        db.close()\n",
    "    except Exception as e:\n",
    "        print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "269567e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "delete_table('employee', 'employee')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "d57bf824",
   "metadata": {},
   "outputs": [],
   "source": [
    "create_table('employee', 'emp_info')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "b42a70db",
   "metadata": {},
   "outputs": [],
   "source": [
    "insert_value('employee', 'emp_info', 1, 'hasan', 'seles')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "cd389bf1",
   "metadata": {},
   "outputs": [],
   "source": [
    "def fetch_value(db_name, tab_name):\n",
    "    try:\n",
    "        db = database_connection(host='localhost', user='root', password='', database=db_name)\n",
    "        # sql query\n",
    "        query = \"SELECT * FROM \"+tab_name\n",
    "        cursor = db.cursor()\n",
    "        cursor.execute(query)\n",
    "        res = cursor.fetchall()\n",
    "        for i in res:\n",
    "            print(i)\n",
    "        cursor.close()\n",
    "        db.close()\n",
    "    except Exception as e:\n",
    "        print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "3f7feece",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'hasan', 'seles')\n"
     ]
    }
   ],
   "source": [
    "fetch_value('employee', 'emp_info')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "6cd47667",
   "metadata": {},
   "outputs": [],
   "source": [
    "def delete_row(db_name, tab_name, emp_id):\n",
    "    try:\n",
    "        db = database_connection(host='localhost', user='root', password='', database=db_name)\n",
    "        # sql query\n",
    "        query = \"DELETE FROM {} WHERE id={}\".format(tab_name, emp_id)\n",
    "        cursor = db.cursor()\n",
    "        cursor.execute(query)\n",
    "        db.commit()\n",
    "        cursor.close()\n",
    "        db.close()\n",
    "    except Exception as e:\n",
    "        print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "1992cdd6",
   "metadata": {},
   "outputs": [],
   "source": [
    "delete_row('employee', 'emp_info', 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "bfc59a86",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2, 'Jahid hasan', 'IT')\n"
     ]
    }
   ],
   "source": [
    "fetch_value('employee', 'emp_info')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "ca066e49",
   "metadata": {},
   "outputs": [],
   "source": [
    "insert_value('employee', 'emp_info', 2, 'Jahid hasan', 'IT')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8682d503",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}