idea In Arcpy, add a way to detect query layers and their query definition in aprx files in Python Ideas

In Arcpy, add a way to detect query layers and their query definition in aprx files

AndrewRudin1 — Thu, 06 Oct 2022 21:35:15 GMT

We have several aprx files in our organization, and I'm trying to iterate over them and determine the data sources for each layer in them. We have some query layers that are commonly used throughout our organization for viewing certains datasets in our enterprise geodatabases.

Currently when I use Arcpy.Describe on these query layers, the dataset type returned is 'FeatureLayer, which isn't accurate. Through the Pro UI, this same layer shows a type of "Query Feature Layer" when I inspect it's properties. The propertie window also shows the actual database query used by the layer, a property that is unique to query layers. It would be great if query layers returned a unique dataType when being described so I know to process them a little differently. Then it would also be nice if the describe object returned also included a "Query" property that contains the text of the SQL query. This way I can parse that text and determine what table (or multiple tables) are referenced by this map layer.

Re: In Arcpy, add a way to detect query layers and their query definition in aprx files

AndrewRudin1 — Thu, 06 Oct 2022 21:45:36 GMT

I'd also add that query layers that are not spatial should be included as well. Not sure if those kinds of views will appear as "Standalone Tables" instead of Feature Layers, but I think they should be included as well, so their data source can be parsed in python as well.

Re: In Arcpy, add a way to detect query layers and their query definition in aprx files

SamSzotkowski — Wed, 12 Oct 2022 23:06:08 GMT

This is a very janky solution, but maybe it will work for you. Documentation is poor/nonexistent for layers' CIM properties, but I tend to find what I need if I dig around in there long enough using dir() and IDE autofill. Here's an example that might be useful:

# the "sqlQuery" property only exists for query layers, far as I can tell def get_query(layer): cim = layer.getDefinition("V3") f_table = cim.featureTable data_conn = f_table.dataConnection if hasattr(data_conn, "sqlQuery"): return data_conn.sqlQuery return None def main(): aprx = arcpy.mp.ArcGISProject("current") active_map = APRX.activeMap layer = active_map.listLayers("my layer")[0] query = get_query(layer) if query: print(f"{layer.name} is a query layer: {query}") else: print(f"{layer.name} is not a query layer")

Re: In Arcpy, add a way to detect query layers and their query definition in aprx files

TóthRóbert — Wed, 31 May 2023 15:33:11 GMT

unfortunately the cim is not properly documented, but I suggest you open aprx with ctrl+PgDn, find a specific example of what you want to query properties about (e.g. TableViews) and see what tags are in the json.

If you know what you can get from "cim", you can user cim_object.tag_group.tag_subgroup.property... to get a value what you interested:

for example:

aprx = arcpy.mp.ArcGISProject("current")
maps = aprx.listMaps()
for map in maps:
lyrs = map.listTables()
for lyr in lyrs:
desc = arcpy.Describe(lyr)
cim = lyr.getDefinition("V3")
print (map.name + "||" + lyr.name + "||" + cim.dataConnection.sqlQuery)

EVERYTHING that the map can store, you will find in the map's json file as text.
If the json schema gets too cluttered, export a map as a mapx file, which is essentially a json directly, and you will certainly find everything there