Inventory Management System Python MySQL Project. To download scroll down to the source code download link.  Items and Customer database Tables stores all the data of items. Structure of  Items and Transaction table.

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

mysql> desc transaction;
+---------+----------+------+-----+---------+----------------+
| Field   | Type     | Null | Key | Default | Extra          |
+---------+----------+------+-----+---------+----------------+
| tid     | int(11)  | NO   | PRI | NULL    | auto_increment |
| dot     | date     | YES  |     | NULL    |                |
| qty     | int(10)  | YES  |     | NULL    |                |
| type    | char(15) | YES  |     | NULL    |                |
| item_id | int(11)  | YES  |     | NULL    |                |
+---------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

NOTE :

• id is Primary key in items Table having auto_increment feature
• tid is the primary key in and item_id is the foreign key in Transaction Table

The item table stores the basic information of all the items in our stock and the Transaction table stores all the transactions is done. using Item_id in the transaction table we are establishing a relationship between these two tables.

Tutorial on Database Connection between Python and MySQL

sample Database Tables with Data

mysql> select * from items;
+----+-----------+--------+------+---------+
| id | name      | price  | qty  | reorder |
+----+-----------+--------+------+---------+
|  1 | Lux       | 200.00 |   15 |      50 |
|  2 | raxona    |  26.45 |   80 |      20 |
|  3 | Dove      |  67.56 |  200 |      50 |
|  4 | Parachute |  20.00 |  100 |      10 |
+----+-----------+--------+------+---------+
4 rows in set (0.00 sec)

mysql> select * from transaction;
+-----+------------+------+----------+---------+
| tid | dot        | qty  | type     | item_id |
+-----+------------+------+----------+---------+
|   1 | 2020-12-02 |  100 | purchase |       3 |
+-----+------------+------+----------+---------+

Python Modules required

MySQL connector,
Pretty Table
DateTime

• MySQL.connector module is responsible for making a connection between Python and MySQL
• pretty table module helps us to display our results in Tabular format.
• DateTime module is used to fetch the current date from the system.

How to install a Python Module.

Python modules can be installed using the pip command. pip command automatically downloads and install the required binaries in our system.

To know more about modules, please check out our tutorial on Python Modules.

Download Source code of Inventory Management Project

Python source code for inventory system

#   project name    : stock management system
#   made by         : rakesh kumar
#   session         : your session
#   roll no         : your roll no

import mysql.connector
from datetime import date
from prettytable import PrettyTable


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

def item_status(idr):
    conn = mysql.connector.connect(
        host='localhost', database='stock', user='root', password='')
    cursor = conn.cursor()
    sql = 'select * from items where id ='+idr
    cursor.execute(sql)
    result = cursor.fetchone()
    return result[3]

def add_item():
    conn = mysql.connector.connect(
        host='localhost', database='stock', user='root', password='')
    cursor = conn.cursor()

    name = input('Enter Item Name :')
    qty = input('Enter Quantity : ')
    price = input('Enter Price :')
    today = date.today()
    reorder = input('Enter Reorder Level :')
    sql = 'insert into items(name,price,qty,reorder) values ( "' + \
        name + '",' + price+','+qty+','+reorder+' );'
    sql2 = 'insert into transaction(dot,qty,type) values ("'+str(today)+'",'+qty+',"purchase");'
    #print(sql)
    cursor.execute(sql)
    cursor.execute(sql2)
    conn.close()
    print('\n\nNew Item added successfully')
    wait = input('\n\n\n Press any key to continue....')

def sell_item():
    conn = mysql.connector.connect(
        host='localhost', database='stock', user='root', password='')
    cursor = conn.cursor()
    today = date.today()
    item_no = input('Enter Item ID :')
    qty = input('Enter sold Quantity : ')
    
    sql = 'update items set qty = qty-'+ qty +' where id='+item_no+';'
    sql2 = 'insert into transaction(dot,qty,type) values ("' + \
        str(today)+'",'+qty+',"sold");'

    #print(sql)
    if item_status(item_no) > qty:
      cursor.execute(sql)
      cursor.execute(sql2)
      print('\n\nItem upated successfully')
    else:
      print('We do not have this much of Quantity in our Stock')

    conn.close()
    
    wait = input('\n\n\n Press any key to continue....')


def received_item():
    conn = mysql.connector.connect(
        host='localhost', database='stock', user='root', password='')
    cursor = conn.cursor()
    today = date.today()
    item_no = input('Enter Item ID :')
    qty = input('Enter sold Quantity : ')
    sql = 'update items set qty = qty+' + qty + ' where id='+item_no+';'
    sql2 = 'insert into transaction(dot,qty,type) values ("' + \
        str(today)+'",'+qty+',"purchase");'
    #print(sql)
    cursor.execute(sql)
    conn.close()
    print('\n\nItem upated successfully')
    wait = input('\n\n\n Press any key to continue....')

def search_item():
    conn = mysql.connector.connect(
        host='localhost', database='stock', user='root', password='')
    cursor = conn.cursor()
    name = input('Enter Item Name :')
    sql ="select * from items where name like '%"+name+"';"
    cursor.execute(sql)
    records = cursor.fetchall()
    for record in records:
        print(record)
    conn.close()
    print('-'*120)
    wait = input('\n\n\n Press any key to continue....')

def search_menu():
    while True:
      clear()
      print(' Search Menu')
      print("\n1.  Name wise")
      print('\n2.  back to Main Menu')
      print('\n\n')
      choice = int(input('Enter your choice ...: '))

      if choice == 1:
        search_item()
      if choice == 2:
        break


