# p16 Creating table using mysql connectivity
import [Link] as con
mycon=[Link](host='localhost',user='root',passwd='admin',database='mdnschool')
if mycon.is_connected:
print("Connectivity established successfully")
else:
print("No connection")
x=[Link]()
[Link]('use mdnschool')
print("\ndb selected")
[Link]("create table student(name varchar(10), roll int primary key, average float not null)")
print("\nTable created successfully")
[Link]("desc student")
print("\structure of the table")
print("**************************")
y=[Link]()
for i in y:
print(i)
#p16 Output
1
# p17
Inserting values in the table using mysql connectivity
import [Link] as con
mycon=[Link](host='localhost',user='root',passwd='admin',database='mdnschool')
if mycon.is_connected:
print("Connectivity established sucessfully")
else:
print("No conection")
x=[Link]()
[Link]('use mdnschool')
print("\ndb selected")
[Link]('insert into student values("anu",1,89.8)')
[Link]('insert into student values("abi",4,76.9)')
[Link]('insert into student values("geeta",7,86.4)')
[Link]('insert into student values("jeethu",9,94.3)')
[Link]('insert into student values("seetha",11,77.22)')
print("Records in the table")
print("*************************")
[Link]("select * from student")
y=[Link]()
for i in y:
print(i)
2
3
# p18 Selecting rows in the table using mysql connectivity
import [Link] as con
mycon=[Link](host='localhost',user='root',passwd='admin',database='mdnschool')
if mycon.is_connected:
print("Connectivity established successfully")
else:
print("No connection")
x=[Link]()
[Link]('use mdnschool')
print("\nDatabase is selected")
print("\nselecting all the records in the table")
print("-------------------------------------------")
[Link]('select * from supplier')
y=[Link]()
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'
[Link](z)
c=[Link]()
for i in c:
print(i)
g=[Link]
print("\nNo. of records in the given price range : ",g)
print("\nselecting the records with a as second letter in city ")
print("------------------------------------------------------------")
[Link]('select * from supplier where city like "_a%"')
y=[Link]()
print(y)
print("\nSorting the records based on price in descending order ")
print("------------------------------------------------------------")
[Link]('select * from supplier order by price desc ')
y=[Link]()
for i in y:
print(i)
print("\nselecting first 4 the records in the table")
print("-------------------------------------------")
[Link]('select * from supplier')
b=[Link](4)
for i in b:
print(i)
4
5
# p19 Reading records from user and inserting it in the table using mysql connectivity
import [Link] as con
mycon=[Link](host='localhost',user='root',passwd='admin',database='mdnschool')
if mycon.is_connected:
print("Connectivity established successfully")
else:
print("No connection")
x=[Link]()
[Link]('use mdnschool')
print("\ndb selected")
[Link]('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)
[Link](q)
[Link]()
print("\nRecords in the table")
print("*************************")
[Link]("select * from emp")
y=[Link]()
for i in y:
print(i)
#p 19 output
6
# p20 updating records using mysql connectivity
import [Link] as con
mycon=[Link](host='localhost',user='root',passwd='admin',database='mdnschool')
if mycon.is_connected:
print("Connectivity established successfully")
else:
print("No connection")
x=[Link]()
[Link]('use mdnschool')
print("\ndb selected")
print("Records in the table")
print("*************************")
[Link]("select * from emp")
y=[Link]()
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)
[Link](q)
[Link]()
print("Updated Successfully")
print("After Updation Records in the table")
print("*************************")
[Link]("select * from emp")
y=[Link]()
for i in y:
print(i)
#p20 output
