jborgion

Where Clause with AND Statement in Python

Discussion created by jborgion Champion on Sep 11, 2017
Latest reply on Sep 11, 2017 by Dan_Patterson

In Adding to a Selection I asked how to pass a variable set in a search cursor to a where statement. Curtis Price helped me there.  That turned out to be only half of what I need in the particular application I'm working on:  I also need to incorporate an AND statement into the WHERE clause.  It's all about pythons' love of lists...

 

Curtis provided the initial where clause and I expand upon it below:

 

where = "FullStreet IN ({})".format(street_list)
#which creates a selection where FullStreet is a member of street_list
# I change his where variable to this:
where = "FullStreet IN ({}) AND numeric_hn <=({}) AND numeric_hn >= ({})".format(street_list,max,min)
# so I'm selecting not only for the FullStreet value, but checking for a house number value that
#is GTE and LTE to specified values
arcpy.SelectLayerByAttribute_management ("apLayer","ADD_TO_SELECTION",where)
#And I'm golden!


 

For the newbie like myself, each of the ({}) gets populated with the corresponding (in order) list member.  So:

 

FullStreet IN ({street_list})

and

numeric_hn <= ({max})

and

numeric_hn => ({min})

 

Is how the where statement is being interpreted.  

 

Lessons learned:

 

Quote positions are critical

The .format method is your friend.  Sometimes he can be a difficult friend, but he's a friend.

Python is all about lists.  Don't overlook or forget that.

 

Kudos to Ryan Sadler for teaching this old dawg a new trick

 

 

Darren Wiens , Rebecca Strauch, GISP 

Outcomes