I have python code to upload a lot of data in CSV to SQL Server. The code continues to fail for reasons I do not understand. It's throwing error pyodbc.OperationalError. I've got a temporary fix by restarting (manually) and looking for the last uploaded record and continue uploading from there, but that really sucks.
Here's the error message:
('08S01', '[08S01] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.\r\n (10060) (SQLExecDirectW); [08S01] [Microsoft][ODBC Driver 17 for SQL Server]Communication link failure (10060)')
I tried setting the timeout variable in connect()
conn = pyodbc.connect(
r'DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + server_name + '; \
Database=' + db_name + ';Trusted_Connection=yes;DATABASE=', timeout=5, autocommit=True)
The thing is I don't understand the error. It's already made the connection. It's made a lot of queries to upload data. But suddenly the connection drops?
How do I fix this?
Addendum: I added code to catch the error, and retry the call to the stored procedure that is writing the data to the table. I have it wait 30 seconds (I tried 5, 10, now up to 30) and then try to call the SQL again. It continues to fail on the call to execute the sql:
return_key = cursor.execute(sql_str, values).fetchval()
I thought perhaps if I just waited and retried calling the stored procedure, it would work. But no such luck.