script tools, get parameter as text and sql in Select layer by attributes

[dcaESRIwvges]

ESRI community,

I have the following tool wehere the user can enter in a formation, seam, and then a modifier. There is a feature class with three different fields for each one and I'm trying to tell arcpy to select all the records that have the user defined values in field1 and field2 and field3. I am beating my head against the wall becuase this works great in ArcGIS pro when i use the tool, or as a query defintion but I can't get the string right for the SQL because it seems this advanced logic can only use SQL in arcgis... any suggestions are helpful!! 

import arcpy
from pathlib import Path
import os
point_layer_location = Path(arcpy.GetParameterAsText(0))
point_layer_name = arcpy.GetParameterAsText(1)
point_layer = str(point_layer_location / "{0}".format(point_layer_name))
fields = ['formation','seam','zone_modifier']
us_formation = arcpy.GetParameterAsText(2)
us_seam = arcpy.GetParameterAsText(3)
us_modifier = arcpy.GetParameterAsText(4)
arcpy.env.workspace = str(point_layer_location)
current_workspace = arcpy.env.workspace
arcpy.AddMessage("Current Workspace: " + str(current_workspace))
arcpy.env.overwriteOutput = True

target_horizon = arcpy.management.SelectLayerByAttribute(
                   in_layer_or_view= point_layer,
                   selection_type="NEW_SELECTION",
                   where_clause=f"formation = '{0}' And seam = '{1}' And zone_modifier = '{2}'".format(us_formation,us_seam,us_modifier),
                   invert_where_clause=None)
arcpy.AddMessage("records selected")
arcpy.management.CopyRows(in_rows= target_horizon,
                            out_table="Selected_Records")
arcpy.management.SelectLayerByAttribute(
                   in_layer_or_view= point_layer,
                   selection_type="CLEAR_SELECTION",
                   where_clause="",
                   invert_where_clause=None)
count1 = int(arcpy.management.GetCount(target_horizon).getOutput(0))
arcpy.AddMessage(f"Total Selected: ".format(count1))

arcpy.AddMessage("Process Complete and Selection Cleared")

[AlfredBaldenweck · Accepted solution]

Your where clause is wrong. It prints to 

formation = '0' And seam = '1' And zone_modifier = '2'

 I think mixing the f-string with --.format() is messing things up. Removing the f fixed it for me.

That being said, copyrows() gives you a table, not a feature class. idk if that was intended, but that's what you're getting.

Hope this helps!

[dcaESRIwvges]

it's funny, i took out the f in front, closed arcgis pro, reopened it and ran the tool again and it worked. so this is what the where clause should be.

"formation = '{0}' And seam = '{1}' And zone_modifier = '{2}'".format(us_formation,us_seam,us_modifier),

and it worked. but i still need to work on the count part as it never printed the count of the results

[AlfredBaldenweck]

THat's because your print message doesn't contain the place for the value. I missed that earlier

Should be 

count1 = int(arcpy.management.GetCount(target_horizon).getOutput(0))
arcpy.AddMessage("Total Selected: {0}".format(count1))

#Or

arcpy.AddMessage(f"Total Selected: {count1}")

As an aside, you can use the outputCount property instead of having to do getOutput, getCount

[Clubdebambos]

Another aside, you can just use square bracket notation and retrieve the derived output from the Result object.

count1 = int(arcpy.management.GetCount(target_horizon)[0])

The documentation says the derived output is a Long, but it's really a String, hence the need to cast.

count1 = arcpy.management.GetCount(target_horizon)

print(count1) # this will print the number just fine
print(type(count1) # shows that it is a Result object
print(int(count1)) # will fail because you cant cast a Result object to an int
print(int(count1[0])) # will cast the string to an int

But also no need to cast if using formatting

count1 = arcpy.management.GetCount(target_horizon)

print(f"This is the count: {count1}")
print("This is the count: {0}".format(count1))

[AlfredBaldenweck]

The real sin here is that so many arcpy functions return Results objects instead of anything actually useful.

[DavidSolari]

Try using an Add Message to print out that where clause string, I have a feeling your use of a format string with a format method isn't doing what you think it is.

In general you should give your hand-written code a once over. For example, you're using pathlib to turn the current workspace into a Path object, but then you convert it into a string at every turn instead of using the native path building methods. Either use Path objects to their fullest or use the os.path functions to manipulate string paths (which arcpy prefers anyways). You also do a bunch of environment workspace stuff that goes nowhere, unless you're using functions like ListFeatureClasses you don't need to set this variable. Honestly you can probably ask for the feature class directly as one parameter and then peel off the workspace path from a Describe dictionary if needed.

[dcaESRIwvges]

Thank you for your critique. I'm still learning and those are very helpful suggestions. I need to understand the difference. I did it this way so someone could change the workspace if they needed to.