Table analog of MakeQueryLayer

Question

As of 10.1, arcpy provides the rather handy MakeQueryLayer function. This allows us to use arcpy to create query layers programmatically instead of having to go through MXDs (as was the case in 10.0). This is extremely useful when working with a database that does not contain the SDE structure (formerly created via post-install and now created by Enable Enterprise Geodatabase, I believe) or where the tables you want to interact with have not been registered with SDE.

However, now I want to create a table instead of a feature layer/class, as in the query will not contain a shape column. How can I go about this?

What I've tried:

I considered MakeQueryTable, but this takes an existing layer or table as an input, rather than a query.

I also just tried to use MakeQueryLayer and got some... confusing results. conn below is a str that contains the file path of my database connection file.

>>> r = arcpy.MakeQueryLayer_management(conn, 'TEST', 'SELECT ID AS OBJECTID, MY_TABLE.* FROM MY_SCHEMA.MY_TABLE', oid_fields=['OBJECTID'])
>>> r.status
4
>>> r.outputCount
1
>>> r[0]
u'TEST'
>>> type(r[0])
<type 'unicode'>

The status code 4 corresponds to Succeeded, but the result object contains a Unicode string as its only output. So I'm not sure if it successfully created a layer or table and I just can't find it or what. Normally, MakeQueryLayer outputs an actual Layer object:

>>> r2 = arcpy.MakeQueryLayer_management(conn, 'TEST2', 'SELECT ID AS OBJECTID, MY_TABLE_WITH_SHAPE.* FROM MY_SCHEMA.MY_TABLE_WITH_SHAPE', oid_fields=['OBJECTID'])
>>> r2.outputCount
1
>>> r2[0]
<map layer u'TEST2'>
>>> type(r2[0])
<class 'arcpy._mapping.Layer'>

I tried to do some work with arcpy.mapping.ListLayers and arcpy.mapping.ListTableViews to see if I could find the output layer, but those require an input document (and I'm not using a document).

My back end database happens to be Oracle, but I imagine that shouldn't matter.

Answer 1

You've already got it working!

Note that "If the SQL query does not return a spatial column, the output will be a stand-alone table"

>>> r = arcpy.MakeQueryLayer_management(conn, 'TEST', 'SELECT ID AS OBJECTID, MY_TABLE.* FROM MY_SCHEMA.MY_TABLE', oid_fields=['OBJECTID'])
>>> r.status
4
>>> r.outputCount
1
>>> r[0]
u'TEST'
>>> type(r[0])
<type 'unicode'>
>>> with arcpy.da.SearchCursor(r[0],['OBJECTID']) as rows:
>>>     for row in rows:
>>>         print row[0]
0
1
2
3
4
5
etc...

It's a bit confusing that the "layer" is a string instead of a Layer object, but I assume it's just a pointer (think layer/table name in ArcMap table of contents) to the underlying object.

Update: as per @jpmc26's comment, t = arcpy.mapping.TableView(r[0]) will convert the result string into a TableView object.