Connecting to Oracle Database with ODBC/Python

Question

We've recently installed a new piece of software that uses an Oracle Database to store the data. My goal is to connect to that database and pull the data from the back end to do analysis on. I'm having the world's most difficult time connecting to it.

I need to access it two ways:

1. ODBC - I would like to setup an ODBC connection in Windows 7, so that I can pull the information using Excel. I know how to setup the connection, but I cannot for the life of me manage to setup the Oracle Drivers correctly. All the links from documentation for this piece of software go to dead/changed pages. Apparently the Oracle website has changed since this documentation was written.

2. Python - From what I can tell, cx_Oracle seems to be the best module, but again I can't seem to set it up correctly. And again, most documentation refers to sections of the Oracle website that has since changed. I'm not sold on using cx_Oracle if someone has a better recommendation.

Thanks for looking over this, and I appreciate your help.

Answer 1

Are you installing the 32 bit drivers or the 64 bit drivers on Windows 7?

I have to install the 32 bit drivers on all of our Windows 7 machines (even those running 64 bit).

After that I have to set up ODBC using: c:\windows\syswow64\odbcad32.exe instead of through the control panel.

Your issue may very well be different.

Answer 2

You can pull cx_Oracle from anaconda.org

I can verify that these packages have worked for me in the past:

• https://anaconda.org/trent/cx_oracle [linux-64]
• https://anaconda.org/trent/cx_oracle-11g [win]

Answer 3

If you are using cx_Oracle, you will need to have the oracle client installed and your database connections setup already such that you can use them with sqlplus via command line.