database_practice.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "7b40dad6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3 as sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d5618a69",
   "metadata": {},
   "outputs": [],
   "source": [
    "db=sql.connect(\"mypracticedatabase.db\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ca44f853",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur=db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "29b656b0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1b4e5ca0880>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('''create table users(id int, name varchar(25), address varchar(30), mobile int(10))''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1d8382c0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1b4e5ca0880>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('''insert into users(id,name,address,mobile) values(1,'Naresh','Pali',8003523012)''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "9ac0b298",
   "metadata": {},
   "outputs": [],
   "source": [
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "006f4989",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1b4e5ca0880>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute(\"select * from users\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "ac961163",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Naresh', 'Pali', 8003523012)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "4ba54999",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1b4e5ca0880>"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('''insert into users(id,name,address,mobile)values(2,\"Nishad\", \"Ahmedabad\",0123456789)''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "b5c277a1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "6a092f3c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1b4e5ca0880>"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute(\"select * from users\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "5a0affb7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Naresh', 'Pali', 8003523012), (2, 'Nishad', 'Ahmedabad', 123456789)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "dd3da099",
   "metadata": {},
   "outputs": [],
   "source": [
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "7b599a46",
   "metadata": {},
   "outputs": [],
   "source": [
    "data=[\n",
    "    {\n",
    "        'id':3,\n",
    "        'name':\"vishal\",\n",
    "        'address':\"ahmedabad\",\n",
    "        'mobile':9876543210,\n",
    "    },\n",
    "    {\n",
    "        'id':4,\n",
    "        'name':\"Anand\",\n",
    "        'address':\"Rani\",\n",
    "        'mobile':9876543012,\n",
    "    }\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "1bfb4211",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1b4e5ca0880>"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute(\"select * from users\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "fbdc0155",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Naresh', 'Pali', 8003523012),\n",
       " (2, 'Nishad', 'Ahmedabad', 123456789),\n",
       " (4, 'Anand', 'Rani', 9876543012)]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "4623505f",
   "metadata": {},
   "outputs": [],
   "source": [
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "92ebd96a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1b4e5ca0880>"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute(\"select * from users\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "bc835c15",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Naresh', 'Pali', 8003523012), (2, 'Nishad', 'Ahmedabad', 123456789)]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "58003a38",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "insert into users(id,name,address,mobile)values(3, 'vishal', 'ahmedabad', 9876543210)\n",
      "insert into users(id,name,address,mobile)values(4, 'Anand', 'Rani', 9876543012)\n"
     ]
    }
   ],
   "source": [
    "for i in data:\n",
    "    d=tuple(i.values())\n",
    "    query= f'''insert into users(id,name,address,mobile)values{d}'''\n",
    "    print(query)\n",
    "    cur.execute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "eb87cdea",
   "metadata": {},
   "outputs": [],
   "source": [
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "40e045a9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1b4e5ca0880>"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute(\"select * from users\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "eb36575a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Naresh', 'Pali', 8003523012),\n",
       " (2, 'Nareshsisodiya', 'Ahmedabad', 123456789),\n",
       " (4, 'Anand', 'Rani', 9876543012),\n",
       " (3, 'vishal', 'ahmedabad', 9876543210),\n",
       " (4, 'Anand', 'Rani', 9876543012)]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "8b7a6387",
   "metadata": {},
   "outputs": [],
   "source": [
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "386d7bed",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1b4e5ca0880>"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('''update users set name= \"Nareshsisodiya\" where id=2 ''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "d6eeae6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "2f825c83",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1b4e5ca0880>"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute(\"select * from Users\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "43f74018",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Naresh', 'Pali', 8003523012),\n",
       " (2, 'Nareshsisodiya', 'Ahmedabad', 123456789),\n",
       " (4, 'Anand', 'Rani', 9876543012)]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "1ce9e7a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql as sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "53990e9b",
   "metadata": {},
   "outputs": [
    {
     "ename": "OperationalError",
     "evalue": "(1049, \"Unknown database 'mypracticedatabase.db'\")",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "Input \u001b[1;32mIn [4]\u001b[0m, in \u001b[0;36m<cell line: 1>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[0m db\u001b[38;5;241m=\u001b[39m\u001b[43msql\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mconnect\u001b[49m\u001b[43m(\u001b[49m\u001b[43mhost\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mlocalhost\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43mport\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;241;43m3306\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43muser\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mroot\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43mpassword\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43mdatabase\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mmypracticedatabase.db\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\anaconda3\\lib\\site-packages\\pymysql\\connections.py:353\u001b[0m, in \u001b[0;36mConnection.__init__\u001b[1;34m(self, user, password, host, database, unix_socket, port, charset, sql_mode, read_default_file, conv, use_unicode, client_flag, cursorclass, init_command, connect_timeout, read_default_group, autocommit, local_infile, max_allowed_packet, defer_connect, auth_plugin_map, read_timeout, write_timeout, bind_address, binary_prefix, program_name, server_public_key, ssl, ssl_ca, ssl_cert, ssl_disabled, ssl_key, ssl_verify_cert, ssl_verify_identity, compress, named_pipe, passwd, db)\u001b[0m\n\u001b[0;32m    351\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_sock \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mNone\u001b[39;00m\n\u001b[0;32m    352\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[1;32m--> 353\u001b[0m     \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mconnect\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\anaconda3\\lib\\site-packages\\pymysql\\connections.py:633\u001b[0m, in \u001b[0;36mConnection.connect\u001b[1;34m(self, sock)\u001b[0m\n\u001b[0;32m    630\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_next_seq_id \u001b[38;5;241m=\u001b[39m \u001b[38;5;241m0\u001b[39m\n\u001b[0;32m    632\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_get_server_information()\n\u001b[1;32m--> 633\u001b[0m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_request_authentication\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    635\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39msql_mode \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[0;32m    636\u001b[0m     c \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mcursor()\n",
      "File \u001b[1;32m~\\anaconda3\\lib\\site-packages\\pymysql\\connections.py:907\u001b[0m, in \u001b[0;36mConnection._request_authentication\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    904\u001b[0m     data \u001b[38;5;241m+\u001b[39m\u001b[38;5;241m=\u001b[39m struct\u001b[38;5;241m.\u001b[39mpack(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mB\u001b[39m\u001b[38;5;124m\"\u001b[39m, \u001b[38;5;28mlen\u001b[39m(connect_attrs)) \u001b[38;5;241m+\u001b[39m connect_attrs\n\u001b[0;32m    906\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mwrite_packet(data)\n\u001b[1;32m--> 907\u001b[0m auth_packet \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_read_packet\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    909\u001b[0m \u001b[38;5;66;03m# if authentication method isn't accepted the first byte\u001b[39;00m\n\u001b[0;32m    910\u001b[0m \u001b[38;5;66;03m# will have the octet 254\u001b[39;00m\n\u001b[0;32m    911\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m auth_packet\u001b[38;5;241m.\u001b[39mis_auth_switch_request():\n",
      "File \u001b[1;32m~\\anaconda3\\lib\\site-packages\\pymysql\\connections.py:725\u001b[0m, in \u001b[0;36mConnection._read_packet\u001b[1;34m(self, packet_type)\u001b[0m\n\u001b[0;32m    723\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_result \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m \u001b[38;5;129;01mand\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_result\u001b[38;5;241m.\u001b[39munbuffered_active \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mTrue\u001b[39;00m:\n\u001b[0;32m    724\u001b[0m         \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_result\u001b[38;5;241m.\u001b[39munbuffered_active \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mFalse\u001b[39;00m\n\u001b[1;32m--> 725\u001b[0m     \u001b[43mpacket\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mraise_for_error\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    726\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m packet\n",
      "File \u001b[1;32m~\\anaconda3\\lib\\site-packages\\pymysql\\protocol.py:221\u001b[0m, in \u001b[0;36mMysqlPacket.raise_for_error\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m    219\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m DEBUG:\n\u001b[0;32m    220\u001b[0m     \u001b[38;5;28mprint\u001b[39m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124merrno =\u001b[39m\u001b[38;5;124m\"\u001b[39m, errno)\n\u001b[1;32m--> 221\u001b[0m \u001b[43merr\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mraise_mysql_exception\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_data\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\anaconda3\\lib\\site-packages\\pymysql\\err.py:143\u001b[0m, in \u001b[0;36mraise_mysql_exception\u001b[1;34m(data)\u001b[0m\n\u001b[0;32m    141\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m errorclass \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[0;32m    142\u001b[0m     errorclass \u001b[38;5;241m=\u001b[39m InternalError \u001b[38;5;28;01mif\u001b[39;00m errno \u001b[38;5;241m<\u001b[39m \u001b[38;5;241m1000\u001b[39m \u001b[38;5;28;01melse\u001b[39;00m OperationalError\n\u001b[1;32m--> 143\u001b[0m \u001b[38;5;28;01mraise\u001b[39;00m errorclass(errno, errval)\n",
      "\u001b[1;31mOperationalError\u001b[0m: (1049, \"Unknown database 'mypracticedatabase.db'\")"
     ]
    }
   ],
   "source": [
    "db=sql.connect(host=\"localhost\",port=3306,user='root',password='',database='mypracticedatabase.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "56f8b49f",
   "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
}