topic Re: How to get the field values using arcpy.da.SearchCursor() using where clause in Python Questions

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

kumarprince8071 — Wed, 17 May 2023 10:11:07 GMT

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]

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

RPGIS — Wed, 17 May 2023 14:53:42 GMT

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

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

kumarprince8071 — Fri, 19 May 2023 09:33:13 GMT

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

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

RPGIS — Fri, 19 May 2023 11:19:02 GMT

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.