Super Store Billing System- Python MySQL Project


Super Store Billing System- Python MySQL Project is designed for class 12 computer science students. The Billing System project is made using MySQL and Python. The database of this project is stored in MySQL tables and its front-end is developed in Python CUI ie for all operations you have to type the necessory options.

Basic information of Billing System Project

Front End  : Python 3.9
BackEnd   : MySQL

MySQL Table Structure of Super Store Billing System Project

Whole Database is divided and stored in four main tables, they are login, Items, Bills, and Transaction. Detailed description and sample records are as follows

mysql> desc login;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(30) | YES  |     | NULL    |                |
| pwd   | char(30) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> select * from login;
+----+--------+-------+
| id | name   | pwd   |
+----+--------+-------+
|  1 | rakesh | ramji |
+----+--------+-------+
1 row in set (0.00 sec)

mysql> desc items;
+-----------+------------+------+-----+---------+----------------+
| Field     | Type       | Null | Key | Default | Extra          |
+-----------+------------+------+-----+---------+----------------+
| id        | int(11)    | NO   | PRI | NULL    | auto_increment |
| item_name | char(30)   | YES  |     | NULL    |                |
| price     | float(8,2) | YES  |     | NULL    |                |
+-----------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select * from items;
+----+-----------------+---------+
| id | item_name       | price   |
+----+-----------------+---------+
|  1 | fan             | 1250.00 |
|  2 | Tubelight       |  250.00 |
|  3 | Bulb-20         |  350.00 |
|  4 | Bulb-40         |  300.00 |
|  5 | Bulb-60         |  380.00 |
|  6 | mosquito-racket |  380.00 |
|  7 | pan             |   10.00 |
+----+-----------------+---------+
7 rows in set (0.00 sec)
mysql> desc bills;
+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| bill_id   | int(11)  | NO   | PRI | NULL    | auto_increment |
| name      | char(30) | YES  |     | NULL    |                |
| phone     | char(15) | YES  |     | NULL    |                |
| bill_date | date     | YES  |     | NULL    |                |
+-----------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from bills;
+---------+----------+------------+------------+
| bill_id | name     | phone      | bill_date  |
+---------+----------+------------+------------+
|       3 | ramji    | 23423423   | 2021-03-16 |
|       4 | pushkar  | 1122334455 | 2021-03-05 |
|       5 | rakesh   | 9871816901 | 2021-03-18 |
|       6 | swarnima | 9988776655 | 2021-03-18 |
|       7 | ra       | 2322       | 2021-03-18 |
+---------+----------+------------+------------+
5 rows in set (0.01 sec)

mysql> desc transaction;
+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| id      | int(11) | NO   | PRI | NULL    | auto_increment |
| item_id | int(11) | YES  |     | NULL    |                |
| qty     | int(11) | YES  |     | NULL    |                |
| bill_id | int(11) | YES  |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from transaction;
+----+---------+------+---------+
| id | item_id | qty  | bill_id |
+----+---------+------+---------+
|  1 |       4 |    1 |       1 |
|  2 |       3 |    1 |       1 |
|  3 |       1 |    1 |       1 |
|  4 |       1 |    1 |       2 |
|  5 |       5 |    1 |       2 |
|  6 |       6 |    1 |       2 |
|  7 |       3 |    1 |       2 |
|  8 |       2 |    1 |       2 |
|  9 |       1 |   10 |       4 |
| 10 |       2 |   20 |       4 |
| 11 |       3 |   12 |       4 |
| 12 |       1 |    2 |       5 |
| 13 |       1 |    2 |       5 |
| 14 |       1 |    1 |       6 |
| 15 |       2 |    3 |       6 |
| 16 |       4 |   10 |       6 |
+----+---------+------+---------+
16 rows in set (0.00 sec)

Login table contains the user’s Name and password that is used to authenticate the user of the system. Items tables contains items details available in the super store. Bills table contains the bill information along with its customer. Transaction table contains the bill Id and the items purchased aginst that bills.

Ids of all the tables are primary key and have auto increment constraints ie they will increase automtically on insertion of a new record in that table.

