0

I would like to get for example, the first 200 rows from a database by ArcGIS Python SearchCursor. How can I do that? I've got a featureclass which has about 500k features and reading it all in one shot is taking to long. My present code is:

fields = ";".join([field, "OBJECTID"])

rows = gp.searchcursor(connectionString, "", "", fields, "OBJECTID")
row = rows.Next()
while row:
    values.add(row.getValue(field))
    row = rows.Next()

return values

I've tried to set counter and stop iterating when it came to 50, for example. But when I would like to take another 50 records I don't know how to start from the last one that i fetched.

fields = ";".join([field, "OBJECTID"])
counter = 0

rows = gp.searchcursor(connectionString, "", "", fields, "OBJECTID")
row = rows.Next()
while row:
    counter += 1
    values.add(row.getValue(field))
    row = rows.Next()
        if (counter >= 50):
            break;                    

return values
9
  • What are you planning on doing with the batch of 50?
    – Nathan W
    Jul 30, 2013 at 11:26
  • If your data is in a geodatabase of some sort, you could use the SQL LIMIT clause (or whatever it's called in your DBMS).
    – til_b
    Jul 30, 2013 at 11:33
  • @NathanW after I fetch those data I would pass it via geoprocessing service to my web application (something like get unique values) @til_b I'm using SQL SERVER (via ArcSDE) but arcgisscripting.searchCursor doesn't have access to SELECT part of query (only WHERE clause) so I can not use LIMIT, TOP etc...
    – Krystian
    Jul 30, 2013 at 11:42
  • oh, arcGIS 9.3...
    – til_b
    Jul 30, 2013 at 12:32
  • 1
    That wouldn't work anyway as it would only give you the TOP 50 then nothing else. Unless you did WHERE id > 0 AND id < 50 and adjust the max and min each time
    – Nathan W
    Jul 30, 2013 at 12:42

2 Answers 2

1

This should do the trick:

def take50():
    rows = gp.searchcursor(connectionString, "", "", fields, "OBJECTID")
    items = []
    for counter, row in enumerate(rows):
        items.append(row)

        # If we are not the start of the counter
        # or we are mod 50 then return a copy of
        # the items list. 
        # We also clear our internal list for the next round
        # We return using list(items) so that we pass back a copy
        # and not our internal list
        if counter % 50 == 0 and not counter == 0:
            # Use yield so that we pass back a generator
            # and we can pick up from this point next time.
            yield list(items)
            items = []

    # Return what ever is left in case we don't have exact lots of 50
    yield items

# Loop over each lot of 50.
# lot == a list of 50 items
for lot in take50():
    print lot
9
  • yeah it could do the trick, but by the line gp.searchcursor(connectionString, "", "", fields, "OBJECTID") I accualy grab whole dataset and throw only 50 of them which is not good performance..
    – Krystian
    Jul 30, 2013 at 13:42
  • Ahh I see. I don't use Arc* so I wasn't sure if searchcursor ran the results and returned the full thing.
    – Nathan W
    Jul 30, 2013 at 13:44
  • Do you have a unique id column?
    – Nathan W
    Jul 30, 2013 at 13:45
  • yes Arc* made it by default - OBJECTID. I only can change WHERE clause in query..
    – Krystian
    Jul 30, 2013 at 13:50
  • 1
    Tricky situation. Hopefully someone with more arcpy skills might be able to help.
    – Nathan W
    Jul 30, 2013 at 14:14
0

OK I figured it out. As I said before ArcGIS make OBJECTID column by default and it is primary key. So having only access to WHERE clause I have to do this by this way (ofcourse it depends of db engine that are you using, in my case it is MS SQL and ORACLE server)

for MS SQL: OBJECTID IN ( SELECT TOP 50 OBJECTID FROM MyTable)

for ORACLE: ROWNUM<=50

thanks guys for all comments! :)

