topic Re: Getting Error in arcpy using Cursor in Python Questions

Getting Error in arcpy using Cursor

broy06 — Tue, 10 Aug 2021 09:54:46 GMT

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

Re: Getting Error in arcpy using Cursor

BlakeTerhune — Tue, 10 Aug 2021 16:34:20 GMT

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

Re: Getting Error in arcpy using Cursor

broy06 — Tue, 10 Aug 2021 18:15:25 GMT

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?

Re: Getting Error in arcpy using Cursor

BlakeTerhune — Tue, 10 Aug 2021 18:41:36 GMT

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

Re: Getting Error in arcpy using Cursor

broy06 — Tue, 10 Aug 2021 18:46:06 GMT

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

Re: Getting Error in arcpy using Cursor

BlakeTerhune — Tue, 10 Aug 2021 19:00:58 GMT

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

Re: Getting Error in arcpy using Cursor

broy06 — Tue, 10 Aug 2021 19:29:35 GMT

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')]

Re: Getting Error in arcpy using Cursor

BlakeTerhune — Tue, 10 Aug 2021 20:27:50 GMT

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