Wednesday, December 19, 2012

Working with a Definition Query, ArcPy and Python

I recently taught a class in Portland, OR where the students asked about changing a definition query in Python. Well, I thought this was simple -- and it is, but I didn't get there the easy way! I would assume that others may also have this challenge so I thought I would post this little challenge.

First, I thought I would set a varible to an existing definition query (so I'd see exactly how one looks as it comes from ArcPy). I have a map document that contains a layer named "Survey Grid Boundary" with a definition query.

In the python window, I typed the following lines:

>>> mxd = arcpy.mapping.MapDocument("CURRENT")
>>> layers = arcpy.mapping.ListLayers(mxd,"Survey*")
>>> print layers[0].definitionQuery
"TOWNSHIP_C" = 'C7' AND "RANGE_CD" = '2'

Cool, I thought! This is easy! So then I thought I could just change the statement with a query held in a variable. I just wanted to change the last number from a 2 to a 3. So I entered (and I knew this would cause troubles):

>>> query = "TOWNSHIP_C" = 'C7' AND "RANGE_CD" = '3'
Parsing error SyntaxError: invalid syntax (line 1)
Of course we all know that python uses quotes (either " or ') to enclose strings. Dummy me immediately thought of the str() function. WRONG! Then I thought of just quoting everything in quotes. WRONG! Then I started searching for a function to quote complex strings. WRONG!!! I was getting my self into a rabbit hole...

So then a basic issue hit me... the issue about path names and using the backslash '\'. And we've learned that the backslash is an escape character. So I came up with:

>>> query = '\"TOWNSHIP_C\" = \'C7\' AND \"RANGE_CD\" = \'3\''
>>> layers[0].definitionQuery = query

>>> print layers[0].definitionQuery

"TOWNSHIP_C" = 'C7' AND "RANGE_CD" = '3'

It works perfectly! And, it's pretty darn simple.

So just remember the the backslash can be your friend and there are two single quotes at the end of the variable assignment line rather than a double quote. 

Using Jeff's comment, I thought I would respond to his post and put the statements into courier font to read easier... well, it didn't work so I'm adding that here where I can control font. Thanks, Jeff!

query = '"TOWNSHIP_C" = \'C7\' AND "RANGE_CD" = \'3\''


query = """"TOWNSHIP_C" = 'C7' AND "RANGE_CD" = '4'"""

So, which is cleaner and easier to read??? It probably comes down to your choice. That's fun with Python!

Thank you, Jeff.


  1. Nice post Bruce. You really only need to "escape" the single quotes in your example above. query = '"TOWNSHIP_C" = \'C7\' AND "RANGE_CD" = \'3\''. Or your could put the whole where clause in triple double quotes and you won't have to worry about escaping anything - query = """"TOWNSHIP_C" = 'C7' AND "RANGE_CD" = '3'"""

    It's hard to decipher the different quotes in this comment text, but if you copy and paste them into a python shell, it will be easier to see.



    1. Jeff,

      You are entirely correct. Quoting strings can be done a number of different ways. Guess I got a little carried away with my escaping. I took your examples and tried them just to make sure. I thought I would post them here again using the courier type set to show the quoting a bit better.

      query = '"TOWNSHIP_C" = \'C7\' AND "RANGE_CD" = \'3\''


      query = """"TOWNSHIP_C" = 'C7' AND "RANGE_CD" = '4'"""

  2. query = "TOWNSHIP_C = 'C7' AND RANGE_CD = '3'"

  3. with triple quotes you can increase readability by using line returns:

    query = """
    "TOWNSHIP_C" = 'C7' AND "RANGE_CD" = '4'

    Also you can use triple-singles too (my preference):

    query = '''
    "TOWNSHIP_C" = 'C7' AND "RANGE_CD" = '4'