OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]

Using Streamlit
1

I followed the guide from Connect Streamlit to Microsoft SQL Server - Streamlit Docs

.streamlit/secrets.toml

# .streamlit/secrets.toml

server = "DESKTOP-64G377"
database = "SampleDB"
username = "sa"
password = "password*"

streamlit run 1_�_Homepage.py

import streamlit as st
import pyodbc

# Initialize connection.
# Uses st.experimental_singleton to only run once.
@st.experimental_singleton
def init_connection():
    return pyodbc.connect(
        "DRIVER={ODBC Driver 17 for SQL Server};SERVER='DESKTOP-64G377'"
        + st.secrets["server"]
        + ";DATABASE='SampleDB'"
        + st.secrets["database"]
        + ";UID='sa'"
        + st.secrets["username"]
        + ";PWD='password*'"
        + st.secrets["password"]
    )

conn = init_connection()

# Perform query.
# Uses st.experimental_memo to only rerun when the query changes or after 10 min.
@st.experimental_memo(ttl=600)
def run_query(query):
    with conn.cursor() as cur:
        cur.execute(query)
        return cur.fetchall()

rows = run_query("SELECT * from mytable;")

# Print results.
for row in rows:
    st.write(f"{row[0]} has a :{row[1]}:")

when I run it I get this error :

Traceback (most recent call last):
  File "C:\Users\User\AppData\Local\Programs\Python\Python39\lib\site-packages\streamlit\runtime\caching\cache_utils.py", line 225, in get_or_create_cached_value
    result = cache.read_result(value_key)
  File "C:\Users\User\AppData\Local\Programs\Python\Python39\lib\site-packages\streamlit\runtime\caching\singleton_decorator.py", line 272, 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 "C:\Users\User\AppData\Local\Programs\Python\Python39\lib\site-packages\streamlit\runtime\scriptrunner\script_runner.py", line 556, in _run_script
    exec(code, module.__dict__)
  File "E:\multiapp_page\1_�_Homepage.py", line 19, in <module>
    conn = init_connection()
  File "C:\Users\User\AppData\Local\Programs\Python\Python39\lib\site-packages\streamlit\runtime\caching\cache_utils.py", line 251, in wrapper
    return get_or_create_cached_value()
  File "C:\Users\User\AppData\Local\Programs\Python\Python39\lib\site-packages\streamlit\runtime\caching\cache_utils.py", line 242, in get_or_create_cached_value
    return_value = func(*args, **kwargs)
  File "E:\multiapp_page\1_�_Homepage.py", line 8, in init_connection
    return pyodbc.connect(
pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53].  (53) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (53)')
2022-09-05 22:27:38.967 Uncaught app exception
Traceback (most recent call last):
  File "C:\Users\User\AppData\Local\Programs\Python\Python39\lib\site-packages\streamlit\runtime\caching\cache_utils.py", line 225, in get_or_create_cached_value
    result = cache.read_result(value_key)
  File "C:\Users\User\AppData\Local\Programs\Python\Python39\lib\site-packages\streamlit\runtime\caching\singleton_decorator.py", line 272, 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 "C:\Users\User\AppData\Local\Programs\Python\Python39\lib\site-packages\streamlit\runtime\scriptrunner\script_runner.py", line 556, in _run_script
    exec(code, module.__dict__)
  File "E:\multiapp_page\1_�_Homepage.py", line 19, in <module>
    conn = init_connection()
  File "C:\Users\User\AppData\Local\Programs\Python\Python39\lib\site-packages\streamlit\runtime\caching\cache_utils.py", line 251, in wrapper
    return get_or_create_cached_value()
  File "C:\Users\User\AppData\Local\Programs\Python\Python39\lib\site-packages\streamlit\runtime\caching\cache_utils.py", line 242, in get_or_create_cached_value
    return_value = func(*args, **kwargs)
  File "E:\multiapp_page\1_�_Homepage.py", line 8, in init_connection
    return pyodbc.connect(
pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53].  (53) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (53)')
1 Like
2

Have you installed SQL Server and can you connect to it using the client tools or Management Studio?

3

I have installed
image

4

Your connection string looks very wrong :thinking:

5

Thanks for sharing Ienoes. I experience the same issue, using exactly the same code from the Streamlit Docs.

Few things I noted.

  • The issue occurs when running on Streamlit cloud and not when running locally.

  • When removing the decorators, the issue no longer occurs on Streamlit cloud either.

Really like the possibilities offered by using decorators, so would be great if somebody can help fix this.

Many thanks

6

Quick update, this document provides a potential solution.

About using @experimental_singleton:

Although this uses a custom hashing solution for generating cache keys (like @st.cache), it does not use hash_funcs as an escape hatch for unhashable parameters. Instead, we allow you to ignore unhashable parameters (e.g. database connections) by prefixing them with an underscore.

The fix that worked for me was, passing the conn object to the run_query function, prefixed with an underscore.

@st.experimental_memo(ttl=600)
def run_query(query, _conn):
with _conn.cursor() as cur:
cur.execute(query)
return cur.fetchall()

rows = run_query(“SELECT * from mytable;”, conn)

Still happy to hear if somebody can explain what is happening here.

1 Like
7

This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.