Getting Error in my where clause using cursor in arcpy.

broy06 · ‎08-08-2021

I'm currently using Arcmap 10.7 version. I'm guessing my error is in the where clause. Could anyone please help me to get rid from this problem?. I'm not able to print the cursor part i.e.,

Print (MEASUREDLENGTH_Risk_Score) # Not getting the value of MEASUREDLENGTH

Below is my code-:

import arcpy
arcpy.env.workspace = "D:\pipe\map.gdb"
featureClass_1 = "P_Pipes"
Table_Pipe = "Pipe_Risk"
fieldNames_1 = [ "Material" , "OPERATINGPRESSURE" , "MEASUREDLENGTH" , "INSTALLATIONDATE", "Risk_Score"]
fieldNames_Pipe = ["Material" , "OPERATINGPRESSURE" , "MEASUREDLENGTHLOWERLIMIT" ,"MEASUREDLENGTHUPPERLIMIT" , "INSTALLATIONDATELOWERLIMIT" , "INSTALLATIONDATEUPPERLIMIT" , "OVERALLRISKSCORE"]


def Pipe_Risk_Score(Material, OPERATINGPRESSURE, MEASUREDLENGTH,  INSTALLATIONDATE):

    ML_Out_fields = ["MEASUREDLENGTHLOWERLIMIT" ,"MEASUREDLENGTHUPPERLIMIT" , "OVERALLRISKSCORE"]
    ML_where_Clause = "MEASUREDLENGTHLOWERLIMIT <= " + MEASUREDLENGTH + " and MEASUREDLENGTHUPPERLIMIT >= " + MEASUREDLENGTH 
    print(ML_where_Clause)
    
    Table_Pipe_Cursor = arcpy.da.SearchCursor(Table_Pipe, ML_Out_fields, ML_where_Clause)
    for row in Table_Pipe_Cursor:
        lst.append(row[2])
        MEASUREDLENGTH_Risk_Score = row[2]
        print ((MEASUREDLENGTH_Risk_Score))

Pipe_Risk_Score("Material" , "OPERATINGPRESSURE" , "MEASUREDLENGTH" ,  "INSTALLATIONDATE")

broy06 · ‎08-11-2021

