In this article, we are going to create non-GUI book management using python and for the database, we are going to use MySQL.
As today’s need is fully dependent on data and data records which give us information from a pin to an airplane, there are many aspects of management system like book management, student record management, phone records management, etc
The management system is used for various purposes in day-to-day life either by the admin of the system or by the user of the system. for example book management system can be used in the library or in a book shop to collect records. Both the library and book shop has their requirement to make with the system
Book Management system in python using MySQL
Firstly, we have to create a database then in that database we have to create a table named ‘all_book’
userconnector.py will have the code to create a database and create a table in that particular database
usersql.py will have all the functions which contain the query related to the data addition or manipulation
main.py from here whole is directed some instruction will be given from here
Source files of the code
userconnector.py
import mysql.connector as sql
db = sql.connect(host="localhost",
user="root",
password="ABC123")
myc = db.cursor()
myc.execute("CREATE DATABASE book")
db.commit()
newdb = sql.connect(host="localhost",
user="root",
password="ABC123",#enter your password
database="book")
nmyc = newdb.cursor()
nmyc.execute("create table all_books(NAME varchar(200),AUTHOR varchar(50),PRICE int(10))")
newdb.commit()
main.py
import usersql
print("hey user")
while True:
print("for data insertion enter 1,\nfor data updation enter 2, \nfor data deletion enter 3, \nto see all data from "
"the table enter 4")
a = int(input("enter your choice: "))
if a == 1:
usersql.insertion()
elif a == 2:
usersql.updation()
elif a == 3:
usersql.deletion()
elif a == 4:
usersql.seeall()
else:
print("invalid response")
usersql.py
import mysql.connector as sql
password = "ABC123"
db = sql.connect(host="localhost",
user="root",
password="ABC123",
database="book")
myc = db.cursor()
def insertion():
book_name = input("enter book name : ")
author_name = input("enter author of the book : ")
price_book = int(input("enter the price of book : "))
sq = "insert into all_Books(NAME,AUTHOR,PRICE) values(%s,%s,%s)"
val = (book_name, author_name, price_book)
myc.execute(sq, val)
db.commit()
def deletion():
print("for security reasons:\nEnter password of your database server")
passwd = input()
if password == passwd:
book_name = input("enter book name to delete : ")
myc.execute("select * from all_Books")
row = myc.fetchall()
for i in row:
if i[0] == book_name:
query = "delete from all_Books where NAME='{}'".format(book_name)
myc.execute(query)
db.commit()
else:
continue
else:
print("enter correct password")
def updation():
print("for security reasons:\nEnter password of your database server")
passwd = input()
if password == passwd:
print("we cannot update whole entity \n only price can update ")
book_name = input("enter book name to update the price : ")
price = int(input("enter updated price : "))
myc.execute("select * from all_Books")
row = myc.fetchall()
for i in row:
if i[0] == book_name:
query = "UPDATE all_Books SET PRICE ={} WHERE NAME ='{}'".format(price, book_name)
myc.execute(query)
db.commit()
else:
continue
else:
print("enter correct password")
def seeall():
db = sql.connect(host="localhost",
user="root",
password="ABC123",
database="book")
myc = db.cursor()
myc.execute("select * from all_Books")
row = myc.fetchall()
for i in row:
print(i)
Description of the whole code:
From userconnector.py
- importing the mysql.connector module and we create a database and a table in that particular database using MySQL query
From main.py
- importing usersql.py to use functions we define
- here is given some general statements to the user for choosing the command to execute we create for running the database
- then apply while loop to run again and again the same code
- applying conditional statements to run different functions that we describe in usersql.py
- a different choice is associated with a different function in usersql.py
From usersql.py
- importing module mysql.connector and making the connection with the database (here MySQL)
- creating a cursor object to make a pointer through which the command will direct to execute
- defining functions like insertion, deletion, updation, seeall
- def insertion will take data and then using execute method we write a query of mysql insertion and then using commit we feed data to database
- def deletion will take the book name which has to be deleted and then using the fetchall method fetches the data from the database and match the book name and delete the record
- def updation will update the price only by taking the name of the book as input doing the same process as done before and using updating query
- def seeall fetches all the data from the database by using the function fetchall records
For security measures, we can also add an authentication process like we have done in the delete and updating part as only the admin will know the database password, and only the admin can delete or update the date
Queries used in code:-
- to insert data: ‘insert into all_Books(NAME,AUTHOR,PRICE) values(%s,%s,%s)’
- to delete data: ‘delete from all_Books where NAME='{}’.format(book_name)’
- to update data: ‘UPDATE all_Books SET PRICE ={} WHERE NAME ='{}’.format(price, book_name)’
- to see all the records: ‘select * from all_Books’
»here the .format method is from python which fills the data
output code:
here we are inserting data
the database after inserting data 
deleting the book from the database
updating the database
final view of the database after all manipulation