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?

AKX · Accepted Answer · 2021-10-11 16:21:51Z

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, 2021 at 16:21

AKX

171k16 gold badges145 silver badges227 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yanbin Over a year ago

The input ids is a list. Or do this way cursor.execute(f'select * from testdb where id in ({','.join(['%s'] * len(ids)}), ids)

piro · Accepted Answer · 2021-10-11 22:40:54Z

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, 2021 at 22:40

piro

14k6 gold badges39 silver badges40 bronze badges

2 Comments

Yanbin Over a year ago

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.

piro Over a year ago

As it says on the tin, you can add an explicit cast, such as using %s::uuid[] as placeholder.

Yanbin · Accepted Answer · 2021-10-14 04:18:29Z

1

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

answered Oct 14, 2021 at 4:18

Yanbin

1449 bronze badges

Collectives™ on Stack Overflow

How Python psycopg2 query in condition with uuid array

3 Answers 3

1 Comment

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Related