topic Re: Little SQL query help please in Python Questions

Little SQL query help please

JonPedder — Wed, 19 Dec 2012 15:05:55 GMT

I always seem to get hung up on these simple strings.

Here again I don???t quite get the syntax for use in SelectLayerByAttribute_management

If I use the following with a field named ???Name??? the query works just fine

iQuery = ' "Name" = \'Joe Blogs\' ' arcpy.SelectLayerByAttribute_management(lyr, "NEW_SELECTION", iQuery)

However when I try to use a variable I can never seem to get the syntax right

iPerson = ???Joe Blogs??? iQuery = '"Name" = "' + aCenterMapOn + '"' arcpy.SelectLayerByAttribute_management(lyr, "NEW_SELECTION", iQuery)

Thanks

Re: Little SQL query help please

AndrewChapkowski — Sat, 11 Dec 2021 17:43:39 GMT

You can try this:

# your code
iQuery = ' "Name" = \'Joe Blogs\' '
print iQuery
# modified code
print "\"%s\" = '%s'" % ("Name", "Joe Blogs")

You can substitute the Name and Value with a variable to make this more dynamic.

I would also look at the AddFieldDelimiters() in arcpy to ensure every field is formatted correctly.

Re: Little SQL query help please

JonPedder — Wed, 19 Dec 2012 16:26:26 GMT

Sorry, you've totally lost me!

Re: Little SQL query help please

T__WayneWhitley — Sat, 11 Dec 2021 17:43:42 GMT

Here's an example, employing the AddFieldDelimiters method - and since using IDLE and an SDE-contained fc instead of a ready feature layer (the required input for SelectLayerByAttributes), I used a similar tool MakeFeatureLayer to demonstrate the use of query syntax, but I am not yet accustomed to var substitution, so I'd take a keen look at Andrew's example. Here's my simpler one:

>>> arcpy.env.workspace = r'Database Connections\MCPA.sde'
>>> inFC = 'MCPA.DBO.PARCEL_PUBLIC'
>>> qryFieldName = arcpy.AddFieldDelimiters(inFC, 'RECHAR')
>>> print qryFieldName

"RECHAR"

>>> qry = qryFieldName + " = '" + aCenterMapOn + "'"
>>> print qry

"RECHAR" = '00166972-004500'

(syntax):
>>> # SelectLayerByAttribute_management (in_layer_or_view, {selection_type}, {where_clause})
>>> # MakeFeatureLayer_management (in_features, out_layer, {where_clause}, {workspace}, {field_info})
>>> 
>>> arcpy.MakeFeatureLayer_management(inFC, 'lyr', qry)

<Result 'lyr'>

(getting the count to verify the selection took place):
>>> arcpy.GetCount_management('lyr').getOutput(0)

u'1'
>>>

Re: Little SQL query help please

AndrewChapkowski — Wed, 19 Dec 2012 16:31:26 GMT

Sorry, you've totally lost me!

Which part are you lost on?

Re: Little SQL query help please

JonPedder — Wed, 19 Dec 2012 17:16:14 GMT

Thanks very much! Now I "get it" the arcpy.AddFieldDelimiters and the help from Wayne did the trick.

Thanks !!

Re: Little SQL query help please

T__WayneWhitley — Sat, 11 Dec 2021 17:43:44 GMT

Great, and just so we 'get it more' while on the effort, always a good thing -- as suggested by Andrew, the 'variable string substitution' (not sure what it's called, but it's a purely Pythonic technique you can borrow to help make your query syntax more manageable), here's what you get when you combine or build on the above:

>>> qry2 = "%s = '%s'" % (qryFieldName, aCenterMapOn)
>>> print qry2

"RECHAR" = '00166972-004500'

So to offer explanation, if you strip of the double quotes in the above expression component, you get:
%s = '%s'

...then %s is the substitution in the order provided in the paren (after the 3rd unquoted %).
The field delimiters in this case are taken care of by AddFieldDelimiters and already stored in qryFieldName - that's a direct substitution (%s).
The second param required the single quotes as querying for text values requires ('%s'). Had we been querying for numeric, then that would be a direct substitution as well (%s).

Thanks to Andrew at ESRI.

Re: Little SQL query help please

JonPedder — Thu, 20 Dec 2012 03:42:41 GMT

Guys, I'm hung up on one more of these, jeeze!

querying a text field for fields that are not empty. My search string that works in the attribute table is

iField = arcpy.AddFieldDelimiters(fc, "Area_Name")

iQuery = iField+ " IS NOT NULL"

This doesn't want to work in arcpy.SelectLayerByAttribute_management(lyr, "NEW_SELECTION", iQuery)

Killin' me !

Re: Little SQL query help please

T__WayneWhitley — Thu, 20 Dec 2012 05:29:57 GMT

Have you tried IS NULL and get anything? Are errors being returned or is it just not working? If IS NULL works you can execute select by attribute again with SWITCH_SELECTION.

Re: Little SQL query help please

JonPedder — Thu, 20 Dec 2012 11:32:27 GMT

I'm getting a syntax error when using arcpy.SelectLayerByAttribute_management but the actual query works perfectly when running from within an attribute table and using Select By Attribute..

Thanks Wayne

Jon

Re: Little SQL query help please

CarlSunderman — Thu, 20 Dec 2012 11:41:42 GMT

I would try working your way through it. Maybe start simple to figure out where your error is coming from. Try using the python window and use actual feature class names then do again declaring variables and see where the issue is coming from. That's usually how I work my way through

Re: Little SQL query help please

T__WayneWhitley — Thu, 20 Dec 2012 13:03:05 GMT

Okay, sorry for the delay - but see the below, what I did that works (employing the techniques already discussed above).
I used a gdb feature class that I added a 'blank' row to in order to introduce the null vals (so I know I should get results). Comments in the code below (again, it's an IDLE session):

IDLE 2.6.5       >>> import arcpy >>> fc = r'C:\Documents and Settings\whitley-wayne\Desktop\stage.gdb\RightBankPoints'  # (make sure the fc exists) >>> if arcpy.Exists(fc):print 'true'   true  # (could have entered the qry here for MFL, but you wanted to demo SelectLayerByAtt, which I think requires a layer) # (...so created a lyr first to feed into SelectLayerByAtt, a step only for demo purposes...) >>> arcpy.MakeFeatureLayer_management(fc, 'lyr') <Result 'lyr'>  # (verifying there are features in the lyr): >>> arcpy.GetCount_management('lyr').getOutput(0) u'198'  # (setting up the field with the correct delimiters [double quotes]): >>> selFld = arcpy.AddFieldDelimiters(fc,'RiverCode')  # (just checking...) >>> print selFld "RiverCode"  # (setting up the qry string): >>> qry = "%s %s" % (selFld, 'is not Null')  # (looks fine, ready for launch...) >>> print qry "RiverCode" is not Null  # (commence launch, the execution we've been waiting for): >>> arcpy.SelectLayerByAttribute_management('lyr', 'NEW_SELECTION', qry) <Result 'lyr'>  # (checking the count -- I entered 1 null record, so the count should be at least 1 less than before): >>> arcpy.GetCount_management('lyr').getOutput(0) u'197' >>>

And it is. Mission accomplished, hope that helps you.

Re: Little SQL query help please

JonPedder — Thu, 20 Dec 2012 16:43:56 GMT

Perfect Wayne! I'll keep this little function in my toolbox from now on. Thanks again for all your help

Jon