Python Tip: Using ArcSDESQLExecute to access ArcSDE data faster

In a previous post titled “Using Python to access ArcSDE data faster with cx_Oracle” I described a Python module that allowed faster access to ArcSDE data stored in an Oracle database.  Another method exists that will allow you to access ArcSDE data with pure SQL queries using the ArcScripting geoprocessing libraries.  We can accomplish this by creating the ArcSDESQLExecute object.

To help demonstrate the capabilities of this function, we will create an example that queries the ArcSDE database and then uses some Python functionality to generate a CSV file from the results.  Start by importimg the necessary libraries and creating a geoprocessor object:

import arcgisscripting, os

gp = arcgisscripting.create(9.3)

Next create the ArcSDESQLExecute object.  The second parameter is the path to the SDE connection file:

sdeConn = gp.CreateObject("ARCSDESQLEXECUTE", "Database Connections\\GIS.sde")

Now let’s create an SQL statement and execute it.  Note:  This function will execute any valid SQL statement that you have permission to perform, including Updates and Deletes.  This is an excellent way of performing fast attribution changes.  However, use caution when modifying data:

sql = "SELECT OBJECTID, ROADNAME FROM ROADSEGMENT"

sdeReturn = sdeConn.Execute(sql)

The next section of code will generate a CSV file so that the output can easily be opened in Excel or Access.  You can modify it to any type of TXT file you wish.

file = open("output.csv", 'w')

for row in sdeReturn:

    line = ""

    for i in row:

        line += str(i) + ","

    file.write(line + "\n")

Finally, close the file and delete the ArcSDESQLExecute object:

file.close()

del sdeReturn

You will see that this function performs SQL queries much faster than the other geoprocessing tools because it uses the RDBMS to execute them.  ArcSDESQLExecute can even rollback transactions.  For more information see the ESRI documentation.