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.
