Reference ObjectID in a where clause within an UpdateCursor

Question

I have a dynamic list of ObjectID values I need to filter an UpdateCursor on. I am setting a where_clause as:

where_clause_points = "[ObjectID] IN({0})".format([x for x in inspected_points])

Which produces a string something like:

[ObjectID] IN([87, 88, 89, 90])

I then use this where clause in an UpdateCursor to update only the features in that list of ObjectID's:

with arcpy.da.UpdateCursor(lines_backup, fields, where_clause_points) as cursor:
 for row in cursor:
 row[1] = 'processed'
 cursor.updateRow(row)

But this produces the error:

...RuntimeError: Attribute column not found [42S22:[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column name '87, 88, 89, 90, 91, 92'.]...

or if using some other value for ObjectID:

RuntimeError: Attribute column not found [42S22:[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column name 'OID'.]

Any sort of value I use for ObjectID (OBJECTID, OID, OID@, etc.) throws a similar error.

How can I reference the ObjectID in this where clause where the value is variable and in a list of possible values?

Environment: Python 3.6.5 (stand alone script) ArcGIS 10.6 Microsoft SQL Server db

• Michael Stimson: "ObjectID IN({0})".format(','.join([x for x in inspected_points])) is more SQL correct, this will expand to ObjectID IN (87, 88, 89, 90) which is correct SQL syntax for numbers. the square brackets are not needed in the in list, neither for the field name in a select statement - using field delimiters limits portability to other feature types for no benefit in selection. Note that you can get your OID field name from describing your feature class: arcpy.Describe(fc).OIDFieldName
• m.Walker: @MichaelStimson that worked! I did have to convert the x to str(x) but that did output a query string of "ObjectID IN(87, 88, ...) and seemed to work as desired. Thanks! I'll also try Bera's suggestion as well, as there may be variance in the naming of that field in different environments/dbs...

Answer 1

Depending on input source, the objectid field will have different names. You can use Describe to get the correct name, and the code will work with all different inputs.

Combine this with the tuple data type to get correct SQL syntax, [1,2,3] to (1,2,3).

"features" is a feature layer:

ids = [1,2,3,4,5]
tuple(ids)
(1, 2, 3, 4, 5)
oidfield = arcpy.Describe("features").OIDFieldName
oidfield
u'OBJECTID'
sql = "{0} IN{1}".format(arcpy.AddFieldDelimiters("features",oidfield),tuple(ids))
sql
'"OBJECTID" IN(1, 2, 3, 4, 5)'
with arcpy.da.UpdateCursor("features", ["somefield1","somefield2"], sql) as cursor:
 ...

• m.Walker: that worked as well! I'm not fully understanding the syntax you used, mainly the triple quoting the sql string portion, but do see how you made the list of possible, and unknown length, values into a format acceptable to the IN statement along with the proper OID format coming from the esri delimiter. Nice!
• Bera: @m.Walker nice! I dont think it makes any difference with three quotes or one.