Where Clause with AND Statement in Python

09-11-2017 09:25 AM
MVP Esteemed Contributor

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})


numeric_hn <= ({max})


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‌ 

That should just about do it....
1 Reply
MVP Legendary Contributor

Check out Fancy, Fancy Formatting and the links in there on Basic Fancy Formatting etc.

As you move to python 3, this will become more useful over time,

0 Kudos