topic Re: SQL Expression help, Select_Analysis in Python Questions

SQL Expression help, Select_Analysis

ChristopherClark1 — Sun, 12 Dec 2021 16:20:44 GMT

In my code below, I am attempting to select features that have meet this Criteria : SNAP_FLAG (this is a field) < 3. Then I want to export these selected features as a new feature class.

# Import arcpy module
import arcpy, sys, traceback
arcpy.env.overwriteOutput = True
from os import path as p

 
arcpy.env.workspace = r"G:\ChrisGIS\PS_Steelhead\Work"
try:
    for ws in arcpy.ListWorkspaces("*", "FileGDB"):
        arcpy.env.workspace = ws
        print '\n\nSearching in %s\n\n' %ws
#
# Define Variables
        NB = p.join(ws, 'NB')
        NB_out = p.join(ws, 'NB_Snapped')
#
# Figure out Query Statement enter here  
        query = '"SNAP_FLAG" < \'3\''
#
# Select
        arcpy.Select_analysis(NB, NB_out, query)
        print 'NB_Snapped created from %s'%NB

I then get this error :
Error Info:
ERROR 999999: Error executing function.
An invalid SQL statement was used.
An invalid SQL statement was used. [NB]
The table was not found. [NB_Snapped]
Failed to execute (Select).

ArcPy ERRORS:
ERROR 999999: Error executing function.
An invalid SQL statement was used.
An invalid SQL statement was used. [NB]
The table was not found. [NB_Snapped]
Failed to execute (Select).

Any ideas what I am doing wrong? Is it the actual output table variable that is messed up, or is it my query...

Re: SQL Expression help, Select_Analysis

Anonymous User — Wed, 06 Mar 2013 20:44:04 GMT

I believe the problem here is that your SNAP_FLAG field is probably an integer type? If that is the case, the query could simply be:

query = '"SNAP_FLAG" < 3'

OR

query = 'SNAP_FLAG < 3'

The help docs say that you need to always have double quotes around field names but I hardly ever do that anymore and it still works (may just be for shapefiles?). Anyways, you only need the single quotes after an operator (AND, OR, <>, = etc) if it is a text string type.

For example:

"CITY = 'CHICAGO'"

You would need single quotes around the string CHICAGO. Hope this helps.

Re: SQL Expression help, Select_Analysis

ChristopherClark1 — Wed, 06 Mar 2013 20:49:35 GMT

Yep! That was the answer. I was using the Arc Help Docs, and that is where I got that goofy syntax. Thanks for helping out.

Re: SQL Expression help, Select_Analysis

curtvprice — Sat, 11 Dec 2021 15:32:26 GMT

Caleb, you inspired me to drop some info about SQL expressions in arcpy scripts into this thread...

The help docs say that you need to always have double quotes around field names but I hardly ever do that anymore and it still works (may just be for shapefiles?). Anyways, you only need the single quotes after an operator (AND, OR, <>, = etc) if it is a text string type.

Yes, single quotes are required in SQL for all string literals.

Double quotes are needed in SQL to protect your field name from parsing, for example, to make sure reserved words or embedded operators in field names (for example: "COUNT"; "FIELD-1") do not break your SQL expression. That's why you often see double-quotes used in examples -- it's best practice just in case your field names are not legal. This is especially important if your field name came in as a tool parameter (you as the script developer can't control what people will try to do!).

Personal geodatabase and some ODBC-sourced tables always require brackets around field names:

[CITY] = 'CHICAGO'

There is an arcpy method that will automatically add the appropriate field delimiter based on the workspace, just in case someone decides to ignore or can't take your good advice to avoid personal GDB's. Using this method will keep your script from breaking.

>>>> print arcpy.AddFieldDelimiters("C:\\work\\mypersonal.mdb","CITY")
[CITY]

Last but not least, I want to encourage using substitution SQL expressions, as they make dealing with all this easier:

field = "CITY"
cityname = "Chicago"
wks = "C:/Data/MyWorkspace.mdb"
where_expr = "{0} = '{1}'".format(arcpy.AddFieldDelimiters(wks,field),cityName)

Re: SQL Expression help, Select_Analysis

Anonymous User — Thu, 07 Mar 2013 16:03:04 GMT

Ahh, thanks for the detailed information Curtis. I definitely do not want to provide the wrong information for anyone who reads this thread. I never use personal gdb's anyways so maybe that's why I never get the errors when I set up my queries. It would be helpful if they provided all this information in the help docs to avoid confusion like this.

I do like your idea of using the .format() in queries. I have actually been doing that a little bit lately and it seems to clean things up a little better. Thanks for your valuable insight!

EDIT:
Field delimiters can be found in the SelectLayerByAttribute tool in the where clause.

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//001700000071000000

Thanks again Curtis!