Download Super Store Billing_system -Python MySQL Project Source Code 

How to Run Super Store Project on your system

The source code and MysQL database is available in a single Zip file. You need to perform the following tasks inorder to run this Billing system Project on your system

    1. First of all unzip the downloaded file in a folder.
    2. This folder now contains two files 1.Billing_system.py 2.pos.sql
    3. Restore the pos.sql file using mysql restore command
    4. Make changes in the connection string as per your system setting
    5. open billing_system.py file in IDE/IDLE and run this module.

Working of Billing System Python MySQL project

How this python project actually works is defined in this YouTube Videos.

Source code of Billing System

# Project Name          : Billing System
# Made by               : rakesh kumar
# website               : cbsetoday.com
# Licence               : MIT ( Licence)


import mysql.connector as connector
from datetime import date

global conn, cursor
conn= connector.connect(host="localhost",database="pos",user="root",password="")
cursor = conn.cursor()


def clear():
    for _ in range(65):
        print()


def last_bill_no():
    cursor.execute('select max(bill_id) from bills')
    record = cursor.fetchone()
    return record



def find_item(no):
    cursor.execute('select * from items where id ={}'.format(no))
    record = cursor.fetchone()
    return record


def add_item():
    clear()
    print('Add New Item - Screen')
    print('-'*100)
    item_name = input('Enter new Item Name :')
    item_price = input('Enter Item Price :')
    sql = 'select * from items where item_name like "%{}%"'.format(item_name)
    cursor.execute(sql)
    record=cursor.fetchone()
    if record==None:
        sql = 'insert into items(item_name,price) values("{}",{});'.format(item_name,item_price)
        cursor.execute(sql)
        print('\n\nNew Item added successfully.....\nPress any key to continue....')
    else:
        print('\n\nItem Name already Exist.....\nPress any key to continue....')
    wait= input()


#   function name       : modify_item
#   purpose             : change item details in items table
def modify_item():
    clear()
    print('Modify Item Details - Screen')
    print('-'*100)
    item_id = input('Enter Item ID :')
    item_name = input('Enter new Item Name :')
    item_price = input('Enter Item Price :')
    sql = 'update items set item_name = "{}", price ={} where id={}'.format(item_id,item_name,item_price)
    cursor.execute(sql)
    print('\n\nRecord Updated Successfully............')


#   function name           : item_list
#   purpose                 : To display all the items in items tables
def item_list():
    clear()
    sql="select * from items"
    cursor.execute(sql)
    records = cursor.fetchall()
    for row in records:
        print(row)
    print('\nPress any key to continue.....')
    wait = input()



#   function name       : billing
#   purpose             : To generate bills 
def billing():
    clear()
    items =[]
    bill_no = last_bill_no()
    if bill_no[0]==None:
        bill_no =1
    else:
        bill_no = bill_no[0]+1 

    name = input('Enter customer Name :')
    phone = input('Enter Phone No :')
    today = date.today()
    while True:
        no = int(input('Enter item No (0 to stop) :'))
        if no <=0:
            break
        else:
            item = find_item(no)
            if item==None:
                print('Item Not found  ')
            else:
                qty = int(input('Enter Item Qty :'))
                item = list(item)
                item.append(qty)
                items.append(item)

    clear()
    print('                     Absolute Solution              ')
    print('                     D- Block Chander Nagar, GZB     ')
    print('                     Phone: 9871816901, Email: rakesh@binarynote.com ')
    print('Bill No :{}        Date :{}'.format(bill_no,today))
    print('-'*100)
    print('Customer Name :{}          Phone No :{}'.format(name,phone))
    print('-'*100)
    print('Item Id     Item Name                Price            Qty         Amount ')
    print('-'*100)
    total =0
    for item in items:
        print('{:<10d} {:25s} {:.2f} {:>10d}          {:>.2f} \
            '.format(item[0],item[1],item[2],item[3],item[2]*item[3]))
        total = total +item[2]*item[3]
    print('-'*100)
    print('Total Payable amount : {}'.format(total))
    print('\nPress any key to continue........')
    #insert data into tables
    sql ='insert into bills(name,phone,bill_date) values("{}","{}","{}");'.format(name,phone,today)
    cursor.execute(sql)    
    for item in items:
        sql='insert into transaction(item_id,qty,bill_id) values({},{},{});'.format(item[0],item[3],bill_no)
        cursor.execute(sql)
    wait= input()

