Why do I need to renew the connection to the base?

user_4121264 0
Viewed 63

I write a program that, with the help of pyodbc, connects to the base several times and performs selects.

Unfortunately, I have to reestablish the connection before calling any of my methods. Why doesn't a single connection in each method work?

# create object (connect to DB)
conn = db.db_connect()

# Call method with my select
weak_password_list = db.Find_LoginsWithWeakPassword(conn)

# I need to connect again
conn = db.db_connect()

# Call method with my select
logins_with_expired_password = db.LoginsWithExpiredPassword(conn)

# And again...
conn = db.db_connect()

# Call method with my select
logins_with_expiring_password = db.Find_LoginsWithExpiringPassword(conn)

######################################################

def db_connect(self):
    try:
        conn = pyodbc.connect('Driver={SQL Server};'
                                  'Server='+self.server_name+''
                                  'Database='+self.database_name+';'
                                  'Trusted_Connection='+self.trusted_connection+'')
        except Exception as e:
            conn = ""
            self.print_error("Failed to connect to the database.", e)
        
        return conn
############################

def Find_LoginsWithWeakPassword(self, conn):
    try:
        cursor = conn.cursor()
        query_result = cursor.execute('''SELECT * FROM table_name''')

    except Exception as e:
         query_result=""
         self.print_error("Select failed in Find_LoginsWithWeakPassword", e)
        
        return query_result

If I only connect once, the second and subsequent methods with select has no effect. Why?

pyodbc python
1 Answers

When you call

weak_password_list = db.Find_LoginsWithWeakPassword(conn)

the function returns the pyodbc Cursor object returned by .execute():

<pyodbc.Cursor object at 0x012F4F60>

You are not calling .fetchall() (or similar) on it, so the connection has an open cursor with unconsumed results. If you do your next call

logins_with_expired_password = db.LoginsWithExpiredPassword(conn)

without first (implicitly) closing the existing connection by clobbering it, then .execute() will fail with

('HY000', '[HY000] [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt (0) (SQLExecDirectW)')

TL;DR: Consume you result sets before calling another function, either by having the functions themselves call .fetchall() or by calling .fetchall() on the cursor objects that they return.

user_21443900
Related