Inventory Management System with Flask and PostgreSQL

Inventory Management System is an integral part of all organizations to manage the information about availability of items in stock and its issues and returns. In this post we will learn how to create a simple online Inventory Management System that allows you to add items, accept requests from employees and Issue items against their requests. The non-consumable items can be returned thus updating the stock.

Development Tools used in Inventory Management System

Flask, PostgreSQL, BootStrap, CSS

Tables Used

The online inventory management system uses these four tables to store the inventory data. You can check the structure of these tables in models.py

ItemCategory

ItemMaster

Request_tbl

Users_tbl

Interface

The interface has two parts Login and Dashboard. Users are categorized as‘Admin’ or ‘User’. The admin type user can add categories, create new items and issue items against the requests generated by users.When Admin or User logs into the dashboard, the name of the user and Logout option is available at the right. When user logs out, she is taken back to the Log in page.

Other users can request the items which will be displayed in the dashboard of Admin when she logs in. When the item is issued It can be returned by the user from her login and it will be added back to the itemMaster table to update the stock.

Admin Dashboard-Inventory Management System

User Dashboard-Inventory Management System

All these activities are done through different forms presenting the input elements to user to enter the required data. These forms are not created as different HTML documents and called as templates to render. The HTML code of a form is saved in a variable called ‘text’ and passed as an argument in the render_template function to the dashboard.html saved in templates folder of the application. So, this Inventory Management System application has only two HTML files. index.html (login) and dashboard.html (interface to display options, forms and data)

Item Category

Item category form is used to create new categories under which the items can be grouped. The user will be presented an input text element where admin user will type in a new category name. On clicking the Save Category button, the category name will be saved in the ItemCategory table and an ID will be automatically assigned to this new category as we have used serial data type for ItemCatID. All the Item categories are displayed at the bottom of this form.

Items

Itemsform is used to add new items in the Inventory database. The user will be presented a dropdown to select the category, input text to enter the name of the item, and its available quantity. On clicking the Save Item button, the Itemdetails will be saved in the ItemMaster table and an Item ID will be automatically assigned to this new item( serial data type for ItemID). All the Items are displayed in a table at the bottom of this form.

Item Request

This form is used by a user of the organization to request Inventory Administrator to isseu an item to her. The user can select an item from the drop down and enter the date of request and quantity required. The entered record will be saved in the Request table along with the userIDof user who sent the request. A request ID of serial data type is generated for each request to be used for issues and returns.

Item Issue

This section is used by the Admin to issue items requested by the user. All pending requests from users will be displayed under ‘Item Issue’ tab along with button ‘Issue’ for each request. On clicking this the request table will be updated for the request ID with an issue date. The status of request will change to ‘I’ for issued. When the requesting user logs into her dashboard, she will see all the items issued to her in her Return tab.

Item Return

This section of the Inventory Management System is given to a user to return the items issued to her. All issued items will be displayed along with button ‘Return’ in each row. On clicking this the request table with specific request ID will be updated with the return date and the updating the status as ‘R’ as returned. This also increments the count of available items in the Item Master.

Inventory Management System Code

Application.py

from flask import Flask, render_template, jsonify, request,redirect,flash, Markup,url_for, session
from models import *
from datetime import date

folder_name="static"

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql://postgres:password@localhost:5432/inventoryDB"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
app.secret_key = b'hkahs3720/'
db.init_app(app)

@app.route("/")
def index():
    return render_template("index.html")
    
@app.route("/logout")
def logout():
    return render_template("index.html")    
    
@app.route("/dashboard", methods=["POST"])
def dashboard():
    unam = request.form.get("username")
    pwd = request.form.get("password")
    
    ulist=user_tbl.query.filter_by(userName=unam).first()
    session['username']= unam
    session['userid']= ulist.userID
    session['utype']= ulist.userType
    if ulist.pwd==pwd:
       # uid=ulist.userid
        return render_template("dashboard.html")
    else:  
        return render_template("index.html")
    

