Inventory Management With Python Flask (Free Download)

Welcome to a tutorial on how to create a simple inventory management system with Python Flask and SQLite. Need something that is a little better than an Excel spreadsheet to manage your inventory? Here’s a simple project that may help – Read on!

 

 

TABLE OF CONTENTS

 

DOWNLOAD & NOTES

Here is the download link to the example code, so you don’t have to copy-paste everything.

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

PYTHON FLASK INVENTORY

All right, let us not get into more details on how the inventory system works in Python Flask and SQLite.

 

 

QUICK SETUP

  • Create a virtual environment virtualenv venv and activate it – venv\Scripts\activate (Windows) venv/bin/activate (Linux/Mac)
  • Install required libraries – pip install flask
  • For those who are new, the default Flask folders are –
    • static Public files (JS/CSS/images/videos/audio)
    • templates HTML pages

 

 

STEP 1) THE DATABASE

1A) ITEMS TABLE

S1A_inventory.sql
-- (A) ITEMS TABLE
CREATE TABLE items (
  item_sku TEXT,
  item_name TEXT NOT NULL,
  item_unit TEXT NOT NULL,
  item_qty INTEGER NOT NULL,
  PRIMARY KEY("item_sku")
);

CREATE INDEX `item_name`
  ON `items` (`item_name`);

-- (B) DUMMY DATA
INSERT INTO "items" VALUES
('ABC001','Foo Bar','PC','123'),
('ABC002','Goo Bar','BX','234'),
('ABC003','Joo Bar','EA','321'),
('ABC004','Koo Bar','CS','456'),
('ABC005','Zoo Bar','PL','543');

First, we have a simple table to store all the available items.

  • item_sku Primary key.
  • item_name Item name.
  • item_unit Unit count (PC pieces, BX boxes, etc…)
  • item_qty Current quantity in stock.

 

1B) ITEM MOVEMENTS TABLE

S1B_inventory.sql
-- (C) ITEMS MOVEMENT
CREATE TABLE `item_mvt` (
  item_sku TEXT,
  mvt_date TEXT,
  mvt_direction TEXT NOT NULL,
  mvt_qty INTEGER NOT NULL,
  mvt_notes TEXT,
  PRIMARY KEY("item_sku", "mvt_date")
);

CREATE INDEX `mvt_direction`
  ON `item_mvt` (`mvt_direction`);

Next, we have another table to store item movements.

  • item_sku Composite primary and foreign key.
  • mvt_date Composite primary key.
  • mvt_direction The direction of the stock movement.
    • I In (receive)
    • O Out (send)
    • S Stock Take
  • mvt_qty Quantity moved.
  • mvt_notes Notes, if any.

 

 

1C) CREATE THE DATABASE

S1B_create.py
# (A) LOAD PACKAGES
import sqlite3, os
from sqlite3 import Error

# (B) DATABASE + SQL FILE
DBFILE = "inventory.db"
SQLFILE = "S1A_inventory.sql"

# (C) DELETE OLD DATABASE IF EXIST
if os.path.exists(DBFILE):
  os.remove(DBFILE)

# (D) IMPORT SQL
conn = sqlite3.connect(DBFILE)
with open(SQLFILE) as f:
  conn.executescript(f.read())
conn.commit()
conn.close()
print("Database created!")

Well, this is just a simple script to read S1A_inventory.sql and create the actual inventory.db file.

 

STEP 2) INVENTORY LIBRARY

S2_lib.py
# (A) LOAD SQLITE MODULE
import sqlite3
from datetime import datetime
DBFILE = "inventory.db"

# (B) HELPER - RUN SQL QUERY
def query (sql, data):
  conn = sqlite3.connect(DBFILE)
  cursor = conn.cursor()
  cursor.execute(sql, data)
  conn.commit()
  conn.close()

# (C) HELPER - FETCH ALL
def select (sql, data=[]):
  conn = sqlite3.connect(DBFILE)
  cursor = conn.cursor()
  cursor.execute(sql, data)
  results = cursor.fetchall()
  conn.close()
  return results

# (D) GET ALL ITEMS
def getAll ():
  return select("SELECT * FROM `items`")

# (E) GET ITEM
def get (sku):
  item = select("SELECT * FROM `items` WHERE `item_sku`=?", [sku])
  return item[0] if bool(item) else ()

# (F) GET ITEM MOVEMENT
def getMvt (sku):
  return select("SELECT * FROM `item_mvt` WHERE `item_sku`=? ORDER BY `mvt_date` DESC", [sku])

