Flask 101: Adding, Editing and Displaying Data

Last time we learned how to add a search form to our music database application. Of course, we still haven’t added any data to our database, so the search form doesn’t actually do much of anything except tell us that it didn’t find anything. In this tutorial we will learn how to actually add data, display search results and edit entries in the database.

Let’s get started!

Adding Data to the Database

Let’s start by coding up our new album form. Open up the “forms.py” file we created in the last tutorial and add the following class:

class AlbumForm(Form):
    media_types = [('Digital', 'Digital'),
                   ('CD', 'CD'),
                   ('Cassette Tape', 'Cassette Tape')
                   ]
    artist = StringField('Artist')
    title = StringField('Title')
    release_date = StringField('Release Date')
    publisher = StringField('Publisher')
    media_type = SelectField('Media', choices=media_types)

This defines all the fields we need to create a new Album. Now we need to open “main.py” and add a function to handle what happens when we want to create the new album.

# main.py

from app import app
from db_setup import init_db, db_session
from forms import MusicSearchForm, AlbumForm
from flask import flash, render_template, request, redirect
from models import Album

init_db()


@app.route('/', methods=['GET', 'POST'])
def index():
    search = MusicSearchForm(request.form)
    if request.method == 'POST':
        return search_results(search)

    return render_template('index.html', form=search)


@app.route('/results')
def search_results(search):
    results = []
    search_string = search.data['search']

    if search.data['search'] == '':
        qry = db_session.query(Album)
        results = qry.all()

    if not results:
        flash('No results found!')
        return redirect('/')
    else:
        # display results
        return render_template('results.html', table=table)
    
    
@app.route('/new_album', methods=['GET', 'POST'])
def new_album():
    """
    Add a new album
    """
    form = AlbumForm(request.form)
    return render_template('new_album.html', form=form)
    

if __name__ == '__main__':
    app.run()

Here we add an import to import our new form at the top and then we create a new function called new_album(). Then we create an instance of our new form and pass it to our render_template() function which will render a file called “new_album.html”. Of course, this HTML file doesn’t exist yet, so that will be the next thing we need to create. When you save this new HTML file, make sure you save it to the “templates” folder inside of your “musicdb” folder.

Once you have “new_album.html” created, add the following HTML to it:


New Album - Flask Music Database
New Album

{% from "_formhelpers.html" import render_field %}

    
        {{ render_field(form.artist) }}
        {{ render_field(form.title) }}
        {{ render_field(form.release_date) }}
        {{ render_field(form.publisher) }}
        {{ render_field(form.media_type) }}

This code will render each field in the form and it also creates a Submit button so we can save our changes. The last thing we need to do is update our “index.html” code so that it has a link that will load our new album page. Basically all we need to do is add the following:

 New Album

So the full change looks like this:



    Flask Music Database


Flask Music Database


 New Album 

{% with messages = get_flashed_messages() %}
  {% if messages %}
    

    {% for message in messages %}
      {{ message }}
    {% endfor %}
    
  {% endif %}
{% endwith %}

{% from "_formhelpers.html" import render_field %}

  
    {{ render_field(form.select) }}
    
    {{ render_field(form.search) }}

Now if you load the main page of your web application, it should look like this:

If you click on the “New Album” link, then you should see something like this in your browser:

Now we have an ugly but functional new album form, but we didn’t actually make the submit button work. That is our next chore.

Saving Data

We need our new album form to save the data when the submit button is pushed. What happens when you press the submit button though? If you go back to the “new_album.html” file, you will note that we set the form method to POST. So we need to update the code in main.py so it does something on POST.

To do the save, we need to update the new_album() function in main.py so it ends up like this:

@app.route('/new_album', methods=['GET', 'POST'])
def new_album():
    """
    Add a new album
    """
    form = AlbumForm(request.form)

    if request.method == 'POST' and form.validate():
        # save the album
        album = Album()
        save_changes(album, form, new=True)
        flash('Album created successfully!')
        return redirect('/')

    return render_template('new_album.html', form=form)

Now when we post, we create an Album instance and pass it to a save_changes() function along with the form object. We also pass along a flag that indicates if the item is new or not. We will go over why I added that last one later on in the article. For now though, we need to create the save_changes() function. Save the following code in the main.py script.

def save_changes(album, form, new=False):
    """
    Save the changes to the database
    """
    # Get data from form and assign it to the correct attributes
    # of the SQLAlchemy table object
    artist = Artist()
    artist.name = form.artist.data

    album.artist = artist
    album.title = form.title.data
    album.release_date = form.release_date.data
    album.publisher = form.publisher.data
    album.media_type = form.media_type.data

    if new:
        # Add the new album to the database
        db_session.add(album)

    # commit the data to the database
    db_session.commit()

