How to add query table to map in python

661
2
Jump to solution
09-17-2019 10:54 AM
DonMorrison1
Occasional Contributor III

I have an SQL Server view that at I want to include in my ArcGIS server's REST endpoint as a table. I'm able to do it manually using ArcMap - it appears to create a query table when I drag the view onto the table of contents. How can I do this in python?

Currently I go through this sequence

  1. Start with a blank mxd file: mxd_template_obj = arcpy.mapping.MapDocument(blank_template_fn)
  2. Get the dataframe: df = ListDataFrames(mxd_template_obj)[0]
  3. Create the query table (according to the documentation is creates a TableView): arcpy.MakeQueryTable_management(db_view, table_view_name, ....)
  4. Convert it to a TableView object: table_view = arcpy.mapping.TableView(table_view_name)
  5. Add it to the map: arcpy.mapping.AddTableView(df, table_view)
  6. Save the map: mxd_template_obj.saveACopy(<mxd_file_name>)

It appears to generate a good mxd file - I can go into ArcMap and everything looks correct, but when I try to stage it for publishing I get this error:

ExecuteError: ERROR 001272: Analyzer errors were encountered (codes = 33, 33)

Standalone table's data source is not supported

I suspect that my step 4 is wrong - once I have the query table I need to do something else to get it into the map correctly but I'm not sure what that is. 

0 Kudos
1 Solution

Accepted Solutions
DonMorrison1
Occasional Contributor III

I got it working with Python 3 here are the steps.

  1. create aprx file: aprx = arcpy.mp.ArcGISProject(<blank aprx file>)
  2. get the map object: m_map = aprx.listMaps()[0]
  3. create a table object from the view:  table = arcpy.mp.Table(<path to db view>)
  4. name the table: table.name = <name I make up>
  5. add a where clause: table.definitionQuery = '<where clause>'
  6. add the table to the map: m_map.addTable (table)
  7. save aprx file: aprx.save()
  8. publish the aprx

This sequence is actually the same as the code I use to add regular tables (not views) to a map - so it should have been fairly obvious to me.  I think the real reason my code kept failing was that my view did not include an OBJECTID column - or perhaps more precisely a column with non-null unique values (at least that is what the documentation says is required of all tables and views). Once I added a non-null unique column column I was able to create and publish the map.

View solution in original post

0 Kudos
2 Replies
DonMorrison1
Occasional Contributor III

I got this to work on Python 2.7 by changing step 3 from MakeQueryTable to MakeQueryLayer. Now I'm trying to figure out how to do the equivalent in Python 3/ArcPro.

  1. Start with a blank mxd file: mxd_template_obj = arcpy.mapping.MapDocument(blank_template_fn)
  2. Get the dataframe: df = ListDataFrames(mxd_template_obj)[0]
  3. Create the query layer: arcpy.MakeQueryLayer_management(db_view, table_view_name, ....)
  4. Convert it to a TableView object: table_view = arcpy.mapping.TableView(table_view_name)
  5. Add it to the map: arcpy.mapping.AddTableView(df, table_view)
  6. Save the map: mxd_template_obj.saveACopy(<mxd_file_name>)
0 Kudos
DonMorrison1
Occasional Contributor III

I got it working with Python 3 here are the steps.

  1. create aprx file: aprx = arcpy.mp.ArcGISProject(<blank aprx file>)
  2. get the map object: m_map = aprx.listMaps()[0]
  3. create a table object from the view:  table = arcpy.mp.Table(<path to db view>)
  4. name the table: table.name = <name I make up>
  5. add a where clause: table.definitionQuery = '<where clause>'
  6. add the table to the map: m_map.addTable (table)
  7. save aprx file: aprx.save()
  8. publish the aprx

This sequence is actually the same as the code I use to add regular tables (not views) to a map - so it should have been fairly obvious to me.  I think the real reason my code kept failing was that my view did not include an OBJECTID column - or perhaps more precisely a column with non-null unique values (at least that is what the documentation says is required of all tables and views). Once I added a non-null unique column column I was able to create and publish the map.

0 Kudos