Getting Error in arcpy using Cursor

746
7
08-09-2021 10:23 PM
broy06
by
New Contributor III

Hello everyone, I am getting error while printing the "INSTALLATIONDATE" & "MEASUREDLENGTH". MEASUREDLENGTH is having double datatype & INSTALLATIONDATE is having date data type. Could you please check my complete code?

 

 

Below is my code:  

                               

import arcpy
     from datetime import datetime
     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"] #Lookup table



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

Case: Material

    #Material_Out_fields = ["Material" , "OVERALLRISKSCORE"]

    Material_where_Clause = "Material = " + Material
    Table_Pipe_Cursor = arcpy.da.SearchCursor(Table_Pipe, ["Material" , "OVERALLRISKSCORE"],Material_where_Clause)
    for row in Table_Pipe_Cursor:

        Material_Risk_Score = row[1]
        print Material_Risk_Score

Case: Operating Pressure

    OP_Out_fields = ["OPERATINGPRESSURE" , "OVERALLRISKSCORE"]
    OP_where_Clause = "OPERATINGPRESSURE = " + OPERATINGPRESSURE
    Table_Pipe_Cursor = arcpy.da.SearchCursor(Table_Pipe, OP_Out_fields, OP_where_Clause)

    for row in Table_Pipe_Cursor:

        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

#Not giving me output after Print (ML_where_Clause)
    Table_Pipe_Cursor = arcpy.da.SearchCursor(Table_Pipe, ML_Out_fields, ML_where_Clause)

    for row in Table_Pipe_Cursor:

        MEASUREDLENGTH_Risk_Score = row[2]
        print MEASUREDLENGTH_Risk_Score #No output of this print function

Case: ID

    INSTALLATIONDATE = datetime.strptime("1935-01-01 " ,"%Y-%m-%d") #"01/01/2035 00:00:00")
    ID_Out_fields = ["INSTALLATIONDATELOWERLIMIT" , "INSTALLATIONDATEUPPERLIMIT" , "OVERALLRISKSCORE"]
    ID_where_Clause = "INSTALLATIONDATELOWERLIMIT <= datetime(1935-01-01 ) and INSTALLATIONDATEUPPERLIMIT >= datetime(2035-01-01 )"

    Table_Pipe_Cursor = arcpy.da.SearchCursor(Table_Pipe, ID_Out_fields, ID_where_Clause)
    for row in Table_Pipe_Cursor:

        ID_Risk_Score = row[2]
        print (ID_Risk_Score) # Error: Unconverted data remains:

#Final_Risk_Score = (Material_Risk_Score + OP_Risk_Score + ML_Risk_Score + ID_Risk_Score)/4

#Pipe_Risk_Score(Material , OPERATINGPRESSURE , MEASUREDLENGTH , INSTALLATIONDATE) # Showing me Name error using this line without quotes

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

 

0 Kudos
7 Replies
BlakeTerhune
MVP Regular Contributor

For your text based where clauses, you'll need single quotes around the text value. For example:

Material_where_Clause = "Material = '{}'".format(Material)

 

The date expressions are failing because you have a trailing space.

INSTALLATIONDATE = datetime.strptime("1935-01-01", "%Y-%m-%d")

 

0 Kudos
broy06
by
New Contributor III

Sir, See if i have ranges for the date like from 1935-2035 so below statement is correct? Actually while printing the below statement it's only giving me the upper value i.e., ('2035-01-01', '%Y-%m-%d')

INSTALLATIONDATE = datetime.strptime("1935-01-01" , "%Y-%m-%d") and ("2035-01-01" , "%Y-%m-%d")

 

Also what will be the "Where Clause" for the same as per you?

0 Kudos
BlakeTerhune
MVP Regular Contributor

The <= and >= you used in your original code should work for querying a date range. Just get rid of all trailing spaces.

0 Kudos
broy06
by
New Contributor III

Sir, so what will be my where clause? Could you please tell?

0 Kudos
BlakeTerhune
MVP Regular Contributor

I should have asked where your data is because that will change the syntax a little.

Re: Using where clause to query date ranges in Arc... - Esri Community

For example, a file geodatabase where clause with date would be

ID_where_Clause = "INSTALLATIONDATELOWERLIMIT >= date '1935-01-01' AND INSTALLATIONDATEUPPERLIMIT <= date '2035-01-01'"

0 Kudos
broy06
by
New Contributor III

Sir, I'm using file gdb. But the code gives me error. Please get me rid out of this problem.

 

Below is the error statement in bold:

for row in Table_Pipe_Cursor:
RuntimeError: An invalid SQL statement was used. [SELECT INSTALLATIONDATELOWERLIMIT, INSTALLATIONDATEUPPERLIMIT, OVERALLRISKSCORE, OBJECTID FROM Gas_Valve_Risk WHERE INSTALLATIONDATELOWERLIMIT <= datetime('1935-01-01') and INSTALLATIONDATEUPPERLIMIT >= datetime('2035-01-01')]

 

PrabalTripathi_0-1628623356656.png

 

0 Kudos
BlakeTerhune
MVP Regular Contributor

You're using the wrong syntax for a file geodatabase.

SQL reference for query expressions used in ArcGIS—ArcGIS Pro | Documentation

Try

ID_where_Clause = "INSTALLATIONDATELOWERLIMIT >= date '1935-01-01' AND INSTALLATIONDATEUPPERLIMIT <= date '2035-01-01'"

If that doesn't work, try adding the time component like

ID_where_Clause = "INSTALLATIONDATELOWERLIMIT >= date '1935-01-01 00:00:00' AND INSTALLATIONDATEUPPERLIMIT <= date '2035-01-01 00:00:00'"