5

I would like to base my query on the last row of a table? I know the basic syntax/functionality for my search cursor, how do I query only the last row;

Below is an example of my search criteria, this data comes from a log of success/fail for a script that runs, I will base other decisions off of the success or fail later in the application.

How do I add to my existing code the function to look in the last row, to getValue of timeand success or fail

I am using ArcGIS 10.2.

cursor = arcpy.SearchCursor(aTable,"""Time > DATEADD(minute, -2,  GETDATE())""")
for row in cursor:
    print(row.getValue("Time"))
if row.getValue("Time") < datetime.datetime.now().strftime('%m/%d/%Y %H:%M:%S'):
    print "true"
else:
    print "false"

This is later implemented in the script and creates the success or fail values as yes and norespectively.

rows = arcpy.InsertCursor(aTable)
for x in xrange(0, outputobjects):
    row = rows.newRow()
    row.setValue("Success", 'YES')
    row.setValue("Fail", 'NO')
    row.setValue("Time", Start)
    rows.insertRow(row)
  • to get the last value you could loop through a feature class (or table) overwriting a value on each iteration, the last value will be in that variable. This can get very slow for exceptionally large tables so perhaps keep the OID of the last iteration and then next time use the whereclause to trim it down to OID more than (does not work with shapefiles, but shapefiles don't contain a lot of records due to the 2GB restriction, so this doesn't relate to them anyway) – Michael Stimson Apr 23 '15 at 21:37
  • What does "last" mean? I would add an sql order clause to make sure that you grab the truly "last" one. – JasonT Apr 23 '15 at 21:43
  • I think using the result of GetCount with FID in a where_clause on a SearchCursor may be quicker/easier but will need to performance test to be sure. – PolyGeo Apr 23 '15 at 22:01
  • 1
    note though that GetCount with FID would work on shapefiles, but for a feature class it wouldn't because OBJECTID is not consecutive if you've ever deleted a row. So the last OBJECTID is likely to be a higher number than the GetCount result. – mr.adam Apr 23 '15 at 22:11
5

I would usually suggest the da.SearchCursor as well, but its order by clause only works withe data in a database. So, if it is in a database:

a_table = "YourTable"
order_fld = "Time"
return_flds = ["Time", "SomeOtherField"]
where_str = """Time > DATEADD(minute, -2,  GETDATE())"""

sql_clause = (None,'ORDER BY {} DESC'.format(order_fld))

last_row = ''
last_time = ''
with arcpy.da.SearchCursor(a_table, return_flds, where_clause=where_str, sql_clause=sql_clause) as cursor:
  last_row = cursor.next()
  last_time = last_row[0]

Else, if it is a shapefile:

sort_string = "{} D".format(order_fld)
arcpy.SearchCursor(a_table, where_clause=where_str, sort_fields=sort_string)
last_row = cursor.next()
last_time = last_row.Time

Both of these, you don't have to loop through the entire result to get the "last" row. By ordering by descending, the last row is the 1st row, and you can move on.

  • What does order_fld indicate here? – Geoffrey West Apr 24 '15 at 0:46
  • It's whatever field you are sorting by to determing the "last" row. I think you would just use FID or ObjectID, but you could use time or any other field to sort by. – JasonT Apr 26 '15 at 21:49
6

If you're using 10.2, I would first recommend using the data analysis version of the search cursor. Then you can use python's list comprehension to get the last value.

Try something like the code below. It creates a list of all values in the 'time' field, and then accesses the last record through indexing (the [-1] at the end of the list comprehension line).

#Time field name
TimeFld = "Time"

#List comprehension and data analysis search cursor
LastTimeValue = [r[0] for r in arcpy.da.SearchCursor(aTable, TimeFld)][-1]

#Logic check
if LastTimeValue < datetime.datetime.now().strftime('%m/%d/%Y %H:%M:%S'):
    print "true"
else:
    print "false"
5

You can do this with a Search Cursor wrapped in a list comprehension:

import arcpy

# Your input shp of fc  
fc = r'C:\path\to\your.shp'

# List all of the row values as a list of tuples
rows = [row for row in arcpy.da.SearchCursor(fc, "*")]

# Get the last row
print rows[-1]
  • 2
    Like it Aaron but if the dataset is HUGE (like SDE huge) you might crash as you're reading the whole table into a list. Perhaps include a 'count rows' to see how many rows are there before deciding to read the whole table. – Michael Stimson Apr 23 '15 at 21:40
  • Good points @MichaelMiles-Stimson. The list comprehension should be robust enough to handle even very large datasets. However, to be ultra-efficient you could always limit the rows to only those that are needed within the SearchCursor. – Aaron Apr 23 '15 at 21:55
  • @Aaron Do you know how I can update an attribute value for just the last record? I was able to retrieve the last record using your code but can't figure out how to update the value of one of the fields for that record. – ketar Apr 19 '18 at 17:06
  • @ketar you will need to use an update cursor for that. – Aaron Apr 19 '18 at 17:49
3

Another idea, though all these answers should work. This will avoid having to iterate the whole cursor. Not sure if the sql_clause will decrease performance though:

oid_field = "OBJECTID"
sql = ("ORDER BY {0} D".format(oid_field),"")
with arcpy.da.SearchCursor(table,"*",sql_clause=sql) as cursor:
    for row in cursor:
        last_row = row
        break

The object id field name depends on the type of dataset you're working with, but the highest value will always be the last row. This cursor will be sorted descending by object id, and then you only have to iterate the first row. I haven't tested this though...

  • 1
    ah, I just saw the comments on the original post, well, here's the code for the ordered cursor. – mr.adam Apr 23 '15 at 22:09
  • 1
    You beat me to it while I was typing my code out. Exactly, just note this only works for geodatabase feature classes. – JasonT Apr 23 '15 at 22:27
  • well your answer you just posted has more info: I didn't know that order by doesn't work with shapefiles, so yours is definitely a more complete answer. – mr.adam Apr 23 '15 at 22:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.