topic Re: Iterate through table values to create SQL expression [SOLVED] in Geoprocessing Questions

Iterate through table values to create SQL expression [SOLVED]

MetralAlexis — Wed, 08 Mar 2017 20:19:40 GMT

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

Re: Iterate through table values to create SQL expression

MetralAlexis — Sun, 12 Dec 2021 07:20:24 GMT

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.

Re: Iterate through table values to create SQL expression [SOLVED]

AbdullahAnter — Thu, 09 Mar 2017 20:36:13 GMT

This is question not document .But you create it as a document.

Re: Iterate through table values to create SQL expression [SOLVED]

curtvprice — Sun, 12 Dec 2021 07:20:26 GMT

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.

https://community.esri.com/community/help-and-feedback/blog/2016/08/15/community-news-and-tips-how-to-ask-questions-and-get-answers?sr=search&searchId=46572bb6-21a1-467e-bf56-3e067a44d3de&searchIndex=0‌

Re: Iterate through table values to create SQL expression [SOLVED]

MetralAlexis — Mon, 13 Mar 2017 12:15:42 GMT

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.