@app.route("/saveItemCategory", methods=["POST"])
def saveItemCategory():
    idesc = request.form.get("ItemCategoryDesc")
    ItemCategor = ItemCategory(ItemCatDesc=idesc)
    db.session.add(ItemCategor)  
    db.session.commit()
    text=idesc+'record Saved Successfully'
    return render_template("dashboard.html", text=text)  
    
@app.route("/ItemsCategory")    
def ItemsCategory():
    catList=ItemCategory.query.all()
    tabl='''<div class="form-group">
            <table class="table table-bordered border-success" ><TH>
            <TD>Item Category ID</TD>
            <TD>Item Category Name</TD></TH>'''  
    for itm in catList:
        tabl=tabl+"<TR ><td></td><TD>"+str(itm.ItemCatID)+"</TD><TD>"+itm.ItemCatDesc+"</TD></TR>"
    tabl=tabl+"</TABLE></div>"   
    strg='''<form action="/saveItemCategory" method="post">
    <div class="text-center"><h5>Add Item Category</h5></div><hr>
<div class="mb-3">
  <label for="ItemCategoryDesc" class="form-label">Item Category Description</label>
  <input type="text" class="form-control" name="ItemCategoryDesc" placeholder="Enter Item Category Name">
</div>
<div class="col-12">
    <button type="submit" class="btn  btn-success mb-3">Save Item Category</button>
</div>

</FORM>
<BR>
'''+tabl
    text= Markup(strg)
    return render_template("dashboard.html", text=text)   

@app.route("/saveItem", methods=["POST"])
def saveItem():
    icatID = int(request.form.get("ItemCatID"))
    itmNam = request.form.get("ItemName")
    AvAmt = int(request.form.get("AvailAmt"))
    ItemMas = ItemMaster(ItemCatID=icatID, ItemDesc=itmNam,AvailAmt=AvAmt )
    db.session.add(ItemMas)  
    db.session.commit()
    text=itmNam+' record Saved Successfully'
    return render_template("dashboard.html", text=text) 
    
@app.route("/Items")    
def Items():
    itmList= ItemMaster.query.all()
    tabl='''<div class="form-group">
            <table class="table table-bordered border-success" ><TH>
            <TD>Item ID</TD>
            <TD>Item Category</TD>
            <TD>Item Name</TD></TH>'''  
    for itm in itmList:
        tabl=tabl+"<TR ><td></td><TD>"+str(itm.ItemID)+"</TD><TD>"+itm.ItemDesc+"</TD><TD>"+itm.itemCat.ItemCatDesc+"</TD></TR>"
    tabl=tabl+"</TABLE></div>"  
    catList= ItemCategory.query.all()
    dd='''<div class="form-group">
            <select class="form-control" name="ItemCatID">'''
    for cat in catList:
        dd=dd+"<option value=\""+str(cat.ItemCatID)+"\">"+cat.ItemCatDesc +"</option>"
    dd=dd+  '''</select>
</div>'''  
    
    
    strg='''<form action="/saveItem" method="post">
<div class="text-center"><h5>Add Items</h5></div><hr>
<div class="mb-3">
  <label for="ItemCatID" class="form-label">Item Category </label>'''+dd+'''
  
</div>
<div class="mb-3">
  <label for="ItemName" class="form-label">Item Description</label>
  <input type="text" class="form-control" name="ItemName" placeholder="Enter Item Name">
</div>
<div class="mb-3">
  <label for="AvailAmt" class="form-label">Available Amount</label>
  <input type="text" class="form-control" name="AvailAmt" placeholder="How many Items available?">
</div>
<div class="col-12">
    <button type="submit" class="btn  btn-success mb-3">Save Item</button>
</div>
</FORM>
<BR>
'''+tabl
    
    text=Markup(strg)
    return render_template("dashboard.html", text=text)

