topic Re: I need some help with python script to create definition query? in Developers Questions

I need some help with python script to create definition query?

DavidSmith — Fri, 16 Mar 2018 21:30:28 GMT

I am trying to write a python script that will allow users to input multiple values from one field(up to 10) to create a definition query for those values. I get the script to work for one value but not for multiple.I am currently testing with just two values using the OR but when I run the script I get one value twice in the definition query for example, Color = ('red') OR Color = ('red')Can one of the experts help me out? Thanks. This is what I have so far:

import arcpy
#Set to current mxd and dataframe
mxd = arcpy.mapping.MapDocument ('CURRENT')
df = arcpy.mapping.ListDataFrames(mxd,"Layers")
Layer = (mxd,"Test",df)
# Set the tool parameters
InputFeatureclass = 'Test'
InputField = 'Sample'
InputValue = arcpy.GetParameterAsText(0)
InputValue1 = arcpy.GetParameterAsText(1)

Sample= arcpy.mapping.ListLayers(mxd, "Test")[0]
Sample.definitionQuery = """Color = ('{1}') OR Color = ('{1}')""".format(arcpy.AddFieldDelimiters(InputFeatureclass, InputField), InputValue)
arcpy.RefreshActiveView()
mxd.save()

Re: I need some help with python script to create definition query?

JamesMacKay3 — Fri, 16 Mar 2018 21:41:14 GMT

There are a couple things happening, it looks like you're using the AddFieldDelimiters function as the first argument to the format method, but you aren't using a placeholder in place of a field name, you've got "Color" hardcoded. The second thing is that you've got the same placeholder ({1}) for both values, which is why you're seeing "red" repeated.

You can probably toss the AddFieldDelimiters section and go with this:

Sample.definitionQuery = """Color = '{0}' OR Color = '{1}'""".format(InputValue, InputValue1)‍

If you're going beyond two values you might want to switch to using the SQL IN operator instead of chaining ORs together:

Sample.definitionQuery = """Color IN ('{0}', '{1}')""".format(InputValue, InputValue1)‍

Re: I need some help with python script to create definition query?

DavidSmith — Mon, 19 Mar 2018 14:46:55 GMT

James, thanks for responding to my post. I used the SQL IN operator just like you suggested but I am still not getting the desired result. It returns just one value: Color IN ('"Color"', 'red'). I assume my parameters are correctly set up? I corrected the "InputValue1" in the script to match the first parameter in my initial post. I also tried to expand the script with a third parameter this way and it returned the same value three times.Color IN ('"Color"', 'red','red','red'). What can I do to get it to work correctly?

"""Color IN ('{0}', '{1}','{1}','{1}')"""

Re: I need some help with python script to create definition query?

JamesMacKay3 — Mon, 19 Mar 2018 15:23:04 GMT

Hi David, when you repeat a numbered placeholder (e.g. {1}) each occurrence of the number will hold the same value. You can either increment the numbers each time or remove the numbers altogether, like this:

"""Color IN ('{0}', '{1}', '{2}', '{3}')"""‍

Or this:

"""Color IN ('{}', '{}', '{}', '{}')"""‍

As far as having the word "Color" in the list, it seems as though you must be passing that into the format function as the first argument?

Re: I need some help with python script to create definition query?

DavidSmith — Mon, 19 Mar 2018 17:37:17 GMT

I have tried pretty much everything you mentioned before but i get error messages. Incrementing the numbers as placeholder or leaving blank results in:

"IndexError: tuple index out of range". Or on some occasions in trying different things I get: "ValueError: cannot switch from manual field specification to automatic field numbering". I am using ArcGIS 10.2. I am not sure if that matters anyway. I know it is probably something simple that is wrong but I can't figure it out right now.

But can I ask, is there a simple way to pass selected records into a definition query in python? That could be a workaround! I already have a script that allows a user to make multiple selections using data from one field. It is written in a 'chunky' way but it works perfect. I do not want to create any additional layer using 'make feature layer' etc. I just want the records selected by the user to be made into a definition query that displays those records of interest only.

Thanks.

Re: I need some help with python script to create definition query?

JamesMacKay3 — Sun, 12 Dec 2021 00:27:43 GMT

Could you post your updated code?

The first error message (tuple index out of range) indicates the number of arguments in your format function doesn't match the number of placeholders you've got. I think the second one means you're mixing numbered and non-numbered placeholders.

Another option is this - stitch the "IN" values together prior to creating the definition query, along these lines:

# Put the query values into a list.
queryValues = [ "Red", "Blue", "Orange" ]

# Join them into a comma-delimited list with apostrophes wrapping each value.
valuesWithApostrophes = [ "'{0}'".format(value) for value in queryValues ]
commaList = ", ".join(valuesWithApostrophes)

# Insert the comma-delimited list into the definition query.
Sample.definitionQuery = """Color IN ({0})""".format(commaList)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Edit: Posted this under the main question instead of where I meant to, but too much of a hassle to move it...

Re: I need some help with python script to create definition query?

DavidSmith — Fri, 27 Apr 2018 22:00:05 GMT

James, thanks for all your suggestions. I finally got back to this after being busy with other stuff for a while. I managed to get the script to work perfectly. This is probably not the best way to do it but since I am not an expert I will take whatever works. I had additional issues with python inserting 'u' in the definition query because I am still using version 2.7. and not 3. but I found information that I needed to use str method for string. I am posting the full working version here for reference. The variables have changed.Like I have said in my initial post I needed a script that would allow users to input multiple values from one field(up to 10 values) and create a definition query for those values.

import arcpy

#Set to current mxd and dataframe
mxd = arcpy.mapping.MapDocument ('CURRENT')
df = arcpy.mapping.ListDataFrames(mxd,"Layers")
Layer = (mxd,"SAMPLE",df)
# Set the tool parameters
InputFeatureclass = 'SAMPLE'
InputField = 'LIDs'
InputValue1 = str(arcpy.GetParameterAsText(0))
InputValue2 = str(arcpy.GetParameterAsText(1))
InputValue3 = str(arcpy.GetParameterAsText(2))
InputValue4 = str(arcpy.GetParameterAsText(3))
InputValue5 = str(arcpy.GetParameterAsText(4))
InputValue6 = str(arcpy.GetParameterAsText(5))
InputValue7 = str(arcpy.GetParameterAsText(6))
InputValue8 = str(arcpy.GetParameterAsText(7))
InputValue9 = str(arcpy.GetParameterAsText(8))
InputValue10 = str(arcpy.GetParameterAsText(9))

# Set the definition query
val = arcpy.mapping.ListLayers(mxd, "SAMPLE")[0]
val.definitionQuery = "{0} in {1} """.format(arcpy.AddFieldDelimiters(InputFeatureclass, InputField), (InputValue1,InputValue2,InputValue3,InputValue4,InputValue5,InputValue6,InputValue7,InputValue8,InputValue9,InputValue10,))

arcpy.RefreshActiveView()
mxd.save()