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

[AndrewChapkowski]

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.

[JonPedder]

Sorry, you've totally lost me!

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

[AndrewChapkowski]

Sorry, you've totally lost me!

Which part are you lost on?

[JonPedder]

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

Thanks !!

[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]

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]

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]

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