Building MVP with Flask Day 6 – Retrieve Data with Queryset

Hi everyone.

Today, I would like to finally fill my SQLite database, that I configured last time, with some dummy data. I will do that manually with a Mac SQLite client DB Browser for Mac.

Fill SQLite DB with dummy data

There is a better way to prepare and migrate your dummy data into Flask MVP. This library will help you with data preparation and migration into your SQLite database. First, you prepare your dataset called fixtures in JSON format, then you can test your MVP with an imported dataset.

But at the time of MVP development, I was in a hurry and didn't explore using fixtures further.

Query data with ORM Querysets

Now, after I filled my SQLite tables with some dummy data, I need to figure out, how to query them in Flask router views and finally display them in the template.

With the help of this guide, I will try to add my first query to my index view.

@app.route('/')
def index():
    user = {'username': 'You'}
    products = Product.all()
    print(products)
    return render_template('landingpage/landingLayout.html', title='Home', user=user)

I added ORM query Product.all() and printed results into console for debugging purposes.

But when I try to refresh the browser, it throws an internal server error.

According to the logs, I forgot to import the SQLAlchemy object into Flask. Let's fix it and add import into routes.py.

from app import db

Then change query syntax to use imported DB object.

products = db.session.query(Product).all()

Hmm, still an internal server error.

Maybe I forgot to import DB models into routes.py? My query uses models, right?

from models import Product

Finally another error. Bad gateway means there is an issue somewhere between Nginx and Gunicorn.

The best way to find the root cause is to look into Gunicorn logs.

ModuleNotFoundError: No module named ‘models'

So my models import is wrong. Try different paths for imports.

After all, my models.py resides inside the app folder.

from app.models import Product

Nope, Internal server error again.

I am out of ideas and out of desperation, I try to revert back ORM query syntax.

products = Product.query.all()

Now, I get a bad gateway again. Gunicorn logs reveal the reason.

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table:

Yeah, my ORM query syntax might be wrong, or my Products table does not exist in the database.

Try to change query syntax again without calling the session object.

products = db.query(Product).all()

Which throws a different error.

AttributeError: 'SQLAlchemy' object has no attribute ‘query'

I have no idea what does it means, but I decide to stick with Flask-SQLAlchemy official guide and change ORM query back to:

Product.query.all()

Long story short, after two hours of trials and errors I noticed a strange thing. I had an SQLite database file both in the main project folder and the app folder.

My hunch tells me, that Flask-Migrate creates an SQLite database file with all migration in the project folder. And Flask created its own empty SQLite database file in an app folder. Additionally, my DB config points to an empty SQLite database file in an app folder. That is why the ORM query couldn't find the Products table.

To prove this theory, I edited the DB config relative path to look for the SQLite database file in the project folder instead.

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///../app.sqlite'

Great.

Now my site works again and I even see query results printed into the console.

Using for loop to render data

The last step for today is to print query results in our template. I am exhausted, but the final step should be easy and I will prove myself, that I can render query results on my webpage.

Let's pass query result to our render template function.

return render_template('landingpage/landingLayout.html', title='Home', user=user, products=products)

Quickly add template tags into template file landingLayout.html. Come one, I am hyped with anticipation.

{% for product in products %}
    <h1>{{product["name"]}}</h1>
{% endfor %}

Hell yeah! Products are displayed.

I need to take a rest. Today's session was too exhausting. At least I filled my database with dummy data and displayed it on my front end.

Next time, I would like to design and style our product list so I work a lot with Jinja2 template tags, Bootstrap 4 snippets and CSS style. See ya.