I've always wanted an affordable way to keep track of everything in my pantry, so a couple months ago I started working on a project that would do just that. The goal was to make a simple, affordable system that was very easy to use while also storing enough information to be worth the extra effort. What I eventually built is an inventory management system that can store and update information about any item that has a bar code, as well as mine basic data about those items from the internet.

In short, the system works like this.

A bar code is scanned.
A Python script reads the data from the scanner.
The request is sent to a REST API running on node-red.
The API processes the request, mines additional data from the internet, and edits the database accordingly.

All of this is done on a single Raspberry Pi, giving you the ability to update and store data about your entire inventory in one small, portable system. This project is a little technical and a basic understanding of databases, HTTP, and Python will be very helpful, but I will do my best to make it easy enough for a beginner to understand. Let's get started!

Step 1: What You'll Need

The parts you'll need for this project are...

Raspberry Pi
USB Bar Code Scanner (link to the one I'm using)
WiFi adapter (If your Pi doesn’t have built in WiFi)
Toggle Switch
Jumper Wires
Case for your Raspberry Pi (optional)

Step 2: Install and Setup the Database

MySQL is the database management system that will hold all of the data we pull in from the bar code scans. This is very easy to do on the Pi, simple run the following command in the terminal of you Pi.

sudo apt-get install mysql-server

You’ll then be walked through the installation process, and prompted to create a password. That’s it. With MySQL installed, your Pi can act as its own little database server. Now we need to create the tables that will hold our data. First, log in. After installation, the only MySql user is root (the user that has access to every table and system). You can log in as root by running the following command.

mysql -uroot -p<your password>

Soon we’ll set up another user that our system to use but first we need to create our database, and the tables within that database. To do this, run the following commands.

create database inventory;
use inventory;
create table upc_count(upc varchar(15) not null, count integer(3) not null default 0, name varchar(255), size varchar(40), manufacturer varchar(80), primary key (upc));

Now we have a simple table with five columns upc (which will be the primary key), count, name, size, and manufacturer. Note: A upc is a number that uniquely identifies a product. That number is what is read from the bar code label when it is scanned.

Finally, we’re going to set up that user we need. I’m going to call mine, to do this, run the following commands, using whatever username and password you’d like:

grant all on inventory.* to '<your username>'@'localhost' identified by <your password>;

Now that we have our database, we can start building the system!

Step 3: Obtain OutPan API Key

OutPan is an API that can be used to get information about a product using its upc number. We’re going to use this to mine more information about the products as they are added into the database. This is a public api, but in order to use it you need to sign up and get an api key. Signing up is pretty straightforward, simply go here and follow the steps to sign up for a key.

Once you get your key, copy it down. You’ll need it in a later step.

Step 4: Install and Setup Node-Red

Node-Red comes preinstalled on all versions of the Raspbian OS since the end of 2015. To find out if you have node-red installed, simply run the following command in the terminal.

node-red

If a “command not found” message is displayed, you’ll need to install node-red. To do this, run the following commands.

sudo apt-get update sudo apt-get install nodered

After starting node-red, you can access node-red from the address displayed in the output.

The only setup left is to install the MySQL nodes. You can do this through the browser. Click on the symbol in the top right corner of the page and then click on the 'Manage Palette' option. From there simply search for 'mysql' and click the install button.

We're now ready to import the API.

Step 5: Setup the API

Below is the entire node-red API that I wrote. Simply copy everything below, click on the symbol in the top right corner, and go to import → from clipboard.

[{"id":"ef09537e.8b96d","type":"subflow","name":"mineOpenPanData","info":"","in":[{"x":64,"y":57,"wires":[{"id":"b8b6d2e4.169e7"}]}],"out":[{"x":755,"y":58,"wires":[{"id":"8dc2d52b.6a6fd8","port":0}]}]},{"id":"b8b6d2e4.169e7","type":"http request","z":"ef09537e.8b96d","name":"Out Pan Request","method":"GET","ret":"txt","url":"http://api.outpan.com/v2/products/{{{upc}}}?apikey=","tls":"","x":202,"y":57,"wires":[["77727b22.7f7d04"]]},{"id":"8dc2d52b.6a6fd8","type":"function","z":"ef09537e.8b96d","name":"cleanOutPanResponse","func":"if(msg.payload.name !== null){\n    msg.payload = [msg.upc, msg.payload.name, msg.payload.attributes['Manufacturer'], msg.payload.attributes['Net Weight']];\n} else {\n    msg.payload = [msg.upc, null, null, null];\n}\nreturn msg;","outputs":1,"noerr":0,"x":586,"y":56,"wires":[[]]},{"id":"77727b22.7f7d04","type":"json","z":"ef09537e.8b96d","name":"","x":377.5,"y":57,"wires":[["8dc2d52b.6a6fd8"]]},{"id":"80c576d1.6593c8","type":"subflow","name":"getAllUpcs","info":"","in":[{"x":50,"y":30,"wires":[{"id":"2b0416c6.684cba"}]}],"out":[{"x":582,"y":31,"wires":[{"id":"f2b75e2d.f1fc1","port":0}]}]},{"id":"f2b75e2d.f1fc1","type":"mysql","z":"80c576d1.6593c8","mydb":"5cded07d.13a9d","name":"Home Inventory DB","x":394,"y":31,"wires":[[]]},{"id":"2b0416c6.684cba","type":"function","z":"80c576d1.6593c8","name":"prepareGetAll","func":"msg.topic = \"select * from upc_count\";\n\nreturn msg;","outputs":1,"noerr":0,"x":183,"y":31,"wires":[["f2b75e2d.f1fc1"]]},{"id":"5cded07d.13a9d","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"inventory","tz":""},{"id":"c1ae685c.11d1e8","type":"subflow","name":"getByUpc","info":"","in":[{"x":50,"y":30,"wires":[{"id":"2c675944.06b406"}]}],"out":[{"x":636,"y":28,"wires":[{"id":"76ca6e3a.23336","port":0}]}]},{"id":"76ca6e3a.23336","type":"mysql","z":"c1ae685c.11d1e8","mydb":"5cded07d.13a9d","name":"Home Inventory DB","x":455,"y":28,"wires":[[]]},{"id":"2c675944.06b406","type":"function","z":"c1ae685c.11d1e8","name":"prepareUpcInsert","func":"msg.payload = [msg.upc]\nmsg.topic = \"select * from upc_count where upc = ?\";\n\nreturn msg;","outputs":1,"noerr":0,"x":216.5,"y":29,"wires":[["76ca6e3a.23336"]]},{"id":"5cded07d.13a9d","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"inventory","tz":""},{"id":"abd1d86e.af40c8","type":"function","z":"87cf0ee0.bb3d6","name":"insertOrUpdate","func":"// Query strings. Which one is used will be determined by the result of our select query\nvar CREATE_QUERY = \"insert into upc_count set upc = ?, name = ?, manufacturer = ?, size = ?, count = 1;\";\nvar INCREMENT_QUERY = \"update upc_count set count = count + 1 where upc = ?;\";\n\n// if the select query returns anything, we know that upc exists, so we increment it's count by 1\nif(msg.payload.length > 0){\n    msg.topic = INCREMENT_QUERY;\n    msg.action = \"update\";\n} \n// if the select query doesn't return anything, we need to insert that upc into our database\nelse{\n    msg.topic = CREATE_QUERY;\n    msg.action = \"create\";\n}\nmsg.payload = [msg.upc]; // set the '?' to be whatever the upc is\nreturn msg;","outputs":1,"noerr":0,"x":703,"y":212,"wires":[["c1e4b906.6c3168","14012aad.d37b35"]]},{"id":"960bbf74.4e891","type":"mysql","z":"87cf0ee0.bb3d6","mydb":"5cded07d.13a9d","name":"Home Inventory DB","x":1078,"y":212,"wires":[["cb03802a.52dbf"]]},{"id":"cf755452.2523d8","type":"function","z":"87cf0ee0.bb3d6","name":"validatePostBodyUpc","func":"var upc = msg.payload.upc.toString();\n// Any input that is not a number from 12-15 digits is not a upc, and should be ignored\nif(upc.match(/\\d{6,15}/) === null){\n    msg.statusCode = 400;\n   throw \"Invalid UPC could not be processed\";\n}\nmsg.upc = upc\nreturn msg;","outputs":1,"noerr":0,"x":369,"y":212,"wires":[["5ff03613.51dd78"]]},{"id":"5c93f15.bdda81","type":"catch","z":"87cf0ee0.bb3d6","name":"Catch Invalid UPC","scope":["cf755452.2523d8","963d2719.7f3338","3692452.f2517ba"],"x":118.5,"y":540,"wires":[["7cba2d34.e64cc4","a591416b.b7ec2"]]},{"id":"7cba2d34.e64cc4","type":"debug","z":"87cf0ee0.bb3d6","name":"","active":true,"console":"false","complete":"payload","x":334,"y":539,"wires":[]},{"id":"8a5844c3.979048","type":"http in","z":"87cf0ee0.bb3d6","name":"UPC Scan Add Request","url":"/upc","method":"post","swaggerDoc":"","x":132,"y":211,"wires":[["cf755452.2523d8"]]},{"id":"43283053.b43ca","type":"http in","z":"87cf0ee0.bb3d6","name":"Get All Inventory Request","url":"/upc","method":"get","swaggerDoc":"","x":127,"y":427,"wires":[["cdc51dfe.438de"]]},{"id":"aa54b868.2273c8","type":"http response","z":"87cf0ee0.bb3d6","name":"200 Ok","x":508,"y":428,"wires":[]},{"id":"71d4ecee.3d90e4","type":"http in","z":"87cf0ee0.bb3d6","name":"UPC Scan Remove Request","url":"/upc","method":"delete","swaggerDoc":"","x":139,"y":321,"wires":[["963d2719.7f3338"]]},{"id":"963d2719.7f3338","type":"function","z":"87cf0ee0.bb3d6","name":"validateQueryStringUpc","func":"var upc = msg.req.query.upc.toString();\n// Any input that is not a number from 12-15 digits is not a upc, and should be ignored\nif(upc.length > 12 && upc.match(/\\d{12,15}/) === null){\n    msg.statusCode = 400;\n   throw \"Invalid UPC could not be processed\";\n}\nmsg.upc = upc\nreturn msg;","outputs":1,"noerr":0,"x":395,"y":321,"wires":[["75aed5ed.e1a28c"]]},{"id":"5e350762.351648","type":"function","z":"87cf0ee0.bb3d6","name":"updateOrDelete","func":"// Query strings. Which one is used will be determined by the result of our select query\nvar DELETE_QUERY = \"delete from upc_count where upc = ?;\";\nvar DECREMENT_QUERY = \"update upc_count set count = count - 1 where upc = ?;\";\n\n// if the select query returns anything, we know that upc exists, so we increment it's count by 1\nif(msg.payload.length > 0 && msg.payload[0].count > 1){\n    msg.topic = DECREMENT_QUERY;\n} \n// if the select query doesn't return anything, we need to insert that upc into our database\nelse{\n    msg.topic = DELETE_QUERY;\n}\nmsg.payload = [msg.upc]; // set the '?' to be whatever the upc is\nreturn msg;","outputs":1,"noerr":0,"x":788,"y":321,"wires":[["43863b04.6741c4"]]},{"id":"a591416b.b7ec2","type":"http response","z":"87cf0ee0.bb3d6","name":"400 Response","x":350,"y":600,"wires":[]},{"id":"5ff03613.51dd78","type":"subflow:c1ae685c.11d1e8","z":"87cf0ee0.bb3d6","name":"","x":549,"y":212,"wires":[["abd1d86e.af40c8"]]},{"id":"75aed5ed.e1a28c","type":"subflow:c1ae685c.11d1e8","z":"87cf0ee0.bb3d6","x":600,"y":321,"wires":[["5e350762.351648"]]},{"id":"cdc51dfe.438de","type":"subflow:80c576d1.6593c8","z":"87cf0ee0.bb3d6","name":"","x":349,"y":427,"wires":[["aa54b868.2273c8"]]},{"id":"8da9d6be.84d848","type":"http response","z":"87cf0ee0.bb3d6","name":"201 Created","x":1554,"y":213,"wires":[]},{"id":"43863b04.6741c4","type":"mysql","z":"87cf0ee0.bb3d6","mydb":"5cded07d.13a9d","name":"Home Inventory DB","x":1016,"y":320,"wires":[["67cff937.3887f8"]]},{"id":"16b0ba4e.21bd46","type":"http response","z":"87cf0ee0.bb3d6","name":"204 Deleted","x":1533,"y":321,"wires":[]},{"id":"cb03802a.52dbf","type":"function","z":"87cf0ee0.bb3d6","name":"prepareAddHttpResponse","func":"msg.statusCode = 201;\nmsg.payload = msg.upc + \" successfully added!\";\nreturn msg;","outputs":1,"noerr":0,"x":1331,"y":212,"wires":[["8da9d6be.84d848","7612bc6c.10ef14"]]},{"id":"67cff937.3887f8","type":"function","z":"87cf0ee0.bb3d6","name":"prepareRemoveHttpResponse","func":"msg.statusCode = 204;\nmsg.payload = msg.upc + \" successfully removed!\";\nreturn msg;","outputs":1,"noerr":0,"x":1283,"y":320,"wires":[["16b0ba4e.21bd46"]]},{"id":"c1e4b906.6c3168","type":"switch","z":"87cf0ee0.bb3d6","name":"isCreate","property":"action","propertyType":"msg","rules":[{"t":"eq","v":"create","vt":"str"}],"checkall":"true","outputs":1,"x":779,"y":143,"wires":[["d7b49272.289f4"]]},{"id":"d7b49272.289f4","type":"subflow:ef09537e.8b96d","z":"87cf0ee0.bb3d6","name":"mineOutPanData","x":975,"y":143,"wires":[["960bbf74.4e891"]]},{"id":"14012aad.d37b35","type":"switch","z":"87cf0ee0.bb3d6","name":"isUpdate","property":"action","propertyType":"msg","rules":[{"t":"eq","v":"update","vt":"str"}],"checkall":"true","outputs":1,"x":875,"y":250,"wires":[["960bbf74.4e891"]]},{"id":"7612bc6c.10ef14","type":"debug","z":"87cf0ee0.bb3d6","name":"","active":true,"console":"false","complete":"false","x":1565,"y":135,"wires":[]},{"id":"5cded07d.13a9d","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"inventory","tz":""}]

Now you have the entire API that we’ll use to insert and update data. Only a couple adjustments need to be made before we’re ready to use it.

First, go into all the MySQL database nodes and change the username and password to the ones you created for the database in the previous step.
Second, edit the mineOutPanData subflow so that the HTTP request used to get the Open Pan data uses your own API key.

Now you’re ready to use the API. This flow creates a simple REST API which allows you to send data from any device connected to the internet using HTTP requests.

Step 6: (Optional) Understanding the API

The API has 3 methods...

GET /upc: Returns all the data from the upc_count table.
POST /upc: Adds an item based on the upc passed in through the post body. Expects a post body with a single key-value pair: {"upc": }.
DELETE /upc: Removes an item based on the upc passed in through the url. Expects a url query param of upc.

Using these three methods, we can easily communicate with the database using HTTP requests. The POST and DELETE requests will be used by the Python script to edit the count of the upc codes that have been scanned in.

Step 7: Python Driver for Bar Code Scanner

We now have a means to store that data, so we can start working on moving data form the physical bar code labels to the database. This starts with the bar code scanner.

Out of the box, the bar code scanner will scan the labels and return that data to us. What we need is a way to grab that data as it's coming in and direct it to our API. To do that, I wrote a simple Python script that reads does just that.

The script is very simple, it listens for input from the scanner, parses the upc number from that input, and makes the request to our API using that number. The interesting part of this is that we have to read from the bar code scanner as a keyboard. Even though the scanner is a USB device, it is designed to mimic a keyboard, and so the Pi recognizes it as a keyboard (Note that while this is true for many scanners, it is not necessarily true for every scanner).

So we’re going to read from the scanner using a keyboard event processing library in Python. First, we need to get that library installed on python. Do this by running the following command in the terminal.

sudo python -m pip install evdev

Now simply run the script, It will wait for a scan to go through. Once it does the script will create and send an HTTP request to our API and the data will be processed and stored. Navigate to "/dev/input/" one of the "event" files is where the devices input will be written to. The easiest way to find which file belongs to your device is to try running the script with all the "/dev/input/event*" combinations until you find that one that works.

Input is read from a GPIO pin (in this case, pin 21) to determine whether the script should send an add or remove request to the API. So the last thing we need to do is connect a toggle switch to that input pin, so we can easily change between add and remove mode at any time.

barcodescanner.py
Download

Step 8: Connect the Toggle Switch

The last thing we need to do is connect a switch to the GPIO so we can scan in two modes, add and remove.

This is pretty straight forward, simply set up a toggle switch to read from GPIO pin 21 on the Pi and you're good to go. Using the circuit on the attached image (known as a PUD DOWN circuit) the script will send an add request when the toggle switch is closed and a remove request when the toggle switch is open.

After that we simply tape the wires to the inside of the case and we're good to go.

Step 9: (Optional) Create a User Interface

This last step is not necessary but certainly helpful if you want to utilize the full potential of the system. I rigged up a very simple user interface that displayed all the data we have in our database in an easy to navigate table. The table can be sorted by column and also searched, making it easy to see what you have on hand.

The UI is pretty simple; I re-purposed some example code I found online to work with our API (if your interested, that example code can be found here).

To run the UI, do the following...

Save the attached index.txt file as index.html (I could not upload the file as an HTML file for some reason).
Put the two files in the same directory on your computer.
Run the 'index.html' file in your favorite web browser.

Now we can easily see and sort through your inventory!

Step 10: Start Scanning!

Now you're ready to start scanning! If you have any questions leave them in the comments and I’ll be sure to answer when I can.

Lastly, your votes in the contest would be greatly appreciated. Thanks for reading!

Introduction: Integrated Inventory Management System