Now that we have a database, and a web server, we can start to think about how we might best display our information. THat’s what we’re going to do now.
6. Displaying our results
6.1 Modifying the app.py file
Firstly, we’re going to modify our app.py file. To do this we will add the following to our app.py file.
import sqlite3
from flask import Flask, render_template
Next, we’ll create a function that creates a database connection and returns it.
def get_db_connection():
conn = sqlite3.connect(‘database.db’)
conn.row_factory = sqlite3.Row
return conn
This opens a connection to database.db, and then sets the row_factory attribute to sqlite3.Row. This means we have name based access to columns. This means the database connection will return rows that behave like regular Python dictionaries.
Now we need to make sure that we’ve got the index() function looking right:
@app.route(‘/’)
def index():
conn = get_db_connection()
posts = conn.execute(‘SELECT * FROM items’).fetchall()
conn.close()
return render_template(‘index.html’, items=items)
What this does is open a database connection (using the function we defined earlier). Then we execute an SQL query to select all entries from the items table. We select fetchall() to fetch all of the rows of the query result. This will return a list of all the posts. Must also remember to close the connection, and then return the result to the index.html file.
6.2 Editing index.html file
Now we’re ready to use a for loop to display each item on the index page.
{% extends ‘base.html’ %}
{% block content %}
<h1>{% block title %} Shopping List {% endblock %}</h1>
{% for item in items %}
<a href=”#”>
<h2>{{ item[‘item’] }}</h2>
</a>
<span class=”badge badge-primary”>{{ item[‘created’] }}</span>
<hr>
{% endfor %}
{% endblock %}