Loop through records

Question

I have written a python script that is supposed to basically loop through selected records of a feature class. Here's a sample of what a table looks like:

OBID   STID FL  TL      
1   1   0   9       
2   1   8   19      
3   2   3   23      
4   3   0   0       
5   1   21  29      
6   3   0   11      
7   1   27  31      
8   3   13  19

Here's what I'm trying to do:

1. Create a SearchCursor (for main file) and InsertCursor (for new file)

2. Sort all records by STID, FL and TL in order with the SearchCursor

3. Record TL for the first row and move onto next row

4. Record STID and FL in a variable

5. Compare current FL to previous TL

6. If FL<=TL, export both rows to new table with InsertCursor

7. Continue until current STID is equal to previous STID. If not, go back to step 3 (ie, repeat the process for new STID)

And here's the code I wrote which is not working:

import arcpy
arcpy.env.workspace = "c:\\temp\\test.mdb"
abc = arcpy.SearchCursor("road","","","STID;FL;TL","STID A;FL A;TL A")
first = 1
for row in abc:
     if first != 1:
         spid = row.getValue("STID")
         frl = row.getValue("FL")
             if spid == prev:
                 if frl <= trl:
                    print spid, frl
                    prev = row.getValue("STID")
                    trl = row.getValue("TL")
             else:
                  prev = row.getValue("STID")
                  trl = row.getValue("TL")
      else:
         prev = row.getValue("STID")
         trl = row.getValue("TL")
         first = 0
del row
del abc

Answer 1

The logic behind what you're doing is somewhat unclear, but it sounds like you're trying to sort a bunch of records on a few different fields and then go through them one at a time trying to compare each record with some previous record based on the STID field and if neighboring records don't have the same STID field, then you move to the next until they match.

I think the trouble you're getting into, and this is a shot in the dark, is that search cursors don't support looping backwards. So if you're looking for STID-x and you iterate through your search cursor rows looking for STID-y == STID-x, you may end up at the end of your cursor object before you've done your complete analysis on each record. You can add some print statements to see what's going on for each iteration in your search cursor to help pinpoint where your script is going wrong.

Also, based on the fields and data you supplied above, it looks like you want to be comparing the OBID field to the STID field. If that's the case you need to re-look at your sorting and your getValue statements.

EDIT:

Ok, I got this to work. It looks like your problem was caused because you don't have a 'proper' Object ID for your rows. Not quite sure why this works, but I added a field called FID in Access and set it's type to 'AutoNumber' which stores a unique value for each record (even though your OBID field is unique already). Maybe someone else can enlighten us both. After doing that, I added logic to scrape your records from the 'roads' table into a new table called 'output' which is either created from scratch or cleared of any pre-existing data each time you run this script.

import arcpy
ws = 'C:/temp/test.mdb'
arcpy.env.workspace = ws
arcpy.env.overwriteOutput = True
rows = arcpy.SearchCursor('road','','','STID;FL;TL','STID A; FL A; TL A')

#-- create new table to hold results using 'road' table as template
if arcpy.Exists('output'):
    arcpy.DeleteRows_management('output') #-- clear contents of output table
else:
    arcpy.CreateTable_management(ws,'output','road')
    arcpy.AddField_management('output','originalFID','LONG')

irows = arcpy.InsertCursor('output')  #-- create insert cursor

first = True
for row in rows:
    if first:
        first = False
    else:
        fid = row.FID
        stid = row.STID
        fl = row.FL
        tl = row.TL
        if stid == prev_stid and fl <= prev_tl:
            #-- add previous record to new table
            irow = irows.newRow()
            irow.originalFID = prev_fid
            irow.STID = prev_stid
            irow.TL = prev_tl
            irow.FL = prev_fl
            irows.insertRow(irow)
            del irow

            #-- now let's add the current record
            irow = irows.newRow()
            irow.originalFID = fid
            irow.STID = stid
            irow.TL = tl
            irow.FL = fl
            irows.insertRow(irow)
            del irow

            print stid,fl

    #-- let's store all information from this row
    prev_fid = row.FID
    prev_stid = row.STID
    prev_tl = row.TL
    prev_fl = row.FL

del rows,irows

Answer 2

Well, I copied your script and the test data. The only error I could see was in the second line, where you set the workspace - backslash is an escape character in Python.

There are four reasonably common alternatives. Double backslash (escapes the escape):

arcpy.env.workspace = "c:\\temp\\test.mdb"

Raw strings (prefix r):

arcpy.env.workspace = r"c:\temp\test.mdb"

Triple quotes:

arcpy.env.workspace = """c:\temp\test.mdb"""

And forward slashes:

arcpy.env.workspace = "c:/temp/test.mdb"

Choose one that works for you. And there are more.