topic Re: CalculateField 2 UpdateCursor in Python Questions

CalculateField 2 UpdateCursor

Pierre-LucBoivin — Wed, 16 Jan 2013 22:36:38 GMT

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

Re: CalculateField 2 UpdateCursor

JoelCalhoun — Wed, 16 Jan 2013 23:36:25 GMT

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

Re: CalculateField 2 UpdateCursor

T__WayneWhitley — Thu, 17 Jan 2013 00:15:52 GMT

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

Re: CalculateField 2 UpdateCursor

Pierre-LucBoivin — Sat, 11 Dec 2021 06:47:46 GMT

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

Re: CalculateField 2 UpdateCursor

T__WayneWhitley — Sat, 11 Dec 2021 06:47:48 GMT

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

Re: CalculateField 2 UpdateCursor

Pierre-LucBoivin — Sat, 11 Dec 2021 06:47:51 GMT

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

Re: CalculateField 2 UpdateCursor

T__WayneWhitley — Sat, 11 Dec 2021 06:47:54 GMT

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

Re: CalculateField 2 UpdateCursor

T__WayneWhitley — Sat, 11 Dec 2021 06:47:56 GMT

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'

Re: CalculateField 2 UpdateCursor

Pierre-LucBoivin — Sat, 11 Dec 2021 06:47:59 GMT

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 !!!