Streamlit app code - db: Firebolt

app.py

import os
from dotenv import load_dotenv

from firebolt.client.auth import UsernamePassword
from firebolt.db import connect

import streamlit as st
import altair as alt
import pandas as pd
import matplotlib.pyplot as plt


load_dotenv()

def get_firebolt_connection():
  engine_name = os.getenv("FIREBOLT_ENGINE")
  database_name = os.getenv("FIREBOLT_DATABASE")
  username = os.getenv("FIREBOLT_USER")
  password = os.getenv("FIREBOLT_PASSWORD")

  # create a connection based on provided credentials
  connection = connect(
    auth=UsernamePassword(username, password),
    engine_name=engine_name,
    database=database_name,
  )
  return connection

connection = get_firebolt_connection()
cursor = connection.cursor()

## Title
st.set_page_config(layout="wide")
st.title('NY Philharmonic Performance History')

## Filter slider
cursor.execute("""
    SELECT 
        min(concert_date), 
        max(concert_date) 
    FROM nyc_phil 
""")

(ts_min, ts_max) = cursor.fetchone()

(slider_min, slider_max) = st.slider(
     "Concert Date",
     min_value = ts_min,
     max_value = ts_max,
     value = (ts_min, ts_max),
     format="DD/MM/YYYY - hh:mm")

cursor.execute("""
    SELECT 
        COUNT(DISTINCT program_id)  
    FROM nyc_phil 
    WHERE concert_date > ?
      AND concert_date < ?
""", [slider_min, slider_max])

t = cursor.fetchone()

st.write('Total number of distinct programs in the interval: ', int(t[0])) 

## Chart - Number of programs per season
st.write('Number of programs per season')

cursor.execute("""
    SELECT 
        season,
        COUNT(DISTINCT program_id)  
    FROM nyc_phil 
    WHERE concert_date > ?
      AND concert_date < ?
    GROUP BY ALL
    ORDER BY season
""", [slider_min, slider_max])

program_by_season = cursor.fetchall()

program_by_season_df = pd.DataFrame(program_by_season, columns=['season','no_of_programs'])

st.bar_chart(
  data=program_by_season_df,
  x = 'season',
  y = 'no_of_programs'
)

## Chart - Popular composers per season
st.write('Popular composers per season')

cursor.execute("""
    SELECT 
        season,
        composer_name,
        COUNT(DISTINCT work_id)  
    FROM nyc_phil 
    WHERE concert_date > ?
      AND concert_date < ?
    GROUP BY ALL
    HAVING COUNT(DISTINCT work_id)>25
    ORDER BY season
""", [slider_min, slider_max])

program_by_season_and_composer_name = cursor.fetchall()

program_by_season_and_composer_name_df = pd.DataFrame(program_by_season_and_composer_name, columns=['season','composer_name','count'])

chart = alt.Chart(program_by_season_and_composer_name_df).mark_bar().encode(
    x = 'season',
    y = 'count',
    color = 'composer_name'
).interactive()

st.altair_chart(chart, theme="streamlit", use_container_width=True)

## Chart - Popular venues
st.write('Popular venues')

cursor.execute("""
    SELECT 
        concert_venue,
        COUNT(DISTINCT program_id)  
    FROM nyc_phil 
    WHERE concert_date > ?
      AND concert_date < ?
    GROUP BY ALL
    HAVING COUNT(DISTINCT program_id) > 100
""", [slider_min, slider_max])

programs_by_venue = cursor.fetchall()

programs_by_venue_df = pd.DataFrame(programs_by_venue, columns=['venue','no_of_programs'])

fig1, ax1 = plt.subplots()
ax1.pie(programs_by_venue_df['no_of_programs'], labels=programs_by_venue_df['venue'])

st.pyplot(fig1, width = 0.1)