Python Code CAN Connect to an Oracle Database.
Despite the beaming praise for the simplicity of the cx_Oracle project on their SourceForge page, I had trouble using the Python module to create a connection to an Oracle database. It turns out that the module is quite nice once you get past a couple of problems that have nothing to do with the cx_Oracle module. In my case, my computer’s environment and some confusing information on the Internet were the cause of my troubles. So here I have organized some hints to help you if you are unfortunate and cannot immediately make a successful connection to your Oracle database using cx_Oracle.
Install the Correct Version of cx_Oracle
This was my biggest problem, though I didn’t realize it for a while. The cx_Oracle project has separate binary distributions for both OS and Oracle version, which support Windows and CentOS for Oracle 10.2, 11.1 and 11.2. On Windows for sure, installing the wrong binary will result in a broken installation. However, it’s not necessarily as simple as knowing which version your Oracle database is at.
Here’s what happened in my case. I have an Oracle ODBC driver installed on my Windows XP installation (OraClient10g) but I want to connect to an 11g database. I first assumed that I needed to install the 11g version of cx_Oracle and found that assumption to be wrong. I think because of the ODBC driver version, I had to install the 10g version of cx_Oracle. I simply could not connect to my database otherwise. I don’t have a way to confirm this ODBC complication because I don’t have authority to install different versions of drivers but it makes sense to me.
The indication I got that my connection was not working was the following error from my Python program:
cx_Oracle.DatabaseError: ORA-24315: illegal attribute type
This is a vague error that, I suppose, has something to do with the mismatch between the cx_Oracle code and the ODBC driver. Once I installed the cx_Oracle version that matched my ODBC driver version I was able to successfully connect.
When you connect to an Oracle database using the connect method you have several ways to specify important parameters such as user, password and SID (refered to as Data Source Name, DSN, in cx_Oracle). The easiest way to connect is like this:
# Connect using the ordered parameters user, password and SID. dbconn = cx_Oracle.connect('user', 'password' ,'SID')
You can also be more explicit by naming the parameters like this:
# Connect using named parameters. dbconn = cx_Oracle.Connection(user='user',password='password',dsn='SID')
I suggest just using one of the above methods as is used in the sample code that comes with the cx_Oracle module. There is another method though that utilizes the Oracle Easy Connect string. This string is purported by Oracle to be convenient but is not—especially if you have limited authority on the database. The Easy Connect string requires, instead of the easy to obtain and commonly known SID, that you know the service name for a database. Assuming you have authority to do so, you can execute the command below against your database to obtain the service name:
select sys_context('userenv', 'service_name') from dual;
Once you have the service name, you can connect to the database like so:
# Connect using Oracle's Easy Connect connection string. dbconn = cx_Oracle.connect(u'user/password@db-server:1521/service.name')
The cx_Oracle module is a nice library to have around when you’re working with Oracle from Python code. You’ll probably not have the problems I did if you read the cx_Oracle documentation carefully and understand your OS environment properly. However, if you do have some problems, I hope this article helped solve them.
One thing to note is that when you install cx_Oracle it does install documentation. I think it’s in an odd place, but maybe this is common for Python modules. The documentation will be in <python-install-dir>/cx_Oracle-doc. This directory contains documentation, test cases and sample Python code.
Links and References
Here are links to materials that I’ve referenced and other useful links.
cx_Oracle project page – http://sourceforge.net/projects/cx-oracle/
cx_Oracle download page – http://cx-oracle.sourceforge.net/
Oracle whitepaper describing the EasyConnect string – http://www.oracle.com/technetwork/database/enterprise-edition/oraclenetservices-neteasyconnect-133058.pdf
Oracle tutorial on connecting to a database with cx_Oracle – http://www.oracle.com/technetwork/articles/dsl/python-091105.html
Some StackOverflow discussions on this topic that helped me: