Python: Load Oracle table directly from Pandas (write to Oracle)

Question

Is there any way to load data directly into an Oracle table from Pandas.

Currently I'm writing the data set into a csv file and then loading the table. I would like to bypass "writing to csv" step.

I'm using cx_Oracle for connecting to Oracle database. url is passed as a parameter when calling the python script. result will be stored as a pandas dataframe in variable dataset . The layout of dataset and table definition are same.

    import cx_Oracle as cx
    response = requests.get(url)
    data = response.json()
    dataset = json_normalize(data['results'])

Please let me know if you require any further .

Pretty much every operation in every data pipe line entails generating a CSV file and passing it to the next step. — APC
– APC, Commented May 2, 2018 at 10:21
@APC when you just need to perform some operation you can read directly from DB, or source. — harshil9968
– harshil9968, Commented May 2, 2018 at 10:24
@harsh - I was being - or trying to ba - humourous. Apologies for any confusion caused. — APC
– APC, Commented May 2, 2018 at 10:28
Apologies if there was any confusions.. The question is about write to Oracle from pandas dataframe and not reading from Oracle. Currently, I've been writing it to csv file and then to Oracle. But I would like to know if I can bypass this step. It would be great if I can bypass it for some of the data loads. For most processes in the pipeline, we are writing it to csv. — Leo
– Leo, Commented May 2, 2018 at 10:37

user6855124 · Accepted Answer · 2018-05-02 10:21:20Z

3

Did you try to_sql function from pandas module?

from sqlalchemy import create_engine
engine = create_engine('oracle://[user]:[pass]@[host]:[port]/[schema]', echo=False)
dataset.to_sql(name='target_table',con=engine ,if_exists = 'append', index=False)

answered May 2, 2018 at 10:21

user6855124

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

4 Comments

Leo Over a year ago

No the question is write to Oracle (Load table from Pandas)

APC Over a year ago

@:harsh - actually the question is about writing from pandas to Oracle table. So this seems like a reasonable response.

Leo Over a year ago

@F.J I tried it, but it says "InvalidRequestError: Could not reflect: requested table(s) not available in Engine" ; But name of the table is present and same, because first I received another message with invalid identifier : "DatabaseError: (cx_Oracle.DatabaseError) ORA-00904: "population": invalid identifier" and I corrected the name of the column.

Leo Over a year ago

My bad. It is working when I put if_exists = 'append', I thought I could truncate and reload if I put if_exists = 'replace' which seems to be not working.

Collectives™ on Stack Overflow

Python: Load Oracle table directly from Pandas (write to Oracle)

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related