PYMYSQL q-3 Harshil

import pymysql

#Q;-1
conn=pymysql.connect(host='localhost',user='root',passwd='root',db='product')
Cursor=conn.cursor()
sql=("create table item (itemid int,itemname varchar(40),producer char(20),price int)")
print(sql)
Cursor.execute(sql)
Cursor.close()
conn.close()
print("Table Created")

#Q:-2
conn=pymysql.connect(host='localhost',user='root',passwd='root',db='product')
Cursor=conn.cursor()
try:
      Itemid=int(input("Enter Your ItemID"))
      Itemname=input("Enter Your Product")
      Producer=input("Enter Your Product Name")
      Price=(input("Enter Price "))
      sql=("insert into item(Itemid,Itemname,Producer,Price) values("+str(Itemid)+",'"+Itemname+"','"+Producer+"',"+str(Price)+");")
      print(sql)
      Cursor.execute(sql)
      conn.commit()
      Cursor.close()
      conn.close()

except:
      conn.rollback()
      Cursor.close()
      conn.close()
      print ("Sorry")


#Show Element
conn=pymysql.connect(host='localhost',user='root',passwd='root',db='product')
Cursor=conn.cursor()
sql=("Select * from item")
Cursor.execute(sql)
row=Cursor.fetchone()
print('Records are ')
while row is not None:
      print(row)
      row=Cursor.fetchone()
Cursor.close()
conn.close()


#DELETE ELEMENT
conn=pymysql.connect(host='localhost',user='root',passwd='root',db='product')
Cursor=conn.cursor()
sql=("delete from item where Itemid=104")
print(sql)
Cursor.execute(sql)
Cursor.close()
conn.close()

#AVERAGE PRICE
conn=pymysql.connect(host='localhost',user='root',passwd='root',db='product')
Cursor=conn.cursor()
sql=("Select avg(price) from item")
print(sql)
Cursor.execute(sql)
Cursor.close()
conn.close()

#Q:-4
conn=pymysql.connect(host='localhost',user='root',passwd='root',db='product')
Cursor=conn.cursor()
sql=("Update item set producer='Ipad' where Itemid=103")
print (sql)
Cursor.execute(sql)
Cursor.close()
conn.close()