def report_items():
    conn = mysql.connector.connect(
        host='localhost', database='stock', user='root', password='')
    cursor = conn.cursor()
    name = input('Enter Item Name :')
    sql = "select * from items;"
    cursor.execute(sql)
    records = cursor.fetchall()
    clear()
    print('Item List')
    t = PrettyTable(['ID', 'Item Name', 'Price', 'Quantity', 'Reorder Level'])

    for idr, name, price, qty, reorder in records:
      t.add_row([idr,name,price,qty,reorder])
    # print(idr,name,fname,add,phone,email)
    print(t)
    conn.close()
    wait = input('\n\n\n Press any key to continue....')


def report_qty_greater_reorder():
    conn = mysql.connector.connect(
        host='localhost', database='stock', user='root', password='')
    cursor = conn.cursor()
    sql = "select * from items where qty > reorder;"
    cursor.execute(sql)
    records = cursor.fetchall()
    clear()
    print('Item List')
    t = PrettyTable(['ID', 'Item Name', 'Price', 'Quantity', 'Reorder Level'])

    for idr, name, price, qty, reorder in records:
      t.add_row([idr, name, price, qty, reorder])
    # print(idr,name,fname,add,phone,email)
    print(t)
    conn.close()
    wait = input('\n\n\n Press any key to continue....')


def report_qty_lesser_reorder():
    conn = mysql.connector.connect(
        host='localhost', database='stock', user='root', password='')
    cursor = conn.cursor()
    sql = "select * from items where qty < reorder;" 
      cursor.execute(sql) 
      records = cursor.fetchall() 
       clear() 
       print('Item List') 
       t = PrettyTable(['ID', 'Item Name', 'Price', 'Quantity', 'Reorder Level']) 
       for idr, name, price, qty, reorder in records:
           t.add_row([idr, name, price, qty, reorder]) 
           # print(idr,name,fname,add,phone,email) 
           print(t) 
      conn.close() 
      wait = input('\n\n\n Press any key to continue....') 

def report_sold_items(): 
      conn = mysql.connector.connect( host='localhost', database='stock', user='root', password='') 
      cursor = conn.cursor() 
       today = date.today() 
      sql = "select id name,dot,t.qty,price from items,transaction t where id=tid and type='sold' and dot='"+str(today)+"';" 
      cursor.execute(sql) 
      records = cursor.fetchall() 
      t = PrettyTable(['ID', 'Item Name', 'DOT', 'Quantity', 'Price']) 
      for idr, name, dot, qty, price in records: 
         t.add_row([idr, name, dot,qty,price]) 
         # print(idr,name,fname,add,phone,email) 
      clear() 
      print('Items Sold Today :', today) 
      print(t) 
      conn.close() 
      wait = input('\n\n\n Press any key to continue....') 

def report_received_item(): 
      conn = mysql.connector.connect( host='localhost', database='stock', user='root', password='') 
      cursor = conn.cursor() 
      today = date.today() 
      sql = "select id, name,dot,t.qty,price from items,transaction t where id=tid and type='purchase' and dot='" + \ 
             str(today)+"';" 
      cursor.execute(sql) 
      records = cursor.fetchall() 
      t = PrettyTable(['ID', 'Item Name', 'DOT', 'Quantity', 'Price']) 
      for idr, name, dot, qty, price in records:
          t.add_row([idr, name, dot, qty, price])
          # print(idr,name,fname,add,phone,email) 
      clear() 
      print('Items Received Today :',today) 
      print(t) 
      conn.close() 
      wait = input('\n\n\n Press any key to continue....') 

def report_menu(): 
  while True: 
      clear() 
      print(' Report Menu') 
      print("\n1. Item List") 
      print('\n2. sold Items - Today') 
      print('\n3. Received Items- Today') 
      print('\n4. Item Qty > Reorder')
      print('\n5.  Item Qty < Reorder')
      print('\n6.  Exit to main Menu')
      print('\n\n')
      choice = int(input('Enter your choice ...: '))

      if choice == 1:
        report_items()
      if choice == 2:
        report_sold_items()
      if choice == 3:
        report_received_item()
      if choice == 4:
        report_qty_greater_reorder()
      if choice == 5:
        report_qty_lesser_reorder()
      if choice == 6:
        break

def main_menu():
    while True:
      clear()
      print(' Main Menu')
      print("\n1.  Add Items")
      print('\n2.  sell Items')
      print('\n3.  Received Items')
      print('\n4.  Search Menu')
      print('\n5.  Report Menu')
      print('\n6.  Close application')
      print('\n\n')
      choice = int(input('Enter your choice ...: '))
      
      if choice == 1:
        add_item()
      if choice == 2:
        sell_item()
      if choice == 3:
        received_item()
      if choice == 4:
        search_menu()
      if choice == 5:
        report_menu()
      if choice == 6:
        break

if __name__ == "__main__":
    main_menu()

How to run Inventory Management System Python Project

Step-1: Unzip the downloaded source code into a folder.
step-2: Folder contains Python Souce code for Inventory management system and MySQL backup file
step-3: Restore MySQL backup data.
step 4: Open Python File in IDE or IDLE and run the project.

Working of Inventory Management software

Please check the attached video for a better understanding of its working.

Hope this project will help you to understand the working of stock management software. Using the sample code you can create your very own inventory management software in Python.