Microsoft SQL connection very unsteady when deployed on Azure

Deployment
1

So this is the first time I’ve connected a Streamlit app to a SQL server using the pyodbc package and thinking there might be something wrong with how I’ve written the connection/cursor/queries which keeps causing there to sporadically be a connection error message (i.e. ERROR [08S01] [Microsoft][ODBC Driver 17 for SQL Server]Communication link failure)

Here are my functions for connections and writing to the database and I’m wondering if I should be ‘closing’ the cursor/connection more? But I am unsure how I can keep closing the connection while the user is still on the web app (as the web app essentially contains multiple ‘read SQL’ queries simultaneously to display various data)

def open_connection(engine = False):
    connection_string = f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}'

    if engine:
        conn = create_engine(f'mssql+pyodbc:///?odbc_connect={connection_string}')

    else:
        @st.cache_resource
        def init_connection():
            return pyodbc.connect(
                connection_string
            )

        conn = init_connection()
    
    return conn

def submit_sql(conn, query):
    cursor = conn.cursor()
    cursor.execute(query)
    cursor.commit()

And then for reading in SQL, I simply use pd.read_sql()

Thanks!

2

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