0

Here is what I'm trying to do. The database is Postgres.

numbers is a Python set() or some other iterable.

session.execute(table.update().\
                        where(func.substring(table.c.number,1 ,5) in numbers).\
                        values(provider="testprovider")

The problem with this is that in numbers doesn't seem to work. It will work for a single number if I do something like where(func.substring(table.c.number,1, 5)== '12345')

I've googled a lot on how to do WHERE IN with sqlalchemy, but all of them are with a SELECT query or do use the .in_() function on the column itself. For example this answer: link

The documentation also says the .in_() only works on the columns.

So, I don't really know how to proceed here. How can I write this expression?

fooiey
  • 1,040
  • 10
  • 23

1 Answers1

0

Ah nevermind, although the documentation doesn't say it, I can use the .in_() function on the result of func.substring.

So

where(func.substring(table.c.number, 1, 5).in_(numbers))

worked.

fooiey
  • 1,040
  • 10
  • 23