Extract records

• import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user=“root",
passwd="",
database="mydatabase"
)
x = mydb.cursor()
x.execute("SELECT * FROM student")
result = x.fetchall()
for y in result:
print(y)
Extracting records
• import mysql.connector

mydb = mysql.connector.connect(
host="localhost",user=“root",passwd="",database="mydatabase")
x = mydb.cursor()
n=input(“enter a name to search”)
• sql="select * from student where stdname='"+n+"'“
• x.execute(sql)
result =x.fetchall()

for y in result:
print(y)
Fetchone() and fetchmany()
• Fetchone() :sql="select * from student”
• x.execute(sql)
result =x.fetchone()
for y in result:
print(y)
• It displays only one record
Fetchmany(n)
• sql="select * from student”
• x.execute(sql)
result =x.fetchmany(4)
for y in result:
print(y)
• It shows 4 records.
Parameterized query
• Display records whose total mark >m
Import mysql.connector
con=mysql.connector.connect(host="localhost",user="root",password="",dat
abase="aisect")
x=con.cursor()
m=int(input(“enter total mark”))
Sql=“select * from student where total>%s”%(m,)
x.execute(sql)
Data=x.fetchall()
For I in Data:
print(i)
 Parameterized query
• Display records whose total mark >m and class=n
• Import mysql.connector
• con=mysql.connector.connect(host="localhost",user="root",password="",da
tabase="aisect")
• x=con.cursor()
• m=int(input(“enter total mark”))
• n=input(“enter class”)
• Sql=“select * from student where total>%s and class=‘%s’”%(m,n)
• x.execute(sql)
• Data=x.fetchall()
• For I in Data:
• print(i)
 Parameterized query
• Import mysql.connector
• con=mysql.connector.connect(host="localhost",user="root",password="",database
="aisect")
• x=con.cursor()
• m=int(input(“enter total mark”))
• n=input(“enter class”)
• sql="select * from student where mark>%s and address='%s'"%(m,n)
• or
• sql="select * from student where mark>{} and address='{}'".format(m,n)
• x.execute(sql)
• Data=x.fetchall()
• For I in Data:
• print(i)
 Display id field in combo box
• from tkinter import *
• from tkinter import ttk
• from tkinter import messagebox
• import mysql.connector
• con=mysql.connector.connect(host="localhost",user="root",password="",database="babi")
• cur=con.cursor()
• cur.execute("SELECT sname FROM student")
• sn=cur.fetchall()
• def show(event):
• a=n.get()
• messagebox.showinfo("",a)
• main=Tk()
• n=StringVar()
• combo=ttk.Combobox(main,textvariable=n)
• combo['values']=sn
• combo.bind("<<ComboboxSelected>>",show)
• combo.pack()
 DISPLAY THE PHONE NOS WHO ARE FROM BBSR
• import mysql.connector
• mydb =
mysql.connector.connect(host="localhost",user="root",passwd="",database="python")
• x=mydb.cursor()
• a=[]
• sql="SELECT * FROM student"
• x.execute(sql)
• result =x.fetchall()
• a=list(result)
• for y in a:
• if y[1]=="bbsr":
• print(y[2])
 Update table
• import mysql.connector
mydb =
mysql.connector.connect(host="localhost",user=“root",passwd="",
database="mydatabase")
x = mydb.cursor()
sql = "UPDATE student SET sname = ‘rajesh hota' WHERE roll=2"
x.execute(sql)
mydb.commit()
print(x.rowcount, "record(s) affected")
 Update table by getting input value
• import mysql.connector
• con=mysql.connector.connect(host="localhost",user="root",password="",database="studmark")
• x=con.cursor()
• n=int(input("enter a roll no"))
• x.execute("select * from mark where roll="+str(n))
• data=x.fetchall()
• record=list(data)
• for i in record:
• tot=i[1]+i[2]+i[3]
• sql="update mark set tot=%s where roll=%s"%(tot,n)
• x.execute(sql)
• con.commit()
• if (x):
• print("updated successfully")
 Delete the table
• import mysql.connector

mydb = mysql.connector.connect host="localhost",user=“root",

passwd="", database="mydatabase")
x = mydb.cursor()
sql = "DROP TABLE student"
x.execute(sql)
 Arrange the records in ascending or descending
order
• import mysql.connector
mydb = mysql.connector.connect(host="localhost",user=“root",passwd="",
database="mydatabase")
x = mydb.cursor()
sql = "SELECT * FROM student ORDER BY sname"
x.execute(sql)
myresult = x.fetchall()
for y in myresult:
print(y)
 project
