1

I've come across this problem a few times and have not been able to find a satisfactory answer. I am new to Python though so perhaps I have not been looking in the right places!

Anyway, In ArcGIS I have been using the following code to select entries by attribute in the attribute table:

import arcpy


arcpy.env.workspace = "C:\Users\T0015685\Documents\WCGIS\Geog485\Lesson1\us_hydro.shp"

qry = '"COUNTRY" = \'CANADA\''
flayer = arcpy.MakeFeatureLayer_management("us_hydro", "us_hydro_Hudson6")
arcpy.SelectLayerByAttribute_management(flayer, "NEW_SELECTION", qry)
cnt = arcpy.GetCount_management(flayer)
print "The number of selected records is: " + str(cnt)

This script works fine. HOWEVER, when I alter the query entries from verbal to numeric attributes I get an error. So, for example, if I change the query to - qry = '"HYDRO_= \'1744\'' - the script fails. I think this is something to do with the SQL expression but I'm not sure.

I want to be able to use code such as this to search feature ID's but can't because every time I use numbers the code doesn't work. Could someone please explain why this is the case or perhaps offer a solution.

2 Answers 2

4

If your field is a float or double field, you don't need the escaped quotes in your query. In SQL, numeric attribute values cannot be encased in quotes or they will be treated as strings. The proper SQL for a numeric attribute in your query is as follows:

qry = "'HYDRO_' = 1744"

If you take the SQL statement out of the query string it looks like this:

'HYDRO_' = 1744

You will notice that the syntax matches (without quotes around the column name) what it looks like if you built a query from the query builder GUI in ArcGIS. Thus, an easy way to check if your query is going to work is to try it in the GUI window first and then imitate it within your script.

For more information, see the following ESRI docs:

SQL expressions within an arcpy select

General SQL expressions in ArcGIS

1
  • Thanks, job done. Still not too confident using SQL in python scripts though.
    – Geord359
    Jun 17, 2014 at 14:33
0

This seems a common stumbling block - I prefer to use Python's format function because it's flexible and tends to be easier to read. Here's a very short demo, my comments included:

>>> mynumber = 123.436
>>> mytext = 'One, Two, Buckle My Shoe'

>>> qry = "'{0}' = {1}".format('thefield', mynumber)
>>> print qry
'thefield' = 123.436

>>> # This will not work on strings:
>>> qry = "'{0}' = {1}".format('thefield', mytext)
>>> print qry
'thefield' = One, Two, Buckle My Shoe

>>> # This works OK if you don't like to use escape sequencing:
>>> qry = "'{0}' = {1}".format('thefield', '"' + mytext + '"')
>>> print qry
'thefield' = "One, Two, Buckle My Shoe"

>>> # This rather odd line works, but probably against the Zen of Python:
>>> qry = "'{0}' = {2}{1}{2}".format('thefield', mytext, '"')
>>> print qry
'thefield' = "One, Two, Buckle My Shoe"

>>> # This is arguably the best way, using escape sequencing:
>>> qry = "'{0}' = \"{1}\"".format('thefield', mytext)
>>> print qry
'thefield' = "One, Two, Buckle My Shoe"

Hope that helps.

By the way, I noticed an error in setting the workspace in your original post. You have it set to a shapefile -- if the shapefile is your target dataset, i.e. what you want to query against, your workspace should be set to its parent folder, like so:

arcpy.env.workspace = "C:\Users\T0015685\Documents\WCGIS\Geog485\Lesson1"

Also, if you want arcpy to set your field delimiters on your field you can use AddFieldDelimiters in order to form your SQL query correctly - this is particularly useful if, say, your workspace is a script parameter and your workspace type changes (which in turn affects what delimiters are used). So, in that case, you could do something like this:

fieldWithDelimiters = arcpy.AddFieldDelimiters(arcpy.env.workspace,'thefield')
qry = "{0} = \"{1}\"".format(fieldWithDelimiters, mytext)

-Wayne

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.