Connecting to Oracle Spatial using OGR and Python

Let there be no mistake: Oracle sucks. It's a bloated hog that wades slowly through the corrupt data morasses of corpocracy. But sometimes, as an aloof mercenary to the corporate beast in the field of GIS, you have to swallow your contempt and do that for which you are paid: solve problems. Think of yourself as Mr Wolf: composed, BS-repellent and efficient, with an unexpected inclination for the finer things in life (after the job's done).

I installed the latest mainline OSGeo4W with all the libs and command-line tools (well, at a minimum, a custom install with with python, gdal and oracle drivers. The oracle drivers (OCI) are not installed by default). Version of python is 2.5 and GDAL 1.8. Dropped a cx_Oracle library in the install folder in order to perform some classical database operations on the Oracle server (10G R2). Then I started scripting.

The OGR OCI driver has several issues, the two main of which: its connection string syntax is a mess and it doesn't support schema's. If I were an altruistic guy I'd delve into the code and arrange this for my fellow coders. But I'm a profiteering bastard in a hurry, lacking time and (most of all) a decent development enviroment (I'm a UNIX man stuck in a Windows suit). So I need quick workarounds.

A connection string that works for OGR (Python 2.5):

 ds_str = "OCI:%s/%s@localhost/database:%s" % (user,passwd,tablename)  #getting directly the table

A connection string that works for cx_Oracle (5.1 with Python 2.5):

 ds_str = "%s/%s@localhost/database" % (user,passwd)  

Intrerestingly, using cx_Oracle (5.1.1) in Python 2.7 you don't need to speciy the host, just doing this:

 ds_str = "%s/%s@database" % (user,passwd)  

Another (minor) caveat: the Python driver won't accept that you specify, on connection, a username and password that are identical. If you're working, like myself, in a environment where this can occur, give the account temporarily a different password (no quotes for either user name or password).

 alter user user_name identified by new_password;  

After this, you can finally start talking to your server and manipulating your data and geometries in a decent way - using python and OGR.