topic update cursor invalid sql statement in Python Questions

update cursor invalid sql statement

BrianLomas — Fri, 11 Mar 2016 17:01:55 GMT

I'm new to python so forgive me if this seems like a dumb question... I work for the city and we get parcel information monthly from the county. Usually the data comes in a text file which I then put in an mdb. I'm trying to write some code to run an update our layers with the updated monthly information. Currently, I've just been trying to get a few fields to update inside a 'test' file geodatabase but I'm stuck. I get the following error message with the following code. Any ideas?

Re: update cursor invalid sql statement

DarrenWiens2 — Fri, 11 Mar 2016 17:11:21 GMT

Not sure about the rest, but you don't have the same number of single and double quotes.

Re: update cursor invalid sql statement

BrianLomas — Fri, 11 Mar 2016 17:16:48 GMT

Oops I forgot I corrected that one... When that is taken out I get the following error...

Runtime error

Traceback (most recent call last):

File "<string>", line 9, in <module>

RuntimeError: An invalid SQL statement was used. [Parcel_092015COPY]

Re: update cursor invalid sql statement

DarrenWiens2 — Fri, 11 Mar 2016 17:22:11 GMT

Try building your SQL expression in a previous line, then printing it to confirm that it is what you think it is.

Re: update cursor invalid sql statement

WesMiller — Fri, 11 Mar 2016 18:08:59 GMT

Have you tried doing this in model builder. Most everything you'll need should be in there and it will give you a head start if you need to do any coding.

What is ModelBuilder?—Help | ArcGIS for Desktop

Re: update cursor invalid sql statement

VinceAngelo — Fri, 11 Mar 2016 18:23:09 GMT

Please post Python questions with the code in the question as text (formatted in a code block) -- It allows others to try your code without retyping it from scratch. If you need to redact pieces, be sure to explain what they were sufficiently so that suitable scaffolding can be applied.

- V

Re: update cursor invalid sql statement

BrianLomas — Fri, 11 Mar 2016 22:14:41 GMT

I thought about model builder but this will run for more than 10,000 features so I was worried about it being too slow. Although, I'm not too familiar with MB so I may be wrong.

Re: update cursor invalid sql statement

DanPatterson_Retired — Fri, 11 Mar 2016 22:21:54 GMT

are your fields really situs and current_ow or those supposed to be sites and current_row which I did a brain translation to until I looked again

Re: update cursor invalid sql statement

BrianLomas — Sun, 12 Dec 2021 05:02:31 GMT

I wasn't sure on how to do that, so thanks for the info. Here's the code.

import arcpy
countyTB =...January2015Ownership.mdb\January2015Ownership'
fc = ...ParcelPythonCOPIES.gdb\Parcel_092015COPY"
with arcpy.da.SearchCursor(countyTB, ["*"]) as rows:
    for row in rows:
        fields = rows.fields
        idfield = fields.index('PARCEL_')
        with arcpy.da.UpdateCursor(fc,['situs', 'current_ow'], "%s = %s" % ('parcel_id', row[idfield])) as urows:
            for urow in urows:
                  urow[1] = row[1]
                  urows.UpdateRow(urow)
                  del urow
        del row

Re: update cursor invalid sql statement

BrianLomas — Fri, 11 Mar 2016 22:25:14 GMT

yea, 'situs' and 'current_ow' are actual field names.

Re: update cursor invalid sql statement

DanPatterson_Retired — Fri, 11 Mar 2016 22:28:28 GMT

just checking sometimes it is the little things...

Re: update cursor invalid sql statement

XanderBakker — Sun, 12 Dec 2021 05:02:33 GMT

I would probably do something along the lines of:

import arcpy
countyTB = r'...January2015Ownership.mdb\January2015Ownership'
fc = r'...ParcelPythonCOPIES.gdb\Parcel_092015COPY'

