2

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?

5
  • 1
    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)
    – Vince
    May 7 at 11:38
  • @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.
    – PolyGeo
    May 8 at 2:31
  • 1
    You can rename the *.lyrx file to *.json and open it. pro.arcgis.com/en/pro-app/latest/arcpy/geoprocessing_and_python/…
    – Xeppit
    May 8 at 5:20
  • 1
    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.
    – Vince
    May 8 at 10:04
  • Yeah - I'm thinking that @Xeppit has indicated a way forward that may be accessible to my skillset.
    – PolyGeo
    May 8 at 11:54

2 Answers 2

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
2
  • Your first code works so well that I have not tried the second code block because I try to avoid try/except, except when there's a compelling reason to use that construct.
    – PolyGeo
    May 9 at 23:38
  • 1
    It's actually faster, if the dictionary is as expected, to go gung-ho inside a try. It's a pythonism that it's "Easier to ask for forgiveness than permission" aka EAFP. Old dog, new tricks.
    – Vince
    May 10 at 0:04
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.