CODEWITHSUNDEEP
pythonsqlpsycopg2
PapeK24
PapeK24

Reputation: 1126

Is there any way to convert python set to be used in SQL's "IN" statement?

I am trying to make an SQL statement that updates multiple rows with set of keys. I do realize that I could just run for each with statement for every single key, but this seems like "more correct" and much faster for me.

This is what I've got:

    def end_actions(self, timestamp, ids):
         statement = "UPDATE items " \
                "SET end_time = " + timestamp + " " \
                "WHERE auction IN " + ids + ";"
        self.execute_statement(statement)

question is how do I format IDs set to be accepted by SQL's IN statement.

Thanks.

Upvotes: 3

Views: 394

Answers (1)

Andy Carlson
Andy Carlson

Reputation: 3909

I don't know what type your IDs are. I'll assume it's a set of integers. You want the SQL clause to look something like this WHERE auction IN (1, 2, 3);. So you can map your set to a list of strings and pass it to join with a comma separator and wrap it in parentheses.

"WHERE auction IN (" + ", ".join(map(str, ids)) + ");"

EDIT: If instead you want strings, you might need to wrap it in quotes first

"WHERE auction IN (" + ", ".join("'" + id + "'" for id in ids) + ");"

Upvotes: 3

Related Questions