How To Add Filters For Querying Databases In Streamlit

Photo by Tobias Fischer on Unsplash

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']