ACKNOWLEDGEMENT

We take this opportunity to express our

profound gratitude and deep regards to our Management ,
Principal , Subject teacher for their exemplary guidance,
monitoring and constant encouragement throughout the course
of this project.
The blessing, help and guidance given by him time to shall
carry us a long way in the journey of life on which we are
about to embark.

- Made By:
- Atharv Tiwari
- Swasti Singhal
- Suhani Gupta

1
 INDEX

1. Certificate
2. Acknowledgements
3. Declaration
4. Aim
5. Coding
6. Output Screenshots
7. Hardware Requirements

8. Bibliography

2
 DECLARATION

I hereby declare that the project work

entitled:
“Book Store Management”
is prepared by us,

- Atharv Tiwari
- Swasti Singhal
- Suhani Gupta

under the supervision of our subject teacher

for the partial fulfilment of All India Senior Secondary Certificate
Examination (AISSCE)

3
 AIM

Write a program using Python and MY-SQL connectivity for Book

Store Management

4
 Explanation:

This is a Python program that creates a simple bookstore management system. The program allows

users to sign up or login, add books to the store, delete books from the store, search for books by various

criteria, view staff details, record sales, and view the total income after the latest reset. The program uses

MySQL as its database management system.

When the program is run, it first creates a connection to the MySQL server running on the local

machine, using the root user and the password “admin”. It then creates a database called “store” (if it

doesn’t already exist) and uses it. It creates a table called “signup” with columns for “username” and

“password”. This table is used to store user account information.

The program then enters a loop that presents the user with a choice between signing up and logging

in. If the user chooses to sign up, they are prompted to enter a username and password, which are then

stored in the “signup” table. If the user chooses to log in, they are prompted to enter a username. If the

username is found in the “signup” table, the program prompts the user to enter their password. If the

password is correct, the user is presented with a menu of options for managing the bookstore.

The options for managing the bookstore are:

5
 Add Books: The user is prompted to enter information about a new book (name, genre, quantity,

author, publication, and price). If the book is already in the store, the quantity is increased. Otherwise, the

book is added to the “Available_Books” table.

Delete Books: The user is shown a list of available books and prompted to enter information about a

book to delete (name, quantity, customer name, phone number, and price). If the requested quantity of the

book is available, a record of the sale is added to the “Sell_rec” table and the quantity of the book is

decreased. Otherwise, an error message is displayed.

Search Books: The user is prompted to choose a search criterion (name, genre, or author) and enter

a search term. The program searches the “Available_Books” table for books matching the search criterion

and term and displays the results.

Staff Details: The user is shown a list of staff details (name, gender, age, phone number, and

address) stored in the “Staff_details” table.

Sell Record: The user is shown a list of records of book sales stored in the “Sell_rec” table.

Available Books: The user is shown a list of available books stored in the “Available_Books” table.

Total Income after the Latest Reset: The user is shown the total income from book sales since the

last reset.
6
 Exit: The program exits.

Overall, this program provides a basic framework for managing a small bookstore. However, it has

several limitations and potential issues. For example, the program does not include any error handling or

input validation, which could lead to incorrect or invalid data being stored in the database. Additionally, the

program does not include any authentication or authorization mechanisms, so anyone with access to the

program can perform any action on the database.

SYSTEM DEVELOPMENT LIFE CYCLE (SDLC)

7
Source Code:

#https://cbsepython.in

import mysql.connector

mydb=mysql.connector.connect (host="localhost", user="root", password="admin")

#CREATING DATABASE AND TABLE

mycursor=mydb.cursor()

mycursor.execute("create database if not exists store")

8
mycursor.execute("use store")

mycursor.execute("create table if not exists signup(username varchar(20),password

varchar(20))")

while True:

print("""1:Signup

2:Login""")

ch=int(input("SIGNUP/LOGIN(1,2):"))

#SIGNUP

if ch==1:

username=input("USERNAME:")

pw=input("PASSWORD:")

mycursor.execute("insert into signup values('"+username+"','"+pw+"')")

mydb.commit()

#LOGIN

elif ch==2:

username=input("USERNAME:")

mycursor.execute("select username from signup where username='"+username+"'")

pot=mycursor.fetchone()

if pot is not None:

9
print("VALID USERNAME!!!!!!")

pw=input("PASSWORD:")

mycursor.execute("select password from signup where password='"+pw+"'")

a=mycursor.fetchone()

if a is not None:

print("""+++++++++++++++++++++++

+++LOGIN SUCCESSFULL+++

+++++++++++++++++++++++""")

print("""==================================================================

====

++++++++++++++++++++++++++ MY BOOK STORE +++++++++++++++++++++++++

=========================================================================

=""")

