topic Re: Intermittent pypyodbc connection in Python Questions

Intermittent pypyodbc connection

JohnConnor01 — Tue, 08 Mar 2022 17:31:34 GMT

Hello,

I am experiencing intermittent pypyodbc server connections. I am able to successfully run a simple query on a SQL Server database, and without making changes, the same query won't work 5 minutes later. Any help would be appreciated.

-JC from Boston

(Update: When looking at the debugger, I got an error regarding "Trying to use a closed connection")

(Update: I believe the problem is arising with numerous open/close connections strings in the program and then running certain lines when the connection is inadvertently still closed.)

Re: Intermittent pypyodbc connection

KimberlyGarbade — Tue, 08 Mar 2022 16:38:55 GMT

Would you share the piece of your code that is creating/using the connection and tell us what environment your creating and running the python in... python window, notebook, Spyder...?

Re: Intermittent pypyodbc connection

JohnConnor01 — Tue, 08 Mar 2022 16:50:08 GMT

Thanks for the reply... using PyCharm see below:

import pandas as pd
import pypyodbc

connStr = pypyodbc.connect("DRIVER={SQL Server};SERVER=server;DATABASE=database;UID=uid;PWD=password")
query=pd.read_sql("select * from table;",connStr)
connStr.close()
print(query)

Re: Intermittent pypyodbc connection

KimberlyGarbade — Tue, 08 Mar 2022 19:28:38 GMT

If this is stand alone code (I.E. its not embedded in some other code I'm not seeing) than that is odd. I can't replicate the problem, the code works for me (with a slight modification not to return all the records in my test table by using a Where clause on your Select statement)...

Could you have created the connection in one part of your code, closed it, and then tried to call it again later by accident?

For example the code in the first image works because the "conn.close()" is commented out. The second image shows the results of the same code with "conn.close" included and the function call fails:

Only other thing I can think of is that some process running in the database is taking a long time (in computer terms longer than 5 seconds) and your connection is timing out. You could try adding a "timeout=some number of seconds" parameter to your connection string, but that doesn't sound right even to me.

Re: Intermittent pypyodbc connection

JohnConnor01 — Tue, 08 Mar 2022 20:02:01 GMT

Thanks for looking into this Kim, I really appreciate your time! The code is embedded in a very long program, where there are multiple pypyodbc.connect and close() statements - which might be part of the problem - ie maybe its closing when I dont need it to. I might give the connection string a shot just to try it and see what happens. Thanks again for your time.