# (G) SAVE ITEM
def save (sku, name, unit, osku=""):
  # (G1) ADD NEW
  if (osku==""):
    query(
      """INSERT INTO `items` 
      (`item_sku`, `item_name`, `item_unit`, `item_qty`) 
      VALUES (?, ?, ?, 0)""",
      [sku, name, unit]
    )

  # (G2) UPDATE
  else :
    # (G2-1) ITEM ITSELF
    query(
      """UPDATE `items` SET
      `item_sku`=?, `item_name`=?, `item_unit`=?
      WHERE `item_sku`=?""",
      [sku, name, unit, osku]
    )

    # (G2-2) SKU FOR MOVEMENT HISTORY
    if sku != osku:
      query(
        "UPDATE `item_mvt` SET `item_sku`=? WHERE `item_sku`=?",
        [sku, osku]
      )
  return True

# (H) DELETE ITEM
def delete (sku):
  # (H1) DELETE ITEM
  query("DELETE FROM `items` WHERE `item_sku`=?", [sku])

  # (H2) DELETE ITEM MOVEMENT
  query("DELETE FROM `item_mvt` WHERE `item_sku`=?", [sku])
  return True

# (I) SAVE MOVEMENT
def saveMV (sku, direction, qty, notes):
  # (I1) MOVEMENT ENTRY
  query(
    """INSERT INTO `item_mvt` 
    (`item_sku`, `mvt_date`, `mvt_direction`, `mvt_qty`, `mvt_notes`) 
    VALUES (?, ?, ?, ?, ?)""",
    [sku, datetime.now().isoformat(), direction, qty, notes]
  )

  # (I2) UPDATE QUANTITY
  sql = "UPDATE `items` SET `item_qty`="
  if direction=="I":
    sql += "`item_qty`+?"
  elif direction=="O":
    sql += "`item_qty`-?"
  else:
    sql += "?"
  sql += " WHERE `item_sku`=?";
  query(sql,[qty, sku])
  return True

Not going to explain this line-by-line… So here’s a quick summary of the functions.

  • query(sql, data) A helper function to run an SQL query.
  • select(sql, data) A helper function to run a SELECT query.
  • getAll() Get all items.
  • get(sku) Get the specified item.
  • getMvt(sku) Get the movement history of the specified item.
  • save(sku, name, unit, osku) Add or update an item.
  • delete(sku) Delete the specified item.
  • saveMV(sku, direction, qty, notes) Add a new item movement entry.

 

 

STEP 3) FLASK SERVER

S3_server.py
# (A) INIT
# (A1) LOAD MODULES
from flask import Flask, render_template, request, make_response
import sqlite3, json
import S2_lib as invlib

# (A2) FLASK SETTINGS + INIT
HOST_NAME = "localhost"
HOST_PORT = 80
DBFILE = "inventory.db"
app = Flask(__name__)
# app.debug = True

# (B) APP PAGE
@app.route("/")
def index():
  return render_template("S4A_main.html")

# (C) AJAX REQUESTS
# (C1) GET ALL ITEMS
@app.route("/req/getall/", methods=["POST"])
def rGetAll():
  return json.dumps(invlib.getAll())

# (C2) GET ITEM
@app.route("/req/get/", methods=["POST"])
def rGet():
  data = dict(request.form)
  return json.dumps(invlib.get(data["sku"]))

# (C3) SAVE ITEM
@app.route("/req/save/", methods=["POST"])
def rSave():
  data = dict(request.form)
  print(data)
  invlib.save(data["sku"], data["name"], data["unit"], data["osku"])
  return "OK"

# (C4) DELETE ITEM
@app.route("/req/delete/", methods=["POST"])
def rDelete():
  data = dict(request.form)
  invlib.delete(data["sku"])
  return "OK"

# (C5) GET ITEM MOVEMENT
@app.route("/req/getmvt/", methods=["POST"])
def rGetMvt():
  data = dict(request.form)
  return json.dumps(invlib.getMvt(data["sku"]))

# (C6) SAVE ITEM MOVEMENT
@app.route("/req/savemvt/", methods=["POST"])
def rSaveMvt():
  data = dict(request.form)
  invlib.saveMV(data["sku"], data["direction"], data["qty"], data["notes"])
  return "OK"

# (D) START
if __name__ == "__main__":
  app.run(HOST_NAME, HOST_PORT)
  1. Loading the required modules, and a bunch of settings.
  2. Well, the main HTML page itself.
  3. To handle all the AJAX calls. Pretty much “mapping” URL endpoints to the library functions.
  4. Self-explanatory.

 

 

STEP 4) USER INTERFACE

4A) HTML PAGE

templates/S4A_main.html
<!-- (A) ITEMS LIST -->
<div id="itemWrap">
  <div id="itemAdd" class="row" onclick="inv.toggle(2, true)">
    <i class="mi">add</i>
  </div>
  <div id="itemList"></div>
</div>
 
