PyMySQL and Flask I

In our project, there are three main data sources currently in the form of a CSV. We’ll need to switch each of these over to a database table, load the data from the CSV, then write functions to be able to query that data.

1. Supplies listed on supplies.html
2. Recipes displayed on index.html
3. Tagged recipes displayed on index.html and in each recipe page

We’ll hang onto those CSV files, but this time as a way to easily access stored data rather than as our only data storage method. In fact, let’s add to our initial functions running as part of our setup.

-- at the end of database.py
if __name__ == "__main__":
    initialize_db()

    with open("supplies.csv") as csvf:
        for supply in csv.DictReader(csvf):
            add_supply(supply)

The “with” statement opens “supplies.csv”, reads in each record (row), then inserts it into the “supplies” database table. We’re using a helper function to streamline this work.

Follow Along with the Instructor

Practice with the instructor. Not an exact replacement for the written directions. Read the text above and below this first before proceeding.

• Continue to up PyMySQL and initialize database.

Add data to the database tables

Just as we did to create the tables, we’ll define a function, then make use of the five steps for using PyMySQL to add data to the tables.

1. create a connection to the database
2. obtain a cursor
3. execute a query
4. commit the changes
5. close the cursor and connection

The rest of the functions we add to database.py will also follow this structure.

Add supplies data

Take a moment to really parse this code and follow what is happening here:

• Find the the five steps for using PyMySQL.
• Notice that we came up with a logical and active function name.

The attribute names in your SQL statement need to match the attribute names set when we created the data table.

# add to database.py after initialize_db function 
def add_supply(supply: dict[str, str]) -> None:
    """takes a dictionary and inserts into a database table"""
    conn = get_connection()
    with conn.cursor() as curr:
        curr.execute(
            "insert into supplies (supply_name, description) values (%s, %s)",
            (
                supply["supply_name"],
                supply["description"],
            ),
        )
    conn.commit()
    conn.close()

Now let’s take a look at how we’ve written the SQL statement.

How we got there

Take the basic SQL needed to insert one row of supply data at a time, written as a string (so no ; because we are not at the command line):

"insert into supplies (supply_name, description) values ('Can Opener','The cheap versions of these usually work just fine, but an upgraded option is OXO. No need for an electric one.')"

The version in our function is written to accomodate supply names and descriptions coming in through a parameter, which means we can reuse this query as we loop through the supply records:

"insert into supplies (supply_name, description) values (supply['supply_name'], supply['description'])"

We then use placeholders to create a more secure format because anytime we are manipulating data in a database, we run the risk of bringing in misformatted or incorrect data, or even malicious code:

"insert into supplies (supply_name, description) values (%s, %s)", (supply['supply_name'], supply['description'])"

• the statement is now a list of two things where the first item is the SQL query, the second is parentheses holding the data to replace the %s placeholders
• %s means the placeholder is a string, and any code coming in will be converted to a string
• the order matters as the first placeholder will be replaced by the first item in the parens and so on
• this doesn’t solve all security issues, but it does mean we won’t accidentally execute malicious code

Techniques like this are required when working with forms. Hackers will actually SKIP the form and jump straight to the functions accessing the database, so anytime you are preparing to manipulate the data in your database, make sure you’ve done everything you can to ensure that, for example, what you thought was a form input asking for a name is indeed what you expect, and that you’re not executing rogue code instead.

Add recipes data

Repeat the techniques used to add the supplies data with the recipes data.

• when running as main, write a “with” statement that opens the “recipes.csv”
• write an add_recipe() function:
  • where the attributes in the INSERT match the attributes set up in the recipe data table EXCEPT for the id because that is “auto incremented” and already set for us,
  • and the number of %s placeholders match the number of attributes mentioned

(Re-)set up the database

Run database.py to add the data to the database:

1. Make sure you are connected to IU’s VPN if you are not on campus (Ivanti)
2. Activate the virtual environment inside your lecture repository
3. Run the database module

source venv/bin/activate
python3 -m flaskapp.database

In a terminal, log into MariaDB to check that the data is there:

SELECT supply_name FROM supplies;

SELECT recipe_name FROM recipes;

You can also do a SELECT * FROM <table>; too if you want to see all the data.

Follow Along with the Instructor

Practice with the instructor. Not an exact replacement for the written directions. Read the text below this first before proceeding.

• Refactor supplies and index routes.

Connect app.py to database.py

Connecting our application to the database is almost complete. What we need to do next is connect app.py to the database module database.py, then scan for WHERE we are working with data, and replace those interactions with calls to the database instead.

At the top of app.py lets add an import for the database module. (I chose to import with a nickname to make using the module a little simpler.)

