Inventory Management With Python Flask (Free Download) (2023)

Welcome to a tutorial on how to create a simple inventory management system withPython 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!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

TABLE OF CONTENTS

QUICK NOTES

• Create your own project folder, e.g. D:\inventory, unzip the code inside this folder.
• Open the terminal (or command line), navigate to your project folder cd D:\inventory.
• As usual, create a virtual environment.
  • virtualenv venv
  • Windows – venv\scripts\activate
  • Mac/Linux – venv/bin/activate
• Get flask – pip install Flask
• Run python s2_create.py to create the database file.
• Launch python s4_server.py and access http://localhost.

If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

SCREENSHOT

EXAMPLE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

PART 1) THE DATABASE

1A) ITEMS TABLE

s1_inventory.sql

-- (A) ITEMS TABLECREATE 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 DATAINSERT 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

s1_inventory.sql

-- (C) ITEMS MOVEMENTCREATE 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.

PART 2) CREATE THE DATABASE

s2_create.py

# (A) LOAD PACKAGESimport sqlite3, osfrom sqlite3 import Error# (B) DATABASE + SQL FILEDBFILE = "inventory.db"SQLFILE = "s1_inventory.sql"# (C) DELETE OLD DATABASE IF EXISTif os.path.exists(DBFILE): os.remove(DBFILE)# (D) IMPORT SQLconn = 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 s1_inventory.sql and create the actual inventory.db file.

PART 3) INVENTORY LIBRARY

s3_lib.py

# (A) LOAD SQLITE MODULEimport sqlite3from datetime import datetimeDBFILE = "inventory.db"# (B) HELPER - RUN SQL QUERYdef query (sql, data): conn = sqlite3.connect(DBFILE) cursor = conn.cursor() cursor.execute(sql, data) conn.commit() conn.close()# (C) HELPER - FETCH ALLdef select (sql, data=[]): conn = sqlite3.connect(DBFILE) cursor = conn.cursor() cursor.execute(sql, data) results = cursor.fetchall() conn.close() return results# (D) GET ALL ITEMSdef getAll (): res = [] for row in select("SELECT * FROM `items`"): res.append(row) return res# (E) GET ITEMdef get (sku): item = select("SELECT * FROM `items` WHERE `item_sku`=?", [sku]) return item[0] if bool(item) else ()# (F) GET ITEM MOVEMENTdef getMvt (sku): res = [] for row in select("SELECT * FROM `item_mvt` WHERE `item_sku`=?", [sku]): res.append(row) return res# (G) SAVE ITEMdef 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 ITEMdef 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 MOVEMENTdef 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.

PART 4) FLASK SERVER

s4_server.py

# (A) INIT# (A1) LOAD MODULESfrom flask import Flask, render_template, request, make_responseimport sqlite3, jsonimport s3_lib as invlib# (A2) FLASK SETTINGS + INITHOST_NAME = "localhost"HOST_PORT = 80DBFILE = "inventory.db"app = Flask(__name__)# app.debug = True# (B) APP PAGE@app.route("/")def index(): return render_template("s5_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) STARTif __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.

PART 5) USER INTERFACE

5A) HTML PAGE

templates/s5_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> <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.

5B) JAVASCRIPT

static/s5_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 hmItem : null, // html movement current item 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.hmItem = document.getElementById("moveItem"); 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><i class="mi">category</i>[${i[0]}] ${i[1]}</div> <div><i class="mi">tag</i>${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.hmItem.innerHTML = ""; inv.hmList.innerHTML = ""; inv.toggle(3); // (H2) LOAD ITEM inv.fetch("get", { sku: inv.csku }, item => { item = JSON.parse(item); inv.hmItem.innerHTML = `[${item[0]}] ${item[1]}`; }); // (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"> <i class="mi">calendar_today</i> ${r[1]} </div> <div class="mnotes"> <i class="mi">notes</i> ${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 items 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.

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.
• s4_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 s4_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

• Python Flask
• Simple User Login In Python Flask (No Database) – Code Boxx
• qrcode.js
• Javascript QR Code Scanner