topic Re: Replace Definition Query in Python Questions

Replace Definition Query

SallieVaughn — Wed, 16 Dec 2015 13:31:21 GMT

I've got a series of MXDs with definition queries with file gdb-specific syntax. I'd like to batch modify those DQs to work with enterprise gdb syntax. Not every layer has a DQ, and not all DQs are the same. The problem I'm running into is with the expressions portion... the function is passing 'SHAPE.STLength()' with the quotes to the definition query, which doesn't match the syntax (need it without the quotes). Changing them to double quotes returns similar results, and removing the quotes all together causes the function to fail.

Any ideas?

import arcpy

from arcpy import env

from os import path

env.workspace = ws = r"C:\temp\DefQueryScript\test1"

expressions = {

'shape_length' : 'SHAPE.STLength()',

'shape_area' : 'SHAPE.STArea()',

'SHAPE_Length' : 'SHAPE.STLength()',

'SHAPE_Area' : 'SHAPE.STArea()',

'SHAPE_length' : 'SHAPE.STLength()',

'SHAPE_area' : 'SHAPE.STArea()',

}

def letsGetThisPartyStarted():

''' For each Map Document in the workspace, check each layer for a

definition query, and if found, update it according to the contents of

the expression dictionary.'''

print "Let's get this party started!\n"

mxds = getMXDs()

for i, mxd in enumerate(mxds):

for layer in getLayers(mxd):

evaluateExpression(layer)

saveMXD(mxd, i)

def getMXDs():

''' Create a list of each Map Document in the workspace(s).'''

mxds = [arcpy.mapping.MapDocument(m) for m in arcpy.ListFiles("*.mxd")]

return mxds

def getLayers(mxd):

''' Collects all feature layer in a Map Document.'''

print "{0} layers:\n".format(mxd.filePath)

layers = [l for l in arcpy.mapping.ListLayers(mxd) if l.isFeatureLayer]

return layers

def evaluateExpression(lyr):

''' If the layer has a Definition Query, update that query using the

dictionary of expressions.'''

if lyr.definitionQuery:

for exp in expressions.items():

key, value = exp[0], exp[1]

if key in lyr.definitionQuery:

updateDefinitionQuery(lyr, key, value)

else:

print " {0}: No Definition Query found\n".format(lyr.name)

pass

def updateDefinitionQuery(lyr, old_exp, new_exp):

''' Replaces the old expression with the new one. Fill in the old/new

associations in the "expressions" dictionary above.'''

print " {0}:".format(lyr.name)

print " Old expression: {0}".format(lyr.definitionQuery)

lyr.definitionQuery = lyr.definitionQuery.replace(old_exp, new_exp)

print " New expression: {0}\n".format(lyr.definitionQuery)

def saveMXD(mxd, i):

''' Saves a copy of the Map Document which has had it's layers Definition

Queries updated.'''

mxd.saveACopy(path.join(ws, "COPY_" + mxd.filePath + "_{0}.mxd".format(i)))

del mxd

if __name__ == "__main__":

letsGetThisPartyStarted()

print "'fin.'"

Re: Replace Definition Query

JoshuaBixby — Wed, 16 Dec 2015 15:06:06 GMT

I just tried your code on an MXD I threw together and everything worked fine, the definition query was updated properly and not extraneous quotes were included. At some point is the code generating an error on your end? If so, sharing the specific error message/output might be helpful to others.

Re: Replace Definition Query

SallieVaughn — Wed, 16 Dec 2015 15:20:29 GMT

Thanks for the reply. The code works fine in that it runs without errors, but for whatever reason it's keeping those quotes in the resulting definition query. Using the code I provided above, my expected result is: SHAPE.STLength() and I'm getting: 'SHAPE.STLength()' which is a syntax error in the definition query.

I've tried swapping out double quotes/single quotes and the code will bring over either one that is used in the script. Removing the quotes altogether generates the following error:

File "C:\temp\DefQueryScript\ReplaceStatement.py", line 25, in <moldule>

'shape_length' : SHAPE.STLength(),

NameError: name 'SHAPE' is not defined.

which is to be expected.

If I go into the MXD and create the same definition query manually, no quotes are used. Is it possible there is a setting somewhere that allows DQs to run without the quotes or would put them back in?

Re: Replace Definition Query

JoshuaBixby — Wed, 16 Dec 2015 16:20:10 GMT

Are you processing shape files, by chance? Since your code worked fine on a feature class I had in a file geodatabase, it got me thinking about different storage formats having different field delimiters. I know shape files use quotes, and I think file geodatabases used to or still do in some circumstances.

Anyhow, I wonder if the quotes are already there and you are substituting your expression within the existing quotes, so it looks like you are passing quotes when you are not actually doing so. Since I don't have one of your MXDs to look at, that is the best guess I can come up with. Otherwise, I am stumped.

Re: Replace Definition Query

SallieVaughn — Wed, 16 Dec 2015 16:32:19 GMT

No, I'm in an SDE environment with SQL Server 2008 R2 on the back end. I wondered about the storage format too.

I'll change up the quotes and see if I can get anything to stick -- the obvious choice is to remove them completely, but that throws the syntax error. I'm wondering if the .replace function just isn't meant to function this way.

Re: Replace Definition Query

DanPatterson_Retired — Fri, 25 Dec 2015 23:06:48 GMT

Updating and fixing data sources with arcpy.mapping—Help | ArcGIS for Desktop link has a bunch of stuff towards the bottom going through the hoops you need to use to get definitionQuery to work with. it is about 2/3rds of the way down. might apply