import sqlite3
conn = sqlite3.connect("sqlite.db") # directly create and connect the data base
conn.execute('''
Create table student(
st_id INTEGER PRIMARY KEY AUTOINCREMENT,
st_name VARCHAR(50),
st_class VARCHAR (10),
st_email VARCHAR(30)
)
'''
)
conn.close() # if it is run twice then it will show table already exists
# download the bdbrowser to see the created database, open db browser and goto the open 
#file where the data is stored,the location, open it and browse it.
# while inserting we dont need execute before create table content but have to connect the database

Insert the data in database

import sqlite3
conn = sqlite3.connect("sqlite.db")
ins = '''
insert into student (st_name,st_class,st_email) VALUES ('SHY','10TH',"[[email protected]](<mailto:[email protected]>)")
'''
conn.execute(ins) # connecting the database using conn variable and executing and storing the ins var or the

Data present in ins

conn.commit() # while updating or inserting the data we have to use commit keyword
conn.close()

After executing the program, we can change the value, and the new value will be stored in our database.

Select Query

import sqlite3
conn = sqlite3.connect("sqlite.db")
data = conn.execute("SELECT * FROM student")  # It will show or select the data from the  student table
print("S")
print("ID, NAME, CLASS, EMAIL")  # it can be used as label
for n in data:  # the  selected data from the table will be stored in var n and we can print it in the terminal
	 print(n)  # we can use as tuple aswell
	 print(n[1])  # index number is passed and we can get the vale according to tuple index value

Limit Query

import sqlite3
conn = sqlite3.connect("sqlite.db")
data = conn.execute("SELECT * FROM student limit 1, 2") 
# 1 (after 1 data it will show and 2 is for only show
# 2 data): after 1 row it will give 2 other data,
print("ID, NAME, CLASS, EMAIL")
	for n in data:
		print(n)

Delete Query

import sqlite3
conn = sqlite3.connect("sqlite.db")
st_id = input("Enter the student id : -")
conn.execute("DELETE FROM student where st_id=" + st_id)
conn.commit()  # whenever we make some changes in database we use this commit function
conn.close()

Select Query

import sqlite3
conn = sqlite3.connect("sqlite.db")
data = conn.execute("SELECT * FROM student")  
# It will show or select the data from the  student table
print("S")
print("ID, NAME, CLASS, EMAIL")  # it can be used as label
	for n in data:  # the  selected data from the table will be stored in var n and we can print it in the terminal
		print(n)  # we can use as tuple aswell

Update Query

import sqlite3
conn = sqlite3.connect("sqlite.db")
conn.execute('''
update student set st_name='ram1',st_class='12' where st_id=1
'''
)  # we can take input from the user and concatenate it
conn.commit()
conn.close()

Searching or Filtering in Queries

data = conn.execute("SELECT * FROM student where st_name='ram1'")
	for a in data:
		print(a)

import sqlite3
conn1 = sqlite3.connect("sqlite.db")
#User input and by any letter, like queries is used
print(" ")
st_name = input("Enter the user name")
st_class= input("Enter the Class ")
# data = conn.execute("SELECT * FROM student where st_name='"+st_name+"'")
data = conn1.execute("SELECT * FROM student where st_name like '%"+st_name+"%' and st_class = '"+st_class+"'") 
# and will give if two condition is true and aslo we can use or for any one condition is true
for a in data:
	print(a[0],    a[1],      a[2])