from flask import Flask, render_template, url_for, redirect, request
from collections import defaultdict

import flaskapp.database as db
import csv

app = Flask(__name__)

Supplies refactor

Next, let’s work through each route, starting with the simplest dataset, the supplies list.

@app.route("/supplies")
def render_supplies():
    all_supplies = get_all_supplies()
    return render_template("supplies.html", all_supplies=all_supplies)

In the current version, we’re getting all the supply data by calling a function get_all_supplies that opens the supplies.csv and grabs all the data as a nested list.

Instead, let’s call a function in our database module to handle getting all the supplies:

# lives in app.py
@app.route("/supplies")
def render_supplies():
    all_supplies = db.get_supplies()
    return render_template("supplies.html", all_supplies=all_supplies)

• The function get_supplies() lives in database.py so we have to reference the module we imported before we can use the function.
• Since the data in all_supplies can still be passed to the supplies template as is, that’s the only change we need to make.

We now need to write the function get_supplies() in database.py:

# lives in database.py
def get_supplies():
    conn = get_connection()
    with conn.cursor() as curr:
        curr.execute("SELECT * FROM supplies")
        supplies = curr.fetchall()
    conn.commit()
    conn.close()
    return supplies

Once we have selected the supplies data, we return it back to where the function was called in app.py. We can also DELETE the get_all_supplies() function in app.py because we no longer need it!

Fetchall and Fetchone

Because we are expecting information to come back to us from the SELECT query, we need a way to grab that information for use in our application.

• fetchall() gets all data returned
• fetchone() gets the first row in the data selected

Home page refactor

1. Update the “/” route (index) so all_recipes is getting recipe data from the database
2. Write the function get_recipes() in database.py (it should look similar to get_supplies)

Check our work

Make sure you’ve activated your virtual environment, start flask and take a look at your application in the web brower.

Uh oh 😬, the browser is displaying an error message. Let’s take a look:

The error mentions “jinja” and sometimes errors like this will also mention “templates”. This means it’s likely an issue in one of the HTML templates.

