topic Re: How Perform A Select Using "OR"? in Python Questions

How Perform A Select Using "OR"?

Mark_Hotz — Sat, 11 Dec 2021 17:27:21 GMT

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

Re: How Perform A Select Using "OR"?

DanPatterson_Retired — Sat, 11 Dec 2021 17:27:24 GMT

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'

Re: How Perform A Select Using "OR"?

RandyBurton — Sat, 11 Dec 2021 17:27:27 GMT

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) + "'")‍‍‍‍

Re: How Perform A Select Using "OR"?

JoshuaBixby — Sat, 11 Dec 2021 17:27:29 GMT

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

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")‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: How Perform A Select Using "OR"?

DanPatterson_Retired — Sat, 11 Dec 2021 17:27:32 GMT

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, ‍‍‍‍‍‍‍‍‍‍‍

Re: How Perform A Select Using "OR"?

JoshuaBixby — Mon, 20 Jan 2020 16:15:35 GMT

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.

Re: How Perform A Select Using "OR"?

DanPatterson_Retired — Mon, 20 Jan 2020 17:20:00 GMT

You can tell how often I do queries

Re: How Perform A Select Using "OR"?

JoshuaBixby — Mon, 20 Jan 2020 18:08:34 GMT

To your defense, Esri handled the change in a very passive way over a period of time, and they didn't clean up all their documentation completely. The Add Field Delimiters tool still states that file geodatabase and shape files use double quotes.

Re: How Perform A Select Using "OR"?

Mark_Hotz — Mon, 20 Jan 2020 18:17:14 GMT

Thank you all for such awesome (and timely) advice. I got my script to work, so I'm glad I posted the question. I have only just recently returned to Python scripting so I'm in the process of relearning the syntax. Obviously I need to learn a few things...even simple things, like the difference between single and double quotes, the combinations of these, and when to use them. A couple of these responses have also opened up other options and approaches to solving the same problem...something of which I really appreciate.

I'm going to put all of these responses into my "How 2" folder for future reference for sure.

I'm scheduled to take the first ESRI Python course in February, on the 20th, with plans to take the more advanced course in probably June if possible. I might as well really sink my teeth into this as the benefits are HUGE when it comes to geoprocessing for sure (and much more).

I delved into arcpy in August-September as I needed to write a script that would automate a process from FME data conversion to Portal for ArcGIS publishing. I got it to work entirely in December, but then I ran into problems. The file geodatabase to which FME adds the converted AIS data has grown to be very cumbersome...it is now over 100-million points and I have only completed data from January to August (the plan is to convert data for each year). However, before it's uploaded to Portal I filter it using the MOD option to select 1 in 10 records...so the final file size is much more manageable (I'm thinking that I should perhaps get FME to do this before it adds to the GDB too).

What I'm planning to do is every 4 months (using the Select script you helped me fix) select the previous 3 months of AIS data and then dump those records into a Q1, Q2 etc. file geodatabase, which will keep the main file geodatabase at a much more management size. With over 100-million points my script currently takes over 40 hours to run, but it's only 3-4 hours if it's 3-4 months worth of data (the script will just run after midnight on the first of each month).

The final data in Portal is then used to create heat maps where our Operations Department can then analyze to see where ship traffic is mostly concentrated in and around our harbor.

Thank you very much again

Re: How Perform A Select Using "OR"?

JoshuaBixby — Mon, 20 Jan 2020 18:30:20 GMT

Mark, please mark one of the responses as Correct to close out the question. If multiple responses helped, picked the one that helped the most (or was the first) as Correct and mark the others helpful.

Re: How Perform A Select Using "OR"?

Mark_Hotz — Mon, 20 Jan 2020 19:43:22 GMT

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.

Re: How Perform A Select Using "OR"?

Mark_Hotz — Mon, 20 Jan 2020 19:45:51 GMT

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

Re: How Perform A Select Using "OR"?

Mark_Hotz — Mon, 20 Jan 2020 19:49:46 GMT

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.

Re: How Perform A Select Using "OR"?

Mark_Hotz — Mon, 20 Jan 2020 19:50:59 GMT

Done.

Re: How Perform A Select Using "OR"?

JoshuaBixby — Mon, 20 Jan 2020 20:32:21 GMT

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