topic Re: Definition Query in Python Questions

Definition Query

StephenKruzik — Thu, 19 Apr 2012 12:32:01 GMT

I know this probably the upteenth time a question like this has been asked, but my non-programming mind can't seem to wrap its head around it..

I'm using data driven pages to plot towersites in an area. Each page goes to a new site based on it's SiteID. I only want the map to show the site that I'm currently on. Presently, it shows all of the sites, some of which are very close to the current one. I ran through a stand alone script and it went through fine without any errors, but it didn't return the definition query, so the exported png still had all the sites on each map.

what am I doing wrong?

[HTML]# Workspace
import os
import arcpy
import arcpy.mapping

mxd = arcpy.mapping.MapDocument("C:\GIS Data\Schuylkill\Validations.mxd")
mapLyr = arcpy.mapping.ListLayers(mxd, "Cell_Sites")[0]
carryElem = arcpy.mapping.ListLayoutElements(mxd, "TEXT_ELEMENT","Carriers")[0]

# activate data driven pages
ddp = mxd.dataDrivenPages
pageNum = ddp.currentPageID
print pageNum
contents = range(1,ddp.pageCount)
print contents

# set active view to first ddp, then iterate through each page
while pageNum in contents:

    print "we got to the while statement"

    # single out the one you are working on
    pageName = mxd.dataDrivenPages.pageRow.CellSiteID
    Exporter = mxd.dataDrivenPages.pageRow.LDTProvider
    df = arcpy.mapping.ListDataFrames(mxd, "Sites")[0]
    CurrentSite = arcpy.mapping.ListLayers(mxd, "Cell_Sites", df)[0]
    CurrentField = "CellSiteID"
    arcpy.MakeFeatureLayer_management("F:\GIS Data\Schuylkill County\Schuylkill MasterData.gdb\Validations\Cell_Sites", "IndexSites")

    for lyr in arcpy.mapping.ListLayers(mxd):
        if lyr.name == CurrentSite:
            lyr.definitionQuery = '"CellSiteID" = %s' % pageName[/HTML]

There is more after this, but for space conservation, I only pasted up to the def. query

As an aside, if you get me to where I need to be with this, and it works, I'll happily name a variable after you in my next script! 🙂

Re: Definition Query

BruceNielsen — Thu, 19 Apr 2012 12:50:16 GMT

I think you've just about got it. Try this:

lyr.definitionQuery = '"CellSiteID" = \'%s\'' % pageName

The value in a definition query needs to be surrounded by single quotes. The backslashes tell Python to use the next character as part of the string.

Re: Definition Query

JeffBarrette — Sun, 12 Dec 2021 00:22:46 GMT

Or you can also do it with double quotes. Either way, you'll need to use an escape character.

lyr.definitionQuery = "\"CellSiteID\" = '%s'" % pageName

Jeff

Re: Definition Query

StephenKruzik — Mon, 23 Apr 2012 17:28:01 GMT

thanks all!

it's working now.

and for anyone interested, since I know I'm going to constantly forget how many ",',/ I need, and where, I saved a python module that has the syntax pre-set. It's just a single function, so cut and paste as needed. All you need is the field, operator, and value. Feel free to use if it helps

[HTML]def DQ(name, operator, value):
sql = "\"" + name + "\"" + operator + "'" + value + "'"
return sql[/HTML]

just note that the value field is defined for a string, not an integer. If you use this querying integers, you'll need to get rid of the 2 single quotes at the end.

Re: Definition Query

curtvprice — Sun, 12 Dec 2021 00:22:48 GMT

I saved a python module that has the syntax pre-set

