Raise TypeError("Query must be a string unless using sqlalchemy.")

I’m a bit stuck with streamlit’s database connection functions…


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.


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:


  1. 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))
  1. 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
        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)


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).
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>
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)



  • 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.


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 :expressionless:

I think there might be something going on with pandas/sqlalchemy… I am running into the same error but in a jupyter notebook environment with code I have been running with no issue as of 2 weeks ago

Thanks - see a link to a reply on a similar question of mine.

Apparently, the following versions now work:
streamlit==1.31.1, SQLAlchemy==2.0.27