COMMAND TO CHECK MYSQL
CONNECTOR IS INSTALLED OR
NOT
IN PYTHON IDLE
UNINSTALL MYSQL CONNECTOR
IF ALREADY INSTALLED
pip uninstall mysql-connector-python
INSTALL MYSQL CONNECTOR
pip install mysql-connector-python
Fetchone
import mysql.connector as c
con=c.connect(host="localhost",
username="root",
password="linus@321", Fetchone
database="carshowroom"
)
cur=con.cursor()
query="select empid, empname, gender, designation from employee"
cur.execute(query)
row1=cur.fetchone() # fetch one record/row
print(row1)
row2=cur.fetchone() # fetch one record/row
print(row2)
row3=cur.fetchone() # fetch one record/row
print(row3)
row=cur.fetchmany(7) # fetch 7 more records/rows
print(row)
row11=cur.fetchone() # fetch one record/row
print(row11)
row12=cur.fetchone() # fetch None as now more records fetch
print(row12)
con.close()
Fetchmany
import mysql.connector as c
con=c.connect(host="localhost",
username="root", Fetchmany
password="linus@321",
database="carshowroom"
)
cur=con.cursor()
query="select empid, empname, gender, salary from employee"
cur.execute(query)
row=cur.fetchmany() # fetch only 1 record if no value given in parameter
print(row)
row=cur.fetchmany() # fetch only 1 record if no value given in parameter
print(row)
row=cur.fetchmany(9) # fetch remaining 9 records
print(row)
row=cur.fetchmany()
print(row) # Now more record to fetch therefore fetch
empty list []
con.close()
Fetchall
import mysql.connector as c
con=c.connect(host="localhost", Fetchall
username="root",
password="linus@321",
database="carshowroom"
)
cur=con.cursor()
query="select empid, empname, gender, salary from
employee"
cur.execute(query)
row=cur.fetchall()
print(row) # fetch all record in the form of List of Tuples
row=cur.fetchall()
print(row) # Now more record to fetch therefore fetch empty
list []
import mysql.connector as c
con=c.connect(host="localhost",
username="root",
password="linus@321",
database="carshowroom"
)
cur=con.cursor()
g=input("Enter gender (F/M):")
s=int(input("Enter salary >:"))
query="select empid, empname,gender,designation,salary from
\
employee where gender='%s' and salary>=%s"%(g,s)
cur.execute(query)
data=cur.fetchall()
for i in data:
import mysql.connector as c
con=c.connect(host="localhost",
username="root",
password="linus@321",
database="carshowroom"
)
cur=con.cursor()
g=input("Enter gender (F/M):")
s=int(input("Enter salary >=:"))
query="select empid, empname,gender,designation,salary
from \
employee where gender='{}' and salary>={}".format(g,s)
cur.execute(query)
data=cur.fetchall()
for i in data:
update
Without
commit()
import mysql.connector as c
con=c.connect(host="localhost",
username="root", Update
password="linus@321", without
database="carshowroom"commit()
)
cur=con.cursor()
query="update employee set empname='Golu'\
where empid='E008’”
cur.execute(query)
#con.commit()
print("Data updated successfully.....")
con.close()
import mysql.connector as c
con=c.connect(host="localhost",
username="root",
Update with
password="linus@321", commit()
database="carshowroom"
)
cur=con.cursor()
query="update employee set empname='Golu’\
where empid='E008’”
cur.execute(query)
con.commit()
print("Data updated successfully.....")
con.close()
Insert new row
import mysql.connector as c
con=c.connect(host="localhost",
username="root",
password="linus@321",
Insert new row
database="carshowroom"
)
cur=con.cursor()
empid=input("Enter New Employee ID:")
empname=input("Enter New Employee Name:")
gender=input("Enter New Employee Gender(M/F):")
dob=input("Enter Employee Date of Birth:(YYYY-MM-DD):")
doj=input("Enter Employee Date of Joining:(YYYY-MM-DD):")
d=input("Enter Empolyee Designation:")
s=int(input("Enter Employee Salary:"))
query="insert into employee values ('%s','%s','%s','%s','%s','%s',%s)\
"%(empid,empname,gender,dob,doj,d,s)
cur.execute(query)
con.commit()
print("Data inserted successfully.....")
con.close()
Insert
multiple new
rows
import mysql.connector as c
con=c.connect(host="localhost",
username="root",
password="linus@321",
database="carshowroom"
Insert multiple new
)
cur=con.cursor()
rows
while True:
empid=input("Enter New Employee ID:")
empname=input("Enter New Employee Name:")
gender=input("Enter New Employee Gender(M/F):")
dob=input("Enter Employee Date of Birth:(YYYY-MM-DD):")
doj=input("Enter Employee Date of Joining:(YYYY-MM-DD):")
d=input("Enter Empolyee Designation:")
s=int(input("Enter Employee Salary:"))
query="insert into employee values ('%s','%s','%s','%s','%s','%s',%s)\
"%(empid,empname,gender,dob,doj,d,s)
cur.execute(query)
con.commit()
choice=input("More (Y/N):")
if choice in 'Nn':
break
print("Data inserted successfully.....")
con.close()
Delete row
import mysql.connector as c
con=c.connect(host="localhost",
username="root", Delete row
password="linus@321",
database="carshowroom"
)
cur=con.cursor()
empid=input("EnterEmployee ID to delete it record:")
#query="delete from employee where
empid='{}'".format(empid)
query="delete from employee where empid='%s'"%(empid)
cur.execute(query)
con.commit()
print("Record successfully deleted")
con.close()
