Using domain coded values to define arcpy SQL query variable based on field.type

Question

I am attempting to export selected features from many feature classes within a series of geodatabases into new ones - maintaining schema and all that. The trouble is that each geodatabase has different "ownership" field types. Some are coded value integers, while the rest are strings. Of those that are coded values, the values aren't even consistent!

I am trying to create a script that will reliably search for the coded value descriptions, then apply the corresponding value to the SQL query in the FeatureClasstoFeatureClass_conversion function. It works on some feature classes, but I am getting an invalid SQL query error on others.

env.workspace = item
for ds in arcpy.ListDatasets():
 listfc1 = arcpy.ListFeatureClasses(feature_dataset=ds)
 print listfc1
 for fc in listfc1:
 fcname =os.path.split(fc)[1]
 ownfieldlist=[]
 for field in arcpy.ListFields(fc,"*owne*"):
 if field.name != "DOWNELEV":
 ownfieldlist.append(field)
 else:
 pass
 if len(ownfieldlist) == 0:
 naughtylist.append(fc)
 try:
 ownfield = ownfieldlist[0]
 print ownfield.name + " {}".format(ownfield.type)
 except:
 print (fcname+" has no ownership field")
 if fc not in naughtylist:
 if ownfield.type == "String":
 arcpy.FeatureClassToFeatureClass_conversion(fc, os.path.join(output,itemname), fc,
 """{} = 'USU Eastern'""".format(arcpy.AddFieldDelimiters(fc,ownfield.name)) )
 elif ownfield.type == "SmallInteger":
 number = getthenumber()
 arcpy.FeatureClassToFeatureClass_conversion(fc, os.path.join(output,itemname), fc,"""{} = {}""".format(arcpy.AddFieldDelimiters(fc,ownfield.name), number) )
 else:
 pass

The script above is just adapted to work within a single GDB, but normally, it refers to a function that creates a list of databases and iterates over those.

The getnumber call refers to another function, which passes the coded value which matches the ownership description I want. Like I said, it works for the first geodatabase, but not the second.

Here is the getnumber function:

def getthenumber(item):
 import arcpy
 from arcpy import env
 import os
 from arcpy import da
 item = r"D:\Eastern02\Electric.gdb"
 env.workspace = item
 for domain in arcpy.da.ListDomains(item):
 print domain
 if domain.name == "AssetOwner":
 coded_values = domain.codedValues
 answer = "USU Eastern"
 for val, desc in coded_values.items():
 if desc == answer:
 thenumber = int(val)
 print ("{} is represented by {}".format(desc,val))
 return thenumber
 else:
 pass

• Kevin: Whoa, I found at least the first problem: the Domain names for "AssetOwner" aren't consistent. Some have spaces and some don't. I added a startswith wildcard to solve this. I will update this if I can get the whole script-bundle to iterate over these GDBs

Answer 1

I figured it out. It was actually a 'problem' with the underlying data domain names. Garbage in, garbage out. I built-in a pretty robust solution to the problem by adding wildcard: startswith because all the asset owner fields begin with the word, "asset."

def getthenumber(item):
 import arcpy
 from arcpy import env
 import os
 from arcpy import da
 env.workspace = item
 for domain in arcpy.da.ListDomains(item):
 if domain.name.startswith(("Asset")):
 print domain.name
 coded_values = domain.codedValues
 answer = "USU Eastern"
 for val, desc in coded_values.items():
 if desc == answer:
 thenumber = int(val)
 print ("{} is represented by {}".format(desc,val))
 return thenumber
 else:
 pass