How Do I Insert A Dataframe To An Oracle Table Illegal/variable Oracle

I have the following dataframe df2 = {names: [PEPE, LUIS], id: [1,2], stages: [0,1], ord: [3, 2]} but these are the required fields, the table to insert, you have more fields, whi

Solution 1:

You can use df2.values.tolist in order to get a parameter list, and such a code as below in order to insert the values within the dataframe :

import pandas as pd
import cx_Oracle
conn = cx_Oracle.connect('un/pwd@host:port/dbname')

try:
    cursor = conn.cursor()
    df2 = pd.DataFrame({'names':['Pepe','Luis'], 'stages': [0,1], 'order': [3, 2]}) 
    col_list = df2.values.tolist()

    cursor.prepare("INSERT INTO customer_prf(names,label,type,id,n_stage,ctry,\"order\") VALUES(:1,'references','text',seq_customer.nextval,:2,2,:3)")
    cursor.executemany(None,col_list)
    print("executed!")
except Exception as err:
    print('Error', err)
else:
    conn.commit()

where

• prefer using executemany rather than execute as being more performant

• create a sequence (seq_customer) and add to your values list (seq_customer.nextval) as using DB version 11g, if your database was of version 12c+, then the ID column could be identified within the create table ddl statement(eg. during table creation) such as

  ID INT generated always as identity primary key