Unable to read data from Query Layer

[deleted-user-Ohz6rwd1kavx]

Hi,

I'm unable to use Python/Geoprocessing to read data from an ESRI Query Layer. How can I make this work?

I've tried two things:
1) Use Make Feature Layer to read from .lyr

Reference: http://forums.arcgis.com/threads/584-beta-10-system-tool-for-extracting-query-layer-data-to-file-gdb

Code explained: Works for .lyr based on layer (data source is sde spatial view which arcgis sees as a feature class). Does not work for .lyr based on query layer (data source is sde view which arcgis sees as a table).

# Import system modules
import sys, string, os, arcpy

arcpy.AddMessage("start script....")

#lyr = "C:\Projects\BranchMapping\dev\src\GP_Tasks\ppOwner.lyr"
lyr = "C:\Projects\BranchMapping\dev\src\GP_Tasks\BRANCHES.lyr"

#qry = "KeyInstn = 4001616" # D
qry = "KeyInstn = 4019974" # VABK

arcpy.AddMessage("Making feature layer....")
arcpy.MakeFeatureLayer_management(lyr, "lyr", qry)

arcpy.AddMessage("Getting count....")
result = arcpy.GetCount_management("lyr")

count = int(result.getOutput(0))
arcpy.AddMessage(str(count))

2) MakeQueryTable

Reference: http://forums.arcgis.com/threads/25496-Query-Layer?highlight=query+layer+geoprocessing+python

Opening the tool I browse to the sde view. Afterwards I get this error: ERROR 000793
Invalid data element type ERROR 000840: The value is not a Raster Layer.

I read the doc, but I don't understand. Can I use this tool to create a layer from a "query layer" table data source (i.e. same data source that I can use to create a Query Layer in ArcMap).

Does ESRI have a way to use PY to read data from a Query Layer data source?

Thanks,

-Cory

[MikeMacRae]

Just having a look, a couple things come to mind. First off, if your input is already a layer, it shouldn't need a conversion to a feature layer...it already is. The script may be confusing itself with that logic. Try just getting a count on the lyr instead of "lyr"

The second thought you might try is to see if your layer is being sourced properly to the underlying feature class. Go into catalog and navigate to the layer file. Right click and go into properties to see if it has a source. Then, go to table view and see if there is any data in the table. If there isn't, then either the underlying feature class is empty or the source isn't connecting properly.

Finally, I don't work in sde and know very little about it, but maybe check how your query is built. Queries on fields are built differently based on personal and file geodatabases. If in doubt, I build a test model and export to script to see how the query syntax should be built.

Anyways, hope some of this helps.

Mike

[deleted-user-Ohz6rwd1kavx]

Hi Mike

Thanks for the advice. I am getting closer...

Example 1 (based on your tip, thanks!) works...

# Import system modules
import sys, string, os, arcpy

arcpy.AddMessage("start script....")

lyr = "C:\Temp\gpFun\BRANCHES_VABK.lyr" # includes query

arcpy.AddMessage("Getting count....")
result = arcpy.GetCount_management(lyr)

count = int(result.getOutput(0))
arcpy.AddMessage(str(count))

... now, how to apply query external to the .lyr definition.

Example 2 Select layer by attribute fails....

# Import system modules
import sys, string, os, arcpy

arcpy.AddMessage("start script....")

lyr = "C:\Temp\gpFun\BRANCHES.lyr"

qry = "KeyInstn = 4019974" # VABK
arcpy.AddMessage(qry)

arcpy.AddMessage("Selecting layer by attribute....")
arcpy.SelectLayerByAttribute_management (lyr, "NEW_SELECTION", qry)

arcpy.AddMessage("Getting count....")
result = arcpy.GetCount_management(lyr)

count = int(result.getOutput(0))
arcpy.AddMessage(str(count))

.... here is the error:

