# p16 Creating table using mysql connectivity
import mysql.connector as con
mycon=con.connect(host='localhost',user='root',passwd='admin',database='mdnschool')
if mycon.is_connected:
print("Connectivity established successfully")
else:
print("No connection")
x=mycon.cursor()
x.execute('use mdnschool')
print("\ndb selected")
x.execute("create table student(name varchar(10), roll int primary key, average float not null)")
print("\nTable created successfully")
x.execute("desc student")
print("\structure of the table")
print("**************************")
y=x.fetchall()
for i in y:
print(i)
#p16 Output
1
# p17
Inserting values in the table using mysql connectivity
import mysql.connector as con
mycon=con.connect(host='localhost',user='root',passwd='admin',database='mdnschool')
if mycon.is_connected:
print("Connectivity established sucessfully")
else:
print("No conection")
x=mycon.cursor()
x.execute('use mdnschool')
print("\ndb selected")
x.execute('insert into student values("anu",1,89.8)')
x.execute('insert into student values("abi",4,76.9)')
x.execute('insert into student values("geeta",7,86.4)')
x.execute('insert into student values("jeethu",9,94.3)')
x.execute('insert into student values("seetha",11,77.22)')
print("Records in the table")
print("*************************")
x.execute("select * from student")
y=x.fetchall()
for i in y:
print(i)
2
3
# p18 Selecting rows in the table using mysql connectivity
import mysql.connector as con
mycon=con.connect(host='localhost',user='root',passwd='admin',database='mdnschool')
if mycon.is_connected:
print("Connectivity established successfully")
else:
print("No connection")
x=mycon.cursor()
x.execute('use mdnschool')
print("\nDatabase is selected")
print("\nselecting all the records in the table")
print("-------------------------------------------")
x.execute('select * from supplier')
y=x.fetchall()
for i in y:
print(i)
print("\nselecting the records with price between 50 and 70")
print("---------------------------------------------------------")
z='select * from supplier where price between 50 and 70'
x.execute(z)
c=x.fetchall()
for i in c:
print(i)
g=x.rowcount
print("\nNo. of records in the given price range : ",g)
print("\nselecting the records with a as second letter in city ")
print("------------------------------------------------------------")
x.execute('select * from supplier where city like "_a%"')
y=x.fetchall()
print(y)
print("\nSorting the records based on price in descending order ")
print("------------------------------------------------------------")
x.execute('select * from supplier order by price desc ')
y=x.fetchall()
for i in y:
print(i)
print("\nselecting first 4 the records in the table")
print("-------------------------------------------")
x.execute('select * from supplier')
b=x.fetchmany(4)
for i in b:
print(i)
4
5
# p19 Reading records from user and inserting it in the table using mysql connectivity
import mysql.connector as con
mycon=con.connect(host='localhost',user='root',passwd='admin',database='mdnschool')
if mycon.is_connected:
print("Connectivity established successfully")
else:
print("No connection")
x=mycon.cursor()
x.execute('use mdnschool')
print("\ndb selected")
x.execute('create table emp(eid int primary key, ename varchar(15),salary float not null)')
print("Table created")
n=int(input("Enter number of records to be inserted"))
for i in range(0,n):
id=int(input("Enter employee id : "))
name=input("Enter employee name : ")
sal=float(input("Enter employee salary : "))
q="insert into emp values({},'{}',{})".format(id,name,sal)
x.execute(q)
mycon.commit()
print("\nRecords in the table")
print("*************************")
x.execute("select * from emp")
y=x.fetchall()
for i in y:
print(i)
#p 19 output
6
# p20 updating records using mysql connectivity
import mysql.connector as con
mycon=con.connect(host='localhost',user='root',passwd='admin',database='mdnschool')
if mycon.is_connected:
print("Connectivity established successfully")
else:
print("No connection")
x=mycon.cursor()
x.execute('use mdnschool')
print("\ndb selected")
print("Records in the table")
print("*************************")
x.execute("select * from emp")
y=x.fetchall()
for i in y:
print(i)
newid=int(input("Enter the employee ID : "))
newsal=float(input("Enter new salary value : "))
q="update emp set salary={} where eid={}".format(newsal,newid)
x.execute(q)
mycon.commit()
print("Updated Successfully")
print("After Updation Records in the table")
print("*************************")
x.execute("select * from emp")
y=x.fetchall()
for i in y:
print(i)
#p20 output