@app.route("/UpdateItems", methods=["POST"])
def UpdateItems():
    iID = int(request.form.get("ItemID"))
    aDate = request.form.get("AddDate")
    aQty = int(request.form.get("AddQty"))
    vName = request.form.get("VendorName")
    aItems = AddItem(AddDate=aDate, AddQty=aQty,VendorName=vName, ItemID=iID)
    upVal = ItemMaster.query.filter_by(ItemID=iID).first()
    newUnits= upVal.AvailAmt
    upVal.AvailAmt = newUnits+aQty
    db.session.add(aItems)  
    db.session.commit()
    text=str(aQty)+' '+upVal.ItemDesc+' Added Successfully'
    return render_template("dashboard.html", text=text) 

@app.route("/AddItems")    
def AddItems():
    itmList= ItemMaster.query.all()
    tabl='''<div class="form-group">
            <table class="table table-bordered border-success" ><TH>
            <TD>Item ID</TD>
            <TD>Item Category</TD>
            <TD>Item Name</TD>
            <TD>Items Available</TD></TH>'''  
    for itm in itmList:
        tabl=tabl+"<TR ><td></td><TD>"+str(itm.ItemID)+"</TD><TD>"+itm.ItemDesc+"</TD><TD>"+itm.itemCat.ItemCatDesc+"</TD><TD>"+str(itm.AvailAmt)+"</TD></TR>"
    tabl=tabl+"</TABLE></div>"  
    dd='''<div class="form-group">
            <select class="form-control" name="ItemID">'''
    for itm in itmList:
        dd=dd+"<option value=\""+str(itm.ItemID)+"\">"+itm.ItemDesc +"</option>"
    dd=dd+  '''</select>
</div>''' 
    strg='''<form action="/UpdateItems" method="post">
<div class="text-center"><h5>Items Available</h5></div><hr>
<div class="mb-3">
  <label for="ItemID" class="form-label">Select Item </label>'''+dd+'''
</div>
<div class="mb-3">
  <label for="AddDate" class="form-label">Select Date</label>
  <input type="date" class="form-control" Name="AddDate" placeholder="Enter Date of Adding Items in Inventory">
</div>
<div class="mb-3">
  <label for="AddQty" class="form-label">Items Count</label>
  <input type="text" class="form-control" name="AddQty" placeholder="Items Count">
</div>
<div class="mb-3">
  <label for="VendorName" class="form-label">Vendor Name</label>
  <input type="text" class="form-control" name="VendorName" placeholder="Vendor Name">
</div>
<div class="col-12">
    <button type="submit" class="btn btn-success mb-3">Save Item</button>
</div>
</FORM><BR>
'''+tabl
    text=Markup(strg)
    return render_template("dashboard.html", text=text)  


@app.route("/issUpdt/<int:tid>")
def issUpdt(tid):
    
    ilist=request_tbl.query.filter_by(reqID=tid).first()
    upVal="I"
    ilist.Status=upVal
    ilist.issueDate= date.today()
    imlist=ItemMaster.query.filter_by(ItemID=ilist.ItemID).first()
    currCnt=imlist.AvailAmt
    imlist.AvailAmt=currCnt-ilist.ReqQty
    db.session.commit()
    text="Item Issued"
    return render_template("dashboard.html", text=text)

@app.route("/itemIssue")    
def itemIssue():
    itmList= request_tbl.query.filter_by(Status=None,)
    strg='''<Form><div class="form-group">
            <table class="table table-bordered border-success" ><TH>
            <TD>Item Name</TD>
            <TD>Date of Request</TD>
            <TD>Quantity Requested</TD>
            <TD>User Name</TD>
            <TD>Issue</TD></TH>'''  
    for itm in itmList:
        strg=strg+"<TR ><td></td><TD>"+str(itm.item.ItemDesc)+"</TD><TD>"+str(itm.ReqDate)+"</TD><TD>"+str(itm.ReqQty)+"</TD>"
        strg=strg+"<TD>"+str(itm.user.userName)+"</TD><TD>"
        strg=strg+"<div class=\"col-12\"><a href="+ url_for('issUpdt',tid=itm.reqID)+">Issue</a></div></TD></TR>"
    strg=strg+"</TABLE></div></FORM>"
    
    text=Markup(strg)
    return render_template("dashboard.html", text=text) 
  
