Write function to connect python with SQL Server in Streamlit?-CodePudding

I have Streamlit app that is connected with SQL Server database. I tried to create a function to connect the app but the app crash and display the below error:

name con is not defined

Code:

@st.cache(allow_outup_mutation=True) # this is changed to st.experimantal_singleton
def connect_db():
   try:
      con=pyodbc.connect(
      driver = 'ODBC DRIVER 17 FOR SQL SERVER',
      Server = 'localhost',
      DATABASE='test_db',
      UID = 'test',
      PWD ='test',
      )
      cursor = con.cursor()
      df = pd.read_sql_query('select * from test_db',con)
      data = df
   except Exception as e:
      st.write("error is :{}".format(e))
   return data

def main()
  # call connect_db in order to use it parameters in latter queries
  connect_db()

based on the answer of @InsertCheesyLine i added this generator to the function

st.experimantal_singleton

CodePudding user response：

You should use st.experimental_singleton for database connectors instead.

From the docs - Each singleton object is shared across all users connected to the app. Singleton objects must be thread-safe, because they can be accessed from multiple threads concurrently.

st.cache on the other hand: The first time Streamlit runs the function and stores the result in a local cache. next time the function is called, if those three values have not changed Streamlit knows it can skip executing the function altogether. Instead, it just reads the output from the local cache.

CodePudding user response：

I did solve this issue I just had to return con variable instead of data variable and assign the con variable to the function connect_db()