SQL Results in a Dropdown

Using Streamlit
1

I wanted to make a dropdown that contain values from the response of a SQL query, it contains more than 35000 rows and its should searchable and selectable in a dropdown and based on that i want another dropdown to appear with a different query, i am very new to streamlit just wanted to know if this is possible, since this is a huge amount of data from SQL response

@st.cache
def get_advertisers():
    df_ad = pd.read_sql_query("""SELECT name FROM trc.publishers LIMIT ALL""",connection, index_col = None)
    return df_ad
advertiser_data = get_advertisers()
st.sidebar.title("Advertisers")
advertisers = st.sidebar.selectbox('Advertisers',[advertiser_data])

Above code shows data as below which is not iterable and formating is wrong

Screen Shot 2020-07-08 at 06.46.30
Screen Shot 2020-07-08 at 06.46.301913Ă—1017 71.7 KB

2

try this

1 Like
3

Hi @khushal111, welcome to the Streamlit forums!

It looks you might be running into this issue:

The quick solution here is instead of passing in advertiser_data to the selectbox, pass in advertiser_data["name"], so that the pandas Series is passed to the selectbox.

Separately, you might run into issues with having 35,000 rows in a drop-down. It’s not really a strong user experience either…is there a way you can do the top 1000 by occurrence, or have multiple drop-downs for people to hone in on which advertiser they are interested in?

Best,
Randy

4

Thanks @randyzwitch, advertiser[“name”] worked to pull advertiser_data without index and also in the format of rows, and as you suggested i tried dividing the rows of advertiser into smaller chunks using below code, but it not working, the get_advertisers() keeps on running without sending an data to streamlit, and in my case its not possible to have multiple dropdowns for people.

cur = connection.cursor()
n_rows = cur.execute("SELECT count(*) FROM trc.publishers").fetchone()[0]
print (n_rows)

@st.cache
def get_advertisers(query, connection, nrows, chunksize=100):
    start = 1
    dfs = []
    while start < nrows:
        df = pd.read_sql("%s LIMIT %s OFFSET %s" % (query, chunksize, start), connection)
        dfs.append(df)
    return pd.concat(dfs, ignore_index = True)

advertiser_data = get_advertisers("SELECT name from trc.publishers", connection, n_rows, 100)
st.sidebar.title("Advertisers")
advertisers = st.sidebar.selectbox('Select Advertisers',advertiser_data["name"])