0

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 enter image description here

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

enter image description here

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

enter image description here

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:

enter image description here

Improve this question
2
  • 1
    The probable root cause - this is in Oracle invalid column name (quoted with apostrophs) "INSERT INTO TEMPO('GIORNO','MESE','ANNO') you should use quotes 'INSERT INTO TEMPO("GIORNO","MESE","ANNO"). Additionaly executemany would be better. Commented Oct 22, 2021 at 14:23
  • 1
    Apart from suggested to_sql method never use loop and insert, as it is very slow: too much roundtrips and parsing at every loop pass. Use executemany with list of tuples to perform bulk insert. Commented Oct 22, 2021 at 15:17

1 Answer 1

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

now I am receving this error: DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ORA-01036: illegal variable name/number. Is it possible I need to establish the connection with sqlalchemy?
Yup for Oracle is SQLAlchemy required, Using SQLAlchemy makes it possible to use any DB supported by that library. Legacy support is provided for sqlite3.Connection objects see doc
what module are you using to create your database connection?
@el_oso I am using cx_Oracle, but how can I use executemany without loop to insert everything? I am sorry guys if I am stubborn, it's the first time I use python to do such things ç_ç
No need for a loop. pandas module will automatically handle all rows in your dataframe. This can cause an issue with large DataFrames, so you can use the chunk size parameter to tell pandas to do the insert in batches of 50, 100, 1k rows at a time. Again, this is all handled in the background
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.