Load table to Oracle through pandas io SQL

Question

Im executing the following code, the purposes of the exeuction is to create a lookup-table in the Oracle data base to speed up my load of data. The table I want to load in is simply a vector with ID values, so only one column is loaded.

The code is written per below:

lookup = df.id_variable.drop_duplicates()

conn = my_oracle_connection()
obj = lookup.to_sql(name = 'lookup', con = conn, if_exists = 'replace')

I get the following error when exeucting this:

DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ORA-01036: illegal variable name/number

I can execute a psql.read_sql() query but above fails.

Now, I dont exactly know how to go about fixing it, im quite new to the technical aspects of getting this to work so any pointers in what direction to take it would be greately appriciated.

Thanks for any time and input!

Answer 1

I had the same issue when using cx_Oracle connection (I was able to use .read_sql function, but not the .to_sql one)

Use SQLalchemy connection instead:

import sqlalchemy as sa
oracle_db = sa.create_engine('oracle://username:password@database')
connection = oracle_db.connect()
dataframe.to_sql('table_name', connection, schema='schema_name', if_exists='append', index=False)