 database_practice.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c622e8bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3 as sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "87ec9364",
   "metadata": {},
   "outputs": [],
   "source": [
    "db = sql.connect(\"mydatabasepractice.db\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "8d4d6ab8",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur = db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "ab2de4c2",
   "metadata": {},
   "outputs": [
    {
     "ename": "OperationalError",
     "evalue": "table users already exists",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "Input \u001b[1;32mIn [18]\u001b[0m, in \u001b[0;36m<cell line: 1>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[0m \u001b[43mcur\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mcreate table users(id int, name varchar(25), email varchar(25))\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\n",
      "\u001b[1;31mOperationalError\u001b[0m: table users already exists"
     ]
    }
   ],
   "source": [
    "cur.execute(\"create table users(id int, name varchar(25), email varchar(25))\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "14299036",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1cde35ac1f0>"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('''insert into users(id,name,email) values('1','nishad','mehtanishad@gmail.com')''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "052ba517",
   "metadata": {},
   "outputs": [],
   "source": [
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "e716337c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1cde35ac1f0>"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute(\"select * from users\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "e3358eae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'nishad', 'mehtanishad@gmail.com'),\n",
       " (1, 'nishad', 'mehtanishad@gmail.com')]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "19bc8a48",
   "metadata": {},
   "outputs": [],
   "source": [
    "data = [\n",
    "    {\n",
    "        'id' : 2,\n",
    "        'name' : 'naresh',\n",
    "        'email' : 'naresh96@gmail.com',\n",
    "    },\n",
    "    {\n",
    "        'id' : 3,\n",
    "        'name' : 'vishal',\n",
    "        'email' : 'vishad@gmail.com',\n",
    "    }\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "c4844147",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'id': 2, 'name': 'naresh', 'email': 'naresh96@gmail.com'}\n",
      "{'id': 3, 'name': 'vishal', 'email': 'vishad@gmail.com'}\n"
     ]
    }
   ],
   "source": [
    "for i in data:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "13f464b0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id\n",
      "name\n",
      "email\n",
      "id\n",
      "name\n",
      "email\n"
     ]
    }
   ],
   "source": [
    "for i in data:\n",
    "    for r in i:\n",
    "        print(r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "50678ebe",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id 2\n",
      "name naresh\n",
      "email naresh96@gmail.com\n",
      "id 3\n",
      "name vishal\n",
      "email vishad@gmail.com\n"
     ]
    }
   ],
   "source": [
    "for i in data:\n",
    "    for r in i:\n",
    "        print(r,i[r])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "ac7f5bf0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id 2\n",
      "name naresh\n",
      "email naresh96@gmail.com\n",
      "id 3\n",
      "name vishal\n",
      "email vishad@gmail.com\n"
     ]
    }
   ],
   "source": [
    "for i in data:\n",
    "    for k,v in i.items():\n",
    "        print(k,v)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "d718349b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2\n",
      "naresh\n",
      "naresh96@gmail.com\n",
      "3\n",
      "vishal\n",
      "vishad@gmail.com\n"
     ]
    }
   ],
   "source": [
    "for i in data:\n",
    "    for r in i.values():\n",
    "        print(r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "852c926d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['id', 'name', 'email']\n",
      "['id', 'name', 'email']\n"
     ]
    }
   ],
   "source": [
    "for i in data:\n",
    "    d = list()\n",
    "    for r in i:\n",
    "        d.append(r)\n",
    "    print(d)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "b05b28b6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[2, 'naresh', 'naresh96@gmail.com']\n",
      "[3, 'vishal', 'vishad@gmail.com']\n"
     ]
    }
   ],
   "source": [
    "for i in data:\n",
    "    d =[*i.values()]\n",
    "    print(d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "bd4c3557",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "insert into users(id, name, email) values(2, 'naresh', 'naresh96@gmail.com')\n",
      "insert into users(id, name, email) values(3, 'vishal', 'vishad@gmail.com')\n"
     ]
    }
   ],
   "source": [
    "for i in data:\n",
    "    d = tuple(i.values())\n",
    "    \n",
    "    query = f'''insert into users(id, name, email) values{d}'''\n",
    "    print(query)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "d78f9e8f",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in data:\n",
    "    d = tuple(i.values())\n",
    "    \n",
    "    query = f''' insert into users(id, name, email) values{d}'''\n",
    "    cur.execute(query)\n",
    "\n",
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "ef74cc7e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1cde35ac1f0>"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('select * from users')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "520946b5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'nishad', 'mehtanishad@gmail.com'),\n",
       " (1, 'nishad', 'mehtanishad@gmail.com'),\n",
       " (2, 'naresh', 'naresh96@gmail.com'),\n",
       " (3, 'vishal', 'vishad@gmail.com')]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "87728ee8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1cde35ac1f0>"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('update users set name = \"nishad mehta, nishad\" where id = 2')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "77569f19",
   "metadata": {},
   "outputs": [],
   "source": [
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "511eb267",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create new cennection\n",
    "import pymysql as sql\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "98117341",
   "metadata": {},
   "outputs": [
    {
     "ename": "OperationalError",
     "evalue": "(1049, \"Unknown database 'nov_database'\")",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "Input \u001b[1;32mIn [52]\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[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[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[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[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;43mnov_database\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 'nov_database'\")"
     ]
    }
   ],
   "source": [
    "db = sql.connect(host=\"localhost\", port=3306, user=\"root\", password='', database=\"nov_database\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a99ca22c",
   "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
}