Make Query Table works in Python Window but not as Tool???

[LonnieBilyk]

Hi,

I'm a newbie at coding, so I hope my question is clear.

The below code works perfectly when I run it in the Python Window of 10.1.

But when I make a tool and attach the script, it runs and indicates that the script completed successfully but yet the table is not added to the TOC. Why will it not automatically appear?

Any ideas? I've tried so many things to make the table appear, I can't even remember anymore.

Lonnie




import arcpy, datetime, arcgisscripting, os
from arcpy import env

# Local variables.
tableList = "Database Connections/Database.odc/FWS_WMIS.FWI_INVENTORY_PROJECT;Database Connections/Database.odc/FWS_WMIS.FWI_INVENTORY_SURVEY"
   
fieldList = "FWS_WMIS.FWI_INVENTORY_PROJECT.INV_PROJ_ID 'INVENTORY_PROJECT_ID';FWS_WMIS.FWI_INVENTORY_SURVEY.INV_SURVEY_ID 'INV_SURV_ID';FWS_WMIS.FWI_SURVEY_CREW_FNC(FWS_WMIS.FWI_INVENTORY_SURVEY.INV_SURVEY_ID) 'SURVEY_CREW'"
whereClause = "FWS_WMIS.FWI_INVENTORY_PROJECT.INV_PROJ_ID=FWS_WMIS.FWI_INVENTORY_SURVEY.INV_PROJ_ID AND FWS_WMIS.FWI_INVENTORY_PROJECT.INV_PROJ_ID=17909"
keyField = "FWS_WMIS.FWI_INVENTORY_PROJECT.INV_PROJ_ID"

#Get the current date and time.
now = datetime.datetime.now()

#Use the current time as the name for the layer to produce a unique name for every layer.
lyrName = now
   
# Make Query Table.
arcpy.MakeQueryTable_management(tableList, lyrName,"USE_KEY_FIELDS", keyField, fieldList, whereClause)

[JasonScheirer]

You'll need to make your table an output parameter of your tool. Only tables and layers that are specified as outputs will be added to the table of contents, the geoprocessing framework assumes any other layers and tables that aren't named are intermediate data.

So do this:

1. Add a parameter to your script tool: Output, Table View


2. At the end of your script, use arcpy.SetParameterAsText(0, lyrName) to pass the table along

[LonnieBilyk]

Thanks.

I do remember trying that one. I just tried it again and I get the same result: the script indicates that it ran successfully but the table does not appear in the TOC.

It's killing me...

[KevinBell]

a couple thoughts here:

