topic Re: Python select_analysis where clause for numeric field in Python Questions

Python select_analysis where clause for numeric field

PitersonPaulgek — Tue, 18 Oct 2016 22:52:17 GMT

Hi,

I get error "Invalid expression" for:

where_clause ='"Yr" IS NULL'
arcpy.Select_analysis(InFC, OutFC, where_clause)

What is the correct syntax?

Re: Python select_analysis where clause for numeric field

DarrenWiens2 — Tue, 18 Oct 2016 23:09:35 GMT

Try 'Yr IS NULL'. Otherwise, verify your expression in the tool GUI, and consult: Building a query expression—Help | ArcGIS for Desktop

edit: it's also good practice to use AddFieldDelimiters to ensure correct field delimiters (brackets, quotes, or none).

edit 2: once you get something to work through the GUI, you can right-click in the results window and copy as Python snippet to see the syntax. It looks like ArcGIS will accept this syntax: where_clause=""""YR" IS NULL""" (four double-quotes at the beginning, three at the end)

Re: Python select_analysis where clause for numeric field

PitersonPaulgek — Wed, 19 Oct 2016 04:28:28 GMT

Thanks Darren,

I tried: the same error: Invalid expression.

I definitely got working with ModelBuilder first, then exported to Python.

So,

In GUI (ModelBuilder) (with SQL Query Builder wizard):

Yr IS NOT NULL (it works in ModelBuilder without any quotes)

in Python (*.py):

I tried:

1) where_clause ='Yr IS NULL' - ERROR

2) where_clause ='"Yr" IS NULL' - ERROR

3) where_clause =""""Yr" IS NULL""" - ERROR
arcpy.Select_analysis(InFC, OutFC, where_clause)

For another field (text) it works fine in Python

arcpy.Select_analysis(InFC, OutFC, "Prime = 'Iron Ore'")

I've noticed that I work with layer(not feature class). I do MakeFeatureLayer that is the input for Select. Could it have an impact?

Re: Python select_analysis where clause for numeric field

MitchHolley1 — Wed, 19 Oct 2016 14:02:39 GMT

Do a Select By Attributes on the table in ArcMap, then copy that SQL syntax to your Python code.

Re: Python select_analysis where clause for numeric field

DarrenWiens2 — Wed, 19 Oct 2016 16:36:08 GMT

What is the datasource for the feature layer (FGDB feature class, PGDB feature class, shapefile, other) and what is the datatype of the field YR?

Re: Python select_analysis where clause for numeric field

PitersonPaulgek — Thu, 20 Oct 2016 22:15:47 GMT

Datasource for the feature layer - FGDB feature class.

Datatype of the field YR - Long Integer

(Datatype of the field Prime - Text (it work fine - see comments above))

Re: Python select_analysis where clause for numeric field

PitersonPaulgek — Thu, 20 Oct 2016 22:38:07 GMT

Thanks Mitch,

I did - it works fine in ArcMap Select By Attributes (SQL wizard). The syntax is: Yr IS NOT NULL

No quotes!

Any ideas, please!

Re: Python select_analysis where clause for numeric field

DanPatterson_Retired — Thu, 20 Oct 2016 22:43:26 GMT

You got it to work in modelbuilder as you indicate. Could you export the model to a python script and report the syntax from the script? Don't run the script, just produce the format. Perhaps there is a difference in the translation from model to script that doesn't work, or at least you will have the correct syntax to use from a standalone script.

Re: Python select_analysis where clause for numeric field

PitersonPaulgek — Thu, 20 Oct 2016 22:50:48 GMT

Thanks Dan,

I did the export. The syntax from the exported:

# Process: Select (2)
arcpy.Select_analysis(***, ***, "Yr IS NOT NULL")

So, what is the correct? Please help!

Re: Python select_analysis where clause for numeric field

DanPatterson_Retired — Thu, 20 Oct 2016 23:01:57 GMT

Piterson, from what is given, then it appears field delimiters are not needed, which seems to be consistent with the suggestions in ArcGIS Pro ... now I am assuming that you hide your layer and field names to preserve anonymity given the syntax .... Select_analysis (in_features, out_feature_class, {where_clause}) ...

since ***, and *** are not obviously going to work in a python script. You could try a simple script to test. by replacing the in_features and out_feature_class with the path to a file (shapefile or featurelayer in a gdb) for source and destination.

Re: Python select_analysis where clause for numeric field

PitersonPaulgek — Mon, 24 Oct 2016 11:46:34 GMT

Fortunatelly, I run the simple test (Delete and Select) - it worked in ModelBuilder and then exported to PythonScript with syntax :

arcpy.Select_analysis(in_features,out_features, "Yr IS NOT NULL")

Thanks for help!!!