Getting SQL that defines Query Feature Class using ArcPy with ArcGIS Pro

Question

A map in my ArcGIS Pro 3.0.3 project has a layer with a Query Feature Class as its source. I can see the SQL that defines the Query Feature Class by looking at its Layer Properties. That SQL is based on tables in an SQL Server Enterprise Geodatabase.

I can get at many properties of the layer using the test code below:

import arcpy

aprx = arcpy.mp.ArcGISProject(r"\\testfolder\testproject.aprx")
mapx = aprx.listMaps("Test Map")[0]
lyrx = mapx.listLayers("Test Layer")[0]
arcpy.AddMessage(f"Layer Name: {lyrx.name}")
arcpy.AddMessage(f"Data Source: {lyrx.dataSource}")
arcpy.AddMessage(f"Definition Query: {lyrx.definitionQuery}")
arcpy.AddMessage(f"Connection Properties: {lyrx.connectionProperties}")
# definition_queries = lyrx.listDefinitionQueries
arcpy.AddMessage(f"Definition Queries: {lyrx.listDefinitionQueries()}")

# Create a Describe object from the feature layer.
#
desc = arcpy.Describe(lyrx)

# Print some properties of the feature layer, and its featureclass.
#
arcpy.AddMessage("Name String:        " + desc.nameString)
arcpy.AddMessage("Where Clause:       " + desc.whereClause)
arcpy.AddMessage("Feature class type: " + desc.featureClass.featureType)

desc = arcpy.da.Describe(lyrx)
for k, v in desc.items():
    arcpy.AddMessage(f"{k}: {v}")

None of these are able to access the SQL code that defines the Query Feature Class. The last part of the code iterates all the Describe properties of the Layer object and fails to find it.

Is there a way to bring the SQL code that defines the Query Feature Class into a Python variable for me to work with?

Answer 1

Back in ArcMap, the .lyr file was binary, with a mixture of float, double, short, int, and UTF-16 strings. With ArcGIS Pro (as @Xeppit indicated in comments), the .lyrx is JSON. It's even UTF-8 JSON, making it that much easier to read.

If you write a singleton layer with arcpy.Layer.saveACopy(), you're looking for the sqlQuery key in the dataConnection key in the featureTable key in the first layerDefinitions key.

The ArcPy code for this could look like:

import os
import json
import uuid

import arcpy

tmp_name = os.path.join(r'C:\Temp',format("{:s}.lyrx".format(str(uuid.uuid4()))))
p = arcpy.mp.ArcGISProject('current')
m = p.listMaps()[0]
for i,l in enumerate(m.listLayers(),start=1):
    if l.supports('DATASOURCE'):
        l.saveACopy(tmp_name)

        with open(tmp_name,'rb') as fd:
           s = fd.read().decode('UTF-8')
           d = json.loads(s)

           if 'layerDefinitions' in d:
               l0 = d['layerDefinitions'][0]
               if 'featureTable' in l0:
                   ft = l0['featureTable']
                   if 'dataConnection' in ft:
                       dc = ft['dataConnection']
                       if 'sqlQuery' in dc:
                           print("{:5d} -".format(i))
                           print("{:>6s}: {:s}".format('Layer',l.name))
                           print("{:>6s}: {:s}".format('SQL',dc['sqlQuery']))

        os.remove(tmp_name)

With output something like:

    1 -
 Layer: gis311.vince.test64inta
   SQL: select objectid,nominal,shape from gis311.vince.test64inta
    2 -
 Layer: gis311.vince.test64intb
   SQL: select objectid,nominal,shape from gis311.vince.test64intb
    3 -
 Layer: gis311.vince.test64intc
   SQL: select objectid,nominal,shape from gis311.vince.test64intc

But if you're willing to be bold, you could replace the l.supports() block with:

    try:
        l.saveACopy(tmp_name)

        with open(tmp_name,'rb') as fd:
           s = fd.read().decode('UTF-8')
           d = json.loads(s)
        sql = d['layerDefinitions'][0]['featureTable']['dataConnection']['sqlQuery']
        print("{:5d} -".format(i))
        print("{:>6s}: {:s}".format('Layer',l.name))
        print("{:>6s}: {:s}".format('SQL',sql))

        os.remove(tmp_name)
    except:
        pass

Answer 2

I think the ArcGIS Idea at In ArcPy, add a way to detect query layers and their query definition in aprx files, if implemented, might make the workaround in @Vince's answer unnecessary.

The description of that ArcGIS Idea starts:

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.