Multi-file Python project, one file unable to connect to SQL Server after a while

Question

I have a multi-file Python Project, of which many of the files make connections to an Azure SQL Database. The project works fine but, for some reason, one of the files stops being able to connect to the database after a while of the application running, and I can see no reason as to why; especially when other connection attempts work fine.

The connection string, for all the connections (so across all the files), is define as the following:

SQLServer = os.getenv('SQL_SERVER')
SQLDatabase = os.getenv('SQL_DATABASE')
SQLLogin = os.getenv('SQL_LOGIN')
SQLPassword = os.getenv('SQL_PASSWORD')

SQLConnString = 'Driver={ODBC Driver 17 for SQL Server};Server=' + SQLServer + ';Database=' + SQLDatabase + ';UID='+ SQLLogin +';PWD=' + SQLPassword

sqlConn = pyodbc.connect(SQLConnString,timeout=20)

And the function I am calling, when the error happens is below:

def iscaptain(guild,user):
    userRoles = user.roles
    roleParam = ""
    for role in userRoles:
        roleParam = roleParam + "," + str(role.id)
    cursor = sqlConn.cursor()
    roleParam = roleParam[1:]
    cursor.execute('EXEC corgi.GetUserAccess ?, ?;',guild.id,roleParam)
    for row in cursor:
        if row[1] == "Team Captain":
            cursor.close()
            return True
    cursor.close()
    return False

The error specifically happens at cursor.execute. I currently get the error

pyodbc.OperationalError: ('08S01', '[08S01] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x68 (104) (SQLExecDirectW)')

Previously I didn't have the timeout in the connection on the specific file that was having a problem, and I did get a different error:

Communication link failure

Apologies, I don't have the full previous error.

Other connections, in other files in the same project, work fine, so the problem is not a network issue; if it were none of the connections would work. The problem only happens in one file, where all the connection attempts fail.

Googling the latest error really doesn't get me far. For example, there's a Github issue that gets nowhere, and this question isn't related as connecting works fie from other files.

Note, as well, that this happens after a period of time; I don't really know how long that period is but it's certainly hours. Restarting the project fixes the issue as well; the above function will work fine. That isn't really a solution though, I can't keep restarting the application ad-hoc.

The error is immediate as well; it's like Python/PyODBC isn't trying to connect. When stepping into the cursor.execute the error is generated straight after; it's not like when you get a timeout and you'll be waiting a few seconds, or more, for the timeout to occur.

I'm at a loss here. Why is the file (and only that one) unable to connect any more later on? There are no locks on the database either, so It's not like I have a transaction left hanging; though I would expect a timeout error again then as the procedure would be unable to gain a lock on the data.

Note, as well, that if I manually execute the procedure, in sqlcmd/SSMS/ADS, data is returned fine as well, so the Procedure does work fine. And, again, if I restart the application it'll work without issue for many hours.

Edit: I attempted the answer from Sabik below, however, this only broke to application, unfortunately. The solution they provided had the parameter self on the function validate_conn and so calling validate_conn() failed as I don't have a parameter for this "self". The method they said to use, just validate_conn didn't do anything; it doesn't call the function (which I expected). Removing the parameter, and references to self, also broke the application, stating that sqlConn wasn't declared even though it was; see image below where you can clearly see that sqlConn has a value: Yet immediately after that line I get the error below:

UnboundLocalError: local variable 'sqlConn' referenced before assignment

So something appears to be wrong with their code, but I don't know what.

user_683329 · Answer

One possibility being discussed in the comments is that it's a 30-minute idle timeout on the database end, in which case one solution would be to record the time the connection has been opened, then reconnect if it's been more than 25 minutes.

This would be a method like:

def validate_conn(self):
    if self.sqlConn is None or datetime.datetime.now() > self.conn_expiry:
        try:
            self.sqlConn.close()
        except:  # pylint: disable=broad-except
            # suppress all exceptions; we're in any case about to reconnect,
            # which will either resolve the situation or raise its own error
            pass

        self.sqlConn = pyodbc.connect(...)
        self.conn_expiry = datetime.datetime.now() + datetime.timedelta(minutes=25)

(Adjust as appropriate if sqlConn is a global.)

At the beginning of each function which uses sqlConn, call validate_conn first, then use the connection freely.

Note: this is one of the rare situations in which suppressing all exceptions is reasonable; we're in any case about to reconnect to the database, which will either resolve the situation satisfactorily, or raise its own error.

Edit: If sqlConn is a global, it will need to be declared as such in the function:

def validate_conn():
    global sqlConn, conn_expiry
    if sqlConn is None or datetime.datetime.now() > conn_expiry:
        try:
            sqlConn.close()
        except:  # pylint: disable=broad-except
            # suppress all exceptions; we're in any case about to reconnect,
            # which will either resolve the situation or raise its own error
            pass

        sqlConn = pyodbc.connect(...)
        conn_expiry = datetime.datetime.now() + datetime.timedelta(minutes=25)

As an unrelated style note, a shorter way to write the function would be using (a) a with statement and (b) the any operator, like this:

    with sqlConn.cursor() as cursor:
        roleParam = roleParam[1:]
        cursor.execute('EXEC corgi.GetUserAccess ?, ?;', guild.id, roleParam)
        return any(row[1] == "Team Captain" for row in cursor)

The with statement has the advantage that the cursor is guaranteed to be closed regardless of how the code is exited, even if there's an unexpected exception or if a later modification adds more branches.

user_12885751 · Answer

Although the solution for Sabik didn't work for me, the answer did push me in the right direction to find a solution. That was, specifically, with the use of the with clauses.

Instead of having a long lasting connection, as I have been informed I had, I've now changed these to short lived connections with I open with a with, and then also changed the cursor to a with as well. So, for the iscaptain function, I now have code that looks like this:

def iscaptain(guild,user):
    userRoles = user.roles
    roleParam = ""
    for role in userRoles:
        roleParam = roleParam + "," + str(role.id)
    #sqlConn = pyodbc.connect(SQLConnString,timeout=20)
    with pyodbc.connect(SQLConnString,timeout=20) as sqlConn:
        with sqlConn.cursor() as cursor:
            roleParam = roleParam[1:]
            cursor.execute('EXEC corgi.GetUserAccess ?, ?;', guild.id, roleParam)
            return any(row[1] == "Team Captain" for row in cursor)
    return False

It did appear that Azure was closing the connections after a period of time, and thus when the connection was attempted to be reused it failed to connect. As, however, both hosts are in Azure, but the Server running the Python application and the SQL Database, I am happy to reconnect as needed, as speed should not be a massive issue; certainly it hasn't been during the last 48 hours of testing.

This does mean i have a lot of code to refactor, but for the stability, it's a must.

Multi-file Python project, one file unable to connect to SQL Server after a while

2 Answers