{UPDATED (1)} Python-MySQL Management Project

This is a Shop Management System

CODE :-

from tabulate import tabulate import mysql.connector mydb=mysql.connector.connect(host="localhost", user="root", passwd=" ",database='Shop_Management') cur=mydb.cursor() # ***** FUNCTIONS ***** # def additem(): while True: print("\nContents in table SHOP") a=cur.execute("select Itemno, Itemname, Quantity from Shop") cur.execute(a) print(tabulate(cur, headers=['Itno', 'Itnm', 'Qty'],tablefmt='psql')) print("\nADDING RECORDS ...") itno=int(input("\nEnter Item Number - ")) itnm=input("Enter Item Name - ") qty=int(input("Enter Quantity - ")) pr=int(input("Enter price - Rs. ")) cur.execute("insert into shop values( {},'{}',{},{})".format(itno,itnm,qty,pr)) mydb.commit() print("\nRecord Added Successfully !!!") abc=input("\nDo you want to Add more Records ? (y/n) - ") if abc=='n': break def updateitem(): print("\nContents in table SHOP") a=cur.execute("select Itemno, Itemname from Shop") cur.execute(a) print(tabulate(cur, headers=['ItemNo',' ItemName ',], tablefmt='grid')) try: print("\nUPDATING RECORDS ...") print('''\nWhat do you want to Update ? - 1. Item Name 2. Quantity of an Item 3. Price of an Item''') while True: ch=int(input("\nEnter your Choice - ")) if ch==1: print("\nUPDATING ITEM NAME...") itemno=int(input("\nEnter Item Number - ")) itemnm=input("Enter Updated Item Name - ") cur.execute("update shop set itemname = '{}' where itemno = {}".format(itemnm,itemno)) mydb.commit() print("\nRecord Updated Successfully !!!") elif ch==2: print("\nUPDATING QUANTITY...") itemno=int(input("\nEnter Item Number - ")) qty=int(input("Enter Updated Quantity - ")) cur.execute("update shop set Quantity = {} where itemno = {}".format(qty,itemno)) mydb.commit() print("\nRecord Updated Successfully !!!") elif ch==3: print("\nUPDATING PRICE...") itemno=int(input("\nEnter Item Number - ")) pr=int(input("Enter Updated Price - ")) cur.execute("update shop set Price = {} where itemno = {}".format(pr,itemno)) mydb.commit() print("\nRecord Updated Successfully !!!") else: print("Please Enter a Valid Input") break except: print("Enter Valid Input") def delitem(): while True: print("\nItems present in SHOP Inventory") a=cur.execute("select * from Shop order by ItemNo asc") cur.execute(a) print(tabulate(cur, headers=['ItemNo','ItemName','Quantity','Price'],tablefmt='grid')) ch=input("\nDo you want to continue to delete a Record ? (y/n) - ") if ch == 'y': itemno=int(input("\nEnter Item Number - ")) cur.execute("delete from shop where itemno = {} ".format(itemno)) mydb.commit() print("\nRecord Deleted Successfully !!!") else: print("Thanks!!!") break def inventory(): print('\n') print("Inventory :-\n") a=cur.execute("select * from Shop order by ItemNo asc") cur.execute(a) print(tabulate(cur, headers=['ItemNo','ItemName','Quantity','Price'],tablefmt='grid')) # *************************************************************************** # def stock(): print("\n*** INVENTORY MANAGEMENT ***") print(''' 1.ADD 2.UPDATE 3.DELETE 4.SHOW INVENTORY''') ch=int(input("\nEnter your Choice -- ")) if ch==1 : additem() elif ch==2: updateitem() elif ch==3: delitem() elif ch==4: inventory() else: print("\nPlease Enter a Valid Input") def adddata(): print("\nNames of Customers") aaa=cur.execute("show tables") cur.execute(aaa) print(tabulate(cur, headers=['Customers'], tablefmt='psql')) id=int(input('Customer ID :- ')) name=input("Enter the name of the buyer : ") no=int(input("Enter the phone number of the buyer : ")) y=int(input("Enter number of products : ")) cur.execute("create table {}(Cid integer, cname varchar (25), cno integer, item varchar(25), itpr integer, itqty integer)".format(name)) i=0 while(i<y): na=input("\nEnter the name of the item : ") pr=int(input("Enter the price of the item : ")) quan=int(input("Enter the quantity of the item : ")) cur.execute("insert into `{}` values ( '{}','{}',{},'{}',{},{})".format(name,id,name,no,na,pr,quan)) i=i+1 cur.execute("Select Quantity from shop where ItemName='{}'".format(na)) myrecords=cur.fetchall() x=myrecords[0][0] cur.execute("update shop set Quantity = {}-{} where itemName = '{}'".format(x,quan,na)) mydb.commit() print("Records Added!!") def deldata(): name=input("Enter Name of Customer : ") no=int(input("Enter the phone number of the buyer : ")) cur.execute("delete from `{}` where cno={}".format(name,no)) mydb.commit() print("Record deleted!!") def fetchdata(): mydb=mysql.connector.connect(host='localhost',user='root',passwd=' ', database='shop_management') cur=mydb.cursor() cur.execute("show tables") a=cur.fetchall() print(tabulate(a,headers=['Customers'],tablefmt='grid')) b=input("\n\nEnter the name of buyer you want to search for :- ") cur.execute("\n\nSelect * from {}".format(b)) c=cur.fetchall() print(tabulate(c,headers=['Cname','cno', 'item','itpr', 'itqty' ],tablefmt='grid')) # ******************************************************************************* # def billing(): print("\n*** BILLING ***") #while True: print (''' 1. Add record 2. Delete record 3. Display records 4. Exiting''') choice=int (input ("\nEnter your choice: ")) if choice == 1: adddata() elif choice== 2: deldata() elif choice== 3: fetchdata() elif choice == 4: print ("Exiting") else: print("Wrong Input") # ******************************************************************************* # while True: print(''' 1. Inventory 2. Billing ''') ch=int(input("\nEnter your Choice -- ")) if ch==1 : stock() elif ch==2: billing() else: print("\nPlease Enter a Valid Input") aa=input("\n\nDo you want to Continue to the Program ? (y/n) - ") if aa=='n': print("\nEXITING PROGRAM ...") print() break