Generate sql WHERE clause from selected features

Question

In ArcGIS Desktop, I can use various tools to interactively select features. These selection tools are handy for making complex selections (tabular or spatial) that would be otherwise cumbersome to perform.

I would like to create an SQL WHERE clause from my selected features so that I can use the query/selection in programs other than ArcGIS Desktop.

For example, using the selected features in ArcMap, create a string of OBJECTIDs:

where objectid in 
        (5982722, 2916887, 1932266, 7961145, 9767401, 
        5028271, 1280191, 1766943, 1539731, 7509119, 
        283535, 2908486, 4728132, 1292375, 1376223, 
        382195, 6840113, 1609989, 3346030, 3617504, 
        5087102, 6160716, 177817, 2271321, 3535963, 
        9511098, 739973, 1960256, 660265, 9496836, 
        6976042, 6830024, 6618473, 8688161, 9325979, 
        9736524, 5834279, 1704444, 8568749, 7174666, 
        4697259, 4614739, 8327862, 1177620, 539499)

How can I do this?

I would like the solution to be a quick & easy process (3 clicks or less), to be used many times daily.

Related:

• Creating definition query from selected features using ArcGIS Pro
• Does the “Create Layer from Selected Features” tool store cached data in the feature layer/map document?
• How is the WHERE clause stored in a 'Create Layer from Selected Features' layer?

Answer 1

I can do this using ArcPy's describe.fidset function:

1. Get a list of objectids using describe.fidset. The list is delimited with a ;
2. Replace the ; with ','
3. Concatenate where objectid in ( + the list of objectids + )

---

import arcpy
tablename = "USER.A_TEST_FC"
layerdesc = (arcpy.Describe(tablename))
whereclause = str(layerdesc.FIDSet)
whereclause = whereclause.replace(";", ",")
print "select * from " + tablename
print "where objectid in (" + whereclause + ")"

Output:

select * from USER.A_TEST_FC 
where objectid in (14115, 14233, 14237, 14263, 14508, 14603, 14771, 14936, 14955, 
15049, 15173, 15257, 15459, 15483, 15539, 15564, 15580, 15597, 15715, 15741, 15934, 
15971, 16014, 16066, 16076, 16309, 16399, 16563, 16586, 16674, 16700, 16732, 16734, 
16815, 17056, 17094, 17296, 17312, 17329, 17337, 17420, 17449, 17558, 17675, 17843, 
17849, 17959, 17977, 18071, 18187, 18232, 18269, 18289, 18321, 18345, 18362, 18491, 
18579, 18741, 19014, 19148, 19275, 19287, 19383, 19926, 20022, 20303, 20501, 20725, 
20760, 20827, 20893, 20932, 21240, 21440, 21442, 21463, 21518, 21672, 21722, 21736, 
21806, 21813, 21919, 21965, 22090, 22404, 22499, 22564, 22565, 22587, 22725, 22965, 
23009, 23351, 23377, 23705, 23862, 24056, 24097, 24146, 24151, 24153, 24295, 24297, 
24330, 24494, 24520, 24765, 24819, 24875, 24904, 25098, 25219, 25306, 25387, 25450, 
25498, 25520, 25522, 25695, 25698, 25699, 25769, 25875, 26004, 26114, 26362, 26383, 
26401, 26466, 26496, 26553, 26590, 26601, 26615, 26625, 26654, 26997, 27159, 39309, 
39330, 39403)

Answer 2

From jbalk in a related question:

OIDlist = []
with arcpy.da.SearchCursor(layer, 'OBJECTID') as scur:
     for row in scur:
         OIDlist.append(row[0])

Once I have this list, I can assemble (concatenate) a where clause, just as I did in my other answer.

Answer 3

This functionality was added in ArcGIS Pro 3.4:

• Generate Definition Query From Selection
• Selection to Definition Query