Python-MySql DB.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect To Database Server"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Database Connected\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "\n",
    "db = mysql.connector.connect(\n",
    "    host=\"127.0.0.1\",\n",
    "    user=\"root\",\n",
    "    password=\"123456\"\n",
    ")\n",
    "\n",
    "if db.is_connected():\n",
    "    print(\"Database Connected\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Database Created Successful !!!\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "\n",
    "db = mysql.connector.connect(\n",
    "    host=\"127.0.0.1\",\n",
    "    user=\"root\",\n",
    "    password=\"123456\"\n",
    ")\n",
    "\n",
    "cursor = db.cursor()\n",
    "cursor.execute(\"CREATE DATABASE employee_data\")\n",
    "\n",
    "print(\"Database Created Successful !!!\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table Created Successful !!!\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "\n",
    "db = mysql.connector.connect(\n",
    "    host=\"127.0.0.1\",\n",
    "    user=\"root\",\n",
    "    password=\"123456\",\n",
    "    database=\"employee_data\",\n",
    ")\n",
    "\n",
    "cursor = db.cursor()\n",
    "sql = \"\"\"CREATE TABLE customers (\n",
    "  customer_id INT AUTO_INCREMENT PRIMARY KEY,\n",
    "  name VARCHAR(255),\n",
    "  address Varchar(255)\n",
    ")\n",
    "\"\"\"\n",
    "cursor.execute(sql)\n",
    "\n",
    "print(\"Table Created Successful !!!\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Insert One Data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 data added\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "\n",
    "db = mysql.connector.connect(\n",
    "    host=\"127.0.0.1\",\n",
    "    user=\"root\",\n",
    "    password=\"123456\",\n",
    "    database=\"employee_data\",\n",
    ")\n",
    "\n",
    "cursor = db.cursor()\n",
    "sql = \"INSERT INTO customers (name, address) VALUES (%s, %s)\"\n",
    "val = (\"Mr Taif\", \"Dhaka\")\n",
    "cursor.execute(sql, val)\n",
    "\n",
    "db.commit()\n",
    "\n",
    "print(\"{} data added\".format(cursor.rowcount))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Insert Many Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 data added\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "\n",
    "db = mysql.connector.connect(\n",
    "    host=\"127.0.0.1\",\n",
    "    user=\"root\",\n",
    "    password=\"123456\",\n",
    "    database=\"employee_data\",\n",
    ")\n",
    "\n",
    "cursor = db.cursor()\n",
    "sql = \"INSERT INTO customers (name, address) VALUES (%s, %s)\"\n",
    "values = [\n",
    "  (\"Shakib\", \"Magura\"),\n",
    "  (\"Tamin\", \"Ctg\"),\n",
    "  (\"Taskin\", \"Dhaka\"),\n",
    "  (\"Mushfiq\", \"Rajshahi\")\n",
    "]\n",
    "\n",
    "for val in values:\n",
    "  cursor.execute(sql, val)\n",
    "\n",
    "db.commit()\n",
    "\n",
    "print(\"{} data added\".format(cursor.rowcount))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Select "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'Mr Taif', 'Dhaka')\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "\n",
    "db = mysql.connector.connect(\n",
    "    host=\"127.0.0.1\",\n",
    "    user=\"root\",\n",
    "    password=\"123456\",\n",
    "    database=\"employee_data\",\n",
    ")\n",
    "\n",
    "cursor = db.cursor()\n",
    "sql = \"SELECT * FROM customers\"\n",
    "cursor.execute(sql)\n",
    "\n",
    "result = cursor.fetchone()\n",
    "\n",
    "print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fetch All Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'Mr Taif', 'Dhaka')\n",
      "(2, 'Shakib', 'Magura')\n",
      "(3, 'Tamin', 'Ctg')\n",
      "(4, 'Taskin', 'Dhaka')\n",
      "(5, 'Mushfiq', 'Rajshahi')\n",
      "(6, 'Shakib', 'Magura')\n",
      "(7, 'Tamin', 'Ctg')\n",
      "(8, 'Taskin', 'Dhaka')\n",
      "(9, 'Mushfiq', 'Rajshahi')\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "\n",
    "db = mysql.connector.connect(\n",
    "    host=\"127.0.0.1\",\n",
    "    user=\"root\",\n",
    "    password=\"123456\",\n",
    "    database=\"employee_data\",\n",
    ")\n",
    "\n",
    "cursor = db.cursor()\n",
    "sql = \"SELECT * FROM customers\"\n",
    "cursor.execute(sql)\n",
    "\n",
    "results = cursor.fetchall()\n",
    "\n",
    "for data in results:\n",
    "  print(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Delete Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 data deleted\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "\n",
    "db = mysql.connector.connect(\n",
    "    host=\"127.0.0.1\",\n",
    "    user=\"root\",\n",
    "    password=\"123456\",\n",
    "    database=\"employee_data\",\n",
    ")\n",
    "\n",
    "cursor = db.cursor()\n",
    "sql = \"DELETE FROM customers WHERE customer_id=%s\"\n",
    "val = (4, )\n",
    "cursor.execute(sql, val)\n",
    "\n",
    "db.commit()\n",
    "\n",
    "print(\"{} data deleted\".format(cursor.rowcount))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Update Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 data changed\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "\n",
    "db = mysql.connector.connect(\n",
    "    host=\"127.0.0.1\",\n",
    "    user=\"root\",\n",
    "    password=\"123456\",\n",
    "    database=\"employee_data\",\n",
    ")\n",
    "\n",
    "cursor = db.cursor()\n",
    "sql = \"UPDATE customers SET name=%s, address=%s WHERE customer_id=%s\"\n",
    "val = (\"ShakibAL\", \"Dhaka\", 2)\n",
    "cursor.execute(sql, val)\n",
    "\n",
    "db.commit()\n",
    "\n",
    "print(\"{} data changed\".format(cursor.rowcount))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### curds_app"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== APPLICATION DATABASE PYTHON ===\n",
      "1. Insert Data\n",
      "2. Show Data\n",
      "3. Update Data\n",
      "4. Delete Data\n",
      "5. Search Data\n",
      "0. GO Out\n",
      "------------------\n",
      "Choose menu> 2\n",
      "(2, 'ShakibAL', 'Dhaka')\n",
      "(5, 'Mushfiq', 'Rajshahi')\n",
      "(6, 'Shakib', 'Magura')\n",
      "(7, 'Tamin', 'Ctg')\n",
      "(9, 'Mushfiq', 'Rajshahi')\n",
      "=== APPLICATION DATABASE PYTHON ===\n",
      "1. Insert Data\n",
      "2. Show Data\n",
      "3. Update Data\n",
      "4. Delete Data\n",
      "5. Search Data\n",
      "0. GO Out\n",
      "------------------\n",
      "Choose menu> 1\n",
      "Enter Name: 2\n",
      "Enter Address: Dhaka\n",
      "1 data Inserted\n",
      "=== APPLICATION DATABASE PYTHON ===\n",
      "1. Insert Data\n",
      "2. Show Data\n",
      "3. Update Data\n",
      "4. Delete Data\n",
      "5. Search Data\n",
      "0. GO Out\n",
      "------------------\n",
      "Choose menu> 2\n",
      "(2, 'ShakibAL', 'Dhaka')\n",
      "(5, 'Mushfiq', 'Rajshahi')\n",
      "(6, 'Shakib', 'Magura')\n",
      "(7, 'Tamin', 'Ctg')\n",
      "(9, 'Mushfiq', 'Rajshahi')\n",
      "(10, '2', 'Dhaka')\n",
      "=== APPLICATION DATABASE PYTHON ===\n",
      "1. Insert Data\n",
      "2. Show Data\n",
      "3. Update Data\n",
      "4. Delete Data\n",
      "5. Search Data\n",
      "0. GO Out\n",
      "------------------\n",
      "Choose menu> 0\n",
      "=== APPLICATION DATABASE PYTHON ===\n",
      "1. Insert Data\n",
      "2. Show Data\n",
      "3. Update Data\n",
      "4. Delete Data\n",
      "5. Search Data\n",
      "0. GO Out\n",
      "------------------\n"
     ]
    }
   ],
   "source": [
    "import mysql.connector\n",
    "import os\n",
    "\n",
    "db = mysql.connector.connect(\n",
    "    host=\"127.0.0.1\",\n",
    "    user=\"root\",\n",
    "    password=\"123456\",\n",
    "    database=\"employee_data\",\n",
    ")\n",
    "\n",
    "\n",
    "def insert_data(db):\n",
    "  name = input(\"Enter Name: \")\n",
    "  address = input(\"Enter Address: \")\n",
    "  val = (name, address)\n",
    "  cursor = db.cursor()\n",
    "  sql = \"INSERT INTO customers (name, address) VALUES (%s, %s)\"\n",
    "  cursor.execute(sql, val)\n",
    "  db.commit()\n",
    "  print(\"{} data Inserted\".format(cursor.rowcount))\n",
    "\n",
    "\n",
    "def show_data(db):\n",
    "  cursor = db.cursor()\n",
    "  sql = \"SELECT * FROM customers\"\n",
    "  cursor.execute(sql)\n",
    "  results = cursor.fetchall()\n",
    "  \n",
    "  if cursor.rowcount < 0:\n",
    "    print(\"There is not any data\")\n",
    "  else:\n",
    "    for data in results:\n",
    "      print(data)\n",
    "\n",
    "\n",
    "def update_data(db):\n",
    "  cursor = db.cursor()\n",
    "  show_data(db)\n",
    "  customer_id = input(\"Choose id customer> \")\n",
    "  name = input(\"New Name: \")\n",
    "  address = input(\"New Address: \")\n",
    "\n",
    "  sql = \"UPDATE customers SET name=%s, address=%s WHERE customer_id=%s\"\n",
    "  val = (name, address, customer_id)\n",
    "  cursor.execute(sql, val)\n",
    "  db.commit()\n",
    "  print(\"{} data successfully changed\".format(cursor.rowcount))\n",
    "\n",
    "\n",
    "def delete_data(db):\n",
    "  cursor = db.cursor()\n",
    "  show_data(db)\n",
    "  customer_id = input(\"Choose id customer> \")\n",
    "  sql = \"DELETE FROM customers WHERE customer_id=%s\"\n",
    "  val = (customer_id,)\n",
    "  cursor.execute(sql, val)\n",
    "  db.commit()\n",
    "  print(\"{} data successfully deleted\".format(cursor.rowcount))\n",
    "\n",
    "\n",
    "def search_data(db):\n",
    "  cursor = db.cursor()\n",
    "  keyword = input(\"Keyword: \")\n",
    "  sql = \"SELECT * FROM customers WHERE name LIKE %s OR address LIKE %s\"\n",
    "  val = (\"%{}%\".format(keyword), \"%{}%\".format(keyword))\n",
    "  cursor.execute(sql, val)\n",
    "  results = cursor.fetchall()\n",
    "  \n",
    "  if cursor.rowcount < 0:\n",
    "    print(\"There is not any data\")\n",
    "  else:\n",
    "    for data in results:\n",
    "      print(data)\n",
    "\n",
    "\n",
    "def show_menu(db):\n",
    "  print(\"=== APPLICATION DATABASE PYTHON ===\")\n",
    "  print(\"1. Insert Data\")\n",
    "  print(\"2. Show Data\")\n",
    "  print(\"3. Update Data\")\n",
    "  print(\"4. Delete Data\")\n",
    "  print(\"5. Search Data\")\n",
    "  print(\"0. GO Out\")\n",
    "  print(\"------------------\")\n",
    "  menu = input(\"Choose menu> \")\n",
    "\n",
    "  #clear screen\n",
    "  os.system(\"clear\")\n",
    "\n",
    "  if menu == \"1\":\n",
    "    insert_data(db)\n",
    "  elif menu == \"2\":\n",
    "    show_data(db)\n",
    "  elif menu == \"3\":\n",
    "    update_data(db)\n",
    "  elif menu == \"4\":\n",
    "    delete_data(db)\n",
    "  elif menu == \"5\":\n",
    "    search_data(db)\n",
    "  elif menu == \"0\":\n",
    "    exit()\n",
    "  else:\n",
    "    print(\"Menu WRONG!\")\n",
    "\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "  while(True):\n",
    "    show_menu(db)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}