Iâm a bit stuck with streamlitâs database connection functionsâŚ
Summary
I have a database on supabase which is connected to my streamlit app via a postgresql connection in my secrets file. The entire app runs fine locally.
However, when I deploy the app to the cloud (public app, community cloud, private github repo) I get the error message shown below.
Connection
My local app runs fine; queries and connections to supabase all work.
conn = st.connection("postgresql", type="sql")
However, when I deploy to the cloud I get an error. See below for an example of:
Functions
- Code without error
The code snippet below works when I use text() from sqalchemy. Originally I had written sql queries using the postgresql syntax, but somehow that led to this.
import streamlit as st
from sqlalchemy import text
# Insert data
def register_user(conn, username, password):
sql = text(''' INSERT INTO users (username, password)
VALUES (:username, :password)''')
with conn.session as s:
s.execute(sql, params=dict(username=username, password=password))
s.commit()
- Code with error
Code snippet examples of sql queries which lead to the error below.
import streamlit as st
import pandas as pd
# Function to authenticate user
def authenticate_user(conn, username, password):
sql = '''SELECT user_id FROM users WHERE username = :username AND password = :password LIMIT 1'''
user_data = conn.query(sql, params={"username":username, "password":password}, ttl=0.01)
if not user_data.empty:
user_id = user_data.iloc[0, 0]
return user_id # Authentication successful
else:
return False # Authentication failed
Similarly, my database queries (from Supabase, using what I think is postgresql queries), also doesnât work:
conn = st.connection("postgresql", type="sql")
users_df = conn.query('SELECT * from users', ttl=0.001)
st.dataframe(users_df, use_container_width=True, hide_index=True)
Error
TypeError: This app has encountered an error. The original error message is redacted to prevent data leaks. Full error details have been recorded in the logs (if you're on Streamlit Cloud, click on 'Manage app' in the lower right of your app).
Traceback:
File "/home/adminuser/venv/lib/python3.11/site-packages/streamlit/runtime/scriptrunner/script_runner.py", line 535, in _run_script
exec(code, module.__dict__)
File "/mount/src/f1-pole-predictor/F1.py", line 258, in <module>
main()
File "/mount/src/f1-pole-predictor/F1.py", line 105, in main
user_id = db.authenticate_user(conn, username, password)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/mount/src/f1-pole-predictor/functions/database.py", line 65, in authenticate_user
user_data = conn.query(sql, params={"username":username, "password":password}, ttl=0.01)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/adminuser/venv/lib/python3.11/site-packages/streamlit/connections/sql_connection.py", line 238, in query
return _query(
^^^^^^^
File "/home/adminuser/venv/lib/python3.11/site-packages/streamlit/runtime/caching/cache_utils.py", line 212, in wrapper
return cached_func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/adminuser/venv/lib/python3.11/site-packages/streamlit/runtime/caching/cache_utils.py", line 241, in __call__
return self._get_or_create_cached_value(args, kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/adminuser/venv/lib/python3.11/site-packages/streamlit/runtime/caching/cache_utils.py", line 268, in _get_or_create_cached_value
return self._handle_cache_miss(cache, value_key, func_args, func_kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/adminuser/venv/lib/python3.11/site-packages/streamlit/runtime/caching/cache_utils.py", line 324, in _handle_cache_miss
computed_value = self._info.func(*func_args, **func_kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/adminuser/venv/lib/python3.11/site-packages/tenacity/__init__.py", line 289, in wrapped_f
return self(f, *args, **kw)
^^^^^^^^^^^^^^^^^^^^
File "/home/adminuser/venv/lib/python3.11/site-packages/tenacity/__init__.py", line 379, in __call__
do = self.iter(retry_state=retry_state)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/adminuser/venv/lib/python3.11/site-packages/tenacity/__init__.py", line 314, in iter
return fut.result()
^^^^^^^^^^^^
File "/usr/local/lib/python3.11/concurrent/futures/_base.py", line 449, in result
return self.__get_result()
^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.11/concurrent/futures/_base.py", line 401, in __get_result
raise self._exception
File "/home/adminuser/venv/lib/python3.11/site-packages/tenacity/__init__.py", line 382, in __call__
result = fn(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^
File "/home/adminuser/venv/lib/python3.11/site-packages/streamlit/connections/sql_connection.py", line 216, in _query
return pd.read_sql(
^^^^^^^^^^^^
File "/home/adminuser/venv/lib/python3.11/site-packages/pandas/io/sql.py", line 706, in read_sql
return pandas_sql.read_query(
^^^^^^^^^^^^^^^^^^^^^^
File "/home/adminuser/venv/lib/python3.11/site-packages/pandas/io/sql.py", line 2736, in read_query
cursor = self.execute(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/adminuser/venv/lib/python3.11/site-packages/pandas/io/sql.py", line 2668, in execute
raise TypeError("Query must be a string unless using sqlalchemy.")
Requirements file
( I ended up not using supabase-py syntax because I was more familiar with postgresql syntax)
pandas
plotly
numpy
folium
psycopg2-binary==2.9.9
st-supabase-connection
supabase
alchemy
Question
- I canât figure out why the error says my query must be a string when I am providing it as a string
- Is there a cleaner way to do both QUERY and INSERT type functions without jumping back and forth between Streamlitâs connection.query function, alchemy, and PostgreSQL?
- The main aim of this app is to log a userâs input (text from a selection of F1 drivers) to a database and have that database be up to date and visible to all logged-in users.
Notes
The function snippets above are random examples, I know there are authentication packages that make life easier, but I just want to understand the errors as Iâm using many more queries in the rest of the code. In particular, I have about 5 tables which are interlinked, and so I need to be able to query more complex things which I found easiest using the postgrsql syntax.
Side note
I had this app running smoothly on the cloud without errors. Then I rebooted the app and now none of the queries seem to work (excep the one I showed as working). I must have changed something before rebooting but I have no idea what 