SQL works in python console but not when run as a geoprocessing tool

Question

I'm in the process of transferring all the arcpy scripts I've written to geoprocessing tools so that my team can run them without my hand-holding and it's *mostly* working out ok but I'm running into snags here and there and my current one has to do with the geoprocessing tool complaining about an invalid SQL expression. It throws "arcgisscripting.ExecuteError: ERROR 000358: Invalid expression" when it gets to the SelectLayerByAttributes portion, an issue that does not happen when run from the python console. my entire script runs just fine via console, but breaks when run as a GP tool.  The temp layer, btw, was created by an in_memory feature class if that's relevant.

with arcpy.da.SearchCursor(temp_worklocation_layer, "OID@") as locations:
        for point in locations:
            cur_point_sql = f"OBJECTID={point[0]}"
            arcpy.management.SelectLayerByAttribute(temp_worklocation_layer, "CLEAR_SELECTION")
            arcpy.SelectLayerByAttribute_management(temp_worklocation_layer, 'NEW_SELECTION', cur_point_sql)

Do GP tools handle SQL queries in a slightly different format than whatever handles it in the python console? Why does "OBJECTID = #" work in one but not the other?

Also slightly related, but this method of grabbing multiple features and then iterating back through them with the OID one at a time and doing a SelectLayerByLocation to grab other layers' features nearby is something I do numerous times across my scripts. Is that the most efficient way? Am I doing something redundant?

danretired · Answer

Considerations when using the in_memory workspace—ArcGIS Pro | Documentation

has limits on what it can use as inputs.

intermediate results will vanish unless they are saved to disk, which I suspect might be part of the problem.

In any event, the scratch geodatabase and Delete_management do provide similar functionality especially if one's machine is memory limited

Glasnoct · Answer

I solved my issue, but I'm not really sure how I did so. I have a little all-in-one function that puts a temporary in_memory layer on the map. When I changed 'in_memory' to 'memory', the problem went away.

def gen_inmem_maplayer(name, fctype, spatialreflyrname):  # layer name, layer type [str], layer for spatial reference
    inmem_fc_path = arcpy.management.CreateFeatureclass('in_memory', name, fctype, '', '', '', spatialreflyrname)[0]
    inmem_lyr_path = arcpy.MakeFeatureLayer_management(inmem_fc_path, name)[0]
    mxd.addLayer(inmem_lyr_path, 'TOP')

What I find confusing is that in the process of trying to work out a solution, the script would run fine as a GP tool if I joined all the relevant pieces of code into a single script file instead of referencing functions from my other modules. Originally my GP tool looked something like this:

GP tool script--------------------------------------
import database_Functions # another .py file
import engineering_units  # another .py file

engineering_units.somefunction(args)
    - this function calls database_functions.gen_inmem_maplayer as seen above
    - then attempts to run the problematic loop with the SQL query
    - script fails at this point complaining about the SQL query

if instead I run a GP tool script that looks like this, I don't get the query complaint

GP tool script--------------------------------------
import database_Functions # another .py file
import engineering_units  # another .py file

database_functions.gen_inmem_maplayer
<problematic loop with the SQL query from engineering_units.somefunction()>

My guess is that once gen_inmem_maplayer finishes, it clears anything written to in_memory and thus engineering_units script cannot locate it. This might explain the exclamation point that appears next to the map layer almost immediately after the layer is added to the map and gen_inmem_maplayer completes.

danretired · Answer

Select Layer By Attribute (Data Management)—ArcGIS Pro | Documentation

"[NAME] = 'California'"

'"population" > 10000'

just to add to the confusion...

but the single and double quotes appear to be common so

"OBJECTID = #"  may be '"OBJECTID" = #'

best bet is to run the tool and copy as a python snippet