Excluding field on table from being copied by ArcPy?

Question

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))
22

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))
22

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
u''
>>> some_column.type
u'Integer'
>>> some_column.length
4

...? (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))
17
>>> new_layer = arcpy.MakeFeatureLayer_management(layer, 'TESTLAYER2', field_info=field_changes)[0]
>>> new_layer
<map layer u'TESTLAYER2'>
>>> len(arcpy.ListFields(new_layer))
17
>>> x = arcpy.ListFields(new_layer)[-1]
>>> x
<Field object at 0x12e7ebf0[0x29e07b8]>
>>> x.name
u''
>>> 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.

Answer 1

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).