topic Re: Arcpy Describe returns connected user as schema owner in Python Questions

Arcpy Describe returns connected user as schema owner

PhilLarkin1 — Sun, 12 Dec 2021 05:12:10 GMT

When returning the name of a Feature Class used to create a Query Layer, the schema name is incorrect. It matches the name of the user in the SDE connection file.

In this example I'd expect to the see the name with the correct schema owner: dboExample. However I get userA.

If I create a Feature Layer with the same SDE connection the correct name is returned: assrsde.dboExample.vertical_units

This seems like a bug. Tested on 10.3 and Pro 2.3 arcpy.

input = "...\\userA_Staging_ASSRSDE.sde"

query = "SELECT pid_num as parcel, Shape FROM ASSRSDE.dboExample.Vertical_Units WHERE ISNULL(PID_NUM, '') <> ''"

parcel = arcpy.MakeQueryLayer_management(input_database=input,out_layer_name="memory\\VU",query=query)

print( arcpy.Describe(parcel).baseName.lower())

# returns: assrsde.userA.vertical_units‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Arcpy Describe returns connected user as schema owner

MarcoBoeringa — Sun, 21 Apr 2019 17:20:37 GMT

I don't think this is a bug, but by design. The problem is, while your particular example of a Query Layer's SQL statement is relatively straightforward pulling just a small record set from a single Feature Class, in reality a SQL statement for a Query Layer can have many forms, it can be a statement with SUM and GROUP BY, JOIN data of multiple Feature Classes or other sub queries etc. Contrary to Feature Layers, that just reference a single Feature Class (although that might be a database view), there is no real single data source for a Query Layer, and as such, the schema is likely undefined as well.

Re: Arcpy Describe returns connected user as schema owner

PhilLarkin1 — Mon, 22 Apr 2019 15:58:58 GMT

Thanks for your reply. That makes some sense.

I guess I'll need to alias the table. Connecting as the schema owner just to get the correct name from a simple query is overkill.

Re: Arcpy Describe returns connected user as schema owner

PhilLarkin1 — Sun, 12 Dec 2021 05:12:13 GMT

I can't even alias my way out of this problem (example 1). Describe still returns the connected user as the schema owner. It even seems arbitrary which table name is returned (example 2).

input = "...\\userA_Staging_ASSRSDE.sde"

query = """SELECT pid_num as parcel FROM ASSRSDE.dboExample.vertical_units "ASSRSDE.dboExample.vertical_units" """
parcel = arcpy.MakeQueryLayer_management(input_database=input,out_layer_name="memory\\VU",query=query)

print( arcpy.Describe(parcel).baseName)

# returns: 'ASSRSDE.userA.%vertical_units'

input = "...\\userA_Staging_ASSRSDE.sde"

query = "SELECT ta.pid_num as parcel FROM ASSRSDE.dboExample.tableA ta, ASSRSDE.dboExample.vertical_units"
parcel = arcpy.MakeQueryLayer_management(input_database=input,out_layer_name="memory\\VU",query=query)

print( arcpy.Describe(parcel).baseName)

# returns: 'ASSRSDE.userA.%vertical_unis'

Re: Arcpy Describe returns connected user as schema owner

JoshuaBixby — Mon, 22 Apr 2019 18:43:32 GMT

Likely unrelated, but your output layer name is odd. Are you specifying "memory" in the name because you want the name "memory" in the layer name, or are you trying to create the layer in a specific workspace? Layers and table views are created in-memory by default, you don't have to put the in-memory workspace in the name. In fact, doing so might cause odd behavior.

Re: Arcpy Describe returns connected user as schema owner

JoshuaBixby — Mon, 22 Apr 2019 18:45:15 GMT

The results you are seeing are being driven by the connection properties on userA_Staging_ASSRSDE.sde. What are the connection properties in the connection file?

Re: Arcpy Describe returns connected user as schema owner

PhilLarkin1 — Mon, 22 Apr 2019 19:53:59 GMT

HI Joshua -

The connection is SQL Server with Database Authentication.

Re: Arcpy Describe returns connected user as schema owner

JoshuaBixby — Mon, 22 Apr 2019 20:58:37 GMT

And I am guessing the database user is "userA". At this point, I am more confused about what you are attempting to describe by using Describe the way you are rather than the results being returned. What information are you trying to get from looking up the baseName property of a feature layer?

Re: Arcpy Describe returns connected user as schema owner

PhilLarkin1 — Mon, 22 Apr 2019 21:16:37 GMT

I was expecting that arcpy.Describe would return the fully qualified name of the Query Layer. This would show the true schema owner, as opposed to the user that is connected. In the several examples i've shown my desired return from Describe().baseName:

ASSRSDE.dboExample.Vertical_Units

what I get is:

assrsde.userA.vertical_units <- User connected (Input_database parameter)

At the very least, if I am aliasing a table in the FROM clause it would be nice if that was honored as the baseName.

Re: Arcpy Describe returns connected user as schema owner

JoshuaBixby — Wed, 24 Apr 2019 15:29:33 GMT

I ask the following in all seriousness, what is your idea of a "fully qualified name" for a Query Layer? Is the fully qualified name the first table referenced in the SQL that defines the Query Layer? Or, is the fully qualified name the name of the layer file on disk when/if the Query Layer is exported as a layer file?

I think this boils down to a difference between expectations and implementation, i.e., Esri's implementation of Arcpy Describe's baseName for feature layers and table views doesn't line up with what you would like to see. That said, I don't think Esri's implementation is necessarily wrong, just poorly documented.

Looking at the Describe—ArcPy Functions | ArcGIS Desktop documentation, baseName is a Describe object property, i.e., it is a generic property and not a property specific to feature layers or table views.

Summary
The Describe function returns the following properties for all Describe objects.
Properties
Property Explanation Data Type
baseName
(Read Only)
The file base name
String

Property	Explanation	Data Type
baseName (Read Only)	The file base name	String

So, what does "file base name" mean in the context of something like feature layers or table views that don't exist as a file?

For several Describe object properties (baseName, catalogPath, path) of feature layers and table views, Esri maps the properties to the properties of the underlying data source. I personally disagree with this decision; but regardless of my personal view, Esri doesn't document this decision and it leads to confusion.

Query layers represent a unique kind of feature layer because the data source isn't a specific feature class or table, but an SQL statement. When a feature layer references a feature class, the mapping of baseName to the underlying baseName of the data source is straightforward; however, that mapping gets much more complicated when an SQL statement can reference any number of feature classes or tables.

If you have specific suggestions/ideas on how baseName should be populated with query layers, the path forward at this point is to submit an ArcGIS Ideas‌ and a formal Enhancement Request through Esri Support.

Re: Arcpy Describe returns connected user as schema owner

PhilLarkin1 — Wed, 24 Apr 2019 16:21:15 GMT

The fully qualified name is the name given in the database. This would include the schema owner rather than the connected user schema. Given the scenario I've discussed at length this is what I would expect:

baseName: If I've aliased a table in the FROM clause it should return the alias, if I haven't and there is one table in the FROM clause it should return the full name [Database].[OwnerSchema].[Object], and if for some stupid reason im selecting a cartesian product there should be well documented logic to what name is going to be returned. Regardless, the name should include the owner schema.

name: Returns the name I've given the Query Layer in the out_layer_name parameter.

I'd submit an Idea if I thought it would get enough votes to make a difference. At this point I'm assuming it won't get traction.

Re: Arcpy Describe returns connected user as schema owner

JoshuaBixby — Wed, 24 Apr 2019 19:12:19 GMT

As much as you may be right about the Idea not getting traction, there are lots of factors that motivate developers, and maybe being made aware of the issue might get one of them thinking about it.

How do databases name SQL queries from clients that don't persist as tables, views, etc... in the database? If you created an SQL statement and saved it as a view in the database, it still wouldn't give you want you want because what is returned would be the schema containing the view and not what schemas are referenced in the SQL defining the view.

Re: Arcpy Describe returns connected user as schema owner

PhilLarkin1 — Wed, 24 Apr 2019 21:58:21 GMT

I don't think there is a parallel in MSSQL. Results are passed to clients without generating a schema in the database. I couldn't pass a query and find some log parameter that would show a schema name for my connection.