mycursor.execute("create table if not exists Available_Books(BookName varchar(30) primary

key,Genre varchar(20),Quantity int(3),Author varchar(20),Publication varchar(30),Price int(4))")

mycursor.execute("create table if not exists Sell_rec(CustomerName varchar(20),PhoneNumber

char(10) unique key, BookName varchar(30),Quantity int(100),Price int(4),foreign key

(BookName) references Available_Books(BookName))")

mycursor.execute("create table if not exists Staff_details(Name varchar(30), Gender

varchar(10),Age int(3), PhoneNumber char(10) unique key , Address varchar(40))")

mydb.commit()

10
while(True):

print("""1:Add Books

2:Delete Books

3:Search Books

4:Staff Details

5:Sell Record

6:Available Books

7:Total Income after the Latest Reset

8:Exit""")

a=int(input("Enter your choice:"))

#ADD BOOKS

if a==1:

print("All information prompted are mandatory to be filled")

book=str(input("Enter Book Name:"))

genre=str(input("Genre:"))

quantity=int(input("Enter quantity:"))

author=str(input("Enter author name:"))

publication=str(input("Enter publication house:"))

price=int(input("Enter the price:"))

11
mycursor.execute("select * from Available_Books where bookname='"+book+"'")

row=mycursor.fetchone()

if row is not None:

