nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Little SQL query help please

JonPedder

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

Find more posts tagged with

Accepted answers

T__WayneWhitley

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.

All comments

JonPedder

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

Jon

T__WayneWhitley

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.

CarlSunderman

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

JonPedder

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

T__WayneWhitley

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.

JonPedder

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 !

T__WayneWhitley

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.

JonPedder

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

Thanks !!

achapkowski

Sorry, you've totally lost me!

Which part are you lost on?

T__WayneWhitley

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

JonPedder

Sorry, you've totally lost me!

achapkowski

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.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.