How Python psycopg2 query in condition with uuid array-CodePudding

For given a table

create table test_db (
  id uuid
)

In Python with library psycopg2, we can do query

cursor.execute("select * from test_db where id in"  
   " ('5ed11bbf-ffd1-4124-ba3d-5e392dc9db96','14acfb5b-3b09-4728-b3b3-8cd484b310db')")

But if I parameterize id, change to

cursor.execute("select * from testdb where id in (%s)",
   ("'5ed11bbf-ffd1-4124-ba3d-5e392dc9db96','14acfb5b-3b09-4728-b3b3-8cd484b310db'",))

It's not working, says

psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type uuid: "'5ed11bbf-ffd1-4124-ba3d-5e392dc9db96','14acfb5b-3b09-4728-b3b3-8cd484b310db'"

How can I use in (%s) with uuid array?

CodePudding user response：

One %s per value.

cursor.execute(
   "select * from testdb where id in (%s, %s)",
   ('5ed11bbf-ffd1-4124-ba3d-5e392dc9db96','14acfb5b-3b09-4728-b3b3-8cd484b310db')
)

CodePudding user response：

You have extra quotes, so you are passing only one argument which is a string.

You might use a tuple and use IN, but using a list and any() is a better option because it won't explode in your face as soon as you pass an empty list.

cursor.execute("select * from testdb where id = any(%s)",
   ([UUID('5ed11bbf-ffd1-4124-ba3d-5e392dc9db96'), UUID('14acfb5b-3b09-4728-b3b3-8cd484b310db')],))

It might work without using UUID but you are just confusing the type system that way.