CalculateField 2 UpdateCursor

[Pierre-LucBoivin]

I'm trying to convert a simple calculate field to an UpdateCursor that's suppose to compare row value from two columns.

But i'm having an error 99999: Error executing function.
An invalid SQL statement was used.

Thanks for any help!

Below is what I have right now:
Calculate Field

codeblock = "def isSame(x, y):\n\n    fieldA = str(x)\n\n    fieldB = str(y)\n\n    if fieldA == fieldB:\n\n        return 1\n\n    else:\n\n        return 0\n"     isSameQuery = 'isSame =' + str(1)   arcpy.CalculateField_management(outIntersectGlobal,"isSame","isSame( !Name!, !Name_1! )","Python_9.3",codeblock)  

UpdateCursor

UpdateField = 'isSame' whereClause = '"Name"' + "==" + '"Name_1"'  cursor = arcpy.UpdateCursor(OutIntersectBoucle,whereClause) row = cursor.next  for row in cursor:             row.setValue(UpdateField, 1)             cursor.updateRow(row)             row = cursor.next

[JoelCalhoun · Accepted solution]

It obviously sounds like a problem with SQL statement syntax.  Formatting SQL statements in Python can be a pain.
Different data types can use different syntax.

One solution you could try is to not use a Where Clause at all and instead put the logic inside the loop.
This method might be slower, I'm not sure, but it is less prone to syntactic errors.

I am assuming you wanted to populate the field "isSame" with the value isSame if the values in fields "Name" and "Name1" are identical.

This code is untested but you could try something like this:

rows = arcpy.UpdateCursor(OutIntersectBoucle)  for row in rows:     if row.Name == row.Name1:         row.isSame = "isSame"     rows.updateRow(row)      # Delete cursors to clear locks            if row:     del row if rows:     del rows

Also of note is that if you are using ArcGIS 10.1 you can now use the Data Access module to speed up your cursors.


Good Luck,


Joel

[T__WayneWhitley]

If you just want to know where your error is, it is in the SQL query -- just use '='.

'==' is used in Python as a comparison operator.

At any rate, Stringtheory's code is sound, with minor exception with the line, row.isSame = "isSame".
If you have a numeric field you want to assign the integer value 1, as in your code block, then use row.isSame = 1.

Just something that could further trip you....other than that, I vote in favor of Stringtheory's code!

Only one additional note - you may want to look into using AddFieldDelimiters to auto-add to your SQL query the proper 'punctuation' to your field (checks your workspace type to verify and apply the double-quotes).

Thanks,
Wayne

[Pierre-LucBoivin]

Thanks to both of you !!

The only change I've made was the one propose by Wayne_Whitley.

rows = arcpy.UpdateCursor(OutIntersectBoucle) 
for row in rows:
    if row.Name == row.Name1:
        row.isSame = 1
    rows.updateRow(row)
    
# Delete cursors to clear locks           
if row:
    del row
if rows:
    del rows

One last thing,

Any idea if I would like to use a variable that increment each time it loop in.

I've try this :

if row[Name1] == row[Name]:

Name1 and Name increment by one each time but i'm having this error :

Error Info:
object is not subscriptable


If it's possible the best thing for me will be indexing row:

if row[34] == row[39]:

Error Info:
'Row' object does not support indexing

Thanks again for your help

[T__WayneWhitley]

I don't quite understand - it doesn't make sense what you are doing, or at least it isn't apparent to me yet.

First of all, 'row' is an object representing a record from your cursor...not sure what you intended to do with:

if row[Name1] == row[Name]:


Secondly, in general, the brackets are used to 'look up' and return something, as from a list object, say if you have a list variable, say:

someList = [1, 2, 3, 'aTextVal', 5, 6]

...and you want to return a value in the list, you'd do this:

testReturn = someList[0]
print testReturn
# would return:  1

testReturn = someList[3]
print testReturn
#  would return:  'aTextVal'

testReturn = someList[len(someList) - 1]
print testReturn
# would return:  6


Can you be more specific about what it is you'd like to do?


ADDITIONAL NOTE:
Just to note, you could index your own variable i to track where you are in the loop, for example:

# initalize your own index variable
i = 1

# now enter the loop
for row in rows:
     # do your processing 1st...
     # Before moving on to the next row, increment your index variable by one:
     i += 1

[Pierre-LucBoivin]

Hi Wayne,

The update Cursor was only a little part of my script.

Basically, I have to loop trough 50 inmemory feature class and intersect them with a different shapefile each time.

As you see Name1 = "Name_12_13_14_15"

Next loop my Name1 will become "Name_12_13_14_15_16"

Next ... "Name_12_13_14_15_16_17".

That's why I want to increment name1.

