pyodbc.OperationalError: This app has encountered an error

Deployment
1 
2023-03-08 09:01:28.172 Thread 'MainThread': missing ScriptRunContext

2023-03-08 09:01:28.173 Thread 'MainThread': missing ScriptRunContext

2023-03-08 09:11:54.661 Uncaught app exception

Traceback (most recent call last):

  File "/home/appuser/venv/lib/python3.9/site-packages/streamlit/runtime/caching/cache_utils.py", line 235, in get_or_create_cached_value

    result = cache.read_result(value_key)

  File "/home/appuser/venv/lib/python3.9/site-packages/streamlit/runtime/caching/singleton_decorator.py", line 276, in read_result

    raise CacheKeyNotFoundError()

streamlit.runtime.caching.cache_errors.CacheKeyNotFoundError



During handling of the above exception, another exception occurred:



Traceback (most recent call last):

  File "/home/appuser/venv/lib/python3.9/site-packages/streamlit/runtime/scriptrunner/script_runner.py", line 563, in _run_script

    exec(code, module.__dict__)

  File "/app/ocr-alma/pages/3_QC OCR.py", line 32, in <module>

    conn = init_connection()

  File "/home/appuser/venv/lib/python3.9/site-packages/streamlit/runtime/caching/cache_utils.py", line 281, in wrapper

    return get_or_create_cached_value()

  File "/home/appuser/venv/lib/python3.9/site-packages/streamlit/runtime/caching/cache_utils.py", line 252, in get_or_create_cached_value

    return_value = func(*args, **kwargs)

  File "/app/ocr-alma/pages/3_QC OCR.py", line 21, in init_connection

    return pyodbc.connect(

pyodbc.OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

We are using MS SQL Server for our app to save and extract data. Our IT department had allowed the outbound IP address in the firewall for this to work. However, we are facing this issue.

Version:
Streamlit == 1.14.1
Python == 3.10.4
pyodbc == 4.0.32

This is the snippet of init_connection code:

@st.experimental_singleton
def init_connection():
    return pyodbc.connect(
        "DRIVER={ODBC Driver 17 for SQL Server};SERVER="
        + st.secrets["server"]
        + ";DATABASE="
        + st.secrets["database"]
        + ";UID="
        + st.secrets["username"]
        + ";PWD="
        + st.secrets["password"]
    )

conn = init_connection()
c = conn.cursor()

# Perform query.
def insert_query(id, name):
    c.execute('INSERT INTO Form (id, name) VALUES(?,?,?,?)',(id, name))
    conn.commit()
2

What is the actual error message from pyodbc.OperationalError itself? There must be more.
PS: Please do not post code or logs as an image…

3

I just update the question. Thanks for the advice.

4

On this basis, unfortunately, I can only guess and ask a few questions:

  • Where does the problem occur, only when the app is deployed on Streamlit Cloud? Or also when it is running locally?
  • Has this external database access been checked with other tools?
  • Has the actual full string within pyodbc.connect() been checked to see if it is valid? Just build it beforehand and print it on the console.
  • Is there a domain or ip address in the st.secrets["server"] secret?
  • Does the st.secrets["server"] secret also contain the port? For example: 11.22.33.44,5000
1 Like
5
  1. The problem occur when the app is deployed on Streamlit Cloud. However, it can run locally.
  2. Not yet. Can you suggest what other tools that I can use to check the database access?
  3. Yes. The secrets can be print on the console.
  4. Yes. The server is using the domain of the server as the name.
  5. Yes. st.secrets["server"] secret also contain the port.
6

I googled the error message and found plenty of StackOverflow threads:

The overarching theme seems to be that Microsoft’s SQL Server ODBC drivers for Linux are unable to resolve instance names. The problem might be DNS related, as you can read here. Try to use an IP address and corresponding port number instead of the hostname in the connection string.

2 Likes
7

Thank you. I’m still working on this issue. However I have one question, why I cannot found the Streamlit Outbound page in the Documentation? Is it already not available?

8

Did you ever resolve your issue? I’m encountering the exact same problem. My app runs locally fine but I get a timeout error when using the app on Community Cloud. It previously worked for months and has now stopped without anything being changed.