Select to view content in your preferred language

How to get the field values using arcpy.da.SearchCursor() using where clause

860
3
05-17-2023 03:11 AM
kumarprince8071
New Contributor III

I'm trying to get the Assetgroup field values where assetgroup field values is equal to High Voltage Transformer. I am using this set  of code

assetgroup = {}
assettype = {}
if searchfield == True:
for key in subtypename:
where_clause = "ASSETGROUP = 'High Voltage Transformer' = "+ str(key)
with arcpy.da.SearchCursor(fc,field_name,where_clause) as cursor:
for row in cursor:
if subtypename[row[0]] in assetgroup:
assetgroup[subtypename[row[0]]] +=1
else:
assetgroup[subtypename[row[0]]] = 1
if codedvalue[row[1]] in assettype:
assettype[codedvalue[row[1]]] += 1
else:
assettype[codedvalue[row[1]]] = 1
But getting error . Not able to figure out where am i doing wrong
Traceback (most recent call last):
File "D:\ARCPY\Transformer_Customer_Count_\transformer.py", line 41, in <module>
for row in cursor:
RuntimeError: An invalid SQL statement was used. [SELECT OBJECTID,ASSETGROUP,ASSETTYPE FROM ElectricDevice WHERE ASSETGROUP = 'High Voltage Transformer' = 0]


Tags (3)
0 Kudos
3 Replies
RPGIS
by
Regular Contributor

Hi @kumarprince8071,

Typically using where clauses in any cursor can be a bit of a hassle to deal with. It is better to use the search cursor without the where clause and specify all requirements for each row.

Another unique and simple cursor trick is to create a dictionary of fields and row values for each row, which in turn makes it easier to specify the fields and values exactly.

Also, two things:

    First: the search cursor fieldname/fieldnames need to be in brackets unless those are fieldnames a list of fieldnames

    Second: I am not following what the keys are in the subtype name. I assume those are either coded values with descriptions or something else. In either case you can modify the example to use however you need it.

 

assetgroup = {}
assettype = {}
Fieldname_s = ['ASSETGROUP', 'x', 'y']
if searchfield == True:
    with arcpy.da.SearchCursor(fc,Fieldname_s) as cursor:
        for row in cursor:
            FieldValueDict = dict(zip(Fieldname_s, row))
            AssetGroup = FieldValueDict['ASSETGROUP']
            FieldA =  'x'
            FieldB = 'y'
            
            if AssetGroup == 'High Voltage Transformer':
                if all([ FieldValueDict[FieldA] in subtypename, subtypename[FieldValueDict[FieldA]] in assetgroup ]):
                    assetgroup[subtypename[FieldValueDict[FieldA]]] +=1
                else: assetgroup[subtypename[FieldValueDict[FieldA]]] =1

                if all([ FieldValueDict[FieldB] in codedvalue, codedvalue[FieldValueDict[FieldB]] in assettype ]):
                    assettype[codedvalue[FieldValueDict[FieldB]]] +=1
                else: assetgroup[codedvalue[FieldValueDict[FieldB]]] =1

 

0 Kudos
kumarprince8071
New Contributor III

This is the entire code block but in,  where clause i am not able to get it how will i get the ASSETTYPE field  values where ASSETTYPE is equal to "High Voltage Transformer " . And field values are domain coded description values. means the code for High Voltage Transformer in domain is 42. 



import
arcpy,openpyxl,os
from openpyxl import Workbook
wb =Workbook()
ws = wb.active
ws.cell(row=1, column =1,value = "Feature Dataset")
ws.cell(row=1, column=2, value="Feature Class")
ws.cell(row=1, column=3, value="Feature Count")
ws.cell(row=1, column=4, value="Asset Group")
ws.cell(row=1, column=5, value="Asset Group Count")
ws.cell(row=1, column=6, value="Asset Type")
ws.cell(row=1, column=7, value=" Asset Type Count")
row_num = 2
outputfile = "output8.xlsx"
gdb = 'gdb'
arcpy.env.workspace = gdb
fds = arcpy.ListDatasets("*", "Feature")
for fd in fds:
arcpy.env.workspace = gdb + '\\' + fd
for fc in arcpy.ListFeatureClasses("","ALL"):
counts = arcpy.GetCount_management(fc).getOutput(0)
ws.cell(row=row_num, column=1, value=fd)
ws.cell(row=row_num, column=2, value=fc)
ws.cell(row=row_num, column=3, value=counts)
row_num+=1
all_codedvalue = {}
all_assetgrp = {}
field_name = ["ASSETGROUP", "ASSETTYPE"]
Fields = arcpy.ListFields(fc)
subdict = arcpy.da.ListSubtypes(fc)
searchfield = False
if len(subdict) > 1:
for stcode in subdict:
if subdict[stcode]['SubtypeField'] != "":
all_assetgrp[stcode] = subdict[stcode]['Name']
for i in Fields:
if i.name == 'ASSETGROUP':
subtypename = {}
subtypename[stcode] = subdict[stcode]['Name']
codedvalue = {}
if subdict[stcode]['FieldValues']['ASSETTYPE'][1] != None:
for c in subdict[stcode]['FieldValues']['ASSETTYPE'][1].codedValues:
codedvalue[c] = subdict[stcode]['FieldValues']['ASSETTYPE'][1].codedValues[c]
FieldsNames = []
for fields in Fields:
FieldsNames.append(fields.name)
for field_n in field_name:
if field_n in FieldsNames:
searchfield = True
else:
searchfield = False
assetgroup = {}
assettype = {}
if searchfield == True:
for key in subtypename:
whee_clase = "ASSETGROUP = " + str(key)
# whee_clase = "ASSETGROUP = '38'"
with arcpy.da.SearchCursor(fc,field_name,whee_clase) as cursor:
for row in cursor:
if subtypename[row[0]] in assetgroup:
assetgroup[subtypename[row[0]]] +=1
else:
assetgroup[subtypename[row[0]]] = 1
if codedvalue[row[1]] in assettype:
assettype[codedvalue[row[1]]] += 1
else:
assettype[codedvalue[row[1]]] = 1
for group, type_counts in assetgroup.items():
ws.cell(row=row_num-1, column=4, value=group)
ws.cell(row=row_num-1, column=5, value=type_counts)
for asset, count in assettype.items():
ws.cell(row=row_num-1, column=6, value=asset)
ws.cell(row=row_num-1, column=7, value=count)
row_num += 1

 

0 Kudos
RPGIS
by
Regular Contributor

Have you tried using print statements to see what values are being returned? If not, then I would highly recommend doing so since it will tell you what values are actually returning. Otherwise it might be that there are errors in the data which would cause the sql clause to not work.

Like I had mentioned before, it usually works best if you avoid sql clauses altogether since even a single character could cause the sql clause to either not return anything or not execute at all.