Getting Error in my where clause using cursor in arcpy.

[broy06]

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]

Sir @Anonymous User , I have run your code, it's giving me error while updating & the error name is given below & also see in the console output in a screenshot form.

C:\Users\my>D:\Arcpy\Material.py
Traceback (most recent call last):
File "D:\Arcpy\Material.py", line 54, in <module>
for row in uCur:
RuntimeError: Objects in this class cannot be updated outside an edit session [P_Pipes]

[Anonymous User]

Start an edit session. This will change the Risk_Score value in the featureclass.  Check the documentation for how to set it if you get errors.

https://desktop.arcgis.com/en/arcmap/10.3/analyze/arcpy-data-access/editor.htm

edit = arcpy.da.Editor(arcpy.env.workspace)
edit.startEditing(False, True)
edit.startOperation()

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]

        row[4] = Pipe_Risk_Score(Material, OPERATINGPRESSURE, MEASUREDLENGTH, INSTALLATIONDATE)
        uCur.UpdateRow(row)

# Stop editing
edit.stopOperation()
edit.stopEditing(True)

[broy06]

Oh i forgot to edit my workspace. At last line, I'm getting error.

C:\Users\my>D:\Arcpy\Material.py
Material: 3 OPERATINGPRESSURE: 125.0 MEASUREDLENGTH: 3737.85025923 INSTALLATIONDATE: 1958-07-08 00:00:00
Traceback (most recent call last):
File "D:\Arcpy\Material.py", line 64, in <module>
row[4] = Pipe_Risk_Score(Material, OPERATINGPRESSURE, MEASUREDLENGTH, INSTALLATIONDATE)
File "D:\Arcpy\Material.py", line 13, in Pipe_Risk_Score
Material_where_Clause = "Material = " + Material
TypeError: cannot concatenate 'str' and 'int' objects

[Anonymous User]

Use these

Material_where_clause = "Material =  {}".format(Material)

OP_where_Clause = "OPERATINGPRESSURE = {}".format(OPERATINGPRESSURE)

[broy06]

Sir @Anonymous User . Thanks in advance, just only tell me one last error which I'm getting in "INSTALLATIONDATE"

C:\Users\my>D:\Arcpy\Material.py
Material: 3 OPERATINGPRESSURE: 125.0 MEASUREDLENGTH: 3737.85025923 INSTALLATIONDATE: 1958-07-08 00:00:00
0.3
1.2
MEASUREDLENGTHUPPERLIMIT >= 3737.85025923
1.0
INSTALLATIONDATEUPPERLIMIT >= 1958-07-08 00:00:00
Traceback (most recent call last):
File "D:\Arcpy\Material.py", line 66, in <module>
row[4] = Pipe_Risk_Score(Material, OPERATINGPRESSURE, MEASUREDLENGTH, INSTALLATIONDATE)
File "D:\Arcpy\Material.py", line 47, in Pipe_Risk_Score
for row in sCur:
RuntimeError: An invalid SQL statement was used. [SELECT INSTALLATIONDATELOWERLIMIT, INSTALLATIONDATEUPPERLIMIT, OVERALLRISKSCORE, OBJECTID FROM Pipe_Risk WHERE INSTALLATIONDATEUPPERLIMIT >= 1958-07-08 00:00:00]

[Anonymous User]

Try this with quotes.

ID_where_Clause = "INSTALLATIONDATELOWERLIMIT <= '{0}' and INSTALLATIONDATEUPPERLIMIT >= '{0}'".format(INSTALLATIONDATE)

[broy06]

@Anonymous User Sir, Getting the error with the different name in the where clause. ID is having 'date' data type but I only want the value not the date. So what should I write?

C:\Users\my>D:\Arcpy\Material.py
Material: 3 OPERATINGPRESSURE: 125.0 MEASUREDLENGTH: 3737.85025923 INSTALLATIONDATE: 1958-07-08 00:00:00
0.3
1.2
MEASUREDLENGTHUPPERLIMIT >= 3737.85025923
1.0
INSTALLATIONDATEUPPERLIMIT >= '1958-07-08 00:00:00'
Traceback (most recent call last):
File "D:\Arcpy\Material.py", line 67, in <module>
row[4] = Pipe_Risk_Score(Material, OPERATINGPRESSURE, MEASUREDLENGTH, INSTALLATIONDATE)
File "D:\Arcpy\Material.py", line 48, in Pipe_Risk_Score
for row in sCur:
RuntimeError: An invalid SQL statement was used. [Pipe_Risk]

[Anonymous User]

I don't know what database your featureclass is in and there are different ways of using dates.  Look at the documentation if your database is not postgresql and this doesn't work and format it accordingly.

ID_where_Clause = "INSTALLATIONDATELOWERLIMIT <= timestamp '{0}' and INSTALLATIONDATEUPPERLIMIT >= timestamp '{0}'".format(INSTALLATIONDATE) 

[broy06]

Yes sir @Anonymous User , It's working now. Below error is because of the "with" statement in updating cursor case?

Currently using Arcmap 10.7

C:\Users\my>D:\Arcpy\Material.py
Material: 3 OPERATINGPRESSURE: 125.0 MEASUREDLENGTH: 3737.85025923 INSTALLATIONDATE: 1958-07-08 00:00:00
0.3
1.2
MEASUREDLENGTHUPPERLIMIT >= 3737.85025923
1.0
INSTALLATIONDATELOWERLIMIT <= timestamp '1958-07-08 00:00:00' and INSTALLATIONDATEUPPERLIMIT >= timestamp '1958-07-08 00:00:00'
1.4
Traceback (most recent call last):
File "D:\Arcpy\Material.py", line 69, in <module>
uCur.UpdateRow(row)
AttributeError: 'da.UpdateCursor' object has no attribute 'UpdateRow'

[Anonymous User]

Should be uCur.updateRow(row), lowercase U in the update.