0

I am trying to write a second update cursor within one script. This requires a LIKE condition to look up a value within the same layer to identify rows to update the field within that layer (first update cursor looks up a second table and is working successfully). When I try to run the query it is throwing a syntax error and highlighting LIKE.

See my initial question about Writing python script to select attributes not in growing table with over 4000 values?.

Here is the portion of script in causing the issue:

with arcpy.da.UpdateCursor("in_memory/headphotos",[field2,'Located']) as UCur1:
    for URow1 in UCur1:
        if URow1[0].lower() LIKE '%reserve%':
            URow1[1] = 1

Here is my full script:

import os, sys, arcpy

arcpy.env.workspace = (directory location)
jpgList = arcpy.ListRasters('*','JPG')
photos = ([s.lower().strip('.jpg') for s in jpgList ])

arcpy.CopyFeatures_management(feature class location,r"in_memory/headphotos")

arcpy.AddField_management(r"in_memory/headphotos",'Located','SHORT')

with arcpy.da.UpdateCursor("in_memory/headphotos",['ce_id','Located']) as UCur:
    for URow in UCur:
        if URow[0].lower() in photos:
            URow[1] = 1
        else:
            URow[1] = 0
        UCur.updateRow(URow)

with arcpy.da.UpdateCursor("in_memory/headphotos",[field2,'Located']) as UCur1:
    for URow1 in UCur1:
        if URow1[0].lower() LIKE '%reserve%':
            URow1[1] = 1

arcpy.MakeFeatureLayer_management("in_memory/headphotos","tmp_layer",'Located = 0')
arcpy.CopyFeatures_management("tmp_layer",new layer)
  • 1
    At the moment you have only done a copy/paste from your full script (and then posted that full script) instead of posting a code snippet that can be run by potential answerers. What is the full error message when you run the code that you have presented? – PolyGeo Sep 11 '17 at 2:10
  • 1
    Like is SQL not python, hence the syntax error, to determine if the string from field2 contains the word reserve it should be if URow1[0].lower().find('reserve') > 0: If you're going to filter with the cursor on the other hand 'field2 like \'%reserve%\'' is correct for a where clause but you will need to substitute the name of field2 :'{} like \'%reserve%\''.format(field2) as your cursor where clause as it's not evident what the value of field2 is. – Michael Stimson Sep 11 '17 at 2:14
  • I would never use MakeFeatureLayer(where_clause)/CopyFeatures when Select_analysis(where_clause) does the same thing in a single step. – PolyGeo Sep 11 '17 at 2:39
2

Expanding on the comment.

There's two ways set your rows that contain 'reserve' location attribute to 1, but first I must point out your 2nd update cursor does not contain UCur1.updateRow(URow1) so no matter what no rows are updated.

Firstly by where clause:

with arcpy.da.UpdateCursor("in_memory/headphotos",[field2,'Located'],'{} like \'%reserve%\''.format(field2)) as UCur1:
    for URow1 in UCur1:
      URow1[1] = 1           # all rows contain the word 'reserve' in their field2.
      UCur1.updateRow(URow1) # store the updated row

Where can this be a problem? The wildcard character is different for different feature class types.. % is fine most of the time but personal geodatabases use * as a wildcard which can cause horrible problems when reusing code.

Secondly by finding a substring:

with arcpy.da.UpdateCursor("in_memory/headphotos",[field2,'Located']) as UCur1:
    for URow1 in UCur1:
        if URow1[0].lower().find('reserve') > 0:
            URow1[1] = 1           # this row is found to contain 'reserve'
            UCur1.updateRow(URow1) # store the updated row

So what's the problem with doing it this way? You're still going through all the rows again, this mightn't be a big deal in small datasets but it can be more significant in very large datasets where the power of some SQL to limit the number of rows that python has to chug through gives a significant advantage; SQL is usually faster than python.

On a side note, I see you've made the 2nd cursor UCur1 to avoid reusing the name, this often is commendable, however by the time the execution gets to the second update cursor UCur and URow from the first cursor are out of scope and therefore do not exist - as far as python is concerned it's already forgotten them! Don't feel that you need to have a different variable name for every cursor... I've used the name UCur/URow to remind me that it's an update environment (read/write), were it a search cursor I might use SCur/SRow to remind me it's a search environment (read only) but as soon as you finish the with block those names are safe to be reused.

| improve this answer | |
  • Thanks Michael, you've been a great teacher expanding my understanding in a way that doesn't make me feel too much like an idiot! – N. Begg Sep 11 '17 at 4:51
  • I tried your first suggestion (adding the where clause) before you expanded on it but I did not write it correctly. Why is the format \'reserve\' as in the \' bit? Once seeing this answer expanded I tried the second option with the substring but I found this did not provide the correct response so I went back to the Where clause and this provided the accurate response. – N. Begg Sep 11 '17 at 4:53
  • I have found that due to very poor data quality in the first place, the second clause would need to include a lot more 'do not include this, or that or ...' I have tried to do this in several ways, including trying to select against the original layer before the lookup cursor. But I have realised that this is over complicating it and I will try and do this via a definition query in the map before publishing the service. Thanks again for your help. – N. Begg Sep 11 '17 at 4:57
  • Don't feel too bad, I was a beginner once too. The \'reserve\' is because of escape chars in python, the slash is taken out.. try it. Open up a python window in ArcCatalog, ArcMap or your favorite and try printing that string (let's change it to 'field2 like \'%reserve%\'' because your variable isn't defined yet). The slash includes the ' in the string, which is required for the SQL. Note python accepts either single ' or double " string delimiters as long as they are balanced... if you use a double the backslashes aren't needed: "{} like '%reserve%' ".format(field2) works just fine. – Michael Stimson Sep 11 '17 at 5:10

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.