<!-- (B) ADD/EDIT ITEM -->
<div id="iFormWrap" class="hide"><form id="iForm" onsubmit="return inv.save()">
  <div id="iFormClose" onclick="inv.toggle(2)">X</div>
  <label>SKU</label>
  <input type="text" required name="sku">
  <label>Item Name</label>
  <input type="text" required name="name">
  <label>Item Unit</label>
  <input type="text" required name="unit">
  <input type="submit" value="Save">
</form></div>
 
<!-- (C) ITEM MOVEMENT -->
<div id="moveWrap" class="hide">
  <!-- (C1) CURRENT ITEM -->
  <div id="moveHead">
    <div id="moveItem">
      <div id="moveItemName"></div>
      <div id="moveItemQty"></div>
    </div>
    <div id="moveBack" class="mi" onclick="inv.toggle(1)">reply</div>
  </div>
 
  <!-- (C2) ADD MOVEMENT -->
  <form id="moveAdd" onsubmit="return inv.savemvt()">
    <select name="direction">
      <option value="I">Receive</option>
      <option value="O">Send</option>
      <option value="T">Stock Take</option>
    </select>
    <input type="number" name="qty" placeholder="Quantity" required>
    <input type="text" name="notes" placeholder="Notes (if any)">
    <input type="submit" class="mi" value="done">
  </form>
 
  <!-- (C3) MOVEMENT LIST -->
  <div id="moveList"></div>
</div>

The HTML page is seemingly complicated at first, but keep calm and look closely. There are only 3 main sections.

  1. A “main page” to show the list of items.
  2. This is a hidden popup form to add/edit an item.
  3. Lastly, another page to show/add item movement.

 

4B) JAVASCRIPT