EDIT so in other words my actual class look like this: where: connectionString = path to ArcSDE connection file field = field which we are searching for lastIndex = last item index that was returned in last search pageNumber = :)

def get50UniqueValues(connectionString, field, lastIndex=None, pageNumber=None):
     values = []
     try:
         fields = ';'.join([field, 'OBJECTID'])

         tmp = connectionString.split('\\')
         tableName = tmp[-1]
         dbType = tmp[-2].split('@')[-1]

         if lastIndex == None and pageNumber == None:
             if dbType == 'MSSQL':
                 where = """ OBJECTID IN (
                                         SELECT TOP 50 OBJECTID
                                         FROM %(table)s
                                         WHERE NOT EXISTS (
                                                         SELECT 1
                                                         FROM %(table)s tab2
                                                         WHERE (
                                                             tab2.%(field)s = %(table)s.%(field)s
                                                             OR tab2.%(field)s IS NULL AND %(table)s.%(field)s IS NULL
                                                                 )
                                         AND tab2.OBJECTID > %(table)s.OBJECTID
                                                         )
                                         )""" % {"table":tableName,
                                                 "field":field
                                                 }

             else:
                 where = """NOT EXISTS (
                                     SELECT 1
                                     FROM %(table)s tab2
                                     WHERE (    
                                          tab2.%(field)s = %(table)s.%(field)s
                                          OR (
                                            tab2.%(field)s IS NULL AND %(table)s.%(field)s IS NULL
                                              )
                                            )
                                     AND tab2.ROWID > %(table)s.ROWID
                                     AND ROWNUM <= 50
                                       )
                                            """ % {
                                                   "table":tableName,
                                                   "field":field
                                                   }
         else:
             recordLimit = 50 * pageNumber
             if dbType == 'MSSQL':
                 where = """ OBJECTID IN (
                                         SELECT TOP 50 OBJECTID
                                         FROM %(table)s
                                         WHERE NOT EXISTS (
                                                         SELECT 1
                                                         FROM %(table)s tab2
                                                         WHERE (
                                                             tab2.%(field)s = %(table)s.%(field)s
                                                             OR tab2.%(field)s IS NULL AND %(table)s.%(field)s IS NULL
                                                                 )
                                         AND tab2.OBJECTID > %(table)s.OBJECTID
                                                         )
                             AND OBJECTID > %(lastId)d
                                         )""" % {"table":tableName,
                                                 "field":field,
                                                 "lastId":lastIndex
                                                 }
                 # ORACLE
             else:
                 where = """"NOT EXISTS (
                                             SELECT 1
                                             FROM %(table)s tab2
                                             WHERE (    
                                                   tab2.%(field)s = %(table)s.%(field)s
                                                   OR (
                                                    tab2.%(field)s IS NULL AND %(table)s.%(field)s IS NULL
                                                       )
                                                   )
                                             AND tab2.ROWID > %(table)s.ROWID
                                             AND ROWNUM < %(records)d AND ROWNUM >= %(minrecords)d
                                           )
                                   """ % {
                                           "table":tableName,
                                           "field":field,
                                           "records":recordsLimit,
                                           "minrecords":recordsLimit - 50
                                           }
         where = ' '.join(where.split())
         rows = gp.searchcursor(connectionString, where, "", fields, "OBJECTID")

         row = rows.Next()
         while row:
             values.append(u','.join([str(row.getValue(field)), str(row.getValue('OBJECTID'))]))
             row = rows.Next()

         values = '|'.join(values)
         return values
     except:
         return -1
3
  • @ChadCooper I can do it after 24h of post response
    – Krystian
    Jul 31, 2013 at 12:33
  • How does this return the next lot of 50 though? It will only ever give you top 50.
    – Nathan W
    Aug 2, 2013 at 2:16
  • @NathanW oh sorry mate, I forgot to update answer. Now it is complete. :)
    – Krystian
    Aug 2, 2013 at 14:58

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.