You will be familiar with the basics of Flask from Chapter 9, Building a Web Server. Once again, we will be using this framework to create a website that can interact with a database.
You can always refer to the Flask documentation site if you find a feature you wish to learn more about: http://flask.pocoo.org/docs/0.10/
Our project will involve creating a number of web forms that can update our SQLite database with information on what items are stocked in our inventory.
First, we need to create a database to store our inventory data in.
In our previous SQL example, we logged in to SQLite to create the database. We can in fact write our SQL in a separate file and dump this into SQLite. This makes managing our source code a lot easier, and we can also re-run it against an empty database whenever we wish.
So we will therefore take our existing data model from Chapter 9, Building a Web Server and convert it into an SQL file. We will also include the INSERT INTO statements along with a collection of items.
Create a new empty file called inventory_schema.sql.
To this, add the following code:
/* Create tables/Data Model */ CREATE TABLE storage_location (id INTEGER PRIMARY KEY AUTOINCREMENT, location VARCHAR(25)); CREATE TABLE food_item (id INTEGER PRIMARY KEY AUTOINCREMENT, description VARCHAR(40), location INTEGER, FOREIGN KEY(location) REFERENCES storage_location(id)); /*Insert data*/ INSERT INTO storage_location (location) VALUES ('Fridge'),('Cupboard'),('Draw'); INSERT INTO food_item (location, description) VALUES (1, 'Eggs'), (1, 'Sausages'),(1, 'Steaks'),(2, 'Pasta sauce'),(2, 'Canned peas'), (2, 'Canned beans'),(3, 'Spaghetti');
This is simply an expansion of the DB we built before. Save the file. We can create a new database using this file with the following command:
sqlite3 inventory.db < inventory_schema.sql
Once this command runs, open up the database in SQLite.
Try running SELECT * FROM food_item; and you will see the food you imported via the SQL file.
Now we have a database in place, we can build an app to sit over it.
Our next task is going to be to write the Python and HTML code needed for the inventory management system.
We are going to create two further directories called static and templates to store our website code in.
Once this is done, create a new Python file called inventory.py and open it in your text editor.
This file will contain our applications code. To this file, add in the following:
#!/usr/bin/python
from flask import Flask,request, session, g, redirect, url_for, abort, render_template, flash
import sqlite3
DATABASE = 'inventory.db'
SECRET_KEY = '1234'
app = Flask(__name__)
app.config.from_object(__name__)
def db_connection():
return sqlite3.connect(app.config['DATABASE'])
@app.before_request
def before_request():
g.db = db_connection()
@app.teardown_request
def teardown_request(exception):
db = getattr(g, 'db', None)
if db is not None:
db.close()
@app.route('/')
def show_inventory():
cur = g.db.execute("SELECT food_item.description, storage_location.location FROM food_item INNER JOIN storage_location ON food_item.location WHERE storage_location.id==food_item.location;")
inventory = [dict(description=row[0], location=row[1]) for row in cur.fetchall()]
return render_template('display_inventory.html', inventory=inventory)
if __name__ == '__main__':
app.run(debug=True, host='0.0.0.0')You will be familiar with the concepts displayed in a lot of this code from your earlier website project. However, we have included a number of new features. Let's take a look at these.
The first are some settings used by the application, these being:
DATABASE = 'inventory.db' SECRET_KEY = '1234'
The DATABASE value contains our inventory SQLite DB. The next setting is the SECRET_KEY, which is needed when implementing sessions in a Flask application.
Following this, we include three functions for handling the database functions, these being:
def db_connection():
return sqlite3.connect(app.config['DATABASE'])
@app.before_request
def before_request():
g.db = db_connection()
@app.teardown_request
def teardown_request(exception):
db = getattr(g, 'db', None)
if db is not None:
db.close()The methods are responsible for opening a DB connection, and closing the connection when we are finished with it.
The final portion of this code that is new is located within the show_inventory() function.
As you can see, we have included a query to return all the items in the inventory database and have included code that passes the results to our template as a variable. This can be seen in the following block of code:
return render_template('display_inventory.html', inventory=inventory)Of course, this now leads us to the next item we need to setup—a template to display these results. This will be written in HTML, which you should be familiar with from our first web application.
Navigate into the templates directory and create a new file called template.html. To this file, add the following:
<!doctype html>
<title>Inventory management system</title>
<link rel=stylesheet type=text/css href="{{ url_for('static', filename='style.css') }}">
<div class=page>
<h1>Inventory management system</h1>
{% block body %}{% endblock %}
</div>This code will form the base template that all of our web forms will inherit from. It uses a technology called Jinja, which provides a template mechanism for Python projects. When we implement Jinja web templates, we can pass variables from our Python code to the template and display the results in HTML.
You can read more about Jinja here: http://jinja.pocoo.org/docs/dev/.
In this block of HTML code, we can see where Jinja has been implemented. This includes:
href="{{ url_for('static', filename='style.css') }}"It also includes:
{% block body %}{% endblock %}Our piece of code is used to include a CSS file from the static directory we created. The second is used to provide an area where our page templates will be rendered. You'll see how this works in more detail next.
We now need to create the HTML that will render the inventory contents on the screen. Create a file under the templates directory called display_inventory.html.
To this file, add the following:
{% extends "template.html" %}
{% block body %}
<ul class="items">
{% for item in inventory %}
<li><h2>{{ item.description }}</h2>{{ item.location }}
{% else %}
<li><em>Your inventory is empty</em>
{% endfor %}
</ul>
{% endblock %}This code renders the results from the query we constructed in our application code. You can see in the for loop that the inventory variable is iterated through and its results are embedded in the HTML code. If the inventory is empty, then we display a message to the user.
This code is rendered inside the body block we created within the template.html file. We achieve this through including the extends keyword with the name of the base template. Next, we wrap the code we want rendered in the name of the block from the base template where it will be displayed, in this case body. Save this file and exit.
Let's try checking what we have developed so far. From the command line, run:
python inventory.py
Once the app is running, navigate to the URL and port where your site will be displayed. You should now see a list of items and their location.
So you may notice we are missing a count. Wouldn't be nice if we knew how many of each of these items we had? Stop the application and let's add this feature.
We can open the existing SQLite database and run the following command to add a column to the food_item table that contains a count of the number of items:
ALTER TABLE food_item ADD COLUMN num INTEGER;
If you like, go back and edit your existing inventory_schema.sql file to include this column. Then next time you create a new database from scratch, it will be included.
With a column to store the value, we can now update our Eggs so there are three of them. To do this, run the following command:
UPDATE food_item SET num = 3 WHERE id = 1;
Once again, you can modify your existing SQL file to include counts for each of the items you insert.
We can now exit SQLite shell and update the HTML and Python code to show the count on the web page. First we will edit the HTML template, display_inventory.html:
<li><h2>{{ item.description }}</h2>{{ item.location }},{{item.count}}Here, we have added in {{item.count}}, which will display the number of items that exist.
Next, we can update the Python code to include the count of items and pass it to the HTML template. To the query embedded in the Python code, add the following:
food_item.num
Next, update the variable inventory to return it to the HTML page:
inventory = [dict(description=row[0], location=row[1], count=row[2]) for row in cur.fetchall()]
You can save this file and restart the application.
Now check the web page again you should see the count!
Let's now include the functionality to add and edit items in our inventory.
Web forms are the mechanism by which we add, edit, and delete data from the database. They provide an easy method for a user to update values via their web browser.
We will need two forms, these being the add form and edit form. The edit form will also double up as the delete form, allowing us to update existing items in an inventory.
Let's start by creating the add functionality.
You will need to start by creating some code to handle the addition of a new item via Python. Open up the inventory.py file and add in the following functions above if __name__: == '__main__':
@app.route('/add_item')
def add_item():
return render_template('add_item.html')
@app.route('/add', methods=['POST'])
def add_to_db():
g.db.execute('INSERT INTO food_item (description, location, num) VALUES (?, ?, ?)',
[request.form['description'], request.form['location'], request.form['num']])
g.db.commit()
flash('New inventory item added')
return redirect(url_for('show_inventory'))Here we have added in the add_item() function, which routes traffic to the HTML form add_item.html that we will create shortly.
Next, we include a function to insert values passed by the web form POST method into the database. Once this has been committed, we flash a message to the user and redirect them back to the display_inventory.html page, where they can see their new item.
Let's now create the add_item.html page in the templates directory. To this file, add the following:
{% extends "template.html" %}
{% block body %}
<form action="{{ url_for('add_to_db') }}" method=POST class="add-item">
<div>Description: <input type="text" size="30" name="description"></div>
<div>Location: <input type="text" size="30" name="location"></div>
<div>Number of items to add: <input type="text" size="30" name="num"></div>
<div><input type="submit" value="Add item"></div>
</form>
{% endblock %}This is a simple web form embedded in the body block.
The action value is set to point to the add_to_db() method we just created. When we submit the form, it will POST the values back to our Flask application, which can then add the values to the database.
Save this file and start up the inventory application again. To test the new form that was created, access it via the following URL: http://<rpi ip address>/add_item
From here, you can now add a new item. When you submit the form, you will then be redirected to the inventory page and see the new item there.
You may notice some problems with this form, however. First, we have to enter a number for the location. It would be better if this had a dropdown list of existing locations.
Also, what if an item already exists; do we want to add it again? What if it is in more than one location?
Let's first modify the add_item() function so we pull a list of locations back in their descriptive form. Stop the application and open up inventory.py. Edit the function so it looks as follows:
@app.route('/add_item')
def add_item():
cur = g.db.execute("SELECT id, location FROM storage_location;")
location = [dict(id=row[0], location=row[1]) for row in cur.fetchall()]
return render_template('add_item.html', location=location)Here, we have added in a query that pulls back the descriptive name of each location and passes it to the add_item.html template as a variable. With this value now available to be displayed, we can edit the form to include it.
We'll be using a dropdown list to display these values. Modify your code to replace the location with the following:
<select name="location">
{% for place in location %}
<option value={{place.id}}>{{place.location}}</option>
{% endfor %}
</select>Using the Jinja for loop, we have built up the values in a dropdown list and included the ID of each of the locations. When we submit the form, we pass the ID back rather than the descriptive version of the location so we can insert the new record.
Save the form and restart the application. When you try adding a new value to the database, you should now see the dropdown list present.
We are still left with the problem of deciding whether we can add an existing item to a new location or allow duplicates of an item.
One way around this is to have a table that links items to locations and the number of each item stored at that location.
See if you can work out how to implement this change!
Since we can add new items, we'll need to be able to edit them to update the count. Let's look at this next.
We will now update the inventory application to present a web form that allows us to add and remove the number of each item we have in the inventory.
To the inventory.py file, add the following code:
@app.route('/edit_item')
def edit_item():
cur = g.db.execute("SELECT food_item.id, food_item.description, food_item.location, food_item.num FROM food_item INNER JOIN storage_location ON food_item.location WHERE storage_location.id==food_item.location;")
items = [dict(id=row[0], description=row[1], location=row[2], num=row[3]) for row in cur.fetchall()]
return render_template('edit_items.html', items=items)
@app.route('/edit', methods=['POST'])
def edit_in_db():
g.db.execute('UPDATE food_item SET num = ? WHERE id = ?',
[request.form['num'], request.form['id']])
g.db.commit()
flash('Item updated')
return redirect(url_for('edit_item'))These two methods are not dissimilar to those that we used to add items to the database. Our first function, edit_item(), returns a list of items from the database along with their location and count, and passes the results to a template.
The second function handles an incoming POST request and updates the count for the relevant item we are editing.
Save this file and create a new HTML template under templates called edit_items.html. To this file, add the following code:
{% extends "template.html" %}
{% block body %}
{% for item in items %}
<form action="{{ url_for('edit_in_db') }}" method=POST class="edit-item">
<div>
<input type="hidden" name="id" value={{item.id}}>
<h2>{{item.description}}</h2>
Storage location: {{item.location}}<br />
Update number of items: <input name="num" type="text" value={{item.num}}>
</div>
<div><input type="submit" value="Edit item"></div>
</form>
{% endfor %}
{% endblock %}This template is very similar to the one we used to add items. Here though we have included a hidden input field. This is used to store the ID of the item we are editing. When we submit the form back to the application, it will use this ID in the query that updates the item count.
Save this file and restart your application. You should now be able to edit an existing item.
Do you see that the location is still a number? Think about how you could modify the query to return the description rather than the ID.
Finally, you may remember we added a static directory. This can be used to store a CSS file for styling your web pages. In this directory, create a new file called style.css.
Throughout the HTML templates we created, you will see a number of CSS classes specified. You can try adding your own styling for these class names to the style.css file. When the web page renders, it will include your styling.
For a guide to styling, you can review the following CSS information: http://www.w3schools.com/css/.
So we have our web application up and running. Let's look at how we can use it to interact with some other hardware. Next we are going to add an LED, which lights up when we get low on items in our inventory.