topic Re: feature class and SQL in Python Questions

feature class and SQL

JinMa — Wed, 21 Mar 2012 13:04:38 GMT

I need to use search cursor to iterate a feature class, get 'code' field from each row, use 'code' to query an oracle table, if returns multiple records associated with this code, then remove this code from the feature class, if returns single record associated with this code then keep. Any help or hint will be greatly appreciated!

Re: feature class and SQL

JakeSkinner — Sun, 12 Dec 2021 05:30:48 GMT

Hi Jin,

I was able to do this using the cx_Oracle module. Here is an example:

import arcpy, cx_Oracle
from arcpy import env
env.workspace = r"C:\TEMP\Python\Test.gdb"
env.overwriteOutput = 1

fc = "Hospitals"

list = []

# append all CODE values from feature class to list
rows = arcpy.SearchCursor(fc)
for row in rows:
    list.append(row.getValue("FAC_TYPE"))

# create a connection to Oracle instance    
connstr='vector/vector@orcl'
conn = cx_Oracle.connect(connstr)
curs = conn.cursor()

oracleList = []

# query Oracle table using items from list and append to new list
for item in list:
    curs.execute('select FAC_TYPE FROM Hospital_Info where FAC_TYPE = ' + str(item))
    for row in curs:
        oracleList.append(row[0])

# Find items that have duplicates and delete from feature class
for item in set(oracleList):
    if oracleList.count(item) > 1:
        arcpy.MakeFeatureLayer_management(fc, "Hospital_Lyr", "FAC_TYPE = " + str(item))
        arcpy.DeleteRows_management("Hospital_Lyr")
        print "Successfully deleted rows"
    
conn.close()

Re: feature class and SQL

JinMa — Fri, 23 Mar 2012 19:55:00 GMT

Hi, Jake
Thanks so much for your help. I have ArcGIS 9.3, does this code work?

Re: feature class and SQL

JakeSkinner — Mon, 26 Mar 2012 09:51:31 GMT

Hi Jin,

This is actually for ArcGIS 10. You will need to replace 'arcpy' with 'gp' to have this work with 9.3. Also, you will need to make a few changes to the 'SearchCursor'. Here is a link that discusses how to do this for 9.3.

Re: feature class and SQL

JinMa — Tue, 27 Mar 2012 04:15:55 GMT

Hi Jin,

This is actually for ArcGIS 10. You will need to replace 'arcpy' with 'gp' to have this work with 9.3. Also, you will need to make a few changes to the 'SearchCursor'. Here is a link that discusses how to do this for 9.3.

Hi, Jake,
Thanks again for your help.
Let me tell you more clear what I have and what I need to do:
I have a FGDB feature class derived from intersect_analysis, it contains almost million records, and it has many duplicated records associated with field 'code'. I need to compare records with oracle, if oracle returns only one record associated with 'code', then keep or append to new feature class.
I've tried hard, it's so slow, not success...I have Arc9.3. Thanks so much.