#   function      : Date_wise_sell
#   purpose       : Create a report on date wise sell or sell between two dates 
def date_wise_sell():
    clear()
    print('Sell Between Two Dates -- Screen')
    print('-'*100)
    start_date = input('Enter start Date (yyyy-mm-dd) :')
    end_date   = input('Enter End Date (yyyy-mm-dd) :')
    sql = 'select * from bills where bill_date between "{}" and "{}"'.format(start_date,end_date)
    cursor.execute(sql)
    records = cursor.fetchall()
    clear()
    print('Bill No       Customer Name         Phone No         Bill Date')
    print('-'*100)
    for row in records:
        print('{:10d} {:30s} {:20s} {}'.format(row[0],row[1],row[2],row[3]))
    print('-'*100)
    print('\n\nPress any key to continue....')
    wait= input()



# function name        : bill information
# purpose               : display details of any bill
def bill_information():
     clear()
    bill_no = input('Enter Bill Number :')
    sql = 'select b.bill_id,b.name,b.phone,b.bill_date,t.item_id,t.qty,i.item_name,i.price from bills b,transaction t,items i \
           where b.bill_id = t.bill_id AND t.item_id= i.id AND \
           b.bill_id ={};'.format(bill_no)
    cursor.execute(sql) 
    records = cursor.fetchall()
    n = cursor.rowcount
    clear()
    print("Bill No :",bill_no)
    print('-'*100)
    if n<=0:
        print('Bill number {} does not exists'.format(bill_no))
    else:
        print('Customer Name : {}  Phone No :{}'.format(records[0][1],records[0][2]))
        print('Bill Date : {}'.format(records[0][3]))
        print('-'*100)
        print('{:10s} {:30s} {:20s} {:10s} {:30s}'.format('ID','Item Name','Qty','Price','Amount'))
        print('-'*100)
        for row in records:
            print('{:<10d} {:30s} {:<20d} {:.2f} {:>.2f}'.format(row[4],row[6],row[5],row[7],row[5]*row[7]))
        print('-'*100)
    print('\nPress any key to continue....')
    wait = input()   



#  function name    : amount_collected
#  purpose          : Function to display amount collected between two dates
def amount_collected():
    clear()
    start_date = input('Enter start Date (yyyy-mm-dd) :')
    end_date   = input('Enter End   Date (yyyy-mm-dd) :')
    clear()
    print('Amount collected between: {} and {}'.format(start_date,end_date))
    print('-'*100)
    sql = 'select sum(t.qty*i.price) from bills b,transaction t,items i \
           where b.bill_date between "{}" AND "{}" AND b.bill_id = t.bill_id AND \
           t.item_id = i.id;'.format(start_date,end_date)
    cursor.execute(sql)
    result = cursor.fetchone()
    print(result)
    print('\nPress any key to continue.....')
    wait= input()


def search_item():
    clear()
    item_name =input('Enter Item Name :')
    sql ='select * from items where item_name like "%{}%";'.format(item_name)
    cursor.execute(sql)
    records = cursor.fetchall()
    clear()
    print('Item Names start with :',item_name)
    print('-'*100)
    print('{:10s} {:30s} {:20s}'.format('Item ID','Item Name','Item Price'))
    print('-'*100)
    for row in records:
        print('{:<10d} {:30s} {:.2f}'.format(row[0],row[1],row[2]))
    print('-'*100)
    print('\nPress any key to continue....')
    wait= input()


def search_customer():
    clear()
    cust_name =input('Enter customer Name :')
    sql ='select * from bills where name like "%{}%";'.format(cust_name)
    cursor.execute(sql)
    records = cursor.fetchall()
    clear()
    print('Customer Names started with :',cust_name)
    print('-'*100)
    print('{:10s} {:30s} {:20s} {:20s}'.format('Bill No','Customer Name','Phone No','Bill Date'))
    print('-'*100)
    for row in records:
        print('{:<10d} {:30s} {:20s} {:20s}'.format(row[0],row[1],row[2],str(row[3])))
    print('-'*100)
    print('\nPress any key to continue....')
    wait= input()


