1

I'm working in ArcGIS Pro and writing rows from a source file with Text data type field to another text field in a target file but it generates an error as such:

An invalid SQL statement was used. [SELECT unique_id2, Col2, OID FROM fc2 WHERE unique_id2 is equal to 274162].

*note 274162 is the first value of unique_id1 which is also present in unique_id2. Below is my script:

with arcpy.da.SearchCursor(fc1, ["unique_id1","Col1"]) as cursor:
    for row in cursor:
        SQL_stat = "unique_id2 is equal to " +str(row[0])
        with arcpy.da.UpdateCursor(fc2, ["unique_id2", "Col2"], SQL_stat) as cursor1: #Error
            for row1 in cursor1

My code works if the fields are Integer and not texts, but I need to work with text fields. Also a print statement of the SQL_stat prints out the WHERE clause as it ought to

3

1 Answer 1

1

Try using AddFieldDelimiters and format to create the sql expression string:

SQL_stat = "{0} is equal to '{1}'".format(arcpy.AddFieldDelimiters(datasource=fc2, field="unique_id2"), row[0])

Not sure how your data looks like, but you should probably think about using a dictionary instead of nesting cursors. Something like:

datalookup = {k:v for k,v in arcpy.da.SearchCursor(fc1, ["unique_id1","Col1"])}

with arcpy.da.UpdateCursor(fc2, ["unique_id2", "Col2"]) as cursor:
    if row[0] in datalookup:
        row[1] = datalookup[row[0]]
        cursor.updateRow(row)
1
  • There's a type mismatch issue at play here, so this won't work as-is for the OP, but which table has VARCHAR and which has INTEGER has never been stated.
    – Vince
    Commented Aug 5, 2021 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.