@app.route("/retUpdt/<int:tid>")
def retUpdt(tid):
    ilist=request_tbl.query.filter_by(reqID=tid).first()
    upVal="R"
    ilist.Status=upVal
    ilist.returnDate= date.today()
    imlist=ItemMaster.query.filter_by(ItemID=ilist.ItemID).first()
    currCnt=imlist.AvailAmt
    imlist.AvailAmt=currCnt+ilist.ReqQty
    db.session.commit()
    text="Item Returned"
    return render_template("dashboard.html", text=text)
    
@app.route("/itemReturn")    
def itemReturn():
    itmList= request_tbl.query.filter_by(Status="I",userID=session['userid'])
    strg='''<Form><div class="form-group">
            <table class="table table-bordered border-success" ><TH>
            <TD>Item Name</TD>
            <TD>Date of Issue</TD>
            <TD>Quantity Issued</TD>
            <TD>Return</TD></TH>'''  
    for itm in itmList:
        strg=strg+"<TR ><td></td><TD>"+str(itm.item.ItemDesc)+"</TD><TD>"+str(itm.issueDate)+"</TD><TD>"+str(itm.ReqQty)+"</TD>"
        strg=strg+"<TD><div class=\"col-12\"><a href="+ url_for('retUpdt',tid=itm.reqID)+">Return</a></div></TD></TR>"
    strg=strg+"</TABLE></div></FORM>"
    
    text=Markup(strg)
    return render_template("dashboard.html", text=text)      
    
@app.route("/saveRequest", methods=["POST"])
def saveRequest():
    iID = int(request.form.get("ItemID"))
    rDate = request.form.get("reqDate")
    rQty = request.form.get("reqQuantity")
    uid = session["userid"]
    dept = request.form.get("depttName")
    rItems = request_tbl(ReqDeptt=dept, ReqDate=rDate,ItemID=iID, ReqQty=rQty, userID=uid)
    db.session.add(rItems)  
    db.session.commit()
    text=str(iID)+' '+str(rQty)+' Requested Successfully'
    return render_template("dashboard.html", text=text) 


@app.route("/itemRequest")    
def itemRequest():
    itmList= ItemMaster.query.all()
    dd='''<div class="form-group">
            <select class="form-control" name="ItemID">'''
    for itm in itmList:
        dd=dd+"<option value=\""+str(itm.ItemID)+"\">"+itm.ItemDesc +"</option>"
    dd=dd+  '''</select>
</div>''' 

    itmList= request_tbl.query.filter_by(userID=session['userid'])
    tbl='''<DIV><table class="table table-bordered border-success" ><TH>
            <TD>Item Name</TD>
            <TD>Date of Request</TD>
            <TD>Date of Issue</TD>
            <TD>Quantity Issued</TD></TH>'''  
    for itm in itmList:
        tbl=tbl+"<TR ><td></td><TD>"+str(itm.item.ItemDesc)+"</TD><TD>"+str(itm.ReqDate)+"</TD><TD>"+str(itm.issueDate)+"</TD>"
        tbl=tbl+"<TD>"+str(itm.ReqQty)+"</TD></TR>"
    tbl=tbl+"</TABLE></div>"    
    strg='''<form action="/saveRequest" method="post">
<div class="text-center"><h5>Request Item</h5></div><hr>
<div class="mb-3">
  <label for="ItemID" class="form-label">Select Item </label>'''+dd+'''
</div>
<div class="mb-3">
  <label for="reqDate" class="form-label">Request Date</label>
  <input type="date" class="form-control" name="reqDate" placeholder="Select Request Date">
</div>
<div class="mb-3">
  <label for="reqQty" class="form-label">Request Quantity</label>
  <input type="number" class="form-control" name="reqQuantity" placeholder="Items Count">
</div>
<div class="mb-3">
  <label for="depttName" class="form-label">Department</label>
  <input type="text" class="form-control" name="depttName" placeholder="Department ">
</div>
<div class="col-12">
    <button type="submit" class="btn btn-success mb-3">Request Item</button>
</div>
</FORM><BR>
'''+tbl
    text=Markup(strg)
    return render_template("dashboard.html", text=text)

