Oracle & Python

The module to access an Oracle database with Python is cx_Oracle. This works for all versions from 8i to 11g. To compile you need to have Oracle libraries, but luckily you do not need the full installation — the “instant client” with OCI is enough. (Thanks for pointing that out in this article.)
When using the compiled module make sure your ORACLE_HOME points to the instant client or your Oracle installation.

Here are a few tips if you run into cryptic ORA errors and this is the official Python page in the Oracle Wiki.

If you use a connection pool — do not trust it! Network problems or simple timeouts can happen silently and the connections the pool manages could be gone even though the acquire() works just fine. I learned that the hard way… always got an “ORA-03135 – Connection lost contact” after around 15 minutes idle. Interestingly each third retry worked again. My minimum pool size was two, so the third connection would always be a newly opened one.

My workaround is now a loop around a connect() method…

success = False
for i in range(ORACLE_POOL_MIN+1):
    success = connect()
    if success: break
if not success: return

… and the connect() method has a short query to test the connection, before returning a success.

conn = oracle.pool.acquire()
cursor = conn.cursor()
cursor.execute("SELECT * FROM dual")