2

Can some one tell me the best (fastest) way to send data from a pandas data frame to Oracle? With pandas to_sql, it's depressingly slow due to one insert statement per row.

I tried odo, but cannot get passed a compilation error (construct has no default compilation handler) and documentation is not helping. Here's a related post of mine with a question.

I know there is a sql loader option for Python, but have never run a sub process (does that work in the Jupyter notebook?).

Ultimately, I'm trying to automate loading data from a CSV into Oracle on a monthly basis.

Community
  • 1
  • 1
Dance Party2
  • 7,214
  • 17
  • 59
  • 106

1 Answers1

2

You can use the cx_Oracle python module which can do an insert of a list using the executemany() method. So you can convert your dataframe to a list or just load your csv file as a list and then load it into Oracle.

Jared
  • 2,904
  • 6
  • 33
  • 37
  • That would be great. I just tried that, actually, following this: http://www.oracle.com/technetwork/articles/dsl/python-091105.html Is the speed comparable to SQL Loader? – Dance Party2 Apr 26 '16 at 14:24
  • Yes, I would say the speed is comparable. I use it for a lot of different jobs and never have any issues. – Jared Apr 26 '16 at 14:27
  • Do I need a list of lists (from the data frame)? – Dance Party2 Apr 26 '16 at 14:33
  • You just need to make sure that whatever you pass into the executemany() method is of the python type list. Otherwise it won't work. – Jared Apr 26 '16 at 14:36
  • I have a column that is formatted as datetime64[ns]. How do I specify this as such in cursor.setinputsizes()? I'll ask a separate question if you prefer. – Dance Party2 Apr 26 '16 at 15:17
  • yes please post a separate question with some example code and i will try to help you out – Jared Apr 26 '16 at 15:19
  • Posted here: http://stackoverflow.com/questions/36869371/cx-oracle-equivalent-of-datetime64ns – Dance Party2 Apr 26 '16 at 15:28