如何为Python脚本添加pyodbc错误40001的错误处理与重试机制？

原始脚本与问题 #

你用于更新数据库的Python脚本如下：

connectionString = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={SERVER};DATABASE={DATABASE};UID={USERNAME};PWD={PASSWORD}'
conn = pyodbc.connect(connectionString)
cursor = conn.cursor()
cursor.execute(f'UPDATE MYTABLE SET LastName = 'TestLName', FirstName = 'TestFName' WHERE ID = 1')
conn.commit()
conn.close()

运行时偶尔会遇到死锁错误：

pyodbc.Error: ('40001', '[40001] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Transaction (Process ID 97) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (1205) (SQLExecDirectW)')

解决方案：添加重试逻辑 #

以下是优化后的脚本，针对死锁错误实现自动重试，同时修复原脚本的语法问题：

import pyodbc
import time

def update_record_with_retry(max_retries=3, retry_delay=2):
    for attempt in range(max_retries):
        conn = None
        try:
            # 每次重试重新建立连接，避免复用异常连接
            connection_string = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={SERVER};DATABASE={DATABASE};UID={USERNAME};PWD={PASSWORD}'
            conn = pyodbc.connect(connection_string)
            cursor = conn.cursor()
            # 修复原脚本的字符串引号冲突问题
            cursor.execute("UPDATE MYTABLE SET LastName = 'TestLName', FirstName = 'TestFName' WHERE ID = 1")
            conn.commit()
            print("记录更新成功")
            return True
        except pyodbc.Error as e:
            error_code = e.args[0]
            # 仅对死锁错误（错误码40001，SQL Server错误号1205）进行重试
            if error_code == '40001' and '1205' in str(e):
                print(f"第{attempt+1}次尝试遇到死锁，{retry_delay}秒后重试...")
                time.sleep(retry_delay)
            else:
                # 非死锁错误直接抛出，不进行重试
                print(f"更新失败，错误信息: {e}")
                raise
        finally:
            # 确保无论成功失败，都关闭数据库连接
            if conn:
                conn.close()
    print(f"已完成{max_retries}次重试，更新仍失败")
    return False

# 执行更新操作
update_record_with_retry()

关键说明 #

• 重试时重建连接：死锁发生后，原连接的事务处于异常状态，必须重新创建连接才能保证后续操作正常
• 限制重试次数与间隔：设置max_retries控制最大重试次数，retry_delay设置重试间隔（单位：秒），避免无限循环或频繁重试加重数据库负担
• 精准匹配死锁错误：只针对死锁相关错误（错误码40001且包含SQL Server错误号1205）重试，其他数据库错误直接抛出，避免掩盖问题
• 修复语法问题：原脚本中f-string内部使用单引号会导致语法错误，改为双引号包裹SQL语句解决冲突
• 安全关闭连接：使用finally块确保连接始终被关闭，避免资源泄漏

内容的提问来源于stack exchange，提问作者noonenine