models.py

import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()


class ItemCategory(db.Model):
    __tablename__="ItemsCategory"
    ItemCatID = db.Column(db.Integer, primary_key=True)
    ItemCatDesc =db.Column(db.String,nullable=False)
    
class ItemMaster(db.Model):
    __tablename__="ItemMaster"
    ItemID = db.Column(db.Integer, primary_key=True)
    ItemCatID = db.Column(db.Integer,db.ForeignKey("ItemsCategory.ItemCatID"), nullable=False)
    ItemDesc = db.Column(db.String,nullable=False)
    AvailAmt = db.Column(db.Integer,nullable=False)
    itemCat = db.relationship("ItemCategory", backref="itemCat", lazy=True)
    
class AddItem(db.Model):
    __tablename__="AddItems"
    transID=db.Column(db.Integer, primary_key=True)
    ItemID = db.Column(db.Integer,db.ForeignKey("ItemMaster.ItemID"), nullable=False)
    AddQty = db.Column(db.Integer, nullable=False)
    AddDate = db.Column(db.Date,nullable=False)
    VendorName = db.Column(db.String,nullable=False)
    
    
class request_tbl(db.Model):
    __tablename__="request_tbl"
    reqID=db.Column(db.Integer, primary_key=True)
    ReqDeptt=db.Column(db.String,nullable=False)  
    ReqDate =db.Column(db.Date,nullable=False)
    ItemID = db.Column(db.Integer,db.ForeignKey("ItemMaster.ItemID"), nullable=False)
    ReqQty = db.Column(db.Integer, nullable=False)
    userID = db.Column(db.Integer,db.ForeignKey("user_tbl.userID"), nullable=False)
    Status=db.Column(db.String,nullable=False) 
    issueDate=db.Column(db.Date,nullable=False) 
    returnDate=db.Column(db.Date,nullable=False)
    item = db.relationship("ItemMaster", backref="item", lazy=True)
    user = db.relationship("user_tbl", backref="user", lazy=True)
    
class user_tbl(db.Model):
    __tablename__="user_tbl"
    userID=db.Column(db.Integer, primary_key=True)
    userName=db.Column(db.String,nullable=False)  
    pwd=db.Column(db.String,nullable=False)  
    userType=db.Column(db.String,nullable=False)

index.html

<HTML>
<head>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-BmbxuPwQa2lc/FVzBcNJ7UAyJxM6wuqIj61tLrc4wSX0szH/Ev+nYRRuWlolflfl" crossorigin="anonymous">
<script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.6.0/dist/umd/popper.min.js" integrity="sha384-KsvD1yqQ1/1+IA7gi3P0tyJcT3vR+NdBTt13hSJ2lnve8agRGXTTyNaBYmCR/Nwi" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta2/dist/js/bootstrap.min.js" integrity="sha384-nsg8ua9HAw1y0W1btsyWgBklPnCUAFLuTMS2G72MMONqmOymq585AcH49TLBQObG" crossorigin="anonymous"></script>
</head>
<body>
<div class="row" style="height:50%; margin-top:5%; ">
 	
	<div class="col-4" >
	
	</div>
	<div class="col-4" style="border-style:solid; border-color:grey; border-width:1px;box-shadow: 10px 10px 10px grey;padding-top:10px">
	<form action="{{ url_for('dashboard')}}" method="post"> 
    <div class="form-group mx-sm-3 mb-2">
    <label for="username" class="sr-only">User Name</label>
    <input type="text" class="form-control" name="username" placeholder="User Name">
	</div>
	<div class="form-group mx-sm-3 mb-2">
    <label for="inputPassword2" class="sr-only">Password</label>
    <input type="password" class="form-control" name="password" placeholder="Password">
	</div>
	<button type="submit" class="btn btn-primary mb-2">Confirm identity</button>
	</form>
	</div>

<body>

dashboard.html

