Flask 101: Filtering Searches and Deleting Data

last time we got our flask based music database application partially functional. it could now add data to the database, edit said data, and also display everything in the database. but we didn’t cover how to filter the data by using the user’s filter choice (artist, album name, or publisher name) and search string. we also didn’t cover how to delete items from the database. that is the two-fold goal of this article.

filtering search results

filtering search results using sqlalchemy (via flask-sqlalchemy) is actually quite easy. all you need to do is create some very simple query objects. open up the main.py file that we were editing last time and replace the search_results() function with the following version of the code:

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

    if search_string:
        if search.data['select'] == 'artist':
            qry = db_session.query(album, artist).filter(
                artist.id==album.artist_id).filter(
                    artist.name.contains(search_string))
            results = [item[0] for item in qry.all()]
        elif search.data['select'] == 'album':
            qry = db_session.query(album).filter(
                album.title.contains(search_string))
            results = qry.all()
        elif search.data['select'] == 'publisher':
            qry = db_session.query(album).filter(
                album.publisher.contains(search_string))
            results = qry.all()
        else:
            qry = db_session.query(album)
            results = qry.all()
    else:
        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)

here we added a somewhat lengthy conditional if statement. we first check to see if the user has entered a search string in the search text box. if so, then we check to see which filter the user has chosen from the combobox: artist, album, or publisher. depending on the user’s choice, we create a custom sqlalchemy query. if the user doesn’t enter a search term or if our web application gets confused and doesn’t recognize the user’s filter choice, then we do a query against the full database. this is something that probably shouldn’t be done in production as, if the database gets really large, then doing a query against your database will end up making your web application unresponsive. you can simply add some validation to your form’s input to prevent this from happening (i.e. don’t query the database with an empty search string). however, we won’t be covering that here.

anyway, go ahead and try this code out and see how it works. i tried several different search terms and it seemed to work fine for my use cases. you will note that i simply used the contains method which is great for looking up a string in a table’s column. you can always index your database and do other various optimizations to it including making these queries a lot more focused if you want to. feel free to play around with this code and see how you can improve it.

now we will move on and learn how to delete items from the database!

deleting data

there are times when you enter something into the database that you just want to delete. technically you could use our editing functionality to just edit the entry to whatever you want, but sometimes you just need to purge data permanently. so the first thing we need to do is add a delete column to our results table. you will want to open up tables.py and add a new linkcol instance to the results class:

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'))
    delete = linkcol('delete', 'delete', url_kwargs=dict(id='id'))

just as we did when we created the link for editing our data, we add a new link for deleting the data. you will note that the second argument, which is the endpoint, points to a delete function. so the next step is to open up our main.py file and add said delete() function:

@app.route('/delete/<int:id>', methods=['get', 'post'])
def delete(id):
    """
    delete the item in the database that matches the specified
    id in the url
    """
    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():
            # delete the item from the database
            db_session.delete(album)
            db_session.commit()

            flash('album deleted successfully!')
            return redirect('/')
        return render_template('delete_album.html', form=form)
    else:
        return 'error deleting #{id}'.format(id=id)

this code is actually pretty similar to our edit() function from the last article. you will note that we updated the route though. so instead of specifying ‘/item/‘ , we made it ‘/delete/‘ . this makes the urls between the two functions different so they actually execute the correct function when the link is clicked on. the other difference is that we don’t need to create a special saving function here. we just reference the db_session object directly and tell it to remove the album if it’s found in the database and then commit our changes.

if you run the code, you should see something like the following when doing an empty string search:

the last thing we need to do is create the delete_album.html that we referenced above. let’s create that file and save it to our templates folder. once that file is created, just add the following:

<doctype html>
<title>delete album - flask music database</title>
<h2>delete album</h2>

{% from "_formhelpers.html" import render_field %}
<form method=post>
    <dl>
        {{ render_field(form.artist) }}
        {{ render_field(form.title) }}
        {{ render_field(form.release_date) }}
        {{ render_field(form.publisher) }}
        {{ render_field(form.media_type) }}
    </dl>
    <p><input type=submit value=delete>
</form>

this code will render our form to show the user what they are deleting. let’s try clicking on the delete link for one of the duplicates in our table. you should see a screen like this appear:

when you press the delete button, it will redirect you to the home page where you will see a message that the item was deleted successfully:

to verify that the deletion worked, just do another empty string search. your results should show one less item in the table:

wrapping up

now you should know how to do some basic filtering of search results from the database. you also have learned how to successfully delete items from the database in your flask application. there are several places in the code that could use a refactoring and general cleanup. you could also add some css styling to your application to make it look prettier. those are exercises that i will leave for the reader. have fun playing around with the code and giving flask a try. it’s a neat little web framework and well worth a look!

download code

download a tarball of the code from this article: flask_music_db_v.tar

other articles in the series

• part i – flask 101: getting started
• part ii – flask 101: adding a database
• part iii – flask 101: how to add a search form
• part iv – flask 101: adding, editing and displaying data