How to keep continuously write to Oracle table even if table is not accessible?-CodePudding

I am trying to insert multiple records into the one Oracle table continuously. For which I have written below python script.

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()

So, During the insert, when I change the table name it just create an exception and comes out of script(as the table is not available and cannot write). What I want is, Even if when I do the rename and table is not available, the script needs to keep continuously trying insert. Is there a way this can be possible?

CodePudding user response：

Here's an example of what Ptit Xav was talking about. I added some code to quit after a max number of retries, since that's often desirable.

# 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

See this answer for more explanation of the while... else logic.

CodePudding user response：

You may want to encapsule the insert logik in a function that catches the possible exception and performs the retry

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

Similar to @kfinity's answer I also added a limit on retry - if this limit is exceeded the function raise an exception.

Note also that the function uses bind variables in the INSERT statement which is preferable to the concatenation of the values in the statement.

The usage is as simple as

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