# create a dictionary
dct = {r[0]: r[1] for r in arcpy.da.SearchCursor(countyTB, ('PARCEL_', 'TheOtherFieldNameAtIndex1'))}

with arcpy.da.UpdateCursor(fc, ('parcel_id', 'current_ow')) as curs:
    for row in curs:
        parcel_id = row[0]
        if parcel_id in dct:
            row[1] = dct[parcel_id]
            curs.updateRow(row)

You are reading all the fields of countyTB when you only seem to need two. For the copy you are reading the field 'situs' which I don't think you are using. Using a dictionary and looking up the data might make things a bit faster, but if your have a lot of features, it may be slow.

Re: update cursor invalid sql statement

WesMiller — Sat, 12 Mar 2016 16:52:27 GMT

10,000 features wouldn't be a problem. You could simply join the 2 tables then use field calculator.

Re: update cursor invalid sql statement

BrianLomas — Mon, 14 Mar 2016 13:29:28 GMT

Thanks for the idea... Ultimately, there will be 12 different fields that will need to be updated. The intention of this script was to run a test on just a few fields (current_ow & situs) for 'proof of concept'.

Re: update cursor invalid sql statement

BrianLomas — Mon, 14 Mar 2016 21:17:52 GMT

This worked great and was very fast. However, I'm a bit confused how I can get this to cycle through multiple fields? Maybe use a getparameter to manually set the field after each cycle?

Re: update cursor invalid sql statement

XanderBakker — Mon, 14 Mar 2016 21:28:12 GMT

You could use a dictionary with a list of values, in stead of a dictionary with a single field as value. This would require some changes in building the dictionary and the way the values are retrieved in the update cursor, but it can be done. If the code you tested was fast, it means that you don't have that many features to process. In that case it would not be a problem to create this larger dictionary (with lists as values).

Re: update cursor invalid sql statement

JoshuaBixby — Tue, 15 Mar 2016 16:19:24 GMT

Please remember to mark your question as answered.

Re: update cursor invalid sql statement

BrianLomas — Sun, 12 Dec 2021 05:02:36 GMT

I was having difficulty getting the dictionaries working correctly but I stumbled onto a great blog post which was the exact thing I was looking for (link below). Thanks for everyone's help, I've learned a lot from you during this. I look forward to using python for more automation purposes in the future.

Turbo Charging Data Manipulation with Python Cursors and Dictionaries

import arcpy 
... 
... sourceFC = "...January2015Ownership.mdb\January2015Ownership"
... 
... sourceFieldsList = ["PARCEL_","SITUS", "CURRENT_OWNER", "CO_OWNER", "OWNER_MAILADDR", "OWNER_CITY_STATE", "OWNER_ZIPCODE", "TAX_DISTRICT", "TAX_CODE", "SEC", "TOWNSHIP", "RANGE", "LEGAL"] 
... 
... # Use list comprehension to build a dictionary from a da SearchCursor 
... valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sourceFC, sourceFieldsList)} 
... 
... updateFC = "...\ParcelPythonCOPIES.gdb\TaxParcel_COPY"
... 
... updateFieldsList = ['PARCELID','SITEADDRESS','OWNERNME1', 'OWNERNME2', 'PSTLADDRESS', 'PSTLCITY', 'ZIPCODE', 'TAX_DIST', 'TAX_CODE', 'SEC', 'TWN', 'RGE', 'PRPRTYDSCRP'] 
... 
... with arcpy.da.UpdateCursor(updateFC, updateFieldsList) as updateRows: 
...    for updateRow in updateRows: 
...        # store the Join value of the row being updated in a keyValue variable 
...        keyValue = updateRow[0] 
...        # verify that the keyValue is in the Dictionary 
...        if keyValue in valueDict: 
...            # transfer the values stored under the keyValue from the dictionary to the updated fields. 
...            for n in range (1,len(sourceFieldsList)): 
...                updateRow = valueDict[keyValue][n-1] 
...            updateRows.updateRow(updateRow) 
... 
... del valueDict