# function name      : search_bill
# purpose            : function to find out bill information
def search_bill():
    clear()
    bill_no = input('Enter Bill Number :')
    sql = 'select b.bill_id,b.name,b.phone,b.bill_date,t.item_id,t.qty,i.item_name,i.price from bills b,transaction t,items i \
           where b.bill_id = t.bill_id AND t.item_id= i.id AND \
           b.bill_id ={};'.format(bill_no)
    cursor.execute(sql) 
    records = cursor.fetchall()
    n = cursor.rowcount
    clear()
    print("Bill No :",bill_no)
    print('-'*100)
    if n<=0:
        print('Bill number {} does not exists'.format(bill_no))
    else:
        print('Customer Name : {}  Phone No :{}'.format(records[0][1],records[0][2]))
        print('Bill Date : {}'.format(records[0][3]))
        print('-'*100)
        print('{:10s} {:30s} {:20s} {:10s} {:30s}'.format('ID','Item Name','Qty','Price','Amount'))
        print('-'*100)
        for row in records:
            print('{:<10d} {:30s} {:<20d} {:.2f} {:>.2f}'.format(row[4],row[6],row[5],row[7],row[5]*row[7]))
        print('-'*100)
    print('\nPress any key to continue....')
    wait = input()   


#  function name    : search_menu
#  purpose          : Display search menu on the screen
def search_menu():
    while True:
        clear()
        print('      S E A R C H    M E N U ')
        print('-'*100)
        print('1.   Item Name')
        print('2.   Customer information')
        print('3.   Bill information')
        print('4.   Back to main Menu')
        choice = int(input('\n\nEnter your choice (1..4): '))
        if choice==1:
            search_item()
        if choice==2:
            search_customer()
        if choice==3:
            search_bill()
        if choice==4:
            break


#  function name    : report_menu
#  purpose          : Display report menu on the screen
def report_menu():
    while True:
        clear()
        print('   R E P O R T   M E N U ')
        print('-'*100)
        print('1.   Item List')
        print('2.   Sell Between Dates')
        print('3.   Bill information')
        print('4.   Amount collected')
        print('5.   Back to main Menu')
        choice = int(input('\n\nEnter your choice (1..5): '))
        if choice==1:
            item_list()
        if choice==2:
            date_wise_sell()
        if choice==3:
            bill_information()
        if choice==4:
            amount_collected()
        if choice==5:
            break


def main_menu():
    while True:
        clear()
        print('      M A I N   M E N U')
        print('-'*100)
        print('1.   Add New Item')
        print('2.   Modify Existing Item')
        print('3.   Billing')
        print('4.   Search Menu')
        print('5.   Report Menu')
        print('6.   Exit')
        choice = int(input('\n\nEnter your choice (1..6): '))
        if choice==1:
            add_item()
        if choice==2:
            modify_item()
        if choice==3:
            billing()
        if choice==4:
            search_menu()
        if choice==5:
            report_menu()
        if choice==6:
            break


if __name__=="__main__":
    clear()
    main_menu()

Limitations and further improvements

Since the whole project is designed to cater command line interface. The first improvement that can be implemented on this system is graphical user interface. Besides that we have not stored the data of companies and expiry dates of the items that can be stored in the system.

The same way company wise inventory has also not been maintained in this system.

final conclusion on Billing system

This project has taken the real world scanario thus the same can be implement on restourant and food court to generate thier billing system. If you want to learn the project development in python then this project can give you a real insight.

Though all care has been taken while developing this project but if you are able to find any bug in this system then that is mine. Source code is totally yours. Use it as you want.

Print Friendly, PDF & Email

Related Posts

If you like CBSEToaday and would like to contribute, you can also write an article using submit article or mail your article to contribute@cbsetoday.com See your article appearing on the cbsetoday.com main page and help other students/teachers.