Community

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

1782
3
10-06-2022 02:35 PM
Status: Open
AndrewRudin1
by
Frequent Contributor

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.

3 Comments
AndrewRudin1
by
‎10-06-2022 02:45 PM

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.

SamSzotkowski
by
‎10-12-2022 04:01 PM

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")

 

TóthRóbert
by
‎05-31-2023 08:33 AM

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