Make a Flask App with a CSV as a Flat-File Database

7 min readNov 18, 2019

Let’s build this app, then go on an Italian road trip! (Photo by Jonathan Bean on Unsplash)

So you’ve built Flask apps before, but now you want to dip your toes into the daunting world of databases. The least intimidating way to get started with databases is with the most old-school database in the book: the flat file. It’s just a CSV file that stores rows and columns of data, separated by nothing more than line breaks and commas!

In this tutorial, we’ll make an Italian tourism app that tells users about cities and attractions they should visit. This tutorial assumes familiarity with Flask and Python programming. If you want to get acquainted with Flask, check out Build a Python Flask App on Glitch first.

Getting started

First, remix the starter code on Glitch. A few files have been started for you:

static/style.css — A simple stylesheet to style the body of the page and input boxes.
templates/index.html — A template for the home page that reads “The Italian Tourist.”
server.py — The controller for the Flask app. Its route for the home page renders the index.html template.

When you run the app, you should see:

As you’re building your Flask app, know that you need to consistently run the “refresh” command in the console to see your changes.

To access the console, click on “Tools,” then “Logs,” and then select “Console” from your options.

Creating your flat file in .data

Glitch provides a “magic” folder, .data, that will be hidden from other users when they remix your project. In my experience, the .data folder also hides from me when I’m trying to work on my project, so we can only feed our app some starter data ONCE!

We’re going to create our CSV flat file inside the secret .data folder. Create a CSV file inside the .data folder like so:

Now go into places.csv and add some starter data:

City,Attraction,Gif
Rome,Trevi Fountain,http://giphygifs.s3.amazonaws.com/media/FPLcTlwoaszC/giphy.gif
Venice,St. Mark's Basilica,https://media.giphy.com/media/xT9Ighh4t2CyU4PhaE/giphy.gif
Florence,Ponte Vecchio,https://media.giphy.com/media/TkLCp8NeGuKpa/giphy.gif

CSV stands for comma-separated values. By using commas, we put different pieces of data into different columns. The first row creates the columns: city, attraction, and gif. Each subsequent row provides a comma-separated city, attraction, and gif URL.

Feel free to customize this data depending on your own favorite cities, in Italy or elsewhere!

Reading from the database

Ok, we’ve got some actual data to work with! We can edit our server.py index route to read this data from the CSV and modify our index.html template to display the data to the user.

After reading the data from the CSV, I’m going to need to turn the data into a Python-friendly data structure in order to display it on the index.html template. While reading in the CSV, I’m going to create a list of dictionaries. Each row from the CSV will be an element of the list that is transformed into a dictionary.

For example, the line:

Rome,Trevi Fountain,http://giphygifs.s3.amazonaws.com/media/FPLcTlwoaszC/giphy.gif

would become the following dictionary:

{ "city": "Rome",
  "attraction": "Trevi Fountain",
  "gif_url": "http://giphygifs.s3.amazonaws.com/media/FPLcTlwoaszC/giphy.gif"
}

To read the CSV file, we’ll need to import Python’s csv module at the top of server.py:

import csv

Then, to read and process the CSV, we revise the index route in server.py like so:

@app.route("/")
def index():
  with open('.data/places.csv') as csv_file:
    data = csv.reader(csv_file, delimiter=',')
    first_line = True
    places = []
    for row in data:
      if not first_line:
        places.append({
          "city": row[0],
          "attraction": row[1],
          "gif_url": row[2]
        })
      else:
        first_line = False
  return render_template("index.html", places=places)

In the index function, we first open the CSV from its secret hiding place in the .data folder and create a CSV reader object in order to read the data. We want to ignore the header on the CSV (“city, attraction, gif”) because it doesn’t have any real data, so we create a flag variable marking whether or not we’re at the first line. Then we initialize an empty list for all the places. The list variable is called places.

As we loop through each row of the CSV, we add a new dictionary to the list. Each dictionary has the keys “city,” “attraction,” and “gif_url.” This code only runs if we are not on the first line. If we are at the first line, we switch the flag variable to false so that for all the other lines, we can read in the data.

Finally, we render the index.html template, but this time we feed it some data: places=places. Essentially, we are passing the places variable (the list of dictionaries with all the information) to our template. Inside our template, we can still refer to this variable as places and access all its contents.

Rendering the data on the template

Flask allows us to use Python code inside our HTML with the templating language Jinja. Inside of {{ mustache brackets }} in the HTML, we can print the values of variables and even loop through lists!

After the h1 tags but before the closing </body> tag, add in this mix of HTML and Jinja:

{% for place in places %}
    <h2>
      {{ place.city }}
    </h2>
    <p>
      <b>Attraction</b>: {{ place.attraction }}
    </p>
    <p>
      <img src="{{place.gif_url}}"/>
    </p>
{% endfor %}

Jinja uses a for loop — just like in Python — to loop through all the elements in the places variable. For each place, it prints the city name in a secondary header and the attraction in a paragraph. Then it uses the HTML img tag to render the gif on the screen.

If you view your app now, you should see all the places from your secret .data file appearing on the screen! (Remember to run the “refresh” command in the console.)

Writing to the database

Ideally, users could submit cities they recommend others visit! Let’s add an HTML form to the home page where users can submit cities. Add this code below the h1 tags that contain the header:

<h2>
    Submit your favorite city!
</h2>
<form method="post" action="/submit">
    <p>City Name: <input type="text" name="city"></p>
    <p>City Attraction: <input type="text" name="attraction"></p>
    <p>GIF URL: <input type="text" name="gif"></p>
    <p><input type="submit"></p>
</form>

This form will post the place’s city, attraction, and gif to the “/submit” route.

Your home page should now look like this:

Now let’s hop over to server.py and code that “/submit” route.

In a real app, we would want to prevent users from vandalizing our app and put some content filtering/checking system in place. But for the sake of learning, we’re going to allow anyone to submit anything and store it directly to our CSV flat file database. This is bad practice! But let’s do it anyway.

To start, we’re going to make our “/submit” route able to handle both GET requests (when the user simply goes to a webpage) and POST requests (when the user is sending data from a form). Add this code right below your index route:

@app.route("/submit", methods=["GET", "POST"])
def submit():
  if request.method == "GET":
    return redirect(url_for('index'))

If the user tries to GET the submit page, they are simply redirected to the index route (redirect and url_for have already been imported for you from Flask).

However, if the user POSTs to the submit page…

  elif request.method == "POST":
    userdata = dict(request.form)
    city = userdata["city"][0]
    attraction = userdata["attraction"][0]
    gif_url = userdata["gif"][0]
    if len(city) < 2 and len(attraction) < 3 and (len(gif_url) < 10 or "gif" not in gif_url):
      return "Please submit valid data."
    with open('.data/places.csv', mode='a') as csv_file:
      data = csv.writer(csv_file, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
      data.writerow([city, attraction, gif_url])
  return "Thank you!"

For a POST request, the route gathers the place’s city, attraction, and gif URL from the form data (accessed by request.form — request has been imported for you).

Then it checks if the data is valid primarily by string length and also checking if the gif URL contains “gif.” If the data is not valid, we return an error message.

If the data is valid, we open our secret .data/places.csv file in the “append” mode and create a CSV writer object. Then we write new row on the CSV that contains the city, attraction, and gif.

Finally, if all goes well, we return “Thank you!”

Test out your new route by inputting a new city!