• from tkinter import * • l3.grid(row=2,column=0,pady=2)
• from tkinter import Entry •
• from tkinter import messagebox • e1 =
Entry(master,fg="red",bg="yellow",font=
("Helvetica", 16))
• import mysql.connector • e2 =
• master = Tk() Entry(master,fg="red",bg="yellow",font=
• ("Helvetica", 16))
• master.title("Student Data Entry") • e3=Entry(master,fg="red",bg="yellow",fo
nt=("Helvetica", 16))
• l1 = Label(master, text = "Name:")
• e1.grid(row = 0, column = 1, pady = 2)
• l2 = Label(master, text = "Address:")
• e2.grid(row = 1, column = 1, pady = 2)
• l3=Label(master,text="Phone")
• l1.grid(row = 0, column = 0, pady = 2)
• l2.grid(row = 1, column = 0, pady = 2)
 continue
• e3.grid(row = 2, column = 1, pady = 2) • e1.delete(0, END)
• def sadd(): • e2.delete(0, END)
• mydb = mysql.connector.connect( • e3.delete(0, END)
host="localhost",user="root",passwd="", • e1.focus_set()
database="python")
• x = mydb.cursor()
• b=Button(master,text="submit",
• sql = "INSERT INTO student command=sadd,bd=5)
(sname,sadd,sphone) VALUES (%s,
%s,%s)" • b.grid(row=2,column=2)
• val = (e1.get(),e2.get(),e3.get()) • e1.focus_set()
• x.execute(sql, val) • mainloop()
• mydb.commit()
• messagebox.showinfo('record inserted')
 MARKSHEET
• Create a database and table called mark.It contains roll and three
subject mark. Enter the data and display the marksheet by entering the
roll no
• from tkinter import * • l1 = Label(master, text = "Average:")
• #from tkinter.ttk import * • l1.grid(row = 2, column = 0, pady = 2)
• from tkinter import Entry • l1 = Label(master, text = "Grade:")
• from tkinter import messagebox • l1.grid(row = 3, column = 0, pady = 2)
• import mysql.connector • e1 =
• # creating main tkinter Entry(master,fg="red",bg="yellow",font
window/toplevel =("Helvetica", 16))
• master = Tk() • e2 =
Entry(master,fg="red",bg="yellow",font
• master.geometry("400x200") =("Helvetica", 16))
• master.title("Student Result") • e3 =
• # this wil create a label widget Entry(master,fg="red",bg="yellow",font
=("Helvetica", 16))
• l1 = Label(master, text = "Enter Roll:")
• e4 =
• l1.grid(row = 0, column = 0, pady = 2) Entry(master,fg="red",bg="yellow",font
• l1 = Label(master, text = "Total:") =("Helvetica", 16))
• l1.grid(row = 1, column = 0, pady = 2)
• e1.grid(row = 0, column = 1, pady = 2)
• e2.grid(row = 1, column = 1, pady = 2)
• e3.grid(row = 2, column = 1, pady = 2)
• e4.grid(row = 3, column = 1, pady = 2)
• def display():
• tot=0
• avg=0
• grade=""
• r=int(e1.get())
• mydb = mysql.connector.connect(
host="localhost",user="root",passwd="",database="python")
• x = mydb.cursor()
•
• x.execute("SELECT * FROM mark WHERE roll="+str(r))
• result =x.fetchall() • else:
• a=list(result) • Entry.insert(e2,0,"wrong entry")
• for y in a: • Entry.insert(e3,0,"wrong entry")
• tot=y[1]+y[2]+y[3] • Entry.insert(e4,0,"wrong entry")
• avg=tot/3 • def clr():
• if avg>=80: e1.delete(0, END)
• grade="O" e2.delete(0, END)
• elif avg>=60: e3.delete(0, END)
• grade="A" e4.delete(0, END)
• elif avg>=50: e1.focus_set()
• grade="b" b=Button(master,text="submit",
command=display,bd=5)
• elif avg>=40:
• grade="c" b.grid(row=4,column=0)
• else: clear=Button(master,text="clear",command=clr,bd=5)
clear.grid(row=4,column=1)
• grade="F"
• if tot>0: e1.focus_set()
• Entry.insert(e2,0,str(tot)) mainloop()
• Entry.insert(e3,0,str(avg))
• Entry.insert(e4,0,grade)