Scribd
Upload
7 views8 pages

Output

The document contains a Python script for a stock management system that allows users to add, modify, remove, and view items in a MySQL database. It includes functions for searching, creating, modifying, and deleting items, along with error handling for SQL operations. The script establishes a connection to a MySQL database, creates a database and a table for items, and provides a menu-driven interface for user interaction.

Uploaded by

Raj Thakur
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
7 views8 pages

Output

The document contains a Python script for a stock management system that allows users to add, modify, remove, and view items in a MySQL database. It includes functions for searching, creating, modifying, and deleting items, along with error handling for SQL operations. The script establishes a connection to a MySQL database, creates a database and a table for items, and provides a menu-driven interface for user interaction.

Uploaded by

Raj Thakur
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

def search(idn):

try:
[Link]("select * from items where itemid=%d"%(idn))
myresult=[Link]()
rec=[Link]
if rec==None:
print("Record not found")
return 0
for x in myresult:
print(x)
except [Link] as e:
print("SQL Error : ",e)

def createitem():
try:

idn=int(input("Enter Item ID"))

nm=input("Enter Item Name :")


q=int(input("Enter Qty in stock :"))
pr=float(input("Enter price :"))
sql="Insert into items(itemid,iname,qty,price) values(%s,%s,%s,%s)"
val=(idn,nm,q,pr)
[Link](sql,val)
[Link]()
print([Link],"---------------- Item Added-------------------")
[Link]("select * from items")
myresult=[Link]()
for x in myresult:
print(x)
except [Link] as e:
print("SQL Error : ",e)
def modifyitem():
try:
idn=int(input("Enter Item id to be modified :"))
flag=search(idn)
if flag==0:
return
nm=input("Enter Item Name :")
pr=float(input("Enter modified price"))
sql="update items set iname='%s',price='%d' where itemid='%d'"%(nm,pr,idn)
[Link](sql)
[Link]()
print("---------------- Record Updated-------------------")
[Link]("select * from items")
myresult=[Link]()
for x in myresult:
print(x)
except [Link] as e:
print("SQL Error : ",e)

