CODEWITHSUNDEEP
pythonsqlpandasdataframepyodbc
Dat Nguyen
Dat Nguyen

Reputation: 163

Pandas read_sql_query still running in the background even though connection is closed?

I have a closed connection:

Config = dict(server = serverSample,
              port = 1433,
              username = username_input,
              password = password_input)
conn_str = (‘Server ={server},  {port}; + ‘TRUSTED_CONNECTION=yes’)
conn = pyodbc.connect(
    r’Driver={ODBC Driver 13 for SQL Server};’ + conn_str.format(**config)
)
Do = pd.read_sql_query(query, conn)
conn.close()

Right after I use the read_sql_query method I put the data into DataFrame.

For some reason, I got notified by the data guy that my connection was still going on for a while.

Does the close method actually stop the SQL from running, or I have to do sth else to stop the query from running in the server?

I’m using pyodbc.

Upvotes: 2

Views: 3468

Answers (1)

Max Voitko
Max Voitko

Reputation: 1629

It depends on what you pass as the conn object to the read_sql_query method call.

  1. If you pass Connection object it should be enough, but the problem could be with the place where you close the connection. Maybe this code is unreachable.

  2. If you pass the Engine object. You should dispose of the connection:

conn.dispose()
  1. You could also try turning off the pooling. Details are in the thread:
import pyodbc


pyodbc.pooling = False
  1. One more option is to use the connection as a context_manager and commit and not close the connection. This approach is preferred by pyodbc:
import pyodbc


conn = pyodbc.connect(config) 
with conn:
    crs = conn.cursor()
    do_stuff
    # conn.commit() will automatically be called when Python leaves the outer `with` statement
    # Neither crs.close() nor conn.close() will be called upon leaving the the `with` statement!!

Upvotes: 2

Related Questions