I manage a library of query layers and have recently had the need to apply the same update to a bunch of them at once. Seems the only way to do that in python is to create new ones and apply symbology from the old ones and that's fine. But in creating new ones, one of the important bits is specifying the field(s) that provide a unique identifier. There is a parameter for this in arcpy.MakeQueryLayer_management but the layers that come out of my script have the first field in the list checked rather than the one that I specified near the bottom. What am I missing?
Below is the business end of the script I wrote that contains commented examples of things I've tried to specify that parameter successfully.
arcpy.MakeQueryLayer_management( input_database = self._environment.DatabaseConnections["Database"], out_layer_name = self.LayerName, query = self.Query, # SELECT * FROM Database.Schema.View # None of these work--first field in layer gets auto-selected every time oid_fields = "FieldName", #oid_fields = "View.FieldName", #oid_fields = "Schema.View.FieldName", #oid_fields = "Database.Schema.View.FieldName", #oid_fields = "Instance.Database.Schema.View.FieldName", #oid_fields = ["FieldName"], #oid_fields = ["Instance.Database.Schema.View.FieldName"], #oid_fields = "FieldName;SecondFieldNameJustToMakeAList", #oid_fields = ["FieldName","SecondFieldNameJustToMakeAList"], #oid_fields = "YouCanBeSureThisNameAppearsNowhereInTheDatabase", shape_type = "POINT", spatial_reference = arcpy.SpatialReference(102605) )
I've also tried (to no effect):
- Running the associated tool in ArcMap produces the desired results
- Adding that tool to ModelBuilder and exporting python from there. That parameter was specified by sequence simply as "FieldName" in the exported python, but that script contained dozens of lines of python specifying seemingly every Arcpy environment setting possible and then immediately resetting them to their old values afterward. I dumped all that into my script immediately above and below the tool reference.
- Specifying the parameters by position rather than by keyword
- Source queries in the query layers in our library have always been
SELECT * FROM VIEW...and I tried specifying field names instead
The obvious hack I'd like to avoid:
Arcpy doesn't throw a "Field Not Found" sort of exception but rather just picks the first field that it encounters and keeps trucking (as if that doesn't have great potential to hose things up down the road). Consequently one might hack a solution by re-wiring the views these layers query to put the primary key first, but then there'd have to be other rigmarole to reorder fields in the layer so that users are presented info in the order they expect.