arcpy.SelectLayerByAttributes Where Clause

[JoeBorgione]

In post earlier today, (Another da.SearchCursor Issue ) through an oversight on my part I couldn't execute a select layer by attributes function. Silly me....  At rate, I've moved past that issue but now faced with constructing a where clause that involves membership in a dictionary.

Essentially, I've constructed a dictionary of street types that looks something like this:

typeDict = {"AVE":1,"BLVD":2,"CIR":3}  #etc, etc‍‍‍‍

 As best as I can tell, the way to  see if a given string is a member of a dictionary, one can :

>>> typeDict
{'BLVD': 2, 'AVE': 1, 'CIR': 3}
>>> "AVE" in typeDict
True
>>> "ST" in typeDict
False
>>> "ST" not in typeDict
True
>>> if "somevalue" not in typeDict:
...     print "nope"
...     
nope

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Okay, easy enough. The problem is constructing a valid "where clause' for the SelectLayerByAttribute() when considering membership in a dictionary.  I've tried dozens of {}.format() approaches but the red font on my screen looks something like the U.S. spend deficit.  I need some advice to successfully select a record or records based on the dictionary:

intable = "C:\Path\To\File.gdb\Table
arcpy.MakeTableView_management(intable,"view")
typeDict = {"AVE":1,"BLVD":2,"CIR":3}  #etc, etc‍‍‍

fields = ["Type","StreetName"]
where = "StreetName >= 'A'    #just concerned with named streets, no numbers

with arcpy.da.SearchCursor("view",fields,where) as cursor:
    for row in cursor:
       Select = #need to format a where clause that
                # evalutes row[0] not in typeDict
       arcpy.SelectLayer,ByAttribute(view,"ADD_TO_SELECTION",Selection)‍‍‍‍‍‍‍‍‍‍‍‍

jamesfreddyc‌

bixb0012‌

Dan_Patterson‌

(I should just create a signature block with you guys in it; maybe I can convince my boss to put you on retainer....)

[JoshuaBixby]

Instead of making specific code suggestions, let's take a step back to understand what you are trying to accomplish.  It seems to me you are trying to select all streets that are not avenues, boulevards, circles, etc...., i.e., you have a set of criteria that you want to invert.  Is this correct?  If so, what do you plan on doing with the selected records? 

Generally, Select By Layer or Select By Attribute within a cursor can be refactored, especially when the cursor and selections are all based on the same layer or view.

[JoeBorgione]

Joshua- this is part of an overall QA/QC procedure I'm trying to implement.  In this particular case, I will be examining streets and the value of the "Type" field and if an editor has added a named street, the Type field must be populated by at least on value in the dictionary: a lot like validating a domain, but without the domain. 

Members of the selected set are written to a table in memory called "Errors".  Errors_in_memory has a field called "Error_Description" which I update with a arcpy.CalculateField_management(); once that is done, those records are appended to a table on disc that is used as a check list to 'fix' any errors.  Errors_in_memory is then cleaned out with a DeleteRows_management() and I go on to the next QA/QC loop which could be a look at the numbered streets (where = "StreetName < 'A'") and select those where "Type is not None". They get written to Errors_In_Memory, flagged with a description and then appended to disc, and so on....

Thanks.

[JoshuaBixby]

You have a field named "Type" and you mention domain-like functionality for that field.  Is there only a set list of choices, or at least structured choices, for that field?  That is, you would only find "AVE" but not "avenue?"  Or, you would have "BLVD" but not "West BLVD?"

How structured the data is strongly influences how much work is involved in building an SQL statement.  If the data is loosely structured, i.e., it could have "AVE" and "avenue" or "ST" and "street", then using a cursor with Python to generate a selection set is probably better than building an complicated SQL WHERE statement.

[JoeBorgione]

Thankfully the data is tight" AVE, ST, BLVD etc.  No Ave, no Str etc...

[DarrenWiens2]

Working backwards, you need to use SQL in your where clause, something like:

StreetName not in ('BLVD', 'AVE', 'CIR')‍

So, you need to somehow format your dictionary keys into a list of quoted strings, like in the final line below:

typeDict = {"AVE":1,"BLVD":2,"CIR":3}
print typeDict
print typeDict.keys()
print ','.join(typeDict.keys())
print ','.join("'{}'".format(w) for w in typeDict.keys())

...
{'BLVD': 2, 'AVE': 1, 'CIR': 3}
['BLVD', 'AVE', 'CIR']
BLVD,AVE,CIR
'BLVD','AVE','CIR'‍‍‍‍‍‍‍‍‍‍‍

[JoeBorgione]

I kind of went down that route with

>>> print list
['BLVD', 'AVE', 'CIR']
>>> where = "Type not in list"
>>> where
'Type not in list'
‍‍‍‍‍

but when I try to get where into something SQL likes, that's where things go south...

[deleted-user-qzyyiYme1rz9 · Accepted solution]

Joe, first of all, you have the list in quotes, so it isn't reading your variable. Second, you need to change the list to string format with parentheses instead of brackets. See if this works.

>>> list = ['BLVD', 'AVE', 'CIR']
>>> list = str(list)
>>> list = list.replace("[", "(")
>>> list = list.replace("]", ")")
>>> print list
('BLVD', 'AVE', 'CIR')
>>> where = "Type NOT IN "+list
>>> print where
Type NOT IN ('BLVD', 'AVE', 'CIR')

[JoeBorgione]

Thanks Alex-  I simply created list directly as:

>>> list = "('Blah','BlahBlay','BlahBlahBlah')"
>>> print list
('Blah','BlahBlay','BlahBlahBlah')
>>> where = "NOT IN"+list
>>> print where
NOT IN('Blah','BlahBlay','BlahBlahBlah')
>>>‍‍‍‍‍‍‍

[curtvprice]

Here's how I do it:

list = ['BLVD', 'AVE', 'CIR']
list1 = ",".join(["'{}'".format(k) for k in list])
where = "FIELD IN ({})".format(list1)
print(where) 
# prints: FIELD IN ('BLVD','AVE','CIR')‍‍‍‍‍