Question

A table, named STATIONERY, in ITEMDB database, has the following structure: 

Field	Type
itemNo	int(11)
itemName	varchar(15)
price	float
qty	int(11)

Write the following Python function to perform the specified operation: AddAndDisplay(): To input details of an item and store it in the table STATIONERY. The function should then retrieve and display all records from the STATIONERY table where the Price is greater than 120. 

Assume the following for Python-Database connectivity:
Host: localhost, User: root, Password: Pencil

Answer 1

def AddAndDisplay(): 
    import mysql.connector as mycon
    mydb=mycon.connect(host="localhost",user="root",
    passwd="Pencil",database="ITEMDB")
    mycur=mydb.cursor()
    no=int(input("Enter Item Number: "))
    nm=input("Enter Item Name: ")
    pr=float(input("Enter price: "))
    qty=int(input("Enter qty: ")) 
    query="INSERT INTO stationery VALUES ({},'{}',{},{})"
    query=query.format(no,nm,pr,qty)
    mycur.execute(query)
    mydb.commit()
    mycur.execute("select * from stationery where price>120")
    for rec in mycur:
       print(rec)