如何保持连续写入Oracle表，即使表不可访问?

Question

我正在尝试插入多个记录到一个Oracle表连续。为此我编写了下面的python脚本。

import cx_Oracle
import config
connection = None
try:
# Make a connection
connection = cx_Oracle.connect(
config.username,
config.password,
config.dsn,
encoding=config.encoding)
# show the version of the Oracle Database
print(connection.version)
# Insert 20000 records
for i in range(1, 20001):
cursor = connection.cursor()
sql = "INSERT into SCHEMA.ABC (EVENT_ID, EVENT_TIME) VALUES( "+ str(i)+" , CURRENT_TIMESTAMP)"
cursor.execute(sql)
connection.commit()
except cx_Oracle.Error as error:
print(error)
finally:
if connection:
connection.close()

因此，在插入期间，当我更改表名称时，它只是创建一个异常并从脚本中出来(因为表不可用且无法写入)。我想要的是，即使当我做重命名和表是不可用的，脚本需要保持不断尝试插入。这有可能吗?

Answer 1

下面是Ptit Xav所说的一个例子。我添加了一些代码来在最大重试次数后退出，因为这通常是需要的。

# Insert 20000 records
for i in range(1, 20001):
retry_count = 0
data_inserted = False
while not data_inserted:
try:
cursor = connection.cursor()
sql = "INSERT into SCHEMA.ABC (EVENT_ID, EVENT_TIME) VALUES( "+ str(i)+" , CURRENT_TIMESTAMP)"
cursor.execute(sql)
connection.commit()
data_inserted = True
except cx_Oracle.Error as error:
print(error)
time.sleep(5) # wait for 5 seconds between retries
retry_count += 1
if retry_count > 100:
print(f"Retry count exceeded on record {i}, quitting")
break
else:
# continue to next record if the data was inserted
continue
# retry count was exceeded; break the for loop.
break

关于while... else逻辑的更多解释，请参阅此回答。

Answer 2

您可能希望将插入逻辑封装在函数中，该函数捕获可能的异常并执行重试

def safe_insert(con, i):
"""
insert a row with retry after exception
"""
retry_cnt = 0
sql_text = "insert into ABC(EVENT_ID, EVENT_TIME) VALUES(:EVENT_ID,CURRENT_TIMESTAMP) "
while True:
try:
with con.cursor() as cur:
cur.execute(sql_text, [i])
con.commit()
return
except cx_Oracle.Error as error:
print(f'error on inserting row {i}')
print(error)
time.sleep(1)
retry_cnt += 1
if (retry_cnt > 10):
raise error

类似于@kfinity的回答，我也添加了一个限制重试-如果超过这个限制，函数会引发异常。

还要注意，函数在INSERT语句中使用绑定变量，这比在语句中连接值更可取。

用法就像

一样简单

for i in range(1, 20001): 
safe_insert(con, i)