Hello sir, I tried using the same, but getting the same error in same line. Actually in my lookup table there are 2 fields for Measured Length & 2 fields for Installation Date because i have assigned the risk score in the range. But there is no "MEASUREDLENGTH" field & "INSTALLATIONDATE" field name (It's in the feature class attributes). I have "MEASUREDLENGTHLOWERLIMIT" & "MEASUREDLENGTHUPPERLIMIT" , "INSTALLATIONDATELOWERLIMIT" & "INSTALLATIONDATEUPPERLIMIT". I'm attaching the Lookup table & Feature Class attributes.

Screenshot-99 (Lookup table) & Screenshot-109 (Feature Class Attributes)

Please have a look for a minute. Thankyou for the continuous effort. Please lemme get rid from this problem.

Thanks & Regards

Prabal

Anonymous User · ‎08-11-2021

Edited: Looking at the console log image that you shared, you still have the parameters wrapped in quotes. Are you trying the complete code I posted earlier?

broy06 · ‎08-11-2021

No sir. I just want that how i can rectify my where clause so that it can give me exact value.

Below code is giving me error. I stuck on the same

ML_where_Clause = "MEASUREDLENGTHLOWERLIMIT <= {0} and MEASUREDLENGTHUPPERLIMIT >= {0}".format(MEASUREDLENGTH)

Anonymous User · ‎08-11-2021

Post the full code that you are running again.

broy06 · ‎08-11-2021

Sure Sir @Anonymous User . But please for once also look at the Lookup table & Feature Class attributes.

Below is the code:

import arcpy
arcpy.env.workspace = r"D:\Maps and GDBs\NapervilleGas.gdb"
featureClass_1 = "P_Pipes"
Table_Pipe = "Pipe_Risk"
fieldNames_1 = [ "Material" , "OPERATINGPRESSURE" , "MEASUREDLENGTH" , "INSTALLATIONDATE", "Risk_Score"]
fieldNames = ["Material" , "OPERATINGPRESSURE" , "MEASUREDLENGTHLOWERLIMIT" ,"MEASUREDLENGTHUPPERLIMIT" , "INSTALLATIONDATELOWERLIMIT" , "INSTALLATIONDATEUPPERLIMIT" , "OVERALLRISKSCORE"]
lst=[]
def Pipe_Risk_Score(Material, OPERATINGPRESSURE, MEASUREDLENGTH, INSTALLATIONDATE):
lst = []
# Case: Material
Material_where_Clause = "Material = " + Material
with arcpy.da.SearchCursor(Table_Pipe, ["Material", "OVERALLRISKSCORE"], Material_where_Clause) as sCur:
for row in sCur:
lst.append(row[1])
Material_Risk_Score = row[1]
print Material_Risk_Score
# Case: Operating Pressure
OP_Out_fields = ["OPERATINGPRESSURE", "OVERALLRISKSCORE"]
OP_where_Clause = "OPERATINGPRESSURE = " + OPERATINGPRESSURE
with arcpy.da.SearchCursor(Table_Pipe, OP_Out_fields, OP_where_Clause) as sCur:
for row in sCur:
lst.append(row[1])
OPERATINGPRESSURE_Risk_Score = row[1]
print OPERATINGPRESSURE_Risk_Score
# Case: ML
ML_Out_fields = ["MEASUREDLENGTHLOWERLIMIT", "MEASUREDLENGTHUPPERLIMIT", "OVERALLRISKSCORE"]
#ML_where_Clause = "MEASUREDLENGTHLOWERLIMIT <= " + MEASUREDLENGTH + " and MEASUREDLENGTHUPPERLIMIT >= " + MEASUREDLENGTH
ML_where_Clause = "MEASUREDLENGTHLOWERLIMIT <= {0} and MEASUREDLENGTHUPPERLIMIT >= {0}".format(MEASUREDLENGTH)
print ML_where_Clause
with arcpy.da.SearchCursor(Table_Pipe, ML_Out_fields, ML_where_Clause) as sCur:
for row in sCur:
lst.append(row[2])
MEASUREDLENGTH_Risk_Score = row[2]
print MEASUREDLENGTH_Risk_Score
# Case: ID
ID_Out_fields = ["INSTALLATIONDATELOWERLIMIT", "INSTALLATIONDATEUPPERLIMIT", "OVERALLRISKSCORE"]
ID_where_Clause = "INSTALLATIONDATELOWERLIMIT <= {0} and INSTALLATIONDATEUPPERLIMIT >= {0}".format(INSTALLATIONDATE)
print ID_where_Clause
with arcpy.da.SearchCursor(Table_Pipe , ID_Out_fields, ID_where_Clause) as sCur:
for row in sCur:
lst.append(row[2])
ID_Risk_Score = row[2]
print ID_Risk_Score
Pipe_Risk_Score = (Material_Risk_Score + OPERATINGPRESSURE_Risk_Score + MEASUREDLENGTH_Risk_Score + ID_Risk_Score)/4
return Pipe_Risk_Score

Pipe_Risk_Score("Material", "OPERATINGPRESSURE", "MEASUREDLENGTH", "INSTALLATIONDATE")

Anonymous User · ‎08-11-2021

OK, I've solved your problem in the previous posts and it looks like you are not trying what I provided here in its entirety.

import arcpy

arcpy.env.workspace = "D:\Pipe\map.gdb"
featureClass_1 = "P_Pipes"
Table_Pipe = "Pipe_Risk"
fieldNames_1 = ["Material", "OPERATINGPRESSURE", "MEASUREDLENGTH", "INSTALLATIONDATE", "Risk_Score"]
fieldNames = ["Material", "OPERATINGPRESSURE", "MEASUREDLENGTHLOWERLIMIT", "MEASUREDLENGTHUPPERLIMIT",
              "INSTALLATIONDATELOWERLIMIT", "INSTALLATIONDATEUPPERLIMIT", "OVERALLRISKSCORE"]


def Pipe_Risk_Score(Material, OPERATINGPRESSURE, MEASUREDLENGTH, INSTALLATIONDATE):
    lst = []
    # Case: Material
    Material_where_Clause = "Material = " + Material
    with arcpy.da.SearchCursor(Table_Pipe, ["Material", "OVERALLRISKSCORE"], Material_where_Clause) as sCur:
        for row in sCur:
            lst.append(row[1])
            Material_Risk_Score = row[1]
            print Material_Risk_Score

    # Case: Operating Pressure
    OP_Out_fields = ["OPERATINGPRESSURE", "OVERALLRISKSCORE"]
    OP_where_Clause = "OPERATINGPRESSURE = " + OPERATINGPRESSURE
    with arcpy.da.SearchCursor(Table_Pipe, OP_Out_fields, OP_where_Clause) as sCur:
        for row in sCur:
            lst.append(row[1])
            OPERATINGPRESSURE_Risk_Score = row[1]
            print OPERATINGPRESSURE_Risk_Score

    # Case: ML
    ML_Out_fields = ["MEASUREDLENGTHLOWERLIMIT", "MEASUREDLENGTHUPPERLIMIT", "OVERALLRISKSCORE"]
    ML_where_Clause = "MEASUREDLENGTHLOWERLIMIT <= " + MEASUREDLENGTH + " and MEASUREDLENGTHUPPERLIMIT >= " + MEASUREDLENGTH
    print ML_where_Clause
    with arcpy.da.SearchCursor(Table_Pipe, ML_Out_fields, ML_where_Clause) as sCur:
        for row in sCur:
            lst.append(row[2])
            MEASUREDLENGTH_Risk_Score = row[2]
            print MEASUREDLENGTH_Risk_Score

    # Case: ID
    ID_Out_fields = ["INSTALLATIONDATELOWERLIMIT", "INSTALLATIONDATEUPPERLIMIT", "OVERALLRISKSCORE"]
    ID_where_Clause = "INSTALLATIONDATELOWERLIMIT <= {0} and INSTALLATIONDATEUPPERLIMIT >= {0}".format(INSTALLATIONDATE)
    with arcpy.da.SearchCursor(Table_Pipe, ID_Out_fields, ID_where_Clause) as sCur:
        for row in sCur:
            lst.append(row[2])
            ID_Risk_Score = row[2]
            print ID_Risk_Score

    return sum(lst) / len(lst)


with arcpy.da.UpdateCursor(PipeFCName, ["Material", "OPERATINGPRESSURE", "MEASUREDLENGTH", "INSTALLATIONDATE", "Risk_Score"]) as uCur:
    for row in uCur:
        Material = row[0]
        OPERATINGPRESSURE = row[1]
        MEASUREDLENGTH = row[2]
        INSTALLATIONDATE = row[3]
        print "Material: {}\t OPERATINGPRESSURE: {}\t MEASUREDLENGTH: {}\t  INSTALLATIONDATE: {}".format(Material, OPERATINGPRESSURE, MEASUREDLENGTH, INSTALLATIONDATE)
        row[4] = Pipe_Risk_Score(Material, OPERATINGPRESSURE, MEASUREDLENGTH, INSTALLATIONDATE)
        uCur.UpdateRow(row)

broy06 · ‎08-11-2021

Yes sir, I agreed with your point but while running a where clause for MEASUREDLENGTH & INSTALLATIONDATE, It's giving me error

C:\Users\my>D:\Arcpy\Material.py
0.3
0.9
1.5
1.8
2.4
3.0
0.4
0.4
1.2
1.2
1.6
2.4
4.0
MEASUREDLENGTHLOWERLIMIT <= MEASUREDLENGTH and MEASUREDLENGTHUPPERLIMIT >= MEASUREDLENGTH
Traceback (most recent call last):
File "D:\Arcpy\Material.py", line 55, in <module>
Pipe_Risk_Score("Material", "OPERATINGPRESSURE", "MEASUREDLENGTH", "INSTALLATIONDATE")
File "D:\Arcpy\Material.py", line 36, in Pipe_Risk_Score
for row in sCur:
RuntimeError: An expected Field was not found or could not be retrieved properly. [Pipe_Risk]

It means upto the where clause of "MEASUREDLENGTH", the program or code runs.

Anonymous User · ‎08-11-2021

You are not running the code I just provided. You would see something like:

Material: 0.3 OPERATINGPRESSURE: 2 MEASUREDLENGTH: 4.5 INSTALLATIONDATE: 01/02/1935

being printed in the console if you were.

broy06 · ‎08-11-2021

@Anonymous User Sir, I didn't get your point. What you wanna tell me?

Yes, I want the same thing as you mentioned above but also in the case of INSTALLATIONDATE I will get some float value like INSTALLATIONDATE: 1.2 if my code runs

Anonymous User · ‎08-11-2021

I'm telling you that you are not running the code that I provided for you. You are still trying to run your code, which has problems. Replace all of your code with the code I provided and run it. Run my code and attach the console output.