18

I am using psycopg2 to try to insert an entry into a table where the type of the data is the Postgres type 'uuid'.

According to this page, I should be able to directly use the Python type uuid.UUID, as in the following code:

uuid_entry = uuid.uuid4()
command = "INSERT INTO MyTable (uuid) VALUES (%s)"
cursor.execute(command, (uuid_entry,))

However, when I try to do this, it throws the error:

ProgrammingError(can't adapt type 'UUID')

Any ideas on why this is happening? Thanks.

Resquiens
  • 333
  • 1
  • 3
  • 9
  • 1
    Have you been able to validate the `uuid_entry` in the debugger? Hav you been able to successfully **query** a row with a given `uuid` a la http://initd.org/psycopg/docs/extras.html#adapt-uuid? – Shawn Mehan Jun 29 '18 at 15:35
  • @ShawnMehan Hmmm, it seems like the output from the psycopg2.extensions.adapt(my_uuid).getquoted() was slightly different in my case: b"'2d10d790-f2de-4de5-aaba-7095e04f39cb'::uuid". It seems to be wrapped in b"...". – Resquiens Jun 29 '18 at 15:44
  • 1
    the `b` is telling you that it is a `binary string`. But you should try getting another id from your db and pulling that id and comparing it's type and format to the one you are crafting. Make certain they both appear similarly inside python. – Shawn Mehan Jun 29 '18 at 16:06
  • 5
    @ShawnMehan Seems as though I was able to solve the problem by using ``psycopg2.extras.register_uuid()`` before calling ``cursor.execute``. – Resquiens Jul 03 '18 at 09:15

2 Answers2

21

As author noted in comments, to pass UUID objects into cursor methods one have to call register_uuid() first once:

import psycopg2.extras

# call it in any place of your program
# before working with UUID objects in PostgreSQL
psycopg2.extras.register_uuid()

# now you can pass UUID objects into psycopg2 functions
cursor.execute("INSERT INTO MyTable (uuid) VALUES (%s)", (uuid.uuid4(),))

# ... and even get it from there
cursor.execute("SELECT uuid FROM MyTable")
value, = cursor.fetchone()
assert isinstance(value, uuid.UUID)
Andrey Semakin
  • 2,032
  • 1
  • 23
  • 50
4
uuid_entry = str(uuid.uuid4())

This works for me. Not sure if it is the right approach.

Milo
  • 3,365
  • 9
  • 30
  • 44
Tuaha
  • 41
  • 2
  • This answer works perfect. Not sure what the purpose or advantage of registering it. – noone392 Mar 22 '20 at 22:03
  • 3
    @noone392 - I think one advantage of registering it is that then selecting the value from a UUID field returns it as a native uuid.UUID object, rather than a string. – sql_knievel Oct 19 '21 at 16:32