Communication link failure with pyodbc

Question

import pyodbc
import time
connection = pyodbc.connect(..............)
cursor = connection.cursor()
while True:
    time.sleep(1)
    cursor.execute(INSERT_QUERY)
    cursor.commit()

that works. but suddenly I got an exception pyodbc.Error: ('08S01', '[08S01] [Microsoft][ODBC SQL Server Driver]Communication link failure (0) (SQLExecDirectW)')

Why is that ?Why the link suddenly disconnect ? How can I handle that exeption and re-connect? how can I fix that?

Answer 1

By googling the error code, it means the connection failed for some reason for another.

You might want to add retry/reconnection logic for that case; crudely something like this.

import pyodbc
import time

connection = None
while True:
    time.sleep(1)
    if not connection:  # No connection yet? Connect.
        connection = pyodbc.connect("..............")
        cursor = connection.cursor()
    try:
        cursor.execute(INSERT_QUERY)
        cursor.commit()
    except pyodbc.Error as pe:
        print("Error:", pe)
        if pe.args[0] == "08S01":  # Communication error.
            # Nuke the connection and retry.
            try:
                connection.close()
            except:
                pass
            connection = None
            continue
        raise  # Re-raise any other exception