PYTHON/Oracle. IMPORT txt INTO Table with df.values.tolist() and executemany

Question

I'm trying to import an txt file by using df.values.tolist() and executemany

Creating a def and IMPORT transactions.txt to sql:

def transactions2sql(path, tableName):
    df_transactions2sql = pd.read_csv(path, sep = ';')
    lst_tmp_trnsctns = df_transactions2sql.values.tolist()

Create a table:

def read_transactions():
    try:
            curs.execute('''
            CREATE TABLE tmp_transactions(
                trans_id varchar(128),
                trans_date date,
                card_num varchar(128),
                oper_type varchar(128),
                amt decimal,
                oper_result varchar(128),
                terminal varchar(128),
            )
        ''')
    except:
        print('tmp_transactions already exists')

Import df_transactions2sql.values.tolist() into table:

curs.executemany('INSERT INTO tmp_transactions (trans_id, trans_date, card_num, oper_type, amt, oper_result, terminal) values (?,?,?,?,?,?,?)', lst_tmp_trnsctns) 

transactions2sql('transactions_01032021.txt', 'tmp_transactions')
#read_transactions('tmp_transactions')

And here comes the error: name 'lst_tmp_trnsctns' is not defined

Answer 1

Your exception should only print 'tmp_transactions already exists' if the error was ORA-942.

At the most basic, try changing your exception handler to:

except cx_Oracle.Error as e:
    error, = e.args
    if error.code != 942:
    print(e)

With this you will see ORA-00904 shown because you have a trailing comma in the CREATE statement.

Change the CREATE to:

CREATE TABLE tmp_transactions(
                trans_id varchar(128),
                trans_date date,
                card_num varchar(128),
                oper_type varchar(128),
                amt decimal,
                oper_result varchar(128),
                terminal varchar(128)
            )