How Python psycopg2 query in condition with uuid array

Question

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?

score 1 · Answer 1 · answered Oct 11 '21 at 16:21

1

One %s per value.

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

answered Oct 11 '21 at 16:21

AKX

152,115
15
115
172

The input ids is a list. Or do this way `cursor.execute(f'select * from testdb where id in ({','.join(['%s'] * len(ids)}), ids)` – Yanbin Oct 15 '21 at 22:14

score 1 · Answer 2 · answered Oct 11 '21 at 22:40

1

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.

answered Oct 11 '21 at 22:40

piro

13,378
5
34
38

cursor.execute("select * from testdb where id = any(%s)", (['5ed11bbf-ffd1-4124-ba3d-5e392dc9db96', '14acfb5b-3b09-4728-b3b3-8cd484b310db'],)) E psycopg2.errors.UndefinedFunction: operator does not exist: uuid = text E LINE 1: select * from testdb where id = any(ARRAY['5ed11bbf... E ^ E HINT: No operator matches the given name and argument types. You might need to add explicit type casts. – Yanbin Oct 14 '21 at 04:00
As it says on the tin, you can add an explicit cast, such as using `%s::uuid[]` as placeholder. – piro Oct 14 '21 at 18:12

score 0 · Accepted Answer · answered Oct 14 '21 at 04:18

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).

How Python psycopg2 query in condition with uuid array

3 Answers3