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?

• Vince: If you iterate all the Describe properties and it's not there, you might need to dump it to a layer file and scan the file as a byte stream of UTF-16 characters (sort of like an od -a)
• PolyGeo: @Vince I added code above to iterate all the Describe properties of the Layer object but that too fails to find the SQL code so I may need to resort to your last suggestion. I'm assuming that "dump it to a layer file" is just a Save As Layer File but how do I "scan the file as a byte stream of UTF-16 characters (sort of like an od -a) "? I think that may be beyond me as a Python-only programmer.
• Xeppit: You can rename the *.lyrx file to *.json and open it. pro.arcgis.com/en/pro-app/latest/arcpy/geoprocessing_and_python/…
• Vince: The .lyr files were binary, with a mixture of float, int, and string. I looked for sequences of UTF-16 characters 0x00 + ASCII/ASCII + 0x00 longer than four bytes. It was among the uglier blocks of code I had written for a customer in 35 years. If .lyrx is JSON, then this is now dirt easy, with a json.loads() on the UTF-16 character stream from the file (probably little-endian). I'll check this out when I fire up my laptop in a few hours. Might even blog it in Community.
• PolyGeo: Yeah - I'm thinking that @Xeppit has indicated a way forward that may be accessible to my skillset.

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.