loading

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.

  1. A bar code is scanned.
  2. A Python script reads the data from the scanner, formats an HTTP request, and sends the request to a an api running on node-red.
  3. 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 user is root (the user that acts as the owner of the 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 for 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.

    1. 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.
    2. 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...

    1. GET /upc: Returns all the data from the upc_count table.
    2. 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": }.
    3. 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.

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

    1. Save the attached index.txt file as index.html (I could not upload the file as an HTML file for some reason).
    2. Put the two files in the same directory on your computer.
    3. 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!

    I'm planning on buying the parts to build this tomorrow, but I think I may have a more elegant solution than the toggle switch. You could have two special barcodes stuck to the Pi or nearby that would let you switch between modes. Easy peasy!
    <p>sir! can i used this for my thesis?</p>
    <p>Of course! Best of luck to you and let me know if I can be of any help!</p>
    I have a C.H.I.P. (a $9 &quot;computer&quot; board with built in wifi) laying around. It runs linux, so I should be able to use that for this. Can you tell me where in the code the GPIO pins are selected so I can use another method of selecting add or decrement? Thanks...
    <p>The input pin is specified by the variable MODE_INPUT_PIN on line 12. The input from the pin is read in the get_switch_mode function starting on line 47.</p><p>I recently got a couple CHIP's myself, and was wondering how this project would run on one of them. Let me know how it turns out!</p>
    A solid instructable made great by serving a truly useful task. <br>Nice work! <br><br>Hmm....I'm already thinking about having items that reach 0 automatically added to a Google Sheet or Google Keep list. There's a ton of expansion potential here that will be fairly easy to do because of the well thought out base platform.
    wow, this is a really cool implementation! If I build this I might post it to our intranet and display the inventory on the fridge or something. should try to &quot;obtain&quot; one of the Walmart books for vegetables too!
    <p>Can you read ISBN or EAN codes to manage books ?</p>
    <p>I actually tried scanning some books when I was developing the system just because I had a couple lying around. Not only did it work, but for some reason OutPan had the titles of the two books I scanned (the books were A Game of Thrones and Red Seas Over Red Skies).</p><p>If you want to scan ISBN's you'll need to do a little bit of adjusting to the upc api. Right now the 'validatePostBodyUpc' and 'validateQueryStringUpc' nodes check to see if the input upc is a number between 12 and 15 digits, and if it is not it will throw a 400 and will not continue processing. An ISBN format will fail because it contains dashes, so you'll need to change those nodes to allow for that.</p>
    <p>is there a way to either submit information to outpan to update the entries that come back blank, or edit your inventory through the GUI to update it on your end? or is the GUI just read only to see what you have on hand?</p>
    <p>Currently, the UI I made just reads and displays the data, but my goal is to add edit capabilities to the UI sometime soon.</p><p>The code that I re-purposed for the UI (found at https://editor.datatables.net/examples/advanced/RE...) actually included functionality to bind a REST API call to a button. If you wanted to edit anything more than the count, though, you'd need to add another endpoint to your node-red api that would allow you to edit other fields for a upc as well.</p><p>And if you are already adding attributes for your upc's, might as well submit that information to OutPan so we can improve their api. All you have to do is make a simple POST request. The documentation for how to do that can be found at (https://www.outpan.com/developers).</p>
    <p>Hi all I have a shared Google Keep note with the wife for shopping and was thinking about mounting one of these above the kitchen bin and have anything I scan to get added to the list. Can anyone point me in the right direction :-)</p>
    Just this week I was actually looking for a simple to use inventory program for my shop! This is PERFECT! Thank you so much for publishing this! Too many exclamation points? lo :-)

    About This Instructable

    8,012views

    169favorites

    License:

    More by Justiniann:Integrated Inventory Management System Smart Solar Panel with Arduino 
    Add instructable to: