Scribd
Upload
12 views

p16 Creating Table Using Mysql Connectivity

connectivity

Uploaded by

nithya vembu
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
12 views

p16 Creating Table Using Mysql Connectivity

connectivity

Uploaded by

nithya vembu
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 7

# 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

You might also like