8960 Candida - Practical_8.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Name : Candida Ruth Noronha\n",
    "Class : SE COMPS B\n",
    "Roll No. : 8960\n",
    "Title : Python Experiment 8\n",
    "    \n",
    "Aim : To demonstrate CRUD (Create, Read, Update and Delete) operations on database (SQLite/ MySQL) using Python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " Connected to MySQL Server version  8.0.23\n",
      "\n",
      " 1. Create Tables in the Database \n",
      " 2. Insert Records into the table\n",
      " 3. Show Tables in the Database \n",
      " 4. Update Records in the Table\n",
      " 5. Delete Records from the Table \n",
      " 6. Delete Database \n",
      "\n",
      "\n",
      "------------------------------------------------------------------------------------------------\n",
      "\n",
      " Enter your choice : 1\n",
      " Users & Admin tables are created\n",
      "\n",
      "------------------------------------------------------------------------------------------------\n",
      "\n",
      " Enter your choice : 2\n",
      " Records are inserted into Users & Admin tables\n",
      "\n",
      "------------------------------------------------------------------------------------------------\n",
      "\n",
      " Enter your choice : 3\n",
      " Tables in database testdb are:  [('admin',), ('users',)]\n",
      "\n",
      " Records in ('users',) table\n",
      "(8942, 'Gini ', 'Chennai')\n",
      "(8960, 'Lizel ', 'Pune')\n",
      "\n",
      "\n",
      " Records in ('admin',) table\n",
      "(1001, 'Savita', 'Mumbai')\n",
      "\n",
      "------------------------------------------------------------------------------------------------\n",
      "\n",
      " Enter your choice : 4\n",
      " Record updated successfully\n",
      "\n",
      " Records in ('users',) table\n",
      "(8942, 'Gini ', 'Chennai')\n",
      "(8960, 'Candida', 'Mumbai')\n",
      "\n",
      "------------------------------------------------------------------------------------------------\n",
      "\n",
      " Enter your choice : 5\n",
      " Record updated successfully\n",
      "\n",
      " Records in ('users',) table\n",
      "(8960, 'Candida', 'Mumbai')\n",
      "\n",
      "------------------------------------------------------------------------------------------------\n",
      "\n",
      " Enter your choice : 6\n",
      "\n",
      " Database testdb was deleted successfully\n",
      " MySQL connection is closed\n"
     ]
    }
   ],
   "source": [
    "#menu driven\n",
    "import mysql.connector as connector\n",
    "from mysql.connector import Error\n",
    "\n",
    "try:\n",
    "    con = connector.connect(host='localhost', database='testdb', user='root',password='candida1')\n",
    "    if con.is_connected():\n",
    "        db_Info = con.get_server_info()\n",
    "        print(\" Connected to MySQL Server version \", db_Info)\n",
    "        cursor = con.cursor()\n",
    "        print(\"\\n 1. Create Tables in the Database \\n 2. Insert Records into the table\\n 3. Show Tables in the Database \\n 4. Update Records in the Table\\n 5. Delete Records from the Table \\n 6. Delete Database \\n\")       \n",
    "            \n",
    "        while True:\n",
    "            \n",
    "            print(\"\\n------------------------------------------------------------------------------------------------\\n\");\n",
    "\n",
    "            choice = int(input(\" Enter your choice : \"))\n",
    "            if(choice==1):#create tables in db\n",
    "                \n",
    "                query1 = \" CREATE TABLE users(user_id int PRIMARY KEY, user_name varchar(20), city varchar(20));\"\n",
    "                query2 = \" CREATE TABLE admin(admin_id int PRIMARY KEY, admin_name varchar(20), city varchar(20));\"\n",
    "\n",
    "                cursor = con.cursor()\n",
    "                cursor.execute(query1)\n",
    "                cursor.execute(query2)   \n",
    "                print(\" Users & Admin tables are created\")\n",
    "             \n",
    "            elif(choice == 2):\n",
    "                \n",
    "                query1 = \" INSERT INTO users VALUES('8942', 'Gini ', 'Chennai');\"\n",
    "                query2 = \" INSERT INTO users VALUES('8960', 'Lizel ', 'Pune');\"\n",
    "                \n",
    "                query3 = \" INSERT INTO admin VALUES('1001', 'Savita', 'Mumbai');\"\n",
    "\n",
    "                cursor = con.cursor()\n",
    "                cursor.execute(query1)\n",
    "                cursor.execute(query2)   \n",
    "                cursor.execute(query3)\n",
    "\n",
    "                print(\" Records are inserted into Users & Admin tables\")\n",
    "\n",
    "            #show tables in db\n",
    "            elif(choice==3):\n",
    "                \n",
    "                cursor.execute(\" Show tables;\")\n",
    "                tables = cursor.fetchall()\n",
    "                print(\" Tables in database testdb are: \", tables)\n",
    "                sql1=\"SELECT * from users;\"\n",
    "                sql2=\"SELECT * from admin;\"\n",
    "                cursor.execute(sql1)\n",
    "                print(\"\\n Records in {} table\".format(tables[1]))\n",
    "                while True:\n",
    "                    record1=cursor.fetchone()\n",
    "                    if record1==None:\n",
    "                        break\n",
    "                    print (record1)\n",
    "                print(\"\\n\")\n",
    "                \n",
    "                cursor.execute(sql2)  \n",
    "                print(\" Records in {} table\".format(tables[0]))\n",
    "                while True:\n",
    "                    record2=cursor.fetchone()\n",
    "                    if record2==None:\n",
    "                        break\n",
    "                    print (record2)\n",
    "                \n",
    "            #update records in db\n",
    "            elif(choice==4):\n",
    "                \n",
    "                qry=\"UPDATE users SET user_name= 'Candida', city= 'Mumbai' where user_id= ' 8960 ';\"\n",
    "                cursor.execute(qry)\n",
    "                con.commit()\n",
    "                print(\" Record updated successfully\")\n",
    "\n",
    "                cursor.execute(\" Show tables;\")\n",
    "                tables = cursor.fetchall()\n",
    "                sql1=\" SELECT * from users;\"\n",
    "                cursor.execute(sql1)\n",
    "                print(\"\\n Records in {} table\".format(tables[1]))\n",
    "                while True:\n",
    "                    record1=cursor.fetchone()\n",
    "                    if record1==None:\n",
    "                        break\n",
    "                    print (record1)\n",
    "                \n",
    "            #delete records from db table\n",
    "            elif(choice==5):\n",
    "               \n",
    "                qry=\"DELETE from users WHERE user_id='8942';\"\n",
    "                cursor.execute(qry)\n",
    "                con.commit()\n",
    "                print(\" Record updated successfully\")\n",
    "\n",
    "                cursor.execute(\" Show tables;\")\n",
    "                tables = cursor.fetchall()\n",
    "                sql1=\"SELECT * from users;\"\n",
    "                cursor.execute(sql1)\n",
    "                print(\"\\n Records in {} table\".format(tables[1]))\n",
    "                while True:\n",
    "                    record1=cursor.fetchone()\n",
    "                    if record1==None:\n",
    "                        break\n",
    "                    print (record1)\n",
    "                \n",
    "            #delete database       \n",
    "            elif(choice == 6):\n",
    "                \n",
    "                qry=\" Drop database testdb;\"\n",
    "                cursor.execute(qry)\n",
    "                print(\"\\n Database testdb was deleted successfully\")\n",
    "                break\n",
    "except Error as e:\n",
    "    print(\" Error while connecting to MySQL\", e)\n",
    "finally:\n",
    "    if con.is_connected():\n",
    "        cursor.close()\n",
    "        con.close()\n",
    "        print(\" MySQL connection is closed\")"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}