(If that message says something about apps or python, then you know to look in `app.py’.)

Update index.html

Looks like in updating the structure of the data, we will need to make some minor adjustments to our templates as well.

Update the link to each recipe on index.html

What we want to do here is scan the error message for clues as to what went wrong. In particular the blue highlighted messages will point us in the right direction.

<a href="{{url_for('render_recipe', recipe=all_recipes[recipe]['recipe_slug'])}}">

Looks like we WERE using recipe_slug to identify each recipe, but we now have an unique id for that. We also had a DICTIONARY of dictionaries, and now we have a LIST of dictionaries.

We can see this if we jinja print all_recipes somewhere inside index.html:

{{all_recipes}}

Then we will see the data structure:

[{'id': 1, 'recipe_slug': 'Microwave-Mac-and-Cheese', 'recipe_name': 'Microwave Mac and Cheese', 'description': "This from-scratch mac and cheese...", 'recipe_image': 'images/recipe-images/mac-and-cheese.jpg', 'rating': 4, 'url': 'https://www.foodnetwork.com/recipes/food-network-kitchen/microwave-mac-and-cheese-3363099'}, {'id': 2, 'recipe_slug': '5-Ingredient-Chicken-Pesto-Soup', ...}, {}, ... ]

Which will help us update our code in index.html from:

<a href="{{url_for('render_recipe', recipe=all_recipes[recipe]['recipe_slug'])}}">

to:

<a href="{{url_for('render_recipe', recipe=recipe['recipe_slug'])}}">

The dictionary of dictionaries we has before has advantages, but as you can see, a list of dictionaries is a little shorter in this particular instance. The trick is to not make an assumption about what it should be. Print or jinja print the structure so you can see how best to access the data as you need to.

Update the link to the image for each recipe on index.html

Same issue here. We’re going from a dictionary of dictionaries to a list of dictionaries.

<img class="card-img-top" src="{{url_for('static', filename=recipe]['recipe_image'])}}" alt="{{all_recipes[recipe]['recipe_name']}}">

Once again, we need to adjust to the new data structure.

<img class="card-img-top" src="{{url_for('static', filename=recipe['recipe_image'])}}" alt="{{recipe['recipe_slug']}}">

Update the name of each recipe on index.html

Finally, we update the name of the recipe from:

<h2>{{all_recipes[recipe]['recipe_name']}}</h2>

To:

<h2>{{recipe['recipe_name']}}</h2>

The home page should be running now. If it is not, continue to debug. As you work through this adjustment, you may encounter errors in a different order, or different issue, but the process to fix them is the same. Trace the logic, trace what each variable is set to, and that usually leads to where the issue is located.

Follow Along with the Instructor

Practice with the instructor. Not an exact replacement for the written directions. Read the text below this first before proceeding.

• Refactor recipes route.

Recipe page refactor

In the route /recipes/<recipe>, we are currently getting ALL RECIPES just to be able to find the ONE RECIPE we want. Using SQL, we don’t need to get and set ALL of our database data each time we want to select a single record.

We have a better way of uniquely identifying records

To be clear about what needs to happen, let’s first update the NAME of the route to be /recipes/<recipe_id>. The data coming in from the CSV is a dictionary of dictionaries, and we are using the recipe’s slug to identify an individual recipe.

However, now we now have an unique id in the “recipes” table that we can reference instead of the slug! 🐌

@app.route("/recipes/<recipe_id>")
def render_recipe(recipe_id="None"):
    ...

This means we’ll need to adjust the link on index.html as well:

<a href="{{url_for('render_recipe', recipe_id=recipe['id'])}}">

The left side of the statement saying what data is being passed to app.py needs to match the top of our route @app.route("/recipes/<recipe_id>") – so we will change it to be recipe_id.

Update the recipe route and write get_recipe()

In the recipe route, call a database function called get_recipe(recipe_id) to select a single recipe matching that recipe’s ID.

@app.route("/recipes/<recipe_id>")
def render_recipe(recipe_id="None"):
    one_recipe = db.get_recipe(recipe_id)
    ...

Now head back over to database.py to write the function get_recipe(id).

• our select is needs to be more specific - look at the INSERT examples to see how the formatting works
• the most useful fetch here is “fetchone”, which returns a single tuple. If you use “fetchall” you’ll get a tuple inside a tuple.
• don’t forget to return the recipe data

def get_recipe(id):
    conn = get_connection()
    with conn.cursor() as curr:
        curr.execute("SELECT * FROM recipes WHERE id = %s", (id))
        recipe = curr.fetchone()
    conn.commit()
    conn.close()
    return recipe

The data might be in a different format

One more issue to solve. The data coming back from fetchone is a dictionary. This might mean we need to adjust how we work with it.

{'id': 1, 'recipe_slug': 'Microwave-Mac-and-Cheese', 'recipe_name': 'Microwave Mac and Cheese', 'description': "This from-scratch mac and cheese cooks in one bowl, and you don't have to boil the macaroni or cook the cheese sauce separately. Plus, it's ready in less than half an hour. A blend of American and Jack cheeses makes the sauce smooth and tangy.", 'recipe_image': 'images/recipe-images/mac-and-cheese.jpg', 'rating': 4, 'url': 'https://www.foodnetwork.com/recipes/food-network-kitchen/microwave-mac-and-cheese-3363099'}

Notice the adjustments we made. Change them one at a time and the attempt to navigate to a recipe page in your app to see what errors are thrown. Knowing what it looks like when it’s broken can help you get comfortable with debugging.

@app.route("/recipes/<recipe_id>")
def render_recipe(recipe_id="None"):
    one_recipe = db.get_recipe(recipe_id)
    one_recipe['rating'] = '⭐️ ' * int(one_recipe['rating'])
    return render_template('recipe.html',
                           one_recipe=one_recipe,
                           tagged_as=csv_to_tbr()['recipe_slug'],
                           all_tags=sorted(get_tags())
                           )

Our variable one_recipe was a dictionary before and is still a dictionary. This means when we click on a recipe in the home page, the recipe.html page should still work. If it does not, continue to follow this process to debug your code.

• trace each variable from where it is created in the route, through the new functions we’ve written in database.py, and back to the route’s function, and finally on to a template.
• use print(one_recipe) in Python and {{one_recipe}} in “recipe.html”, for example, to see what a variable looks like at different points in your program. The Python print statements will log their result in the Terminal. The jinja print statements will display in your browser.

Update “Add a Recipe”

One final update based on the recipe data – the /add-recipe route.

@app.route("/add-recipe", methods=['GET', 'POST'])
def add_recipe():
    if request.method == "POST":
        ...
        all_recipes = get_all_recipes()
        all_recipes[recipe_name] = new_recipe
        set_all_recipes(all_recipes)
        # process the form data, then go to the home page
        return redirect(url_for('render_index'))
    else:
        # view the add recipe page
        return render_template("add-recipe.html")

Most of the code here is perfectly fine. And because we take time to make new_recipe into a dictionary, all we really need to do is adjust the three lines getting and setting the data.

Try it out. Replace the following code with a call to the functions in the database module instead:

# replace me
all_recipes = get_all_recipes()
all_recipes[recipe_name] = new_recipe
set_all_recipes(all_recipes)

db.add_recipe(new_recipe)