mysql_database.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "61459932",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql as sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "445a7e51",
   "metadata": {},
   "outputs": [],
   "source": [
    "db=sql.connect(host='localhost',port=3306,user='root',password='',database='student')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "9de8c151",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur=db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "188484fd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute(\"select * from data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6bb1d1d6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((1, 'naresh', 'python'), (1, 'nishad', 'python'))"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e2d00dec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('desc data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d8b8343b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('insert into data values(3,\"Vishal\",\"DA\")')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "0b866ee4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute(\"select * from data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "d424c47e",
   "metadata": {},
   "outputs": [],
   "source": [
    "database=cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "c44af725",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['id', 'name', 'subject']\n"
     ]
    }
   ],
   "source": [
    "new=list()\n",
    "for i in database:\n",
    "    new.append(i[0])\n",
    "print(new)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "0f3d9fa2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('desc data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "5cd7625a",
   "metadata": {},
   "outputs": [],
   "source": [
    "database=cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "984b28be",
   "metadata": {},
   "outputs": [],
   "source": [
    "data=[\n",
    "    {\n",
    "        'name':'Vishal',\n",
    "        'subject':'Data Analyst',\n",
    "    },\n",
    "    {\n",
    "        'name':'Anand',\n",
    "        'subject':'FrontEnd',\n",
    "    },\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "2233c1f7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Vishal', 'Data Analyst']\n",
      "[4, 'Vishal', 'Data Analyst']\n",
      "insert into data(id,name,subject)values(4, 'Vishal', 'Data Analyst')\n",
      "['Anand', 'FrontEnd']\n",
      "[5, 'Anand', 'FrontEnd']\n",
      "insert into data(id,name,subject)values(5, 'Anand', 'FrontEnd')\n"
     ]
    }
   ],
   "source": [
    "total_record=cur.execute(\"select * from data\")\n",
    "for i in data:\n",
    "    total_record+=1\n",
    "    v=list(i.values())\n",
    "    print(v)\n",
    "    v.insert(0,total_record)\n",
    "    print(v)\n",
    "    d=tuple(v)\n",
    "    query=f'''insert into data(id,name,subject)values{d}'''\n",
    "    print(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "d9d7512c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter Your Name: lokesh  gehlot\n",
      "Enter Your Subject: php\n"
     ]
    }
   ],
   "source": [
    "name=input('Enter Your Name: ')\n",
    "subject=input('Enter Your Subject: ')\n",
    "name=name.title()\n",
    "subject=subject.upper()\n",
    "query=f'''insert into data(id,name,subject)values('4','{name}','{subject}')'''\n",
    "cur.execute(query)\n",
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "94509cce",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute(\"select * from data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "79e789bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "da=cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "d01aae60",
   "metadata": {},
   "outputs": [],
   "source": [
    "ka=set(da)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "0f4911af",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{1, 2, 3, 4}\n"
     ]
    }
   ],
   "source": [
    "f=set()\n",
    "for  i in ka:\n",
    "    f.add(i[0])\n",
    "print(f)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "033282f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f956c432",
   "metadata": {},
   "outputs": [],
   "source": [
    " "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}