Tuesday, May 1, 2012

Listing Oracle Columns Using Python

I've started coding a set of helpful functions for oracle and python using the cx_oracle library for python.  Just download the library and install the one associated with your python install.

In this example, I will list a set of columns in a table using python. First we need to connect to the oracle database
import cx_Oracle
username = "user" 
password = "*****" 
ip = "" 
port = 1521 
table = 'mytablename' 
dsn_tns = cx_Oracle.makedsn(ip, port, SID) 
db = cx_Oracle.connect(user,passwword, dsn_tns)

All I did was create the entry like it would appear in the tnsname.ora file then pass that information along with the username and password to the database.
Great, now we are connected to the oracle database.  Now the database can be queried.

cursor = db.cursor()
results = cursor.execute("select * from %s where 1=0" % table)
print results.description

[('OBJECTID', <type 'cx_oracle.number'="">, 39, 22, 38, 0, 0), ('NEWNAME', <type 'cx_oracle.unicode'="">, 50, 100, 0, 0, 1)]

Now you do not have to use arcpy to list the fields in a table.  Some other ideas you could do with your enterprise database is: rename fields, create views, etc...