I thought I could deal with the position of the field in the cursor that's why I put row[34]... Was just testing something.


Here a another part of it :

fclistBoucle = fclist
    IntersectBoucle = "in_memory" + "\\" + "Intersect" + str(j)
    Name1 = "Name_12_13_14_15"
    l = 15
    # Loop troughout 50 in memory feature
    for i in range(len(fclist)):
              
        Name = Name1
        Name_1 = Name + "_" + str(l)
        print Name_1
        arcpy.Intersect_analysis([IntersectBoucle,fclistBoucle],OutIntersectBoucle)
        print "####5th intersection(loop) ###########"
        rows = arcpy.UpdateCursor(OutIntersectBoucle) 
        for row in rows:
            if f row.Name == row.Name1::
                row.isSame = 1
                rows.updateRow(row)
        j += 1
        arcpy.CopyFeatures_management(OutIntersectBoucle, outputPolygons1)
        arcpy.MakeFeatureLayer_management(OutIntersectBoucle,IntersectBoucle,isSameQuery)
        outputPolygons1 = "C:\Temp\Zone_Support_mutuel_test\Test_temp\ServiceAreaV2\Intersect" + "\\" + "Intersect" + str(j)
        fclistBoucle = fclist
        #IntersectBoucle = "in_memory" + "\\" + "Intersect" + str(j)
        OutIntersectBoucle = "in_memory" + "\\" + "OutIntersect" + str(j)
        l += 1
        del row, rows   

[T__WayneWhitley]

Well, maybe instead you can do something like the following - of course, I haven't studied all aspects of your code and I don't pretend to know if this is optimal, but you may be able to adapt this technique to at least better control your naming convention:

import os

# this is just a dummy list of integers:
dumList = [1,2,3,4,5,6]

# this is just a dummy base name:
dumBase = r'\\dummyServerOrDrive\thisFolder\thatSubfolder'

# initiate a new list, just to hold the dummy generated filenames
dumFilenames = []

# make a unique filename, something like this:
i = 1
for each in dumList:
     newFilename = 'baseFilename'
     newFilename = os.path.join(dumBase, newFilename + str(i))
     dumFilenames.append(newFilename)
     i += 1

Test it - just a suggestion to keep your filename lengths from growing out of control.
The code above should run on its own so you can see how it works - it's just working with strings, integers, and a list (not real pathnames of course - you'd make those subs yourself).  So to see what you get by running this, use the print statement (print dumFilenames).  This will print the list of assembled strings.

I flubbed a couple of times - it's simple, but mistakes can creep in.  I figured better to test and show you the output.  The list print looks like:

\\dummyServerOrDrive\thisFolder\thatSubfolder\baseFilename1
\\dummyServerOrDrive\thisFolder\thatSubfolder\baseFilename2
\\dummyServerOrDrive\thisFolder\thatSubfolder\baseFilename3
\\dummyServerOrDrive\thisFolder\thatSubfolder\baseFilename4
\\dummyServerOrDrive\thisFolder\thatSubfolder\baseFilename5
\\dummyServerOrDrive\thisFolder\thatSubfolder\baseFilename6

Hope that helps.
-Wayne

[T__WayneWhitley]

Something else that may help you -
The 'split' and 'rsplit' Python functions are invaluable (and there are many other string functions as well).

See the use of 'rsplit':

>>> Name1 = "Name_12"

>>> Name1.replace(Name1.rsplit('_', 1)[1], '13')

'Name_13'

[Pierre-LucBoivin]

the filename are ok !!! It's the fieldname created with the intersection that giving me some headaches !

u'FID', u'Shape', u'FID_OutIntersect3', u'FID_OutIntersect2', u'FID_OutIntersect0', u'FID_Global_Intersect1', 
u'FID_Global', u'FacilityID', u'Name', u'FromBreak', u'ToBreak', u'FID_CASERNE_09', u'FacilityID_1', u'Name_1', u'FromBreak_1', 
u'ToBreak_1', u'isSame', u'FID_CASERNE_28', u'FacilityID_12', u'Name_12', u'FromBreak_12', u'ToBreak_12', u'FID_CASERNE_30',
 u'FacilityID_12_13', u'Name_12_13', u'FromBreak_12_13', u'ToBreak_12_13', u'FID_CASERNE_38', u'FacilityID_12_13_14'
 u'Name_12_13_14', u'FromBreak_12_13_14', u'ToBreak_12_13_14', u'FID_CASERNE_42', u'FacilityID_12_13_14_15'
 u'Name_12_13_14_15', u'FromBreak_12_13_14_15', u'ToBreak_12_13_14_15']

You can have a look to what i'm trying to do but it's very uncommon what i'm doing... Not easy to understand !!!