How To Add Filters For Querying Databases In Streamlit

In this tutorial, we will show you how you can use filters for databases in Streamlit. We will use PostgresSQL but you can use the same logic everywhere.

The hypothetical database that we are going to use has a table called movies and we want to filter it based on the genre and the language.

SELECT title FROM movies
where
genre = 'Action'
and
language = 'English'

Let’s build a Streamlit app that will have 2 forms so you can filter the database and return the movie titles.

The first step is to get the values of the filters from the database. In other words the unique genres and languages. In this example, we are opening a connection using psycopg2.

import streamlit as st
import psycopg2 as pg2
conn = pg2.connect("your credentials")

genres = sorted(pd.read_sql(con=conn , sql="select distinct(genre) from movies")['genre'])
languages = sorted(pd.read_sql(con=conn , sql="select distinct(genre) from language")['language'])

Of course, if the lists will never change, you can add the values manually as shown below

genres =['Action', 'Crime', 'Drama', 'Comedy', 'Horror', 'Sci-Fi/Fantasy']
languages=['English','French','Spanish','Italian'] 

Now, let’s create the forms.

import streamlit as st
import psycopg2 as pg2
conn = pg2.connect("your credentials")

genres =['Action', 'Crime', 'Drama', 'Comedy', 'Horror', 'Sci-Fi/Fantasy']
languages=['English','French','Spanish','Italian']


st.title('Movie Filtering')

with st.form(key='my_form'):
    genre=st.multiselect('Select Genre', genres)
    language=st.selectbox('Select Language', languages)
    
    submit_button = st.form_submit_button(label='Submit')

The next step is to run the query and return the results. Here is the tricky part.

if submit_button:
    query=f"""SELECT title FROM movies
                where
                {len(genre)}=0 or genre in ({', '.join(["'"+i+"'" for i in genre+['']])}))
                and
                {len(language)}=0 or language in ({', '.join(["'"+i+"'" for i in language+['']])}))"""
    
    df=pd.read_sql(query,con=conn)
    
    st.write(df)

Let me explain what we are doing there. As you can see we are using Python’s F-String to pass variables in the query.

The multi-select form returns a list with the selected values and if there are no values selected, the multi-select will return an empty list. The question is what we are going to do when there is nothing selected and you want to skip the where statement in the query.

We solved this problem using simple mathematical logic. Let’s breakdown the line for genre.

{len(genre)}=0 or genre in ({', '.join(["'"+i+"'" for i in genre+['']])}))

As an example, let’s say that we have selected Action and Crime. The string will be as follows:

 2=0 or genre in ('Action', 'Crime', '')

This will return the rows that have genre Action or Crime. 2=0 is False everywhere and thats why it will not affect the results.

Now, If we have nothing selected the string will be:

 0=0 or genre in ('')

This will return all rows because 0=0 is True everywhere. As you can see, we added an empty string in genre list because if nothing is selected you will end up with an error in your code as shown below:

#This will return an error  because there is nothing in the list
0=0 or genre in ()

#vs

 0=0 or genre in ('')