How Perform A Select Using "OR"?

[Mark_Hotz]

I am using ArcGIS Pro 2.4.3, and just running this script through IDLE for ArcGIS Pro.

I am having some difficulty getting a standard expression in Python to return what I need.  I have created a simple query to try and run...if I can get this to work then I will insert this into my main program with added functions. 

When I run the script it only returns 16 records (i.e. records for only for variable Q1_A).  I'm trying to get it to return 18 records (which would include Q1_B).  

import arcpy

Q1_A = "CHARLES HAYS"
Q1_B = "SMIT CLYDE"
fc1 = (r"F:\ArcGIS_Pro_Projects\AIS\AIS_Data\AIS_Data.gdb\TEST_AIS_Data")

#expression1 = ("Vessel_Name = 'CHARLES HAYS' Or Vessel_Name = 'SMIT CLYDE'")
expression1 = ("Vessel_Name = '" + str(Q1_A) + "'" or "Vessel_Name = '" + str(Q1_B) + "'")
print ("")
print (expression1)
SelectResult = arcpy.SelectLayerByAttribute_management((fc1), "NEW_SELECTION", (expression1))
CountTotal = arcpy.GetCount_management((fc1))
CountResult = arcpy.GetCount_management(SelectResult)
print ("")
print (str(CountResult) + " Of " + str(CountTotal) + " Records Selected")
arcpy.SelectLayerByAttribute_management((fc1), "CLEAR_SELECTION")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Hardcoding it (as per what is commented out) works exactly as I need it to work, and returns 18 records.

If I use an "and" instead of an "or" it returns 2 records (only for Q1_B).  Logic tells me that using an "or" it should select 18 records (i.e. for both Q1_A and Q1_B).

The print statement (e.g. print (expression1)) also only prints the variable Q1_A.  

What am I doing wrong here?  How do I get the variable "expression1" to read the entire string?  I know I'm missing something, but for the life of me I just can't see it...and I have been spinning my wheels on this all afternoon.

Suggestions?

Thank you

[DanPatterson_Retired]

I don't do query expressions, but I do formatting.  

Don't over "str" things.  Use the mini-format capabilities of python.  For example.

Q1_A = "CHARLES HAYS"
Q1_B = "SMIT CLYDE"

expression1 = "Vessel_Name = '{}' or Vessel_Name = '{}'".format(Q1_A, Q1_B)

print (expression1)

Vessel_Name = 'CHARLES HAYS' or Vessel_Name = 'SMIT CLYDE'‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Now you can mess around with it to get it to work like maybe it is OR instead of 'or'

[Mark_Hotz]

Dan:

Thanks very much for this.  I will most definitely experiment with this code...all of the suggestions here actually.  I still have a bit to learn about arcpy I see :-).  I will spend some time learning more about braces "{ }" too...I wasn't aware of this method but I can certainly see how it would be a powerful tool.  It will probably take me a few months, but by the end of 2020 I expect to be a LOT more fluent in Python for ArcGIS.

Thanks again.

[RandyBurton · Accepted solution]

Dan Patterson‌ offers a good suggestion for formatting your OR statement and should correct your issue.

The print statement (e.g. print (expression1)) also only prints the variable Q1_A.

This is because of the double quotes around the word " or ".

# wrong
expression1 = ("Vessel_Name = '" + str(Q1_A) + "'" or "Vessel_Name = '" + str(Q1_B) + "'")
# correct
expression1 = ("Vessel_Name = '" + str(Q1_A) + "' or Vessel_Name = '" + str(Q1_B) + "'")‍‍‍‍

[Mark_Hotz]

Randy:

I liked this solution as it's so very close to what I already had.  I can see I need to definitely work on my double and single quotes...I always seem to have difficulty with those :-).

Thanks again

[JoshuaBixby]

A more common approach to selecting items from a list is to use SQL IN rather than creating SQL OR statements.  SQL reference for query expressions used in ArcGIS—ArcGIS Pro | ArcGIS Desktop 

Comparison operators

You use comparison operators to compare one expression to another.

Operator	Description
[NOT] IN	Selects a record if it has one of several strings or values in a field. When preceded by NOT, it selects a record if it doesn't have one of several strings or values in a field. For example, this expression searches for four different state names: "STATE_NAME" IN ('Alabama', 'Alaska', 'California', 'Florida')

You can take a Python list of names/values you want to look for and convert it to a SQL IN list:

import arcpy

Q1_list = ["CHARLES HAYS", "SMIT CLYDE"]
fc1 = (r"F:\ArcGIS_Pro_Projects\AIS\AIS_Data\AIS_Data.gdb\TEST_AIS_Data")

expression2 = "Vessel_Name IN ('{}')".format("','".join(q1 for q1 in Q1_list))
print ("")
print (expression2)
SelectResult = arcpy.SelectLayerByAttribute_management((fc1), "NEW_SELECTION", (expression2))
CountTotal = arcpy.GetCount_management((fc1))
CountResult = arcpy.GetCount_management(SelectResult)
print ("")
print (str(CountResult) + " Of " + str(CountTotal) + " Records Selected")
arcpy.SelectLayerByAttribute_management((fc1), "CLEAR_SELECTION")‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[Mark_Hotz]

Joshua:

It just so happens I was trying (unsuccessfully) to do a query very similar to this but of course much less complex LOL.  I just ran into issues when trying to use a value in a variable with the "LIKE" operator.  I'm definitely going to experiment with this one as this seems to be something that would work in my case.

I need to be able to select multiple records based on a DateTime extracted from the original data using FME.  The DateTime data looks something like, "20190401091323", with the last 8 characters pertaining to hours, minutes and seconds etc.  However, I only needed to select by year and month (the first 6 characters) but to be able to single out those records after each conversion, and using "LIKE" with a variable, apparently, is not supported.  So the only way I managed to do this (with my limited Python knowledge) was using a new field that I had already created for that feature class (DateTime_2) that was only YearMonth, which would be NULL after each conversion (as the previous month's records would have had that already added)...so all records = NULL sufficed to calculate that field to YearMonth.  But I also don't like querying that way either.

Your method here seems to be a much better way to approach this...so I will definitely revisit my code and rewrite it.

Thanks.

[JoshuaBixby]

LIKE is supported in SQL queries in ArcGIS.  Start a different thread and explain what you tried and what isn't working.

[DanPatterson_Retired]

but the field name isn't quoted if that is needed for the query in either case

Q1_list = ["CHARLES HAYS", "SMIT CLYDE"]

expr0 = "Vessel_Name IN {}".format(tuple(Q1_list))

"Vessel_Name IN ('CHARLES HAYS', 'SMIT CLYDE')"

expr1 = "Vessel_Name IN ('{}')".format("','".join(q1 for q1 in Q1_list))

"Vessel_Name IN ('CHARLES HAYS','SMIT CLYDE')"

# ---- tuple or join, ‍‍‍‍‍‍‍‍‍‍‍

[JoshuaBixby]

Although AddFieldDelimiters—ArcPy Functions | ArcGIS Desktop will still add double quotes around file geodatabase and shape file field names, they haven't been necessary for a while.  The ArcMap SQL reference for query expressions used in ArcGIS—Help | ArcGIS for Desktop (ArcMap) documentation states:

For File geodatabase data you can enclose your field names in double quotes, but it's generally not needed.

The ArcGIS Pro SQL reference for query expressions used in ArcGIS—ArcGIS Pro | ArcGIS Desktop (Pro) documentation has gone one step further and completely removed the Fields section so there are no references to field delimiters.