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 of the Describe properties of the Layer object and fails to find it.

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

Answer 1

The Python CIM access - ArcGIS Pro | Documentation allows for one to get the SQL query for a Query Feature Class directly from Layer - ArcGIS Pro | Documentation without having to save the layer to a file.

# assumes a layer object reference already exists (lyr)
cim = lyr.getDefinition('V3')
for attr in "featureTable.dataConnection.sqlQuery".split("."):
    cim = getattr(cim, attr, None)
if cim:
    print(cim)

If one is willing to work with an undocumented part of Layer - ArcGIS Pro | Documentation, then obtaining the SQL query for a Query Feature Class can also be done via a layer's XML definition.

import xml.etree.ElementTree as ET

# assumes a layer object reference already exists (lyr)
root = ET.fromstring(lyr._arc_object._XML)
sql_query = root.find('.//*/SqlQuery')
if sql_query is not None:
    print(sql_query.text)

The is not None is used because xml.etree.ElementTree.Element has some odd boolean behavior, at least odd to me.

Answer 2

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 3

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.