geoprocessing menu/gp options/ check 'add gp results automatically' (maybe this one's too simple)

or

addLayer = arcpy.mapping.Layer('pathToMyLyr.lyr')
arcpy.mapping.AddLayer(df, addLayer, 'TOP')
arcpy.RefreshActiveView()

[AmyKlug]

can you posting the entire code showing where you have a parameter defined?

a possibility from the code you posted......a query table is intermediate only, you can use CopyRows (as parameter) to make a permanent table that will appear in your table of contents

Here is how to post code:

http://forums.arcgis.com/threads/48475-Please-read-How-to-post-Python-code

[LonnieBilyk]

Sorry for the slow reply. Things got busy.

I got it figured out and the on-line examples are not clear.

This doesn't work:

arcpy.MakeQueryTable_management(tableList, lyrName,"NO_KEY_FIELD", keyField, fieldList, whereClause)

This works:

arcpy.gp.MakeQueryTable_management(tableList, lyrName,"NO_KEY_FIELD", keyField, fieldList, whereClause)

The only difference is the "gp".

As I said, maybe this is simple, but it sure wasn't obvious to me. Thanks for everyone who spent time on this one.

[curtvprice]

Just highlighted just as a test of GeoNet. I did this by pasting the code, selecting the code, picking the advanced editor (upper right), selecting >> icon, syntax highlighting -> Python.

import arcpy, datetime, arcgisscripting, os
from arcpy import env

# Local variables.
tableList = "Database Connections/Database.odc/FWS_WMIS.FWI_INVENTORY_PROJECT;Database Connections/Database.odc/FWS_WMIS.FWI_INVENTORY_SURVEY"

fieldList = "FWS_WMIS.FWI_INVENTORY_PROJECT.INV_PROJ_ID 'INVENTORY_PROJECT_ID';FWS_WMIS.FWI_INVENTORY_SURVEY.INV_SURVEY_ID 'INV_SURV_ID';FWS_WMIS.FWI_SURVEY_CREW_FNC(FWS_WMIS.FWI_INVENTORY_SURVEY.INV_SURVEY_ID) 'SURVEY_CREW'"
whereClause = "FWS_WMIS.FWI_INVENTORY_PROJECT.INV_PROJ_ID=FWS_WMIS.FWI_INVENTORY_SURVEY.INV_PROJ_ID AND FWS_WMIS.FWI_INVENTORY_PROJECT.INV_PROJ_ID=17909"
keyField = "FWS_WMIS.FWI_INVENTORY_PROJECT.INV_PROJ_ID"


#Get the current date and time. 
now = datetime.datetime.now()


#Use the current time as the name for the layer to produce a unique name for every layer.
lyrName = now

# Make Query Table.
arcpy.gp.MakeQueryTable_management(tableList, lyrName,"USE_KEY_FIELDS", keyField, fieldList, whereClause)

This looks a little buggy. I'm wondering if the fieldList were constructed as a Python list whether it would parse correctly using the normal interface (not going through the old "gp" module connection).

[TomBrabant]

I am having a similar issue running

arcpy.MakeQueryTable_management (['XYfeatureTable'], "q92","ADD_VIRTUAL_KEY_FIELD","",[["XYfeatureTable.EHANDLE"]],"")

The function executes and it has done something, because if I try to run it again with out changing the "q92" to something else, it tells me that it can't run (this second time) because "q92" already exists. I've tried to access this result several ways without success.

Unfortunately, I am running 10.2.2.3552 and it seems that arcpy.gp.MakeQueryTable_management

does not exist in this version.

[curtvprice]

> Unfortunately, I am running 10.2.2.3552 and it seems that arcpy.gp.MakeQueryTable_management does not exist in this version.

I'm not seeing this. It's finding all flavors in my copy of 10.2.2.

>>> import arcpy
>>> arcpy.GetInstallInfo()["BuildNumber"]
u'3552'
>>> arcpy.MakeQueryTable_management
<function MakeQueryTable at 0x15AAA8F0>
>>> arcpy.management.MakeQueryTable
<function MakeQueryTable at 0x15AAA8F0>
>>> arcpy.gp.MakeQueryTable_management
<function <lambda> at 0x15F4A5F0>
>>> arcpy.gp.MakeQueryTable
<function <lambda> at 0x15F4A630>

I highly recommend against the "arcpy.gp" construction unless you really need it, for example, to access deprecated tools.

[TomBrabant]

Thanks, Curtis.

I do indeed have all of those gp functions defined - a namespace boo-boo led me to deceive myself. I'll avoid them per your advice: I read they were deprecated but forgot.

I was able to get somewhat farther with arcpy.MakeQueryTable_management. I received a result object from which I could obtain a tableview object which claimed it lived in the geodatabase according to its datasource property. But there I was stuck. Nothing ever changed in the GUI, even after refresh. And, in trying to turn the tableview object into something "tangible",  I got errors when trying to use  arcpy.CopyFeatures_management saying that the value was not a Feature Layer.

However, I was doing all this by importing arcpy into PythonWin. I just ran arcpy.MakeQueryTable_management in the ArcMap python window and it made an entry in the TOC that I can right click and open. It even persists on a close with no save, though there still is no evidence of it in the Catalog window.

What I am trying to work towards is the cleanest way to do joins on a feature class and a data table. Ideally, I was seeking some kind of intermittent data structure that I could produce with python and embedded sql, so I could say, find all buildings in a feature class taller than 10 stories (assuming the height is stored in the data table, but not the feature table.) I have a feeling I might be going about it the wrong way in general.

I'm also obviously curious at this point about perceived limitations in using arcpy through PythonWin.

I went through the whole process described above in IDLE and got identical results to PythonWin. I have attempted to post that trail below this point.

>>> import arcpy

>>> arcpy.env.workspace =r"Y:\lsp\tom\ESRIresearch\Monticello\phase2\smallSampleDB\smallSample.gdb"

>>> outtable='q84'

>>> test=arcpy.MakeQueryTable_management (['XYfeatureTable'], outtable,"ADD_VIRTUAL_KEY_FIELD","",[["XYfeatureTable.EHANDLE"]],"")

>>> test

<Result 'q84'>

>>> o=test.getOutput(0)

>>> o

<TableView object at 0x17d58c10[0x2a995d8]>

>>> o

<TableView object at 0x17d58c10[0x2a995d8]>

>>> o.dataSource

u'Y:\\lsp\\tom\\ESRIresearch\\Monticello\\phase2\\smallSampleDB\\smallSample.gdb\\q84'

>>> o.datasetName

u'q84'

>>> sc=arcpy.da.SearchCursor(o.dataSource,'XYfeatureTable.EHANDLE')

Traceback (most recent call last):

  File "<pyshell#10>", line 1, in <module>

    sc=arcpy.da.SearchCursor(o.dataSource,'XYfeatureTable.EHANDLE')

RuntimeError: cannot open 'Y:\lsp\tom\ESRIresearch\Monticello\phase2\smallSampleDB\smallSample.gdb\q84'

>>> arcpy.CopyFeatures_management(o.dataSource,"Y:/lsp/tom/ESRIresearch/Monticello/phase2/smallSampleDB/smallSample.gdb/test")

Traceback (most recent call last):

  File "<pyshell#11>", line 1, in <module>

    arcpy.CopyFeatures_management(o.dataSource,"Y:/lsp/tom/ESRIresearch/Monticello/phase2/smallSampleDB/smallSample.gdb/test")

  File "C:\Program Files (x86)\ArcGIS\Desktop10.2\arcpy\arcpy\management.py", line 2429, in CopyFeatures

    raise e

ExecuteError: Failed to execute. Parameters are not valid.

ERROR 000732: Input Features: Dataset Y:\lsp\tom\ESRIresearch\Monticello\phase2\smallSampleDB\smallSample.gdb\q84 does not exist or is not supported

Failed to execute (CopyFeatures).

>>> arcpy.CopyFeatures_management(o.datasetName,"Y:/lsp/tom/ESRIresearch/Monticello/phase2/smallSampleDB/smallSample.gdb/test")

Traceback (most recent call last):

  File "<pyshell#12>", line 1, in <module>

    arcpy.CopyFeatures_management(o.datasetName,"Y:/lsp/tom/ESRIresearch/Monticello/phase2/smallSampleDB/smallSample.gdb/test")

  File "C:\Program Files (x86)\ArcGIS\Desktop10.2\arcpy\arcpy\management.py", line 2429, in CopyFeatures

    raise e

ExecuteError: Failed to execute. Parameters are not valid.

ERROR 000840: The value is not a Feature Layer.

ERROR 000840: The value is not a Raster Catalog Layer.

Failed to execute (CopyFeatures).