How to make a variable query in SearchCursor?

Question

NearFIDlst = [33950, 7700, 4340]

I try to make a search query based on the values in the list NearFIDlst.

f=0
        while f <len(NearFIDlst):

Query = '"IN_FID"' +'='+ "'" + NearFIDlst + "'"
            tempcur = arcpy.SearchCursor(NearTable, Query)
            tcur = tempcur.next()

the feedback keeps telling "TypeError: cannot concatenate 'str' and 'int' objects"

when I change the Query to:Query = '"IN_FID"' + " = '" + str(NearFIDlst) + "'"

it gives me "RuntimeError: ERROR 999999: Error executing function."

please help~~, Thanks lot

KimOllivier · Answer

The SQL query using a IN (list) is really successful and is very fast, avoiding having to loop over each item.

The trick is to format the SQL as a string and surround it with round brackets " ID IN (1,2,3)"
If the item is a string then you have to surround each element with single quotes "CODE IN ('1','2','3')"

[Note that if you use "normal" field names without spaces special characters etc etc then you do not need to surround the field name with double quotes, making expression assembly easier]

I recommend that you create the expression as a string and print it out to check for debugging.
Then the error message has an easy feedback and you will see your logic error easily.#
NearFIDlst = [33950, 7700, 4340]

sqlQuery = "FID in (" + ','.join([str(x) for x in NearFIDlst]) +")"
print sqlQuery

codelst = ["abc","xyz","123"]
sqlQuery2 = "CODE in (" + ','.join(["'"+x+"'" for x in codelst]) +")"
print sqlQuery2

FID in (33950,7700,4340)
CODE in ('abc','xyz','123')

BruceNielsen · Answer

If you don't need f as a counter anywhere else, you could do something like this instead:for f in NearFIDlist:
    Query = '"IN_FID" = %i' % f
    tempcur = arcpy.SearchCursor(NearTable, Query)
    tcur = tempcur.next()

WillJames · Answer

NearFIDlst = [33950, 7700, 4340]

I try to make a search query based on the values in the list NearFIDlst.

f=0
        while f <len(NearFIDlst):

Query = '"IN_FID"' +'='+ "'" + NearFIDlst + "'"
            tempcur = arcpy.SearchCursor(NearTable, Query)
            tcur = tempcur.next()

the feedback keeps telling "TypeError: cannot concatenate 'str' and 'int' objects"

when I change the Query to:Query = '"IN_FID"' + " = '" + str(NearFIDlst) + "'"

it gives me "RuntimeError: ERROR 999999: Error executing function."

please help~~, Thanks lot

Without having the full script and dataset it's a bit hard to say what the problem is but I have had issues with variable inputs in SQL that and making them strings. It's at least worth a try adding a second set of quotes to your input variable so:

f=0
        while f <len(NearFIDlst):

Query = '"IN_FID"' +'='+ "'" + str(NearFIDlst) + "'"
            Query1 = "\"" + Query +"\""
            tempcur = arcpy.SearchCursor(NearTable, Query1)
            tcur = tempcur.next()