Monday, May 24, 2010

Using Python to access ArcSDE data faster with cx_Oracle


Using Python and ArcObjects to access data can be painfully slow, especially with large amounts of data.  However, there is another method for accessing data from Oracle that will allow your scripts to run faster.  The Python extension module, cx_Oracle, allows you to access an Oracle database without using ArcObjects.  Using cx_Orcale you can quickly query and modify attributes for tables and feature classes in ArcSDE.

NOTE:  Do NOT attempt to modify (INSERT, UPDATE, DELETE, etc) a table or feature class that is registered as versioned.  The results are unpredictable at best and can result in data loss.  However, you can safely SELECT any table or feature class.

To start you’ll need to download the cx_Oracle module here.  Once you have it installed and are ready to write some code, begin by importing the cx_Oracle module:

import cx_Oracle

Next we need to connect to the Oracle database.  There are several ways to do this.  For a complete reference, see the cx_Oracle documentation.  In this example we first create a DSN:

dsn = cx_Oracle.makedsn("gisdb01.devfaction.com", 
      "1521", "gisdb")

In this example, gisdb01.devfaction.com is the host, 1521 is the port, and gisdb is the name of the database.  Once the DSN is created, we connect to the database using the DSN:

orcl = cx_Oracle.connect('', '', dsn)

Now that we are connected to the database, we need to create a cursor:

cursor = orcl.cursor()

The next step is to create a SQL statement to execute.  This statement can be any SQL syntax that Oracle supports.  We will start off with a simple SELECT statement:

sql = "SELECT id, name FROM DB.ROADS WHERE id < 10"

This statement will return two fields from the ROADS table where the ID is less than 10.  Now it’s time to execute the statement:

cursor.execute(sql)

Now that we have run a query, we need to use the data that was returned.  To this we can loop through each row in the cursor:

for row in cursor:
     print row[0] + ", " + row[1]

The results should look something like this:

1, Walnut
2, Oak
3, Spruce

When you are finished remember to delete the cursor and close your connection:

del cursor
orcl.close()

You can also delete and modify data using cx_Oracle.  It may be possible to INSERT new records but it is not recommended for ArcSDE objects.  Follow the same steps to create the connection and cursor then create a SQL statement and execute it.:

sql = "UPDATE DB.ROADS SET name = '1st' " +\
      "WHERE id = 3"
cursor.execute(sql)

You will need to commit your changes before closing the connection:

orcl.commit()

In addition to creating scripts that run faster, cx_Oracle can help users who are more familiar with writing SQL than using ArcObjects.  But I want to reiterate one more time that modifications should not be performed on feature classes or tables that are registered as versioned.

cx_Oracle consists of many more useful functions.  I encourage you to read through the documentation and see what else exists.

2 comments: