Possibility to change query layer definition with arcpy (not definition query)

1252
9
02-13-2014 08:38 AM
Status: Implemented
Labels (1)
PiotrIwaniuk
New Contributor III

Now I cannot do that. I need to change schema name (schema.tablename) in big set of symbolized layers. Repair datasource of course doesn't work, definition query is different thing as well. I realized that I can't automate my task with arcpy.

9 Comments
RonnieRichards
Agree. Query Layers do not work well when things change such as the database, server or schema. Especially when they are part of map services. We basically have to recreate them between dev/test/production environments and its painful since there is no layer copy option to copy the layer properties for symbology, def queries, labeling, etc... 
RonnieRichards
Agree. Query Layers do not work well when things change such as the database, server or schema. Especially when they are part of map services. We basically have to recreate them between dev/test/production environments and its painful since there is no layer copy option to copy the layer properties for symbology, def queries, labeling, etc... 
MichaelVolz
This would be an extremely useful feature as my organization is looking to build an operational database using ST_Geometry where this data would be accessed in ArcGIS Server services and ArcMap layers using Query Layers.  The data would be stored in an Oracle database (11g to start) and my organization would eventually upgrade the database to Oracle 12c so the database connection informationof the query layers would need to be updated.
VitalijusAgejevas1
Agree, missing arcpy automation functionality for changing query layer datasource and other properties. Very painfull when migrate between dev/test/prod environments, need to fix layer connections manually.
KatieBrander
Agree. We are currently migrating from an Oracle 10g database to Oracle 11g and have many mxd's to update. Arcpy allows us to script and automate some of the layer datasources, but we cannot totally automate the process due to this missing functionality.
MichaelVolz

Is there any update on this idea with python 3.x that is shipped with Pro?

KoryKramer

None that I know of.

JeffBarrette
Status changed to: In Product Plan

A fix was made in Pro 3.2 that enables modifying query layers via Python CIM Access.  We will post a sample to this page.

JeffMoulds
Status changed to: Implemented

This is possible using ArcGIS Pro version 3.2. In the Updating Data Sources help topic, go to the CIM Section, and check out the third code sample:

# Reference project, map and layer 
p = arcpy.mp.ArcGISProject(r'C:\Projects\USA.aprx')
m = p.listMaps('USA')[0]
l = m.listLayers('States')[0]

# Get the layer's CIM definition
lyrCIM = l.getDefinition('V3')         

# Update the sql query where clause for the layer
sql = lyrCIM.featureTable.dataConnection.sqlQuery
newsql = sql.replace("WHERE SUB_REGION = 'Mtn'", "WHERE SUB_REGION = 'Pacific'")
lyrCIM.featureTable.dataConnection.sqlQuery = newsql
    
# Set the layer's CIM definition
l.setDefinition(lyrCIM)

 

The above sample changes the sql query, but other properties of the Query Layer can also be updated.