Changing Query Layer's SQL Expression with Python

Question

I use query layers in ArcMap 10.4. The maps need to be done/repeated periodically. The easiest way is to copy the map, change the source's SQL expression (e.g. from select * from TABLE where Period = '2016-March' to select * from TABLE where Period = '2016-April') and that's it. But the expressions are long and hard to edit. Then I have many similar maps, where I have to make edits manually.

Is there a way to do this with Python?

Using the combination of query layer and definition query is not doable in my case.

Answer 1

Yes it's possible to do this in python. Check out the documentation on the Make Query Layer tool http://pro.arcgis.com/en/pro-app/tool-reference/data-management/make-query-layer.htm

You can also use the make query table tool (http://pro.arcgis.com/en/pro-app/tool-reference/data-management/make-query-table.htm); if you specify a shape field the query table becomes a query layer.

If the expression is all that needs to be edited, you can type the expression out and use Python's .format() to pass in whatever changes you want to make (https://docs.python.org/2/library/string.html#format-string-syntax). Like this:

date = '2016-April'
expression = "select * from TABLE where Period = '{}'".format(date)

You can even automate the updated date for your input

import datetime
import calendar
current_month_year = str(datetime.datetime.now()).split('-')[:2]
date = "{}-{}".format(current_month_year[0], calendar.month_name[int(current_month_year[1])])
expression = "select * from TABLE where Period = '{}'".format(date)

Which gives the result: "select * from TABLE where Period = '2016-April'"

calendar: https://docs.python.org/2/library/calendar.html#calendar.month_name

datetime: https://docs.python.org/2/library/datetime.html

If you use arcpy.MakeQueryTable_management() to create your query layer, use arcpy.CopyFeatures_management() to save it as a feature class/shapefile.

EDIT: Arcpy also has tools for total map production (http://pro.arcgis.com/en/pro-app/arcpy/mapping/getting-started-with-arcpy-mp-tutorial.htm) in python. If your map-making procedure involves changing one layer, adding it to a template with other feature classes/shapefiles, and exporting as a pdf, all of these things can be done in python.

Googling "creating a map with arcpy in python" yields a lot of good resources to help you get started (https://www.google.com/#q=creating+a+map+with+arcpy+in+python)

• Paul: Since you are already using datetime, no need to work with strings at all: datetime.datetime.now().strftime("%Y-%B")
• Alex Zhuk: I'll take a look at this. However in my case, and I should've been more specific, Periods are the text fields, rather than Date/Time. I was thinking more in terms of (if possible) QLayer.SourceQuery.Replace("OldText","NewText")