Trying to utilize string st.session_state values inside a run_query() statement

[Esau_Hutcherson]

Summary

Currently I am building out a web app wherein a user inputs values for the radio widgets inside a st.form() and these values are saved as st.session_state’s once the submit button of the form is pressed. I want the string values received from the st.form() to be included in my run_query() statement such as. I have the session_state values in the string format and tried to convert them into chars by doing query_pd = st.session_state[“program_date”] however i still am unable to use it inside the run_query() statement any ideas?

Steps to reproduce

Code snippet:

if st.session_state["program_time"] == "Summer":
    query_pt = 'Summer'
else:
    query_pt = 'School Year'

if st.session_state["program_date"] == "Weekend":
    query_pd = 'Weekend'
else:
    query_pd = 'Weekday'

if st.session_state["program_type"] == "STEM":
    query_ptype = 'STEM'
elif st.session_state["program_type"] == "Fine Arts":
    query_ptype = 'Fine Arts'
elif st.session_state["program_type"] == "Foreign Language":
    query_ptype = 'Foreign Language'

rows = run_query("SELECT * FROM Program WHERE program_time =" + query_pt + " and program_date = " + query_pd + " and program_type = " + query_ptype + ";")
for row in rows:
    st.write(f"{row[1]} has a {row[2], {row[3]}} {row[4]}:")

Expected behavior:

I expected this code snippet to select all rows in my Program table where the columns program_time, program_date, and program_type equal query_pt, query_pd, and query_ptype respectively. Then the for loop should print out some selected columns from each row
Actual behavior:
It just Errors as the query statement is recognizable by postgresql due to incorrect formatting

Error:
InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block

Trackback:

File "/Users/v.esau.hutcherson/.local/share/virtualenvs/StreamLit-ohTsyygW/lib/python3.10/site-packages/streamlit/runtime/scriptrunner/script_runner.py", line 565, in _run_script
    exec(code, module.__dict__)File "/Users/v.esau.hutcherson/StreamLit/pages/listings.py", line 71, in <module>
    rows = run_query("SELECT * FROM Program WHERE program_time =" + query_pt + " and program_date = " + query_pd + " and program_type = " + query_ptype + ";")File "/Users/v.esau.hutcherson/.local/share/virtualenvs/StreamLit-ohTsyygW/lib/python3.10/site-packages/streamlit/runtime/caching/cache_utils.py", line 194, in wrapper
    return cached_func(*args, **kwargs)File "/Users/v.esau.hutcherson/.local/share/virtualenvs/StreamLit-ohTsyygW/lib/python3.10/site-packages/streamlit/runtime/caching/cache_utils.py", line 223, in __call__
    return self._get_or_create_cached_value(args, kwargs)File "/Users/v.esau.hutcherson/.local/share/virtualenvs/StreamLit-ohTsyygW/lib/python3.10/site-packages/streamlit/runtime/caching/cache_utils.py", line 248, in _get_or_create_cached_value
    return self._handle_cache_miss(cache, value_key, func_args, func_kwargs)File "/Users/v.esau.hutcherson/.local/share/virtualenvs/StreamLit-ohTsyygW/lib/python3.10/site-packages/streamlit/runtime/caching/cache_utils.py", line 302, in _handle_cache_miss
    computed_value = self._info.func(*func_args, **func_kwargs)File "/Users/v.esau.hutcherson/StreamLit/pages/listings.py", line 39, in run_query
    cur.execute(query)

Debug info

• Streamlit version: 1.22.0
• Python version: 3.10.11
• Using PipEnv
• OS version: macOs 13.3.1

Requirements file

Using Conda? PipEnv? PyEnv? Pex? Share the contents of your requirements file here.
Not sure what a requirements file is? Check out this doc and add a requirements file to your app.

Links

• Github repo: GitHub - Esauh/program-finder: Intro to Affective Computing Final project utilizing StreamLIt and mySQL

[Caroline]

Hey @Esau_Hutcherson,

Thanks for sharing this question!

It sounds like you’re running into errors because the query you’re trying to execute has a formatting error or isn’t valid SQL.

What I’d recommend doing in this case is adding a st.write() statement right before you try to execute the query so that you can examine the query and figure out which part is improperly formatted or incorrect.

For example, you could add:

if st.session_state["program_type"] == "STEM":
    query_ptype = 'STEM'
elif st.session_state["program_type"] == "Fine Arts":
    query_ptype = 'Fine Arts'
elif st.session_state["program_type"] == "Foreign Language":
    query_ptype = 'Foreign Language'

st.write("My query is: ")
query = "SELECT * FROM Program WHERE program_time =" + query_pt + " and program_date = " + query_pd + " and program_type = " + query_ptype + ";"
st.write(query)

rows = run_query(query)

This should make it a lot easier for you to get to the root cause.

[Esau_Hutcherson]

Thanks for the advice I you were right the format was wrong also for some reason having cache activated for the queries just breaks it entirely. The correct syntax is like this. Thank you again for the help!

query = f"select * from {program} where program_type ='{query_ptype}'  and program_date ='{query_pd}' and program_time ='{query_pt}';"
run_query(query)