Sqlite data base.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fb8793bf-28c8-4a73-ad87-0fd0d20ca8b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0aea7c3e-f3b3-45c8-bfd9-56c34d45c7e6",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Connect to a table\n",
    "- if table exists it connects to table \n",
    "- if it doesnotexists it creates a new table with a name that you given and connects to that table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cfd1d2d4-0d43-4c4f-a70c-d26b76293144",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Connect to a table \n",
    "    #if table exists it connects to table \n",
    "    #if it doesnotexists it creates a new table with a name that you given and connects to that table\n",
    "conn=sqlite3.connect('customer.db')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b0ee9b2-2a50-449e-80ea-52d6b74f2c88",
   "metadata": {},
   "source": [
    "## to create a corsor\n",
    "- corsor is a middleware acts as a middleware between DATABASE SQL Querys\n",
    "```\n",
    "c=conn.cursor()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc903a10-cc2c-4517-8722-245975ae9211",
   "metadata": {},
   "outputs": [],
   "source": [
    "###to create a corsor\n",
    "    #corsor is a middleware acts as a middleware between DATABASE SQL Querys\n",
    "c=conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc5cc913-f6d3-42fc-a878-3033b69223d6",
   "metadata": {},
   "source": [
    "## create a table using docstring with a six quatation marks with multiple lines of code\n",
    "- ### SQL always should be in doc strink AKA six quatation marks\n",
    "    - SQLite only has six data types\n",
    "        1. NULL -> exist or not\n",
    "        2. INTEGER -> whole number\n",
    "        3. REAL -> decemil number\n",
    "        4. TEXT -> text\n",
    "        5. BLOB -> file extensions\n",
    "- ## ececute will run SQL command from the python code that you given inside the perantasis as string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "afa83399-8a81-4ddd-9c26-2d302153ad24",
   "metadata": {},
   "outputs": [],
   "source": [
    "###create a table using docstring with a six quatation marks with multiple lines of code\n",
    "#SQL always should be in doc strink AKA six quatation marks\n",
    "    #SQLite only has six data types\n",
    "        #NULL -> exist or not\n",
    "        #INTEGER -> whole number\n",
    "        #REAL -> decemil number\n",
    "        #TEXT -> text\n",
    "        #BLOB -> file extensions\n",
    "#.ececute will run SQL command from the python code that you given inside the perantasis as string\n",
    "c.execute(\"\"\"CREATE TABLE custmers \n",
    "                        (first_name text,\n",
    "                        last_name text,\n",
    "                        email text\n",
    "                        )\n",
    "                    \"\"\")# text is a DATATPE of that column\n",
    "#we can do this withonly two quatation marks\n",
    "    #c.execute(\"\"\"CREATE TABLE custmers (first_name text,last_name text,email text)\"\"\")\n",
    "#.commit will sends all commands that we run previously to the database that we created\n",
    "    #without commitit will not save in to the database it just addto ram but not in the data base\n",
    "\n",
    "conn.commit() #commit will execute all the commands done in memory to the database file\n",
    "#close the connection after everythingdone\n",
    "    #python always closses the connection after the file stops running but better to close the connection\n",
    "    ## Without commit data will not store into the data base\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fffe6e6b-aca1-4bbd-9ed6-82deb0719180",
   "metadata": {},
   "outputs": [],
   "source": [
    "###insert the data intlo the database(custmer) table(custmers)\n",
    "conn=sqlite3.connect('customer.db') #connect to data base\n",
    "c=conn.cursor()  #corsor is a middleware acts as a middleware between DATABASE SQL Querys\n",
    "\n",
    "c.execute(\"INSERT INTO custmers VALUES ('jhon','elder','jhon@codemy.com')\") \n",
    "#weused single quotes becausewe already used double quotes outside the query\n",
    "    #to differentiate both we used single quotes\n",
    "#As we have 3 columns in our DataBase we have to pass 3 values in the peranthasis\n",
    "    #1.jhon -> First_name\n",
    "    #2.ellder -> Second_Name\n",
    "    #3.jhon@codemy.com -> email\n",
    "\n",
    "conn.commit()  #commit will execute all the commands done in memory to the database file\n",
    "conn.close()  #Closes the connection between the data base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45039e6c-2c2c-447c-afdd-723857d3b66f",
   "metadata": {},
   "outputs": [],
   "source": [
    "###insert the data intlo the database(custmer) table(custmers)\n",
    "conn=sqlite3.connect('customer.db') #connect to data base\n",
    "c=conn.cursor()  #corsor is a middleware acts as a middleware between DATABASE SQL Querys\n",
    "c.execute(\"INSERT INTO custmers VALUES ('Mary','Brown','mary@codemy.com')\") \n",
    "conn.commit()  #commit will execute all the commands done in memory to the database file\n",
    "conn.close()  #Closes the connection between the data base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "52d5f6f5-bd3c-474a-a82c-6e332dae7178",
   "metadata": {},
   "outputs": [],
   "source": [
    "#insert multiple records into DATABASE\n",
    "conn=sqlite3.connect('customer.db') #connect to data base\n",
    "c=conn.cursor()  #corsor is a middleware acts as a middleware between DATABASE SQL Querys\n",
    "\n",
    "#Insert multiple Records from python list into DATABASE\n",
    "multpl_rcords = [('kiln','left','kiln@test.com'),('srinmu','vas','srinu@test.in'),('michel','jordan','jordan@test.com')] \n",
    "    #python touple with first name ,last name,email inside list\n",
    "c.executemany(\"INSERT INTO custmers VALUES (?,?,?)\",multpl_rcords) \n",
    "    #in SQL we use variable place holder as ? in execute command\n",
    "    #this will take that placeholder from the tuple that we added in the list(tuple) record based on their place holder\n",
    "    #when usingplace holders(AKA ?) add paranthasis around place holders (?)\n",
    "\n",
    "conn.commit()  #commit will execute all the commands done in memory to the database file\n",
    "conn.close()  #Closes the connection between the data base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "64f61f53-8b1c-41f6-9715-c2b3fdb8c701",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Query and Fetch data from DATABASE\n",
    "conn=sqlite3.connect('customer.db') #connect to data base\n",
    "c=conn.cursor()  #corsor is a middleware acts as a middleware between DATABASE SQL Querys\n",
    "\n",
    "c.execute(\"SELECT * FROM custmers\") #it is used to get data from the db\n",
    "\n",
    "#c.fetchone() #to fetch only one record from the last of the table\n",
    "#c.fetchmany(3) #give how many we wanted in the peranthasis \n",
    "                    #it will be indexed from the top to bottom \n",
    "                        #Start from index 0 and it go as we pass the value\n",
    "var3=c.fetchall() #to fetch all records from the table\n",
    "print(var3) #to print data that we featched from db\n",
    "    #we will get a python list from the fetch command\n",
    "\n",
    "conn.commit()  #commit will execute all the commands done in memory to the database file\n",
    "conn.close()  #Closes the connection between the data base"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a07789ac-178d-4539-901c-c74e31fa9ace",
   "metadata": {},
   "outputs": [],
   "source": [
    "##Formate the result that we got from the fetch from the DATA BASE\n",
    "    #Its just a String formation(concatination)\n",
    "var3[2:] #As it was a list we can use indexing to get tformate data\n",
    "for i in var3:\n",
    "    print(i) #to print First Name\n",
    "    #print(i[1]) #to print Last Name\n",
    "    #print(i[2]) #to print Email"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e981724-d6db-4bb6-9e47-8fcdd861d184",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Primary Key ID\n",
    "    #PRimary KEY ID is a unique ID number That each record gets it\n",
    "#Query and Fetch data from DATABASE\n",
    "conn=sqlite3.connect('customer.db') #connect to data base\n",
    "c=conn.cursor()  #corsor is a middleware acts as a middleware between DATABASE SQL Querys\n",
    "\n",
    "c.execute(\"SELECT rowid,* FROM custmers\") #RowID is added befor the * to get the ROWID column from the data base\n",
    "var4=c.fetchall()\n",
    "for i in var4:\n",
    "    print(i)\n",
    "\n",
    "conn.commit()  #commit will execute all the commands done in memory to the database file\n",
    "conn.close()  #Closes the connection between the data base    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b175ea62-e5b6-41d1-8608-a9789b4ee2a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "#to get a specific data\n",
    "conn=sqlite3.connect('customer.db') #connect to data base\n",
    "c=conn.cursor()  #corsor is a middleware acts as a middleware between DATABASE SQL Querys\n",
    "\n",
    "c.execute(\"SELECT * FROM custmers WHERE last_name='Ravi'\") #Where is used to find the value matches with any column and get that record\n",
    "                                                           #we can also use like Where AS a if statement in python\n",
    "var5=c.fetchall()\n",
    "for i in var5:\n",
    "    print(i)\n",
    "\n",
    "conn.commit()  #commit will execute all the commands done in memory to the database file\n",
    "conn.close()  #Closes the connection between the data base    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e69102b0-1996-4108-bc0e-8f5efaf1e6a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Updating record or changing Records\n",
    "conn=sqlite3.connect('customer.db') #connect to data base\n",
    "c=conn.cursor()  #corsor is a middleware acts as a middleware between DATABASE SQL Querys\n",
    "\n",
    "c.execute(\"\"\"UPDATE custmers \n",
    "            SET last_name='Brown' \n",
    "            WHERE rowid='5' \"\"\")#SET is used to change the valuein column where rowid is equal to 3\n",
    "\n",
    "conn.commit()  #commit will execute all the commands done in memory to the database file\n",
    "\n",
    "c.execute(\"SELECT rowid,* FROM custmers\") #Where is used to find the value matches with any column and get that record\n",
    "\n",
    "var6=c.fetchall()\n",
    "for i in var6:\n",
    "    print(i)\n",
    "conn.close()  #Closes the connection between the data base    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0d0a816c-7854-4a4b-87d4-b4fc4c552add",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Deleting a record from the table\n",
    "conn=sqlite3.connect('customer.db') #connect to data base\n",
    "c=conn.cursor()  #corsor is a middleware acts as a middleware between DATABASE SQL Querys\n",
    "\n",
    "c.execute(\"DELETE from custmers WHERE rowid=6\") \n",
    "    #DELETE the record from the costmers table where rowid is equal to 6\n",
    "            #AS the 6 is a numarical we dont need to wrap it in a quatations\n",
    "            #AND once it was deleted it never come back\n",
    "\n",
    "conn.commit()  #commit will execute all the commands done in memory to the database file\n",
    "c.execute(\"SELECT rowid,* FROM custmers\") #Where is used to find the value matches with any column and get that record\n",
    "\n",
    "var6=c.fetchall()\n",
    "for i in var6:\n",
    "    print(i)\n",
    "    \n",
    "conn.close()  #Closes the connection between the data base    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "883b50d1-1bfe-47f5-969f-c891e3ee0ee8",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Ordering the results\n",
    "conn=sqlite3.connect('customer.db') #connect to data base\n",
    "c=conn.cursor()  #corsor is a middleware acts as a middleware between DATABASE SQL Querys\n",
    "\n",
    "c.execute(\"SELECT rowid, * FROM custmers ORDER BY last_name DESC\") #Where is used to find the value matches with any column and get that record\n",
    "\n",
    "var7=c.fetchall ()\n",
    "for i in var7:\n",
    "    print(i)\n",
    "\n",
    "conn.commit()  #commit will execute all the commands done in memory to the database file\n",
    "conn.close()  #Closes the connection between the data base  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "84c86ff3-57ba-4139-a3d7-cb08d3f8c6fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "#AND & OR keywords\n",
    "    #it is used to check more then one thing or get data with multiple conditions\n",
    "conn=sqlite3.connect('customer.db') #connect to data base\n",
    "c=conn.cursor()  #corsor is a middleware acts as a middleware between DATABASE SQL Querys\n",
    "\n",
    "c.execute(\"SELECT rowid, * FROM custmers WHERE last_name LIKE 'Br%' AND rowid=3\")\n",
    "        #AND keyword is used to check multiple conditions in a table and get when all conditions are satisfied\n",
    "            #We can add multiple AND conditions with one query\n",
    "c.execute(\"SELECT rowid, * FROM custmers WHERE last_name LIKE 'Br%' OR rowid=3\") \n",
    "        #OR keyword is used to check multiple conditions in a table and get when any condition is satisfied \n",
    "            #We can add multiple OR conditions with one query\n",
    "\n",
    "var8=c.fetchall ()\n",
    "for i in var8:\n",
    "    print(i)\n",
    "    \n",
    "conn.commit()  #commit will execute all the commands done in memory to the database file\n",
    "conn.close()  #Closes the connection between the data base  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b195d2f4-0335-4e6f-af75-618f1a49e8bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Limit in a Query\n",
    "    #limit is used to limit the no of data we get when quering\n",
    "conn=sqlite3.connect('customer.db') #connect to data base\n",
    "c=conn.cursor()\n",
    "\n",
    "c.execute(\"SELECT rowid,* FROM custmers ORDER BY rowid desc limit 3\") #limit is used to limit the no of record should we get it limits from index 1\n",
    "    \n",
    "var9=c.fetchall ()\n",
    "for i in var9:\n",
    "    print(i)\n",
    "    \n",
    "conn.commit()  #commit will execute all the commands done in memory to the database file\n",
    "conn.close()  #Closes the connection between the data base  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0ad04c67-5941-4c4e-862e-42d981a1fd36",
   "metadata": {},
   "outputs": [],
   "source": [
    "#DELETE A TABLE\n",
    "conn=sqlite3.connect('customer.db') #connect to data base\n",
    "c=conn.cursor()\n",
    "\n",
    "\n",
    "c.execute(\"DROP TABLE custmers\")\n",
    "conn.commit()  #commit will execute all the commands done in memory to the database file\n",
    "c.execute(\"SELECT rowid,* FROM custmers\") \n",
    "        #limit is used to limit the no of record should we get it limits from index 1\n",
    "\n",
    "var9=c.fetchall()\n",
    "for i in var9:\n",
    "    print(i)\n",
    "\n",
    "conn.commit()  #commit will execute all the commands done in memory to the database file\n",
    "conn.close()  #Closes the connection between the data base  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5ba9b230-9b43-4cbc-8144-ac8ba776b671",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Creating Functions to do all tasks we did above and wrap it in a class\n",
    "    #Query DataBase Return all\n",
    "class database:\n",
    "    def __init__():\n",
    "        pass\n",
    "    #Function to print all\n",
    "    def show_all():\n",
    "        conn=sqlite3.connect('customer.db')\n",
    "        c=conn.cursor()\n",
    "        c.execute(\"SELECT rowid,* FROM custmers\") \n",
    "        var9=c.fetchall ()\n",
    "        for i in var9:\n",
    "            print(i)\n",
    "        conn.commit()\n",
    "        conn.close()\n",
    "    #Function to Add a New Record to Table\n",
    "    def add_one(first,last,email):\n",
    "        conn=sqlite3.connect('customer.db')\n",
    "        c=conn.cursor()\n",
    "        c.execute(\"INSERT INTO custmers VALUES (?,?,?)\",(first,last,email))\n",
    "        conn.commit()\n",
    "        conn.close()\n",
    "    #Function to Delete Record to Table\n",
    "    def del_one(id):\n",
    "        conn=sqlite3.connect('customer.db')\n",
    "        c=conn.cursor()\n",
    "        c.execute(\"DELETE from custmers WHERE rowid=(?)\",str(id))\n",
    "        conn.commit()\n",
    "        conn.close()\n",
    "    #Function to insert multiple values in table\n",
    "    def add_many(list):\n",
    "        conn=sqlite3.connect('customer.db')\n",
    "        c=conn.cursor()\n",
    "        c.executemany(\"INSERT INTO custmers VALUES (?,?,?)\",list)\n",
    "        conn.commit()\n",
    "        conn.close()\n",
    "    #Function to WHERE clause\n",
    "    def emain_lookup(email):\n",
    "        conn=sqlite3.connect('customer.db')\n",
    "        c=conn.cursor()\n",
    "        c.execute(\"SELECT * FROM custmers WHERE email = (?)\",(email,))#AS email was a tuplewe have to pass coma after the email\n",
    "        items=c.fetchall()\n",
    "        for i in items:\n",
    "            print(i)\n",
    "        conn.commit()\n",
    "        conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e4396ee-2d87-4646-8336-0e502437a245",
   "metadata": {},
   "outputs": [],
   "source": [
    "database.show_all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d225029-b946-4e16-b09f-7c0fc59caa01",
   "metadata": {},
   "outputs": [],
   "source": [
    "database.add_one('srikanth','annem','srikanthannem@yahoo.com')\n",
    "#write a condition to check wether a record is already present in the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c68d4269-4087-4416-8bd7-b9dd2e3f9c5c",
   "metadata": {},
   "outputs": [],
   "source": [
    "database.del_one(7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8395e45a-6cbe-4c7a-82ef-558601d5b663",
   "metadata": {},
   "outputs": [],
   "source": [
    "stuf=[('lenovo','us','lenovo@gmail.com'),('samsung','us','samsung@yahoo.com')] #list thatwe have to pass into the function\n",
    "database.add_many(stuf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "64d053f0-a8bc-424d-b5b9-6207ee0b6f83",
   "metadata": {},
   "outputs": [],
   "source": [
    "database.emain_lookup('test@gmail.com')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa2bbab8-2bbb-4b2d-88c9-5496aa83f62c",
   "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}