2

I am trying to cursor search through items in a field in an access table. I then append them to a list called 'distList'. I want to pass the list into an SQL Query builder 'WHERE' clause in the equation:

"Field1" in distList

or in long hand, it would look like this:

"Field1" in ('ds(c1)', 'ds(b1)', 'ds(c2)', 'ds(g1)')

The list looks like this:

['ds(c1)', 'ds(b1)', 'ds(c2)', 'ds(g1)']

The problem I am having is, the SQL equation above doesn't like to look 'in' a python list. It doesn't recognize the square brackets. It prefers round brackets, so I thought about using a tuple. The problem I am facing is I don't know how to build a tuple after cursor searching the items from the access table. here's my code example:

distList = []
for lyr in arcpy.mapping.ListLayers(mxd, "", df):
    if lyr.name == "Disturbance":
           for row in arcpy.SearchCursor(lyr):
                if "ds" in row.Field1:
                     distList.append(row.Field1)
    lyr.definitionQuery = '"Field1"' + "in " + distList

Can anyone suggest a way to get my list into a tuple or maybe just a better way to get my items into a format where they have round brackets instead of square ones?

As a work around, I tried converting the list to string str(distList) and then replacing the brackets. That works fine, but it seems cumbersome and I'm sure there's a better way.

Thanks, Mike

Improve this question
1

3 Answers 3

Reset to default
2

Did you try:

str(tuple(distList))

Edit:

The above will only work for lists with multiple items.

Something similar to Arun's answer is more general, in that it'll work with one or more items:

distlist = '({})'.format(','.join(elem for elem in distlist))

Depending on the query though, and the RDBMS, it may be more efficient to check beforehand for a single item and just use an equality operator in that case.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks gerrat. Unfortunately, this didn't seem to work. I think the query box I'm trying to pass this into, doesn't like tuples either.
This is really a comment, not an answer to the question. Please use "add comment" to leave feedback for the author.
@Avi: Well, it was actually an answer, but I stated it like a comment. The "did you try" was kind of rhetorical, since when I used this on his data the resulting string looked correct.
have problem when dicList have one item it will print (xxx,) error will appear
1

Preprocess the list:

distList = ['ds(c1)', 'ds(b1)', 'ds(c2)', 'ds(g1)']
refinedList = ','.join(a for a in distList)

pass refinedList in SQL in WHERE clause, something like this:

SQL_QUERY = """
select *
from abc
where someCol = ({0})

"""

SQL = SQL_QUERY.format(refinedList)

the {0} here is the parameter (refinedList). Look pythonDocs for better understanding of String format() function. Execute the query, will run.

Improve this answer

1 Comment

bear in mind that the way to pass an argument to an SQL query is using cursor.execute(query, params) or equivalent to avoid SQL ejection.
0

I received an answer from another site, so I thought I'd pass it along. The following script changes the query to string and is accepted into my definitionQuery. For added value, I 'set' the list to eliminate duplicates:

if "ds" in row.Field1:
            distList.append('"{}"'.format(row.Field1))
    lyr.definitionQuery = '"Field1" in ({})'.format(", ".join(set(distList)))
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.