How can i append dataframe from pandas to the oracle table?

Question

I want to append dataframe (pandas) to my table in oracle. But this code deletes all rows in table:(

My dataframe and my result become this:

 0, 0, 0, ML_TEST, 0, 5 
 0, 0, 0, ML_TEST, 0, 6

by this code block below :

import cx_Oracle
import pandas as pd

from sqlalchemy import types, create_engine
dataset = pd.read_csv("denemedf.txt", delimiter=",")
print(dataset)
from sqlalchemy import create_engine
engine = create_engine('oracle://***:***@***:***/***', echo=False)
dataset.to_sql(name='dev_log',con=engine ,if_exists = 'append', index=False)

How can i append this dataframe's rows to the last of the table without deleting existing rows in this table?

Now i tried again, now appends to the last, but in first try it deleted all existing rows. How can do this effectively without causing any problem?

Actually problem occurs because of the schema of this table. This table is in gnl owner, but i connected with prg. So it couldnt find the table and created another. Is that any way to write owner or schema in to this function?

Solution 1

I think this may help :

import cx_Oracle
import pandas as pd

dataset = pd.read_csv("C:\\pathToFile\\denemedf.txt", delimiter=",")
con = cx_Oracle.connect('uname/pwd@serverName:port/instanceName')
cursor = con.cursor()

sql='INSERT INTO gnl.tbl_deneme VALUES(:1,:2,:3,:4,:5,:6)'
df_list = dataset.values.tolist()
n = 0
for i in dataset.iterrows():
    cursor.execute(sql,df_list[n])
    n += 1

con.commit()
cursor.close
con.close       

provided insert privilege is already granted to the schema prg for your table tbl_deneme

( after connecting to gnl -> grant insert on tbl_deneme to prg )

where your text file( denemedf.txt ) is assumed to be

col1,col2,col3,col4,col5,col6
0, 0, 0, ML_TEST, 0, 5
0, 0, 0, ML_TEST, 0, 6

Moreover, a dynamic option, which will create a table if not exists by using the column names at the first line and insert the values depending on the splitted elements of the values list derived from the second line without explicitly specified the variable list one by one, and more performant option along with using cursor.executemany might be provided such as

import cx_Oracle
import pandas as pd

con = cx_Oracle.connect(user, password, host+':'+port+'/'+dbname)
cur = con.cursor()
tab_name = 'gnl.tbl_deneme'
cursor.execute('SELECT COUNT(*) FROM user_tables WHERE table_name = UPPER(:1) ',[tab_name])       
exs = cursor.fetchone()[0]
df = pd.read_csv('C:\\pathToFile\\denemedf.txt', sep = ',', dtype=str)
col=df.columns.tolist()
crt=""
for k in col:
    crt += ''.join(k)+' VARCHAR2(4000),'

if int(exs) == 0:
    crt = 'CREATE TABLE '+tab_name+' ('+crt.rstrip(",")+')'
    cursor.execute(crt)

vrs=""
for i in range(0,len(col)):
    vrs+= ':'+str(i+1)+','

cols=[]
sql = 'INSERT INTO '+tab_name+' VALUES('+vrs.rstrip(",")+')'
for i in range(0,len(df)):
    cols.append(tuple(df.fillna('').values[i]))
 
cursor.executemany(sql,cols)
con.commit()
cursor.close
con.close

Solution 2

 dataset.to_sql('dev_log',engine ,if_exists = 'append', index=False)

dev_log use directly as a table name and engine directly for connection not name='dev_log' and con = engine is

paramenter: append: Insert new values to the existing table

so i think it will work for appending new row to the existing table and it will not delte any row from existing table

pandas.DataFrame.to_sql

Solution 3

Considering data_df to be dataframe it can be done by below 3 lines

rows = [tuple(x) for x in data_df.values]
cur.executemany("INSERT INTO table_name VALUES (:1,:2,:3,:4)",rows)
con_ora.commit()

Comments

[Jennings]

Thank you!! This was the concise answer I needed. I added one more line that really helped: 0 - func = lambda ls : [str(x) for x in ls] # convert values of row to string; 1 - [tuple(func(x)) for x in df_upload.head().values]