pyodbc: How to prevent Communication link failure, ConnectionWrite, and ConnectionRead errors?

Question

I'm using pyodbc to connect to my SQL server. My scripts run most of the times as expected but sometimes I get either of these 3 errors when the script is executing a command with the SQL database:

1. pyodbc.Error: ('08S01', '[08S01] [Microsoft][ODBC SQL Server Driver]Communication link failure (0) (SQLExecDirectW)')

2. pyodbc.Error: ('01000', '[01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()). (10054) (SQLExecDirectW)')

3. pyodbc.Error: ('01000', '[01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()). (10054) (SQLExecDirectW)')

What can I do to prevent this?

Edit: Actual commands in my script are really very basic ones so I didn't mention it in my original post.

import pyodbc
connectionString1 = 'x'
connection1 = pyodbc.connect(connectionString1)
cursor1 = connection1.cursor()
query1 = '''
            SELECT/UPDATE ...
            '''
cursor1.execute(query1)
res = cursor1.fetchall()
# do some operations using res
connection1.commit()

Answer 1

sqlalchemy.exc.OperationalError:

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

I have the same problem today because the length of the field is not enough relative to the length of the data. Lengthening the length of the field will solve the problem.

Answer 2

Add these lines to your connectionString1 and it should work

"Trusted_Connection=yes;" "Auto_Commit=true;"