def removeitem():
try:
id=int(input("Enter Item No :"))
search(id)
choice=input("Are you sure ?? You want to delete this record ?? Y/N : ")
if choice=='Y' or choice=='y':
sql="delete from items where itemid='%d'"%(id)
[Link](sql)
[Link]()
print([Link],"---------------- Record
Deleted-------------------")
else:
print("Record Restored")
[Link]("select * from items")
myresult=[Link]()
for x in myresult:
print(x)
except [Link] as e:
print("SQL Error : ",e)
import [Link]
try:
mydb=[Link](host="localhost",user="root",passwd="aa")
print("Connection Established ", mydb)
mycursor=[Link]()
[Link]("drop database stock")
[Link]("create database stock")
print("Database Created")
[Link]("show databases")
for x in mycursor:
print(x)
[Link]("use stock")
[Link]("create table items(itemid integer primary key, iname
varchar(20) not null, qty integer, price decimal(8,2))")
print("Table Created")
[Link]("show tables")
for x in mycursor:
print(x)
print("Structure of the Table Created")
[Link]("desc items")
for x in mycursor:
print(x)
ch=0
while ch in range(0,8) :
print("--------------------------Welcome to Stock
Management------------------------")
print("-------------------------- Administrator Menu
-----------------------------")
print("---- 1 : Add new Item -------")
print("---- 2 : Modify item Details -------")
print("---- 3 : Remove item -------")
print("---- 4 : Records fetched -------")
print("---- 5 : Exit -------")
ch= int(input("---- Enter Your Choice ------- : "))
if ch==1:
createitem()
elif ch==2 :
modifyitem()
elif ch==3 :
removeitem()
elif ch==4:
mycursor=[Link]()
[Link]("select * from items")
myresult=[Link]()
rec=[Link]
print("Total Records : ",rec)
else :
print("Thanks for Using Stock Management System..! Have a nice
Day...!")
except [Link] as e:
print("SQL Error : ",e)
Connection Established <[Link] object at
0x01F03650>
Database Created
('information_schema',)
('bank',)
('employeedb',)
('library',)
('mysql',)
('neetu',)
('performance_schema',)
('stock',)
('test',)
('up15ad1758',)
Table Created
('items',)
Structure of the Table Created
('itemid', 'int(11)', 'NO', 'PRI', None, '')
('iname', 'varchar(20)', 'NO', '', None, '')
('qty', 'int(11)', 'YES', '', None, '')
('price', 'decimal(8,2)', 'YES', '', None, '')
--------------------------Welcome to Stock Management------------------------
-------------------------- Administrator Menu -----------------------------
---- 1 : Add new Item -------
---- 2 : Modify item Details -------
---- 3 : Remove item -------
---- 4 : Records fetched -------
---- 5 : Exit -------
---- Enter Your Choice ------- : 2
Enter Item id to be modified :12
Enter Item Name :asd
Enter modified price11
SQL Error : 1054 (42S22): Unknown column 'id' in 'where clause'
--------------------------Welcome to Stock Management------------------------
-------------------------- Administrator Menu -----------------------------
---- 1 : Add new Item -------
---- 2 : Modify item Details -------
---- 3 : Remove item -------
---- 4 : Records fetched -------
---- 5 : Exit -------
---- Enter Your Choice ------- :
RESTART: C:\Users\JOHN\Desktop\python my projects\python files\stock [Link]

Connection Established <[Link] object at


0x02161370>
Database Created
('information_schema',)
('bank',)
('employeedb',)
('library',)
('mysql',)
('neetu',)
('performance_schema',)
('stock',)
('test',)
('up15ad1758',)
Table Created
('items',)
Structure of the Table Created
('itemid', 'int(11)', 'NO', 'PRI', None, '')
('iname', 'varchar(20)', 'NO', '', None, '')
('qty', 'int(11)', 'YES', '', None, '')
('price', 'decimal(8,2)', 'YES', '', None, '')
--------------------------Welcome to Stock Management------------------------
-------------------------- Administrator Menu -----------------------------
---- 1 : Add new Item -------
---- 2 : Modify item Details -------
---- 3 : Remove item -------
---- 4 : Records fetched -------
---- 5 : Exit -------
---- Enter Your Choice ------- : 2
Enter Item id to be modified :12
Enter Item Name :asd
Enter modified price11
---------------- Record Updated-------------------
--------------------------Welcome to Stock Management------------------------
-------------------------- Administrator Menu -----------------------------
---- 1 : Add new Item -------
---- 2 : Modify item Details -------
---- 3 : Remove item -------
---- 4 : Records fetched -------
---- 5 : Exit -------
---- Enter Your Choice ------- : 4
Total Records : 0
--------------------------Welcome to Stock Management------------------------
-------------------------- Administrator Menu -----------------------------
---- 1 : Add new Item -------
---- 2 : Modify item Details -------
---- 3 : Remove item -------
---- 4 : Records fetched -------
---- 5 : Exit -------
---- Enter Your Choice ------- : 1
Enter Item ID12
Enter Item Name :aadf
Enter Qty in stock :100
Enter price :100
1 ---------------- Item Added-------------------
(12, 'aadf', 100, Decimal('100.00'))
--------------------------Welcome to Stock Management------------------------
-------------------------- Administrator Menu -----------------------------
---- 1 : Add new Item -------
---- 2 : Modify item Details -------
---- 3 : Remove item -------
---- 4 : Records fetched -------
---- 5 : Exit -------
---- Enter Your Choice ------- : 1
Enter Item ID45
Enter Item Name :biscuit
Enter Qty in stock :150
Enter price :10
1 ---------------- Item Added-------------------
(12, 'aadf', 100, Decimal('100.00'))
(45, 'biscuit', 150, Decimal('10.00'))
--------------------------Welcome to Stock Management------------------------
-------------------------- Administrator Menu -----------------------------
---- 1 : Add new Item -------
---- 2 : Modify item Details -------
---- 3 : Remove item -------
---- 4 : Records fetched -------
---- 5 : Exit -------
---- Enter Your Choice ------- : 1
Enter Item ID140
Enter Item Name :Chips
Enter Qty in stock :120
Enter price :20
1 ---------------- Item Added-------------------
(12, 'aadf', 100, Decimal('100.00'))
(45, 'biscuit', 150, Decimal('10.00'))
(140, 'Chips', 120, Decimal('20.00'))
--------------------------Welcome to Stock Management------------------------
-------------------------- Administrator Menu -----------------------------
---- 1 : Add new Item -------
---- 2 : Modify item Details -------
---- 3 : Remove item -------
---- 4 : Records fetched -------
---- 5 : Exit -------
---- Enter Your Choice ------- : 1
Enter Item ID144
Enter Item Name :soap
Enter Qty in stock :124
Enter price :45
1 ---------------- Item Added-------------------
(12, 'aadf', 100, Decimal('100.00'))
(45, 'biscuit', 150, Decimal('10.00'))
(140, 'Chips', 120, Decimal('20.00'))
(144, 'soap', 124, Decimal('45.00'))
--------------------------Welcome to Stock Management------------------------
-------------------------- Administrator Menu -----------------------------
---- 1 : Add new Item -------
---- 2 : Modify item Details -------
---- 3 : Remove item -------
---- 4 : Records fetched -------
---- 5 : Exit -------
---- Enter Your Choice ------- : 4
Total Records : 4
--------------------------Welcome to Stock Management------------------------
-------------------------- Administrator Menu -----------------------------
---- 1 : Add new Item -------
---- 2 : Modify item Details -------
---- 3 : Remove item -------
---- 4 : Records fetched -------
---- 5 : Exit -------
---- Enter Your Choice ------- : 3
Enter Item No :12
(12, 'aadf', 100, Decimal('100.00'))
Are you sure ?? You want to delete this record ?? Y/N : y
1 ---------------- Record Deleted-------------------
(45, 'biscuit', 150, Decimal('10.00'))
(140, 'Chips', 120, Decimal('20.00'))
(144, 'soap', 124, Decimal('45.00'))
--------------------------Welcome to Stock Management------------------------
-------------------------- Administrator Menu -----------------------------
---- 1 : Add new Item -------
---- 2 : Modify item Details -------
---- 3 : Remove item -------
---- 4 : Records fetched -------
---- 5 : Exit -------
---- Enter Your Choice ------- : 4
Total Records : 3
--------------------------Welcome to Stock Management------------------------
-------------------------- Administrator Menu -----------------------------
---- 1 : Add new Item -------
---- 2 : Modify item Details -------
---- 3 : Remove item -------
---- 4 : Records fetched -------
---- 5 : Exit -------
---- Enter Your Choice ------- : 5
Thanks for Using Stock Management System..! Have a nice Day...!
mysql> create table items(itemid integer primary key, iname varchar(20) not null,
qty integer, price decimal(8,2))
-> ;
Query OK, 0 rows affected (0.05 sec)

mysql> desc items;


+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| itemid | int(11) | NO | PRI | NULL | |
| iname | varchar(20) | NO | | NULL | |
| qty | int(11) | YES | | NULL | |
| price | decimal(8,2) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

You might also like