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!

I did some checking in the underlyinc script which loades the connections and it seems to be an cx_Oracle.connect() setup. — swepab
– swepab, Commented Mar 15, 2016 at 8:50
DataFrame.to_sql() doesn't seem to support DBAPI2 connections to Oracle. Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported. That would seem to be supported by the error message related to sqlite_master too. — Joachim Isaksson
– Joachim Isaksson, Commented Mar 15, 2016 at 8:52
Ok, I think I follow, seems to be relatively straightforward. I will try and see if I can change the setup towards SQLAlchemy for my purpose. Could it be possible to load the look-up using the current engine, i.e. cx_Oracle? Maybe by some other function? — swepab
– swepab, Commented Mar 15, 2016 at 8:56

Michal Hruška · Accepted Answer · 2017-08-23 06:17:47Z

6

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)

answered Aug 23, 2017 at 6:17

Michal Hruška

4642 gold badges6 silver badges17 bronze badges

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

1 Comment

Aman J Over a year ago

Can you share the reason why cx_oracle does not work?

Jeremy Thompson · Accepted Answer · 2020-09-15 05:50:52Z

0

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

answered Sep 15, 2020 at 5:50

Jeremy Thompson

66.3k38 gold badges225 silver badges344 bronze badges

Collectives™ on Stack Overflow

Load table to Oracle through pandas io SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related