<class 'arcgisscripting.ExecuteError'>: Failed to execute. Parameters are not valid.
ERROR 000825: The value is not a layer or table view
ERROR 000840: The value is not a Raster Layer.
ERROR 000840: The value is not a Mosaic Layer.
Failed to execute (SelectLayerByAttribute).

Example 3 uses TableSelect...

# Import system modules
import sys, string, os, arcpy

arcpy.AddMessage("start script....")

lyr = "C:\Temp\gpFun\BRANCHES.lyr"

qry = "KeyInstn = 4019974" # VABK
arcpy.AddMessage(qry)

arcpy.AddMessage("Table selecting....")
arcpy.TableSelect_analysis(lyr, "c:/temp/gpFun/tempOut.shp", qry)

... this works, but the .shp is a table, not a feature class. This won't work for me. I need to perform spatial operations on this like buffer, etc.

Weird. I think there is some missing piece for "reading" Query Layers as features, not rows.

BTW, Query Layers aren't geodatabase feature classes. They're actually tables in the DBMS with spatial columns. ESRI application then sees these as shapes. GP/Python seems to have problems seeing them though...

Thanks for your help,

-Cory

[DeeptiPuri]

Hi Cory

I wonder whether you find the solution to your problem. I am on the same boat and looking for a possible solution.

Thanks

[OlivierOlivier]

Hi,

It seems to work if you add makefeaturelayer

arcpy.AddMessage("Making feature layer....")
lay = arcpy.MakeFeatureLayer_management(lyr, "lyr")

qry = "NAME = 'TEST'"
arcpy.SelectLayerByAttribute_management (lay, "NEW_SELECTION", qry)

Olivier

[OlivierOlivier]

Hi,

This works partially for me :

arcpy.AddMessage("Making feature layer....")
lay = arcpy.MakeFeatureLayer_management(lyr, "lyr")

qry = "CODE like 'COD%'"
res=arcpy.SelectLayerByAttribute_management (lay, "NEW_SELECTION",qry)

curs = arcpy.SearchCursor(res,"","","CODE")
for row in curs:
    arcpy.AddMessage(str(row.CODE))

but as I'm not very skilled with arcpy, I don't understand why the filter doesn't work, res contains all objects (so SelectLayerByAttribute) is useless.

Thanks for any help.

Olivier

Edit : But it works if I use qry in MakeFeatureLayer_Management, forgetting about  SelectLayerByAttribute. If someone can explain me my mistake, I would appreciate.

[JohnPenney]

Hi,

What version of Arc are you using?

I did get this working based on your example. There may have been some small syntax errors in your code.
Try writing your lyr and qry like this:
[HTML]
#lyr = "C:\Projects\BranchMapping\dev\src\GP_Tasks\ppOwner.lyr"
lyr = r"C:\Projects\BranchMapping\dev\src\GP_Tasks\BRANCHES.lyr"

#qry = "KeyInstn = 4001616" # D
qry = "\"KeyInstn\" = 4001616" # VABK
[/HTML]

There is a little more info on correct syntax within a where clause for Python here:
http://resources.arcgis.com/en/help/main/10.1/index.html#//001700000071000000

And the error that you are receiving when trying to use "SelectLayerByAttribute" may be fixed by using "MakeFeatureLayer" first which works as input for the Select Layer by Attribute tool. So basically something like this:
[HTML]

# Import system modules
import sys, string, os, arcpy

#lyr = "C:\Projects\BranchMapping\dev\src\GP_Tasks\ppOwner.lyr"
lyr = r"C:\Projects\BranchMapping\dev\src\GP_Tasks\BRANCHES.lyr"

#qry = "KeyInstn = 4001616" # D
qry = "\"KeyInstn\" = 4001616" # VABK

# You do need to make feature layer to use as input select layer by attribute
arcpy.MakeFeatureLayer_management(lyr,"lyr")

arcpy.SelectLayerByAttribute_management("lyr", "NEW_SELECTION", qry)

result = arcpy.GetCount_management("lyr")
count = int(result.getOutput(0))
arcpy.AddMessage(str(count))
[/HTML]

I hope that helps!