我正在嘗試將多條記錄連續插入到一個 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()
因此，在插入期間，當我更改表名時，它只會創建一個例外并從腳本中退出（因為該表不可用且無法寫入）。我想要的是，即使當我重命名并且表不可用時，腳本也需要不斷嘗試插入。有沒有辦法做到這一點？
uj5u.com熱心網友回復：
這是 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。
uj5u.com熱心網友回復：
您可能希望將插入邏輯封裝在一個函式中，以捕獲可能的例外并執行重試
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)
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/314335.html