************************************************************************************************
PROJECT NAME : INVENTORY SYSTEM
************************************************************************************************
This project uses a table whose specification is given below :

Database Name : Inventory

Table Name : Item

+------------+----------+------+-----+---------+
| Field        | Type     | Null  | Key | Default | 
+------------+----------+------+-----+---------+
| itemno     | char(5)  | NO   | PRI | NULL  |   
| itemname | char(20)| YES  |        | NULL  |   
| category  | char(20)| YES  |        | NULL  |     
| price       | float      | YES  |        | NULL  |    
| quantity   | int         | YES |         | NULL  |     
+----------+----------+-------+------+---------+

This project consists of 2 modules in Python
*******************************************************************************************
item.py - This module has functions to operate on the ITEM table such as adding a record, deleteing a record
*******************************************************************************************
"""This module defines functions to add, delete, update, 
display item details using the item table in the 
shop database
"""
import mysql.connector

def check_exists(itemno):
    """This function returns True if the item exists in the table item
    otherwise returns False"""
    
    mycon = mysql.connector.connect(host='localhost',
                                 database='inventory',
                                 user='root',
                                 password='school')
    
    mycursor=mycon.cursor()
    sql="select * from item where itemno='%s'"%(itemno,)
    mycursor.execute(sql)
    mydata=mycursor.fetchall()
    if mycursor.rowcount==0:
        return False
    else:
        return True
        
def add_item():
    """This function add a record of an item"""
    mycon = mysql.connector.connect(host='localhost',database='inventory',
                                    user='root',password='school')
    mycursor=mycon.cursor()
    itemno=input("Enter Item Number :")
    if check_exists(itemno)==False:
        itemname=input("Enter Item Name : ")
        category=input("The Category of various Items are\
                      \nH-Household,G-Grocery,C-Cosmetics\
                      \nEnter the Category :")
        price=float(input("Enter Price :"))
        qty=int(input("Enter Quantity :"))
        sql="INSERT INTO ITEM VALUES ('%s','%s','%s',%s,%s)"%(itemno,itemname,category,price,qty)
        mycursor.execute(sql)
        mycon.commit()
        print("Record inserted")
    else:
        print("Duplicate Item Number")
    mycon.close()
   
def display_item():
    """This function displays the details of an item"""
    
    mycon = mysql.connector.connect(host='localhost',
                                 database='inventory',
                                 user='root',
                                 password='school')
    
    mycursor=mycon.cursor()
    itemno=input("Enter Item Number :")
    sql="select * from item where itemno='%s'"%(itemno,)
    mycursor.execute(sql)
    mydata=mycursor.fetchall()
    if mycursor.rowcount==0:
        print("Invalid Item Number!")
    else:
        print("Item Name :",mydata[0][1])
        print("Category  :",mydata[0][2])
        print("Price     :",mydata[0][3])
        print("Quantity  :",mydata[0][4])



def display_all():
    """This function displays the record of all items"""
    
    mycon = mysql.connector.connect(host='localhost',
                                 database='inventory',
                                 user='root',
                                 password='school')
    
    mycursor=mycon.cursor()
    sql="select * from item"
    mycursor.execute(sql)
    mydata=mycursor.fetchall()
    if mycursor.rowcount==0:
        print("No Record Found")
    else:
        print("%10s%20s%15s%15s"%("Item No","Item Name","Category","Price"))
        for x in mydata:
            print("%10s%20s%15s%15s%15s"%(x[0],x[1],x[2],x[3],x[4]))

    
def update_item():
    """This function updates the record of an item"""
    mycon = mysql.connector.connect(host='localhost',database='inventory',
                                    user='root',password='school')
    mycursor=mycon.cursor()
    itemno=input("Enter Item Number:")
    
    if check_exists(itemno)==True:
        print("Enter 1 to edit Price")
        print("Enter 2 to edit Quantity")
        ch=int(input("Enter your choice :"))
        if ch==1:
            pr=float(input("Enter New Price:" ))
            sql="update item set price=%s where itemno='%s'"%(pr,itemno)
        elif ch==2:
            qty=float(input("Enter Quantity to be Updated:" ))
            sql="update item set quantity=quantity+%s where itemno='%s'"%(qty,itemno)
        else:
            print("Invalid choice")
        if ch in(1,2):
            mycursor.execute(sql)
            mycon.commit()
            print("Record Updated")
    else:
        print("Invalid Item Number")
    mycon.close()


def delete_item():
    """This function deletes the record of an item""" 
    
    mycon = mysql.connector.connect(host='localhost',database='inventory',
                                    user='root',password='school')
    mycursor=mycon.cursor()
    itemno=input("Enter Item Number :")
    if check_exists(itemno)==True:
    
        sql="DELETE FROM ITEM WHERE ITEMNO='%s'"%(itemno,)
        mycursor.execute(sql)
        mycon.commit()
        print("Record Deleted")

    else:
        print("Invalid Item Number")
    mycon.close()


def buy():
    """This function performs all the transactions when an item is bought"""
    mycon = mysql.connector.connect(host='localhost',database='inventory',
                                   user='root',password='school')
    mycursor=mycon.cursor()
    bill=0
    while True:
        itemno=input("Enter Item Number:")
        if check_exists(itemno)==True:
       
            qty=int(input("Enter Quantity :"))
            sql="select quantity,price from item where itemno='%s'"%(itemno,)
            mycursor.execute(sql)
            mydata=mycursor.fetchall()
            Quantity=mydata[0][0]
            Pr=mydata[0][1]
            if qty<=Quantity:
                print("Quantity Available")
                sql="update item set quantity=quantity-%s where itemno='%s'"%(qty,itemno)
                mycursor.execute(sql)
                mycon.commit()
                bill+=Pr*qty
            else:
                print("Quantity Not Available")
        else:
            print("Invalid Item Number")
        ans=input("Do you wish to buy more items(Y/N)")
        if ans=='n' or ans=='N':
            print("Your total Bill is :%8.3f"%(bill,))
            break
    mycon.close()

        

*******************************************************************************************
mainitemprg.py
*******************************************************************************************
from item import *
while True:
    print("\n\nMAIN MENU")
    print("1.Add a new Item")
    print("2.Update Item Details")
    print("3.Delete Item Details")
    print("4.Display Particular Item Details")
    print("5.Display All Item details")
    print("6.Buy an item")
    print("7.Exit The Program")
    ch1=int(input("Enter your choice:"))
    if ch1==1:
        add_item()
    elif ch1==2:
        update_item()
    elif ch1==3:
        delete_item()
    elif ch1==4:
        display_item()
    elif ch1==5:
        display_all()
    elif ch1==6:
        buy()
    elif ch1==7:
        break
    else:
        print("Invalid Choice!Please choose your choice correctly")