Database.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f6ba6afe",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3 as sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6b636abd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Binary', 'Connection', 'Cursor', 'DataError', 'DatabaseError', 'Date', 'DateFromTicks', 'Error', 'IntegrityError', 'InterfaceError', 'InternalError', 'NotSupportedError', 'OperationalError', 'OptimizedUnicode', 'PARSE_COLNAMES', 'PARSE_DECLTYPES', 'PrepareProtocol', 'ProgrammingError', 'Row', 'SQLITE_ALTER_TABLE', 'SQLITE_ANALYZE', 'SQLITE_ATTACH', 'SQLITE_CREATE_INDEX', 'SQLITE_CREATE_TABLE', 'SQLITE_CREATE_TEMP_INDEX', 'SQLITE_CREATE_TEMP_TABLE', 'SQLITE_CREATE_TEMP_TRIGGER', 'SQLITE_CREATE_TEMP_VIEW', 'SQLITE_CREATE_TRIGGER', 'SQLITE_CREATE_VIEW', 'SQLITE_CREATE_VTABLE', 'SQLITE_DELETE', 'SQLITE_DENY', 'SQLITE_DETACH', 'SQLITE_DONE', 'SQLITE_DROP_INDEX', 'SQLITE_DROP_TABLE', 'SQLITE_DROP_TEMP_INDEX', 'SQLITE_DROP_TEMP_TABLE', 'SQLITE_DROP_TEMP_TRIGGER', 'SQLITE_DROP_TEMP_VIEW', 'SQLITE_DROP_TRIGGER', 'SQLITE_DROP_VIEW', 'SQLITE_DROP_VTABLE', 'SQLITE_FUNCTION', 'SQLITE_IGNORE', 'SQLITE_INSERT', 'SQLITE_OK', 'SQLITE_PRAGMA', 'SQLITE_READ', 'SQLITE_RECURSIVE', 'SQLITE_REINDEX', 'SQLITE_SAVEPOINT', 'SQLITE_SELECT', 'SQLITE_TRANSACTION', 'SQLITE_UPDATE', 'Time', 'TimeFromTicks', 'Timestamp', 'TimestampFromTicks', 'Warning', '__builtins__', '__cached__', '__doc__', '__file__', '__loader__', '__name__', '__package__', '__path__', '__spec__', 'adapt', 'adapters', 'apilevel', 'collections', 'complete_statement', 'connect', 'converters', 'datetime', 'dbapi2', 'enable_callback_tracebacks', 'enable_shared_cache', 'paramstyle', 'register_adapter', 'register_converter', 'sqlite_version', 'sqlite_version_info', 'threadsafety', 'time', 'version', 'version_info']\n"
     ]
    }
   ],
   "source": [
    "print(dir(sql))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "c4ffdca7",
   "metadata": {},
   "outputs": [],
   "source": [
    "db=sql.connect(\"mydatabase1.db\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "500c2b21",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['DataError', 'DatabaseError', 'Error', 'IntegrityError', 'InterfaceError', 'InternalError', 'NotSupportedError', 'OperationalError', 'ProgrammingError', 'Warning', '__call__', '__class__', '__delattr__', '__dir__', '__doc__', '__enter__', '__eq__', '__exit__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__le__', '__lt__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', 'backup', 'close', 'commit', 'create_aggregate', 'create_collation', 'create_function', 'cursor', 'enable_load_extension', 'execute', 'executemany', 'executescript', 'in_transaction', 'interrupt', 'isolation_level', 'iterdump', 'load_extension', 'rollback', 'row_factory', 'set_authorizer', 'set_progress_handler', 'set_trace_callback', 'text_factory', 'total_changes']\n"
     ]
    }
   ],
   "source": [
    "print(dir(db))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "70e3b4ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur=db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "412ecbb8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1d5aefbb8f0>"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute(\"create table data(id int,name varchar(25),city varchar(25))\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "c0f2800e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1d5aefbb8f0>"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('''insert into data(id,name,city) values(1,\"Naresh\",\"Rani\")''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "cd7992f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "ba10d0c1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1d5aefbb8f0>"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute(\"select * from data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "03f3c709",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Naresh', 'Rani')]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "e52262c4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1d5aefbb8f0>"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('''insert into data(id,name,city) values(2,\"nishad\",\"ahmedabad\")''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "6419e397",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1d5aefbb8f0>"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute(\"select * from data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "746f0096",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Naresh', 'Rani'), (2, 'nishad', 'ahmedabad')]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "83d13d5b",
   "metadata": {},
   "outputs": [],
   "source": [
    "data1 = [\n",
    "    {\n",
    "        'id':3,\n",
    "        'name':'jay',\n",
    "        'city':'ahmedabad',\n",
    "    },\n",
    "    {\n",
    "         'id':4,\n",
    "        'name':'kishan',\n",
    "        'city':'ahmedabad',\n",
    "    },\n",
    "    {\n",
    "        'id':5,\n",
    "        'name':'virat',\n",
    "        'city':'ahmedabad',\n",
    "    }\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "091df37a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'id': 3, 'name': 'jay', 'city': 'ahmedabad'}\n",
      "{'id': 4, 'name': 'kishan', 'city': 'ahmedabad'}\n",
      "{'id': 5, 'name': 'virat', 'city': 'ahmedabad'}\n"
     ]
    }
   ],
   "source": [
    "for  i  in data1:\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "fa1d7a63",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id\n",
      "name\n",
      "city\n",
      "id\n",
      "name\n",
      "city\n",
      "id\n",
      "name\n",
      "city\n"
     ]
    }
   ],
   "source": [
    "for  i in data1:\n",
    "    for r in i:\n",
    "        print(r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "b81813e7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id 3\n",
      "name jay\n",
      "city ahmedabad\n",
      "id 4\n",
      "name kishan\n",
      "city ahmedabad\n",
      "id 5\n",
      "name virat\n",
      "city ahmedabad\n"
     ]
    }
   ],
   "source": [
    "for i in data1:\n",
    "    for r in i:\n",
    "        print(r,i[r])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "e350fe95",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id 3\n",
      "name jay\n",
      "city ahmedabad\n",
      "id 4\n",
      "name kishan\n",
      "city ahmedabad\n",
      "id 5\n",
      "name virat\n",
      "city ahmedabad\n"
     ]
    }
   ],
   "source": [
    "for  i in data1:\n",
    "    for r,v in i.items():\n",
    "        print(r,v)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "b4079086",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['id', 'name', 'city']\n",
      "['id', 'name', 'city']\n",
      "['id', 'name', 'city']\n"
     ]
    }
   ],
   "source": [
    "for i in data1:\n",
    "    l=list()\n",
    "    for r in i:\n",
    "        l.append(r)\n",
    "    print(l)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "362fa723",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[3, 'jay', 'ahmedabad']\n",
      "[4, 'kishan', 'ahmedabad']\n",
      "[5, 'virat', 'ahmedabad']\n"
     ]
    }
   ],
   "source": [
    "for i in data1:\n",
    "    l=[*i.values()]\n",
    "    print(l)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "6842a120",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(3, 'jay', 'ahmedabad')\n",
      "(4, 'kishan', 'ahmedabad')\n",
      "(5, 'virat', 'ahmedabad')\n"
     ]
    }
   ],
   "source": [
    "for i in data1:\n",
    "    l=tuple(i.values())\n",
    "    print(l)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "2b8e7689",
   "metadata": {},
   "outputs": [
    {
     "ename": "OperationalError",
     "evalue": "unrecognized token: \"{\"",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "Input \u001b[1;32mIn [73]\u001b[0m, in \u001b[0;36m<cell line: 1>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      2\u001b[0m     l\u001b[38;5;241m=\u001b[39m\u001b[38;5;28mtuple\u001b[39m(i\u001b[38;5;241m.\u001b[39mvalues())\n\u001b[0;32m      3\u001b[0m     query\u001b[38;5;241m=\u001b[39m \u001b[38;5;124mf\u001b[39m\u001b[38;5;124m'''\u001b[39m\u001b[38;5;124minsert into users(id, name, city) values \u001b[39m\u001b[38;5;132;01m{\u001b[39;00mi\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m'''\u001b[39m\n\u001b[1;32m----> 4\u001b[0m     \u001b[43mcur\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[43mquery\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m      6\u001b[0m db\u001b[38;5;241m.\u001b[39mcommit()\n",
      "\u001b[1;31mOperationalError\u001b[0m: unrecognized token: \"{\""
     ]
    }
   ],
   "source": [
    "for i in data1:\n",
    "    l=tuple(i.values())\n",
    "    query= f'''insert into users(id, name, city) values {i}'''\n",
    "    cur.execute(query)\n",
    "\n",
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7cca7484",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "5294cb16",
   "metadata": {},
   "outputs": [],
   "source": [
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "868747bd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1d5aefbb8f0>"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('select * from data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "0afbbc9c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Naresh', 'Rani'), (2, 'nishad', 'ahmedabad')]"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "febfe239",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1d5aefbb8f0>"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('update data set name=\"Naresh kumar\" where id=1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "fadf3b78",
   "metadata": {},
   "outputs": [],
   "source": [
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "9f1110f8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1d5aefbb8f0>"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('select * from data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "afc6c35d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Naresh kumar', 'Rani'), (2, 'nishad', 'ahmedabad')]"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1386b7e0",
   "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
}