在python中SQL连接失败后如何重试?

Question

在python中连接/写入数据库失败时重试的正确方法是什么？

我尝试使用此代码，它一直有效，直到我重新启动我的 sql 服务器并且 python 尝试连接到它，我以 30 秒的间隔获得 10 次"30 秒后重试"，但它不会重新连接当 sql 服务器再次运行时。

编辑：当retry_count1 = 10时它会重新连接，但在下一个周期，计数器需要在再次连接之前最多计数10。

有谁知道为什么它不会重新连接？

retry_flag1 = True
retry_count1 = 0
while retry_flag1 and retry_count1 < 10:
try:
cursor = cnxn.cursor()
cursor.execute('SELECT too_id FROM [TTMM].[dbo].[Machines] WHERE MachineID = {}'.format (machineid,))
too_id = cursor.fetchone()[0]
cursor.execute('INSERT INTO [TTMM].[dbo].[{}](counter, effectively, too_id) VALUES ({},{},{})'.format (machineid, counter, effectively, too_id,))
cnxn.commit()
cursor.close()
retry_flag1 = False
except as e:
print(e)
print("Retry after 30 sec")
retry_count1 = retry_count1 + 1
time.sleep(30)

这是重新启动 sql 服务器后的控制台输出。

('08S01', '[08S01] [FreeTDS][SQL Server]Write to the server failed (20006) (SQLExecDirectW)')
Retry after 1 sec
('08S01', '[08S01] [FreeTDS][SQL Server]Communication link failure (0) (SQLExecDirectW)')
Retry after 1 sec
('08S01', '[08S01] [FreeTDS][SQL Server]Communication link failure (0) (SQLExecDirectW)')
Retry after 1 sec
141222 Cykel
('08S01', '[08S01] [FreeTDS][SQL Server]Communication link failure (0) (SQLExecDirectW)')
Retry after 1 sec
('08S01', '[08S01] [FreeTDS][SQL Server]Communication link failure (0) (SQLExecDirectW)')

Answer 1

我通过添加cnxn.close并创建一个新连接找到了解决方案。

retry_flag = True
retry_count = 0
cursor = cnxn.cursor()
while retry_flag and retry_count < 5:
try:
cursor.execute('SELECT too_id FROM [TTMM].[dbo].[Machines] WHERE MachineID = {}'.format (machineid,))
too_id = cursor.fetchone()[0]
cursor.execute('INSERT INTO [TTMM].[dbo].[{}](counter, effectively, too_id) VALUES ({},{},{})'.format (machineid, counter, effectively, too_id,))
retry_flag = False
print("Printed To DB - Counter = ", counter, ", Effectively = ", effectively, ", too_id = ", too_id,)
except Exception as e:
print (e)
print ("Retry after 5 sec")
retry_count = retry_count + 1
cursor.close()
cnxn.close()
time.sleep(5)
cnxn = pyodbc.connect('DRIVER=FreeTDS;SERVER=*;PORT=*;DATABASE=*;UID=*;PWD=*;TDS_Version=8.7;', autocommit=True)
cursor = cnxn.cursor()
cursor.close()