02/07/2025
1.)Write the python mySQL connectivity program to create a database named
"Infomatics"
access the same database and also display the list of existing databases.
import pymysql
p=pymysql.connect(host="localhost",user="root",passwd="admin")
m=p.cursor()
m.execute('create database infomatics')
m.execute('use infomatics')
m.execute('show databases')
for i in m:
print(i)
2.)Write the python mySQL connectivity program to access the database"infomatics"
and also create a
table name "product" with the following attributes.
Pid-char(5)
Pname-varchar(15)
import pymysql
p=pymysql.connect(host="localhost",user="root",passwd="admin")
m=p.cursor()
m.execute('use infomatics')
m.execute('create table product(pid char(5),pname varchar(15))')
m.execute('show tables')
for i in m:
print(i)
3.)Write the python mySQL connectivity program to add new column DOE-date
import pymysql
p=pymysql.connect(host="localhost",user="root",passwd="admin")
m=p.cursor()
m.execute('use infomatics')
m.execute('alter table product add DOE date')
m.execute('desc product')
for i in m:
print(i)
4.)Write the python mySQL connectivity program to add a column price
of int datatype after the column pname.
import pymysql
p=pymysql.connect(host="localhost",user="root",passwd="admin")
m=p.cursor()
m.execute('use infomatics')
m.execute('alter table product add price int after pname')
m.execute('desc product')
for i in m:
print(i)
5.)Write the python script to insert values in all columns of a relation.and also
display
the records from the relation named "product".
import pymysql
p=pymysql.connect(host="localhost",user="root",passwd="admin")
m=p.cursor()
m.execute('use infomatics')
m.execute("insert into product values ('P001','ABC',999,'2025-07-04'),
('P002','XYZ',499,'2024-05-08'),('P003','DEF',799,'2023-09-30')")
m.execute("select * from product")
p.commit()
for i in m:
print(i)
6.)Write the python script to update all price of the product by 80.
import pymysql
p=pymysql.connect(host="localhost",user="root",passwd="admin")
m=p.cursor()
m.execute('use infomatics')
m.execute("update product set price=price+80")
m.execute("select * from product")
m.commit()
for i in m:
print(i)
7.)Parameterised Query-(f%v)
import pymysql as a
b=a.connect(host="localhost",user="root",passwd="admin")
c=b.cursor()
c.execute('use infomatics')
while True:
pid=input('Enter PID:')
pname=input('Enter Pname:')
price=input('Enter Pprice:')
doe=input('Enter date:')
d='insert into product values("%s","%s",%s,"%s")'%(pid,pname,price,doe)
c.execute(d)
b.commit()
ch=input('(Y/N)')
if ch in 'nN':
break
c.execute('select*from product')
for i in c:
print(i)
b.close()
8.)Parameterised Query-(.format with placeholder{})
import pymysql as a
b=a.connect(host="localhost",user="root",passwd="admin")
c=b.cursor()
c.execute('use infomatics')
while True:
pid=input('Enter PID:')
pname=input('Enter Pname:')
price=input('Enter Pprice:')
doe=input('Enter date:')
d="insert into product values('{}','{}','{}','{}')".format(pid,pname,price,doe)
c.execute(d)
b.commit()
ch=input('(Y/N)')
if ch in 'nN':
break
c.execute('select*from product')
g=c.fetchall()
print(g)
print("Total no of rows retrieved",c.rowcount)
10.)Parameterised Query-(.format with placeholder{})-[without showing the table]
import pymysql as a
b=a.connect(host="localhost",user="root",passwd="admin")
c=b.cursor()
c.execute('use infomatics')
while True:
pid=input('Enter PID:')
pname=input('Enter Pname:')
price=input('Enter Pprice:')
doe=input('Enter date:')
d="insert into product values('{}','{}','{}','{}')".format(pid,pname,price,doe)
c.execute(d)
b.commit()
ch=input('(Y/N)')
if ch in 'nN':
break
print("Total no of rows inserted",c.rowcount)
c.close()
