Problems while inserting df values with python into Oracle db-CodePudding

I am having troubles when trying to insert data from a df into an Oracle database table, this is the error: DatabaseError: ORA-01036: illegal variable name/number

These are the steps I did:

This is the dataframe I have imported from yfinance package and elaborated in order to respect the integrity of the data types of my df

I transformed my df into a list, these are my data in the list:

this is the table where I want to insert my data:

This is the code:

sql_insert_temp = "INSERT INTO TEMPO('GIORNO','MESE','ANNO') VALUES(:2,:3,:4)"

index = 0
for i in df.iterrows():
   cursor.execute(sql_insert_temp,df_list[index])
   index  = 1
connection.commit()

I have tried a single insert in the sqldeveloper worksheet, using the data you can see in the list, and it worked, so I guess I have made some mistake in the code. I have seen other discussions, but I couldn't find any solution to my problem.. Do you have any idea of how I can solve this or maybe is it possible to do this in another way?

I have tried to print the iterated queries and that's the result, that's why it's not inserting my data:

CodePudding user response：

If you already have a pandas DataFrame, then you should be able to use the to_sql() method provided by the pandas library.

import cx_Oracle
import sqlalchemy
import pandas as pd

DATABASE = 'DB'
SCHEMA   = 'DEV'
PASSWORD = 'password'
connection_string  = f'oracle://{SCHEMA}:{PASSWORD}@{DATABASE}'
db_conn     = sqlalchemy.create_engine(connection_string)

df_to_insert = df[['GIORNO', 'MESE', 'ANNO']] #creates a dataframe with only the columns you want to insert

df_to_insert.to_sql(name='TEMPO', con=db_connection, if_exists='append')

name is the name of the table
con is the connection object
if_exists='append' will add the rows to end of the table. There are other options to add fail or drop and re-create the table

other parameters can be found on the pandas website. pandas.to_sql()