<HTML>
<head>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-BmbxuPwQa2lc/FVzBcNJ7UAyJxM6wuqIj61tLrc4wSX0szH/Ev+nYRRuWlolflfl" crossorigin="anonymous">
<script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.6.0/dist/umd/popper.min.js" integrity="sha384-KsvD1yqQ1/1+IA7gi3P0tyJcT3vR+NdBTt13hSJ2lnve8agRGXTTyNaBYmCR/Nwi" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta2/dist/js/bootstrap.min.js" integrity="sha384-nsg8ua9HAw1y0W1btsyWgBklPnCUAFLuTMS2G72MMONqmOymq585AcH49TLBQObG" crossorigin="anonymous"></script>
</head>
<body>
<div>
<div style="height: 10px; background-color: rgba(0,0,0,0.8);">
<div class="col-12">

</div>
</div>
<div style="height: 2px; background-color: rgba(0,0,0,0.8);">
<div class="col-12">

</div>
</div>
<div class="container overflow-hidden">

<div style="height: 2px; background-color: rgba(255,255,255,0.8);">
<div class="col-12">

</div>
</div>
<div style="height: 50px; background-color: #ADFF2F; padding-left:30%;padding-top:10px">
<h3>Inventory- Issues and Returns</h3>
</div>
<div style="height: 2px; background-color: rgba(5,6,0,0.8);"></div>

</div>
<div class="row" style="height:100%">
<div class="col-2" style="border-style:solid; border-color:grey; border-width:1px; text-align:left">
<div class="col-12">
{% if session['utype'] =='Admin' %}
<div style="width:100%;" >
<a href="{{url_for('ItemsCategory')}}" style="background-color: #ADFF2F;;
  color: black;border-bottom-style:solid;
  padding: 14px 25px;
  text-align: center;
  text-decoration: none;
  display: inline-block;width:100%">Add Item Category</a>
</div>
<div style="width:100%;">
<a href="{{url_for('Items')}}" style="background-color: #ADFF2F;;
  color: black;border-bottom-style:solid;
  padding: 14px 25px;
  text-align: center;
  text-decoration: none;
  display: inline-block;width:100%" >Add an Item</a>
</div>
<div style="width:100%;">
<a href="{{url_for('AddItems')}}" style="background-color: #ADFF2F;;
  color: black;border-bottom-style:solid;
  padding: 14px 25px;
  text-align: center;
  text-decoration: none;
  display: inline-block; width:100%">Add Items</a>
</div>
<div style="width:100%;">
<a href="{{url_for('itemIssue')}}" style="background-color: #ADFF2F;;
  color: black;border-bottom-style:solid;
  padding: 14px 25px;
  text-align: center;
  text-decoration: none;
  display: inline-block;width:100%">Item Issue</a>
</div>
{% endif %}
{% if session['utype'] !='Admin' %}
<div style="width:100%;">
<a href="{{url_for('itemReturn')}}" style="background-color: #ADFF2F;
  color: black;border-bottom-style:solid;
  padding: 14px 25px;
  text-align: center;
  text-decoration: none;
  display: inline-block;width:100%">Item Return</a>
</div>
<div style="width:100%;">
<a href="{{url_for('itemRequest')}}" style="background-color: #ADFF2F;
  color: black;border-bottom-style:solid;
  padding: 14px 25px;
  text-align: center;
  text-decoration: none;
  display: inline-block;width:100%">Item Request</a>
</div>
{% endif %}
</div>
</div>
<div class="col-8" style="border-style:solid; border-color:grey;border-width:1px; padding-top:10px">
{% block body %}
{{text}}
{% endblock %}
</div>
<div class="col-2" style="border-style:solid; border-color:grey;border-width:1px">
<b><a href={{url_for('logout')}}>logout {{session['username']}}!</a></b>
</div>
</div>
  
</div>
</div>
</div>

</body>
</HTML>

Suggested Updates

The presented code creates a basic structure of Inventory Management System application. You can implement following suggested changes to enhance its capability.

Allow modification of Item Category and Item details
Allow user to return only part of issued items
If a user requests more than available items, display a message to reduce requested count of items.

admin