I want to create a SQL expression based on records selected by the user.
For example my input table has only one column "HOLEID" with two records "4943" and "4944" (string).
I want to iterate through that table to generate the following SQL expression : "HOLEID" IN ('4943','4944') .
I'll then store that expression in a variable and use it in several places to select and delete records in other tables.
I'm still searching for an easy way to do that but sor far with no success. I'm not a Python pro but I can understant basic scripting.
Any ideas ?
Thanks
What’s good with ESRI’s forum it that I rarely get an answer and I’m forced to solve it myself and progress.
So here is the answer and that was actually very simple, I describe my workaround below.
Block code used for the Calculate Value tool :
#Function to complete SELECT IN statement with the multivariable ('str1', 'str2', ...)
#arcpy.da cursor return a tuple and row[0] returns the field value as string
def getSQL(inputData, FieldName):
#variable - SQL code
SQL = ""
#variable - iterator
i = 0
#open cursor for the input table and a specific field
with arcpy.da.SearchCursor(inputData, FieldName) as cursor:
#Loop through Hole IDs
for row in cursor :
i = i + 1
#If first row, start SQL code
if i == 1:
SQL = "('" + row[0]
#Otherwise keep completing the SQL code
else:
SQL = SQL + "','" + row[0]
#Close the SQL code after being out of the table
SQL = SQL + "')"
#return SQL code to the model
return SQL
It looks the most practical solution to me after my researches. However, I have a basic knolwedge of Python and if anyone has something better, please let us know, I’m not sure that the most elegant way to do that.
if anyone has something better, please let us know
How's this, this uses Python formatting, lists, and list comprehensions. Your solution works fine, but I hope you agree that this is prettier.
def csv_list_from_table(tbl, fld):
vals = []
with arcpy.da.SearchCursor(tbl, fld) as rows:
for row in rows:
vals.append(row[0])
slist = list(set(slist)) # remove any duplicates from the list
slist = ",".join(["'{}'".format(v) for v in vals])
# if a numeric field:
# slist = ",".join([str(v) for v in vals]
return "({})".format(slist) # "('a','b','c')"
What’s good with ESRI’s forum it that I rarely get an answer
Just a suggestion: you may have better luck with GeoNet if you share your posts in the relevant forums ie, Python or Model Builder so the right people see it popup in their news feeds. The searching is okay but tagging to forums really pops your post up to people that follow those forums.
Hi Curtis,
Thanks, indeed, your code is way better than mine, I'll update that in my model
And true, I need to get more familiarized with GeoNet, I'll be more carefully next time.
Thanks for your input.
Alex.
This is question not document .But you create it as a document.