Here we extract the data from the form and assign it to the album object’s attributes accordingly. You will also notice that we need to create an Artist instance to actually add the artist to the album correctly. If you don’t do this, you will get a SQLAlchemy related error. The new parameter is used here to add a new record to the database.

Here is a session I did to test it out:

Once the item saves, it should take you back to the home page of the website. One thing to note is that I don’t do any checking in the database to prevent the user from saving an entry multiple times. This is something you can add yourself if you feel like taking on the challenge. Anyway while I was testing this out, I submitted the same entry a few times, so when I do a search I should end up with multiple entries for the same item. If you try doing a search now though, you will end up with an error because we haven’t created the results page yet.

Let’s do that next!

Displaying Search Results

I prefer having tabulated results which requires using a table. Rather than messing around with HTML table elements, you can download yet another Flask extension called Flask Table. To install it, just use pip like this:

pip install flask_table

Now that we have Flask Table installed, we need to create a table definition. Let’s create a file that we will call “tables.py” and save it in our musicdb folder. Open that up in your editor and add the following code:

from flask_table import Table, Col

class Results(Table):
    id = Col('Id', show=False)
    artist = Col('Artist')
    title = Col('Title')
    release_date = Col('Release Date')
    publisher = Col('Publisher')
    media_type = Col('Media')

When you define the table class, you will want to make the class attributes the same name as those in the object that will be passed to it. In this case, I used the attributes from the Album class here. Now we just need to create a results.html file and save it to the templates folder. Here is what should go in that file:


Search Results - Flask Music Database
{{ table }}

As you can see, all we needed to do was add a title element, which is actually optional, and add a table object in Jinja.

Lastly we need to update our search function in our main.py to use a table:

@app.route('/results')
def search_results(search):
    results = []
    search_string = search.data['search']

    if search.data['search'] == '':
        qry = db_session.query(Album)
        results = qry.all()

    if not results:
        flash('No results found!')
        return redirect('/')
    else:
        # display results
        table = Results(results)
        table.border = True
        return render_template('results.html', table=table)

Now when you run the search with an empty string, you should see something like this:

Yes, it’s pretty plain, but it works and you can now see everything in your database.

Editing Data in the Database

The last item that we need to cover is how to edit the data in the database. One of the easiest ways to do this would be to search for an item and add a way for the user to edit the items that were found. Open up the tables.py file and add on a LinkCol:

from flask_table import Table, Col, LinkCol

class Results(Table):
    id = Col('Id', show=False)
    artist = Col('Artist')
    title = Col('Title')
    release_date = Col('Release Date')
    publisher = Col('Publisher')
    media_type = Col('Media')
    edit = LinkCol('Edit', 'edit', url_kwargs=dict(id='id'))

The LinkCol takes the column name as a string along with what the endpoint should be. The endpoint is the function that will be called when the link is clicked. We also pass along the entry’s id so we can look it up in the database (i.e. the url_kwargs argument). Now we need to update our main.py file with a function called edit():

@app.route('/item/', methods=['GET', 'POST'])
def edit(id):
    qry = db_session.query(Album).filter(
                Album.id==id)
    album = qry.first()

    if album:
        form = AlbumForm(formdata=request.form, obj=album)
        if request.method == 'POST' and form.validate():
            # save edits
            save_changes(album, form)
            flash('Album updated successfully!')
            return redirect('/')
        return render_template('edit_album.html', form=form)
    else:
        return 'Error loading #{id}'.format(id=id)

The first item to take note of here is that we have a custom route set up for the URL that uses the id we pass to it to create a unique URL. Next we do a database search for the id in question. If we find the id, then we can create our form using the same form we created earlier. However this time we pass it the album object so the form gets pre-filled with data so we have something to edit. If the user presses the Submit button on this page, then it will save the entry to the database and flash a message to the user to that effect. If we pass in a bad id, then a message will be shown to the user.

We also need to create an edit_album.html file in our templates folder. Here is the HTML you need to put in that file:


Edit Album - Flask Music Database
Edit Album

{% from "_formhelpers.html" import render_field %}

    
        {{ render_field(form.artist) }}
        {{ render_field(form.title) }}
        {{ render_field(form.release_date) }}
        {{ render_field(form.publisher) }}
        {{ render_field(form.media_type) }}

Now when we run the empty search from earlier you should see this:

Let’s click on the first row’s edit link:

Here I edit most of the fields on the page. Then I click Submit and get this:

Supposedly the entry was updated with my changes. To verify, try running another empty search:

That looks right, so now we have the editing functionality complete!

Wrapping Up

At this point you should be able to add entries to the database, display all the entries and edit said entries. The main item missing is how to filter the search results so that it actually looks for the search term you want instead of always returning everything in the database. We should probably also add the ability to delete an item from the database. Those are the topics we will look at in the next article. For now, have fun and happy coding!