Load table to Oracle through pandas io SQL

user_5263412 0
Viewed 5678

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!

pandas oracle python
2 Answers

I think the problem happens writing to the Oracle DB using a connection object created by cx_Oracle. SqlAlchemy has a work around:

import cx_Oracle
from sqlalchemy import types, create_engine

conn = create_engine('oracle+cx_oracle://Jeremy:SuperSecret@databasehost:1521/?service_name=gdw')

df.to_sql('TEST', conn, if_exists='replace')
user_4954550
Related