I have a query "layer":

>>> r = arcpy.MakeQueryLayer_management(r'C:\path\to\connection\file\connection.sde', 'TEST', 'SELECT T.ID AS OBJECTID, T.* FROM MY_TABLE T WHERE T.SOME_COLUMN = 1', oid_fields=['OBJECTID'])
>>> r
<Result 'TEST'>
>>> r[0], type(r[0])
(u'TEST', <type 'unicode'>)
>>> table = arcpy.mapping.TableView(r[0])
>>> len(arcpy.ListFields(table))

Since the database table does not have a spatial column, MakeQueryLayer_management returns a string that can then be turned into TableView.

I want to copy the features into a file geodatabase, but I want to exclude SOME_COLUMN from the output. Since the documentation for MakeTableView says

A subset of fields can be made unavailable in the new layer by using the Field Info control's visible property. ... You cannot use the hidden fields in a workflow if the newly created layer is input to a subsequent process or tool. If the output is saved to disk, only the fields listed as visible will appear in the new data,

it seemed like the perfect fit. But I'm getting strange results, and the field is showing up in my final output anyway:

>>> field_changes = arcpy.FieldInfo()
>>> field_changes.addField('SOME_COLUMN', 'SOME_COLUMN', 'HIDDEN', 'NONE')
>>> new_table = arcpy.MakeTableView_management(table, 'NEWTEST', field_info=field_changes)
>>> new_table
<Result 'NEWTEST'>
>>> len(arcpy.ListFields(new_table))

So this is where it starts getting weird. It looks like all the columns are still on the new table. SOME_COLUMN is the last column on my table, so let's take a look:

>>> some_column = arcpy.ListFields(new_table)[-1]
>>> some_column
<Field object at 0x12a7c1f0[0x29e02a8]>
>>> some_column.name
>>> some_column.type
>>> some_column.length

...? (I have confirmed that all the other fields are account for, so this Field object does correspond to the field I tried to hide.) Apparently, the field is still there, just with an empty string name. When I copy it, the field gets its name back and shows up in the final output:

>>> arcpy.CopyRows_management(new_table, 'C:/path/to/mygdb.gdb/TESTOUT')
<Result 'C:\\path\\to\\mygdb.gdb\\TESTOUT'>

The funny thing is that I can make it work with actual Layers that have a spatial column. It still does the weirdness with the field having an empty name, but the field doesn't show up in the actual output:

>>> layer = arcpy.MakeQueryLayer_management(r'C:\path\to\connection\file\connection.sde', 'TESTLAYER1', 'SELECT T.ID AS OBJECTID, T.* FROM MY_SPATIAL_TABLE T WHERE T.SOME_COLUMN = 1', ['OBJECTID'])[0]
>>> layer
<map layer u'TESTLAYER1'>
>>> len(arcpy.ListFields(layer))
>>> new_layer = arcpy.MakeFeatureLayer_management(layer, 'TESTLAYER2', field_info=field_changes)[0]
>>> new_layer
<map layer u'TESTLAYER2'>
>>> len(arcpy.ListFields(new_layer))
>>> x = arcpy.ListFields(new_layer)[-1]
>>> x
<Field object at 0x12e7ebf0[0x29e07b8]>
>>> x.name
>>> arcpy.CopyFeatures_management(new_layer, r'C:\path\to\mygdb.gdb\NEWLAYER')
<Result 'C:\\path\\to\\mygdb.gdb\\NEWLAYER'>

What am I doing wrong?

Extra Info:

I tried MakeQueryTable since it's also supposed to be able to give me a subset of fields, but that gave me the error ERROR 000055: Cannot create a Query Table for this workspace.

I could just create the table and then delete the field after creating it, but that would force more of my code to be aware of the filtering column (it's name, existence, how it's handled). I'd prefer to centralize all that handling into the code that actually creates the query layer.

  • For the moment, I'm using a FieldMappings object with TableToTable_conversion and deleting fields from the output tables based on the FieldMappins.fields list. This has the problem of the FieldMappings not containing ESRI maintained fields like the object ID or the shape area/length, so I have to detect whether the "unlisted" field is an ESRI maintained field. I do not consider this a solution, so I won't post it as an answer.
    – jpmc26
    Mar 25 '14 at 5:07
  • Hi, there is no way to message you directly. I was in the middle of answering your ST_Difference question when you deleted it. I assume you have figured out the answer? If not, it is to do with precision and the difference between ST_Equals and the = operator. ST_Difference was correct all along, your problem was with the = operator. Oct 21 '16 at 20:54

No I don't think you are doing anything wrong its just arcpy being weird!

If you want to create a new table without a specific subset of fields then use the Table to Table tool and edit the field mapping section accordingly (e.g. delete the fields you do not want in the output table).

  • More like being broken. I tried the field_mapping property, but I get the same result. The field still makes it into the geodatabase, even though I have made sure that the FieldMappings object I'm passing in has no mention of SOME_COLUMN.
    – jpmc26
    Mar 21 '14 at 22:57
  • seems like another one of those arcpy 'idiosyncrasies'. i guess you could just delete the fied once the table is copied - not the ideal solution, but it will save time fiddling around.
    – Adam
    Jul 19 '15 at 23:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.