Getting Error in my where clause using cursor in arcpy.

3187
46
Jump to solution
08-08-2021 01:55 AM
broy06
by
New Contributor III

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")

 

 

0 Kudos
1 Solution

Accepted Solutions
JeffK
by MVP Regular Contributor
MVP Regular Contributor

It looks like you are not using the right ML where clause again.

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

 

For your date rage, maybe drop the = because it is including  01-01-1955 and 01-01-1935 as being valid so its selecting both.

View solution in original post

0 Kudos
46 Replies
DanPatterson
MVP Esteemed Contributor

Select Layer By Attribute (Data Management)—ArcGIS Pro | Documentation

from there, this is an example of a where clause for a string

"[NAME] = 'California'"

What do you get if you do an sql query on the data manually?  Check it out and copy the syntax


... sort of retired...
JeffK
by MVP Regular Contributor
MVP Regular Contributor

It looks like you are trying to use string formatting for the variable but forgot the .format().

 

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

 

 

0 Kudos
broy06
by
New Contributor III

Sir, this statement gives me a blank screen with no output.

0 Kudos
JeffK
by MVP Regular Contributor
MVP Regular Contributor

You are passing "MEASUREDLENGTH" as a string variable to MEASUREDLENGTH in the function call, but are trying to use it as integer in the whereclause.  Where is the actual value of MEASUREDLENGTH for the sql coming from?

0 Kudos
broy06
by
New Contributor III

Yes. See, basically there is 1 feature class & 1 lookup table (Created in a same gdb). The actual value of "MEASUREDLENGTH" is in feature class attributes. but i have assigned some score for the same in the form of range in look up table and now i am calling it from the lookup table. 

0 Kudos
GintautasKmieliauskas
Regular Contributor

Hi,

Change 

 + and "MEASUREDLENGTHUPPERLIMIT >= "

to

 + " and MEASUREDLENGTHUPPERLIMIT >= "
0 Kudos
broy06
by
New Contributor III

Hello, Earlier i tried using the same method which you suggested me but getting no output only able to see the print statement of where clause, not getting the value of "MEASUREDLENGTH" which has a double data type values, which i want. Please suggest me, what i'm lacking of?

0 Kudos
JeffK
by MVP Regular Contributor
MVP Regular Contributor

You are passing strings into your functions:

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

OPERATINGPRESSURE is assigned "OPERATINGPRESSURE"

MEASUREDLENGTH is assigned "MEASUREDLENGTH"

INSTALLATIONDATE is assigned "INSTALLATIONDATE"

If you want the float / date that is assigned to MEASUREDLENGTH (or any of the other values), you need to remove the quotes in the function call so it passes the variables and not "OPERATINGPRESSURE" , "MEASUREDLENGTH" , "INSTALLATIONDATE" strings. For example:

 

OPERATINGPRESSURE = 36

MEASUREDLENGTH = 12

INSTALLATIONDATE = datetime.strptime('08/03/2021 12:43:34')

Pipe_Risk_Score(Material, OPERATINGPRESSURE, MEASUREDLENGTH,  INSTALLATIONDATE)

 

OPERATINGPRESSURE is assigned 36

MEASUREDLENGTH is assigned 12

INSTALLATIONDATE is assigned datetime 08/03/2021 12:43:34

 

We cant tell how you are assigning the variables from the lookup table from the code snippet you provided above so if you are having issues, it would be helpful to post the full code and any error messages that you are getting.

 

0 Kudos
broy06
by
New Contributor III

Sir, Actually while calling a function without "Material" (quotes) , it's showing me "Name error", Name: Material is not defined and so on. okay I'm updating my full code in the question along with the lookup table & feature class attribute pic.  Thank you, Please have a look  then.                                                                   Error:                                                                                                                                                                                                                  Pipe_Risk_Score("Material" , "OPERATINGPRESSURE" , "MEASUREDLENGTH" , INSTALLATIONDATE)
NameError: name 'INSTALLATIONDATE' is not defined

0 Kudos