mycursor.execute("update Available_Books set quantity=quantity+'"+str(quantity)+"' where

bookname='"+book+"'")

mydb.commit()

print("""++++++++++++++++++++++

++SUCCESSFULLY ADDED++

++++++++++++++++++++++""")

else:

mycursor.execute("insert into

Available_Books(bookname,genre,quantity,author,publication,price)

values('"+book+"','"+genre+"','"+str(quantity)+"','"+author+"','"+publication+"','"+str(price)

+"')")

mydb.commit()

print("""++++++++++++++++++++++

++SUCCESSFULLY ADDED++

++++++++++++++++++++++""")

#DELETE BOOKS

elif a==2:

12
print("AVAILABLE BOOKS...")

mycursor.execute("select * from Available_Books ")

for x in mycursor:

print(x)

cusname=str(input("Enter customer name:"))

phno=int(input("Enter phone number:"))

book=str(input("Enter Book Name:"))

price=int(input("Enter the price:"))

n=int(input("Enter quantity:"))

mycursor.execute("select quantity from available_books where bookname='"+book+"'")

lk=mycursor.fetchone()

if max(lk)<n:

print(n,"Books are not available!!!!")

else:

mycursor.execute("select bookname from available_books where bookname='"+book+"'")

log=mycursor.fetchone()

if log is not None:

mycursor.execute("insert into Sell_rec values('"+cusname+"','"+str(phno)

+"','"+book+"','"+str(n)+"','"+str(price)+"')")

13
mycursor.execute("update Available_Books set quantity=quantity-'"+str(n)+"' where

BookName='"+book+"'")

mydb.commit()

print("""++++++++++++++++++++++

++BOOK HAS BEEN SOLD++

++++++++++++++++++++++""")

else:

print("BOOK IS NOT AVAILABLE!!!!!!!")

#SEARCH BOOKS ON THE BASIS OF GIVEN OPTIONS

elif a==3:

print("""1:Search by name

2:Search by genre

3:Search by author""")

l=int(input("Search by?:"))

#BY BOOKNAME

if l==1:

o=input("Enter Book to search:")

mycursor.execute("select bookname from available_books where bookname='"+o+"'")

tree=mycursor.fetchone()

14
if tree!=None:

print("""++++++++++++++++++++

++BOOK IS IN STOCK++

++++++++++++++++++++""")

else:

print("BOOK IS NOT IN STOCK!!!!!!!")

#BY GENRE

elif l==2:

g=input("Enter genre to search:")

mycursor.execute("select genre from available_books where genre='"+g+"'")

poll=mycursor.fetchall()

if poll is not None:

print("""++++++++++++++++++++

++BOOK IS IN STOCK++

++++++++++++++++++++""")

mycursor.execute("select * from available_books where genre='"+g+"'")

for y in mycursor:

print(y)

else:

15
print("BOOKS OF SUCH GENRE ARE NOT AVAILABLE!!!!!!!!!")

#BY AUTHOR NAME

elif l==3:

au=input("Enter author to search:")

mycursor.execute("select author from available_books where author='"+au+"'")

home=mycursor.fetchall()

if home is not None:

print("""++++++++++++++++++++

++BOOK IS IN STOCK++

++++++++++++++++++++""")

mycursor.execute("select * from available_books where author='"+au+"'")

for z in mycursor:

print(z)

else:

print("BOOKS OF THIS AUTHOR ARE NOT AVAILABLE!!!!!!!")

mydb.commit()

#STAFF DETAILS

elif a==4:

print("1:New staff entry")

16
print("2:Remove staff")

print("3:Existing staff details")

ch=int(input("Enter your choice:"))

#NEW STAFF ENTRY

if ch==1:

fname=str(input("Enter Fullname:"))

gender=str(input("Gender(M/F/O):"))

age=int(input("Age:"))

phno=int(input("Staff phone no.:"))

add=str(input("Address:"))

mycursor.execute("insert into Staff_details(name,gender,age,phonenumber,address)

values('"+fname+"','"+gender+"','"+str(age)+"','"+str(phno)+"','"+add+"')")

print("""+++++++++++++++++++++++++++++

+STAFF IS SUCCESSFULLY ADDED+

+++++++++++++++++++++++++++++""")

mydb.commit()

#REMOVE STAFF

elif ch==2:

nm=str(input("Enter staff name to remove:"))

17
mycursor.execute("select name from staff_details where name='"+nm+"'")

toy=mycursor.fetchone()

if toy is not None:

mycursor.execute("delete from staff_details where name='"+nm+"'")

print("""+++++++++++++++++++++++++++++++++

++STAFF IS SUCCESSFULLY REMOVED++

+++++++++++++++++++++++++++++++++""")

mydb.commit()

else:

print("STAFF DOESNOT EXIST!!!!!!")

#EXISTING STAFF DETAILS

elif ch==3:

mycursor.execute("select * from Staff_details")

run=mycursor.fetchone()

for t in mycursor:

print(t)

if run is not None:

print("EXISTING STAFF DETAILS...")

for t in mycursor:

18
print(t)

else:

print("NO STAFF EXISTS!!!!!!!")

mydb.commit()

#SELL HISTORY

elif a==5:

print("1:Sell history details")

print("2:Reset Sell history")

ty=int(input("Enter your choice:"))

if ty==1:

mycursor.execute("select * from sell_rec")

for u in mycursor:

print(u)

if ty==2:

bb=input("Are you sure(Y/N):")

if bb=="Y":

mycursor.execute("delete from sell_rec")

mydb.commit()

elif bb=="N":

19
pass

#AVAILABLE BOOKS

elif a==6:

mycursor.execute("select * from available_books order by bookname")

for v in mycursor:

print(v)

#TOTAL INCOME AFTER LATEST UPDATE

elif a==7:

mycursor.execute("select sum(price) from sell_rec")

for x in mycursor:

print(x)

#EXIT

elif a==8:

break

#LOGIN ELSE PART

else:

print("""++++++++++++++++++++++

++INCORRECT PASSWORD++

++++++++++++++++++++++""")

20
else:

print("""++++++++++++++++++++

++INVALID USERNAME++

++++++++++++++++++++""")

else:

break

Output:

= RESTART: C:\Users\Compaq\BOOKSTORE MANAGEMENT.py

1:Signup

2:Login

SIGNUP/LOGIN(1,2):1

USERNAME:"jitendra"

PASSWORD:"12345"

1:Signup

2:Login

SIGNUP/LOGIN(1,2):2

USERNAME:"jitendra"

VALID USERNAME!!!!!!

21
PASSWORD:"12345"

+++++++++++++++++++++++

+++LOGIN SUCCESSFULL+++

+++++++++++++++++++++++

======================================================================

++++++++++++++++++++++++++ MY BOOK STORE +++++++++++++++++++++++++

=========================================================================

1:Add Books

2:Delete Books

3:Search Books

4:Staff Details

5:Sell Record

6:Available Books

7:Total Income after the Latest Reset

8:Exit

Enter your choice:1

All information prompted are mandatory to be filled

Enter Book Name:"Computer Science with Python"

22
Genre:"Programming"

Enter quantity:10

Enter author name:"cbsepython"

Enter publication house:"cbsepython.in"

Enter the price:320

++++++++++++++++++++++

++SUCCESSFULLY ADDED++

++++++++++++++++++++++

1:Add Books

2:Delete Books

3:Search Books

4:Staff Details

5:Sell Record

6:Available Books

7:Total Income after the Latest Reset

8:Exit

Enter your choice:

Explanation:
23
 This is a Python program that creates a simple bookstore management system. The program allows

users to sign up or login, add books to the store, delete books from the store, search for books by various

criteria, view staff details, record sales, and view the total income after the latest reset. The program uses

MySQL as its database management system.

When the program is run, it first creates a connection to the MySQL server running on the local

machine, using the root user and the password “admin”. It then creates a database called “store” (if it

doesn’t already exist) and uses it. It creates a table called “signup” with columns for “username” and

“password”. This table is used to store user account information.

BIBLIOGRAPHY
24
1. Computer science With Python - Class XI By : SumitaArora

2. Website: https://www.w3resource.com

3. https://en.wikipedia.org/wiki/E_(mathematical_constant)

*****************

25