How Python psycopg2 query in condition with uuid array

user_197741 0
Viewed 247

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?

psycopg2 postgresql python
3 Answers

One %s per value.

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

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.

user_101380

Thank @piro's answer, after try several time, got the working code

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

and id = any(%s::uuid) can't be replaced with id in (%s::uuid).

user_1977410
Related