2

Have an excel spreadsheet in CSV format with a field of APN numbers (8 digit US assessor parcel numbers)

enter image description here

Need to query these in a large layer of parcel polygons using python script and the arcpy.Select_analysis function.

The problem may be the way I created the list object in python. When I print the list, commas are returned inside parenthesis' with the APN's .

enter image description here

The error message is invalid expression, here is the script I have:

import arcpy
import sys
import os
from arcpy import env
env.workspace = "D:/Project"
env.overwriteOutput = True

APNlist = []
Plist = "D:/Project/Preserve_Parcels.csv" #change format??
cursor = arcpy.da.SearchCursor(Plist, ["APN8"])

for row in cursor:
    APNlist.append(row) #should I change how the list is appended?
print APNlist


infc = "D:/Project/Final.gdb/Parcels"
fieldname = "APN_8"
outfc = "D:/Project/Final.gdb/parcels_sel"
qry = """APN_8 IN {0}""".format(str(tuple(APNlist))) # Wrong syntax?


arcpy.Select_analysis (infc, outfc, qry)

I'm a python/arcpy beginner

3
  • See the update to my answer below, it should clear up some confusion as there will be a difference in how your query variable is structured based on if your APN_8 field in infc is a string or numeric field.
    – GeoSharp
    Nov 30, 2016 at 1:23
  • GeoSharp, thank you kindly for your help with this and thanks to all other editors. I have implemented your solution, my infc APN_8 is a string field. Just two issues for editing: APNlist object is spelled with a lowercase l, and I received a syntax error for sql_list = ["'" + str(x) "'" for x in APNList] until I added another + in front of the second single quote like so: sql_list = ["'" + str(x) + "'" for x in APNList] Then everything worked PERFECT. Thanks again so much, I'm now a big fan of gis stackexchange. Dec 2, 2016 at 22:24
  • Accepted, I tried upvoting but my rep is too low for public display Dec 2, 2016 at 23:00

1 Answer 1

3

When you append to your list with your cursor even though you are only searching one field you still need to index to the first field like this row[0]:

with arcpy.da.SearchCursor(Plist, ['APN8']) as cursor:
    for row in cursor:
        APNList.append(row[0])

This should fix the tuple issue you are having.

If APN_8 is a string field in infc you can create a correct SQL syntax list and query like this:

sql_list = ["'" + str(x) "'" for x in APNList]
sql_list = ','.join(sql_list)
qry = "APN_8 IN (%s)" % sql_list

If APN_8 is a numeric field in infc you can create a correct SQL syntax list and query like this:

qry = "APN_8 IN (%s)" % str(APNList)[1:-2]

By doing either of these, depending on whether APN_8 is a string or numeric field in infc, you will create a comma separated string surrounded by proper SQL syntax for a list ( ) which is what you need for the where clause in arcpy.Select_analysis, the new string representation of your list will look like this for the first syntax:

"('00391711','11006553','11007963','22010643')"

and this with the second syntax:

"(00391711, 11006553, 11007963, 22010643)"

The way you did it, directly supplying the string representation of the python to your query, will make your query contain brackets [ ] before and after your list object like this:

"[00391711, 11006553, 11007963, 22010643]"

The SQL parser that arcpy.Select_analysis uses does not know what to do with the [ ].

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.