What a neat idea! Your function can be easily tweaked up to work with all data sources (mdb's use brackets) using the arcpy [URL=http://help.arcgis.com/en/arcgisdesktop/10.0/help/000v/000v0000004n000000.htm]AddFieldDelimiters[/URL] function. I also added a tweak to add quotes around strings and handle IS NULL constructions:

def BuildQuery(table, field, operator, value=None):
    """Generate a valid ArcGIS query expression
    
    arguments
    
      table - input feature class or table view
      field - field name (string)
      operator - SQL operators ("=","<>", etc)
         "IS NULL" and "IS NOT NULL" are supported
      value - query value (optional)
      
    examples
    
      >>> BuildQuery("x.dat","FIELD1","=", "spam")
      u'"FIELD1" = \'spam\''
      >>> BuildQuery("x.dat","FIELD1",">",42)
      u'"FIELD1" > 42'
      BuildQuery(r"E:\tables.mdb\table1","PwsID","IS NOT NULL")
      u'[PwsID] IS NOT NULL'
    """
    # Use correct field delimeter (depends on table type)
    # For 9.3, use "gp" instead of "arcpy" in the following line
    qfield=arcpy.AddFieldDelimiters(table,field)

    # tweak value delimeter for different data types
    if type(value) == type("string"):
        # add single quotes around string values
        qvalue = "'" + value + "'"
    elif value == None:
        # drop value when not specified (used for IS NULL, etc)
        qvalue = ""
    else:
        # numeric values are fine unmodified
        qvalue = value
    
    sql = "%s %s %s" % (qfield,operator,qvalue)
    return sql.strip()

A few helpful references for the good of the order

Arc 10.0 help:

Building a query expression
Specifying a query in Python

ArcGIS Map Automation forum

Re: Definition Query

StephenKruzik — Mon, 30 Apr 2012 14:20:37 GMT

ok, so I thought I had this, but after running through it I discovered a problem and can not for the life of me figure out what's wrong.

The definition query itself is running through fine, but it doesn't produce anything. There is no change to the featurelcass. After lots and lots of digging, I figured out it's within the defquery section of Arcpy itself.

I turned on Python's debugger, and when it gets to the def. query, it throws me an "AttributeError: 'unicode' object has no attribute '_arc_object'"

I'm really unsure what it's looking for. If I change it to string, it gives the same error, just with 'str' instead of unicode. Is my featureclass not being referenced properly, does it not like variables within the query?

here's the snippet that I'm running through...

[HTML]
while pageNum in contents:
    pageName = mxd.dataDrivenPages.pageRow.CellSiteID # data driven page name
    Exporter = mxd.dataDrivenPages.pageRow.LDTProvider # which carrier you are on right now
    df = arcpy.mapping.ListDataFrames(mxd, "Sites")[0] # there's 2 data frames, so call the 1st one
    CurrentSite = arcpy.mapping.ListLayers(mxd,"*Cell_Sites*",df) # get the list of current layers
    LayerSite = CurrentSite[0] # pull Current site out of the list as its own layer
    CurrentField = "CellSiteID" # field used to isolate the F.class
    Othercarriers = arcpy.MakeFeatureLayer_management("F:\GIS Data\Schuylkill County\Schuylkill MasterData.gdb\Validations\Cell_Sites", "IndexSites")
    sql = dquery.DQ(CurrentField,"=",pageName)
    check = LayerSite.supports("DEFINITIONQUERY")
    print check
    LayerSite.definitionQuery = str(sql)
    arcpy.RefreshActiveView()[/HTML]

the variable for sql is using the function I defined in my previous post (though I definitely like the newer, more robust version). Some other baseinfo: I have a featureclass called "Cell_Sites". The CellsiteID is the field driving the dd pages. For each map, I only want the current cell site displayed on the map, and no others. Any help is greatly appreciated. I'm at my wits end here.

Re: Definition Query

curtvprice — Mon, 30 Apr 2012 14:36:43 GMT

If I change it to string, it gives the same error, just with 'str' instead of unicode.

Unless you're using international characters, string and unicode are really the same thing.

What's the contents of the sql variable? The best way to get an exact story is to use repr:

print repr(sql)

Re: Definition Query

StephenKruzik — Tue, 01 May 2012 10:38:55 GMT

when using print repr(sql),

[HTML]>>> print repr(sql)
u'"CellSiteID"=\'1202\''
>>> [/HTML]

in the Python Shell window.

so the variables are definitely calling correctly, as that's what I expect to see.

that line is then getting passed on to the definition query itself.

Re: Definition Query

curtvprice — Wed, 02 May 2012 15:00:55 GMT

I highly recommend putting spaces in there.

Re: Definition Query

StephenKruzik — Wed, 02 May 2012 17:06:55 GMT

no dice. It's still throwing the same error. I have to believe it was simply syntax, then it would have still put it in, and just throw up a "failed to draw: invalid sql reference" error.

I actually tried up to this point with ArcMap open. I changed the mxd to CURRENT, and when I got to the def query, again, it went through without giving any errors, but when I click on the featureclass, the def. query box is empty as though nothing happened.

This is actually why I went into the python debugger and stepped through the Arcpy module itself. That is where I found the "AttributeError: object has no attribute '_arc_object'" error being called.

Re: Definition Query

curtvprice — Wed, 02 May 2012 17:26:21 GMT

That is where I found the "AttributeError: object has no attribute '_arc_object'" error being called.

Looking at the error message, I'm wondering if it's recognizing your layer LayerSite - though you'd think it would fail on your check

check = LayerSite.supports("DEFINITIONQUERY")

Might want to run this one by support.