static/S4B_main.js
var inv = {
  // (A) PROPERTIES
  hiWrap : null, // html items wrapper
  hiList : null, // html items list
  hfWrap : null, // html item form wrapper
  hfForm : null,  // html item form
  hmWrap : null, // html movement wrapper
  hmIName : null, // html movement current item
  hmIQty : null, // html movement current item quantity
  hmForm : null, // html add movement form
  hmList : null, // html movement list
  csku : null, // current sku editing or show movement 
  mdirection : { I : "Receive", O : "Send", T : "Stock Take" },

  // (B) SUPPORT FUNCTION - FETCH
  fetch : (req, data, after) => {
    // (B1) FORM DATA
    let form;
    if (data instanceof FormData) { form = data; }
    else {
      form = new FormData();
      if (data != null) { for (let [k, v] of Object.entries(data)) {
        form.append(k, v);
      }}
    }

    // (B2) AJAX FETCH
    fetch("/req/"+req, { method: "post", body : form })
    .then(res => res.text())
    .then(txt => after(txt));
  },

  // (C) INIT
  init : () => {
    // (C1) GET HTML ELEMENTS
    inv.hiWrap = document.getElementById("itemWrap");
    inv.hiList = document.getElementById("itemList");
    inv.hfWrap = document.getElementById("iFormWrap");
    inv.hfForm = document.getElementById("iForm");
    inv.hmWrap = document.getElementById("moveWrap");
    inv.hmIName = document.getElementById("moveItemName");
    inv.hmIQty = document.getElementById("moveItemQty");
    inv.hmForm = document.getElementById("moveAdd");
    inv.hmList = document.getElementById("moveList");

    // (C2) LOAD ITEMS LIST
    inv.drawlist();
  },

  // (D) TOGGLE HTML INTERFACE
  toggle : (mode, sku) => {
    // (D1) ITEMS LIST
    if (mode == 1) {
      inv.hiWrap.classList.remove("hide");
      inv.hfWrap.classList.add("hide");
      inv.hmWrap.classList.add("hide");
    }
 
    // (D2) ADD/EDIT ITEM
    else if (mode == 2) {
      // (D2-1) HIDE MOVEMENT + SHOW ITEMS LIST
      inv.hiWrap.classList.remove("hide");
      inv.hmWrap.classList.add("hide");
 
      // (D2-2) CLOSE ITEM FORM
      if (sku == undefined) { inv.hfWrap.classList.add("hide"); }
 
      // (D2-3) ADD NEW ITEM
      else if (sku === true) {
        inv.csku = "";
        inv.hfForm.reset();
        inv.hfWrap.classList.remove("hide");
      }
 
      // (D2-4) EDIT ITEM
      else {
        inv.fetch("get", { sku: sku }, item => {
          let fields = inv.hfForm.querySelectorAll("input[type=text]");
          item = JSON.parse(item);
          inv.csku = sku;
          for (let i=0; i<fields.length; i++) {
            fields[i].value = item[i];
          }
          inv.hfWrap.classList.remove("hide");
        });
      }
    }
 
    // (D3) ITEM MOVEMENT
    else {
      inv.hiWrap.classList.add("hide");
      inv.hfWrap.classList.add("hide");
      inv.hmWrap.classList.remove("hide");
    }
  },
 
  // (E) DRAW HTML ITEMS LIST
  drawlist : () => {
    inv.toggle(1);
    inv.fetch("getall", null, items => {
      items = JSON.parse(items);
      inv.hiList.innerHTML = "";
      for (let i of items) {
        let row = document.createElement("div");
        row.className = "row";
        row.innerHTML = `<div class="info">
          <div class="item">[${i[0]}] ${i[1]}</div>
          <div class="stock">${i[3]} ${i[2]}</div>
        </div>
        <input type="button" class="mi" value="delete" onclick="inv.del('${i[0]}')">
        <input type="button" class="mi" value="edit" onclick="inv.toggle(2, '${i[0]}')">
        <input type="button" class="mi" value="zoom_in" onclick="inv.drawmvt('${i[0]}')">`;
        inv.hiList.appendChild(row);
      }
    });
  },
 
  // (F) SAVE ITEM
  save : () => {
    let form = new FormData(inv.hfForm);
    form.append("osku", inv.csku);
    inv.fetch("save", form, res => {
      if (res == "OK") {
        inv.drawlist();
        inv.toggle(2);
      } else { alert(res); }
    });
    return false;
  },
 
  // (G) DELETE ITEM
  del : sku => { if (confirm(`Delete ${sku}?`)) {
    inv.fetch("delete", { sku : sku }, res => {
      if (res == "OK") { inv.drawlist(); }
      else { alert(res); }
    });
  }},
 
  // (H) DRAW MOVEMENT
  drawmvt : sku => {
    // (H1) "INIT"
    if (sku) { inv.csku = sku; }
    inv.hmIName.innerHTML = "";
    inv.hmIQty.innerHTML = "";
    inv.hmList.innerHTML = "";
    inv.toggle(3);
 
    // (H2) LOAD ITEM
    inv.fetch("get", { sku: inv.csku }, item => {
      item = JSON.parse(item);
      inv.hmIName.innerHTML = `[${item[0]}] ${item[1]}`;
      inv.hmIQty.innerHTML = `${item[3]} ${item[2]}`;
    });
 
    // (H3) LOAD HISTORY
    inv.fetch("getmvt", { sku: inv.csku }, rows => {
      rows = JSON.parse(rows);
      for (let r of rows) {
        let row = document.createElement("div");
        row.className = "row";
        row.innerHTML = `<div class="mqty mqty${r[2]}">
          ${inv.mdirection[r[2]]} ${r[3]}
        </div>
        <div class="minfo">
          <div class="mdate">${r[1]}</div>
          <div class="mnotes">${r[4]}</div>
        </div>`;
        inv.hmList.appendChild(row);
      }
    });
  },
 
  // (I) SAVE MOVEMENT
  savemvt : () => {
    let form = new FormData(inv.hmForm);
    form.append("sku", inv.csku);
    inv.fetch("savemvt", form, res => {
      if (res == "OK") {
        inv.hmForm.reset();
        inv.drawmvt();
      } else { alert(res); }
    });
    return false;
  }
};
window.onload = inv.init;

Lastly, not going to explain this massive chunk once again. But the Javascript essentially drives the HTML interface.

  1. Properties – Mostly reference to the related HTML elements.
  2. fetch() A support function to do AJAX fetch calls to s4_server.py.
  3. init() Runs on page load. Gets the related HTML elements and draws the list of items.
  4. toggle() Switches between the items list, item form, and movement list.
  5. drawlist() Get item data from the server and draw them in an HTML list.
  6. save() Save an item.
  7. del() Delete an item.
  8. drawmvt() Get item movement data from the server and draw them in HTML.
  9. savemvt() Add a movement entry.

 

 

EXTRAS

That’s all for the search example, and here are a few extra bits that may be useful to you.

 

IMPROVEMENT IDEAS

Possible upgrades that you can consider:

  • SQLite works, but is not good for “professional” and cloud-based setups. Consider switching to MySQL, Postgre, MSSQL, etc…
  • Small businesses can get by without a user system. But it is better to have an actual login and security as the business grows, also add a “user ID” field to the movement table for integrity.
  • Generate QR codes and even scan them.
  • Pagination.
  • S3_server.py already offers endpoints for AJAX calls. It is totally possible to support mobile apps with these.
  • Add your own reports.

 

HOW ABOUT TKINTER?

If you want an “offline system”, feel free to reuse the entire library. Ditch the entire S3_server.py and rewrite the interface using Tkinter. The reason why I adopted Flask is simple – A web-based system is platform-independent. Things work so long as the device has a good enough web browser.

 

LINKS & REFERENCES

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published. Required fields are marked *