How to use Pandas Write_Frame to export results to Oracle Database in cx_Oracle

Question

I am trying to export a Pandas DataFrame to an Oracle database. I have come across the Write_Frame function in Pandas which sounds like exactly what I need.

However, I have done tons of searches online and just can't get it to work. I have imported cx_Oracle and can connect to the Oracle database as well as running SQL queries without any problems, but when I run this it gives me a 'NotImplementedError':

import pandas.io.sql as psql

 output = psql.write_frame(MyResults, name = 'MySchema.MyTable', con = MyCon, 
                           flavor = 'oracle', if_exists = 'replace')

So far I have seen many examples of wrtie_frame on sqlite and mysql, so does that mean it won't work if flavor = 'oracle'? I have tried changing the flavor to mysql but it gives me an error saying 'Invalid SQL statement'??

Could anyone help me fixing this as I prefer not having to write the results to a CSV file then export to the database table?

Thanks

Tapdance

What version of pandas are you? Open up the python shell and type in "import pandas", then "pandas.__version__" – Jordison

You could read directly into python, but obviously there is potentially a lot of overhead in that. You could try doing this on pandas' master branch (which uses SQLAlchemy under the hood), in fact it would be great to get your feedback on that and ensure it's working in 0.14! – Incubator

Answer 1

To expand on Andy Hayden's comment: oracle is indeed not supported in pandas <= 0.13 and older (only sqlite and mysql were supported).

But the sql module got a big overhaul in 0.14 (in development at the moment). It now uses sqlalchemy under the hood, so normally oracle should now be supported through sqlalchemy. See the dev docs for more details: http://pandas-docs.github.io/pandas-docs-travis/io.html#io-sql. And indeed, if you would be able to test it with oracle and give some feedback, that would be fantastic!

Initiative

Thanks everyone for the responses. I'm using pandas 0.13. I tried 'sqlalchemy' and it seems that I can do engine = create_engine('oracle+cx_oracle://User:Passowrd@mytns'). However, I have no luck after that as I tried writing to the database using to_sql() and it still doesn't work : df.to_sql('TRORE.HW_TEST_PY',engine, flavor = 'oracle, if_exists = 'replace') and not engine.has_table('MY_TABLE','MY_SCHEMA') either. So is it the case that you can create an engine in 0.13 but if you want to do anything with it you need 0.14? – Tapdance

This is a new feature in 0.14. The create_engine is just an sqlalchemy function, so that also works in 0.13, but pandas to_sql only uses it from 0.14. If you can't upgrade to the pandas development version, another option is to download just the file with the sql code (github.com/pydata/pandas/blob/master/pandas/io/sql.py), save this next to your code, import it (import sql) and then you can use the new functionality as sql.to_sql(df, 'name', engine) – Initiative

Ok following what was suggested, I finally was able to use to_sql and it connected to Oracle and created a table succesfully. Thanks for the help! I did find the run time a bit long as I was only inserting 5 rows and less than 10 columns. It probably took almost a minute. As I was only trying to insert into a specific schema, even though I specified it in the name (eg. my_schema.table), it still created the table in the main schema of the database. All my string fields are showing 'Exluded' after the table is created but the number fields are fine. – Tapdance

Glad you succeeded! Strange that it takes such a long time, that should certainly not be the case for that number of rows/columns. If you have specific problem (eg with the string fields, or the schema), please raise an issue at github.com/pydata/pandas/issues, that would be very welcome. I don't think specifying the schema is supported at the moment. – Initiative