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

Question

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_	ransformer.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]

RPGIS · Answer

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.

kumarprince8071 · Answer

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

RPGIS · Answer

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