Re: [sqlalchemy] sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'uuid_utils.UUID'

[Mike Bayer]

I havent read about uuid 7, however if it takes up the same number of bytes as 
other UUIDs, this "uuid_utils" should really be returning Python UUID objects 
and not something ad-hoc, that's the source of the incompatibility here.

otherwise, you'd need to build a custom type, see 
https://docs.sqlalchemy.org/en/20/core/custom_types.html#backend-agnostic-guid-type
 where for PG you'd have to adapt this UUID v7 into a plain Python UUID object, 
which the psycopg dialects can then interpret.

On Mon, Oct 2, 2023, at 6:20 PM, Zer0x00 wrote:
> I want to use UUID v7 with this package:
> https://github.com/aminalaee/uuid-utils/
> 
> Here's my current code:
> from sqlalchemy.dialects.postgresql import UUID as POSTGRES_UUID
> from sqlalchemy.orm import Mapped, as_declarative, mapped_column
> from uuid_utils import UUID, uuid7
> 
> from db.dependencies import get_db
> 
> 
> @as_declarative()
> class Base:
> pass
> 
> 
> class Test(Base):
> __tablename__: str = "test"
> 
> id: Mapped[UUID] = mapped_column(POSTGRES_UUID(as_uuid=True), 
> primary_key=True, default=uuid7)
> 
> 
> a = Test()
> 
> 
> with get_db() as db:
> db.add(a)
> db.commit()
> 
> 
> 
> which throws this error
> 
> Traceback (most recent call last):
>   File 
> "/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", 
> line 1965, in _exec_single_context
> self.dialect.do_execute(
>   File 
> "/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", 
> line 921, in do_execute
> cursor.execute(statement, parameters)
> psycopg2.ProgrammingError: can't adapt type 'uuid_utils.UUID'
> 
> The above exception was the direct cause of the following exception:
> 
> Traceback (most recent call last):
>   File "/dci-ng/test6.py", line 35, in 
> db.commit()
>   File 
> "/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", 
> line 1923, in commit
> trans.commit(_to_root=True)
>   File "", line 2, in commit
>   File 
> "/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/state_changes.py",
>  line 139, in _go
> ret_value = fn(self, *arg, **kw)
> 
>   File 
> "/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", 
> line 1239, in commit
> self._prepare_impl()
>   File "", line 2, in _prepare_impl
>   File 
> "/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/state_changes.py",
>  line 139, in _go
> ret_value = fn(self, *arg, **kw)
> 
>   File 
> "/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", 
> line 1214, in _prepare_impl
> self.session.flush()
>   File 
> "/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", 
> line 4179, in flush
> self._flush(objects)
>   File 
> "/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", 
> line 4314, in _flush
> with util.safe_reraise():
>   File 
> "/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py",
>  line 146, in __exit__
> raise exc_value.with_traceback(exc_tb)
>   File 
> "/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", 
> line 4275, in _flush
> flush_context.execute()
>   File 
> "/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/unitofwork.py", 
> line 466, in execute
> rec.execute(self)
>   File 
> "/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/unitofwork.py", 
> line 642, in execute
> util.preloaded.orm_persistence.save_obj(
>   File 
> "/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/persistence.py",
>  line 93, in save_obj
> _emit_insert_statements(
>   File 
> "/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/persistence.py",
>  line 1226, in _emit_insert_statements
> result = connection.execute(
>  ^^^
>   File 
> "/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", 
> line 1412, in execute
> return meth(
>^
>   File 
> "/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", 
> line 516, in _execute_on_connection
> return connection._execute_clauseelement(
>^^
>   File 
> "/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", 
> line 1635, in _execute_clauseelement
> ret = self._execute_context(
>   ^^
>   File 
> "/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", 
> line 1844, in _execute_context
> return self._exec_single_context(
>^^
>   File 
> "/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", 
> line 1984, in _exec_single_context
> self._handle_dbapi_exception(
>   File 
> "/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", 
> line 2339, in _handle_dbapi_exception
> raise 

[sqlalchemy] sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'uuid_utils.UUID'

[Zer0x00]

I want to use UUID v7 with this package:
https://github.com/aminalaee/uuid-utils/

Here's my current code:
from sqlalchemy.dialects.postgresql import UUID as POSTGRES_UUID
from sqlalchemy.orm import Mapped, as_declarative, mapped_column
from uuid_utils import UUID, uuid7

from db.dependencies import get_db


@as_declarative()
class Base:
pass


class Test(Base):
__tablename__: str = "test"

id: Mapped[UUID] = mapped_column(POSTGRES_UUID(as_uuid=True), 
primary_key=True, default=uuid7)


a = Test()


with get_db() as db:
db.add(a)
db.commit()



which throws this error

Traceback (most recent call last):
  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", 
line 1965, in _exec_single_context
self.dialect.do_execute(
  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", 
line 921, in do_execute
cursor.execute(statement, parameters)
psycopg2.ProgrammingError: can't adapt type 'uuid_utils.UUID'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/dci-ng/test6.py", line 35, in 
db.commit()
  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", 
line 1923, in commit
trans.commit(_to_root=True)
  File "", line 2, in commit
  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/state_changes.py",
 
line 139, in _go
ret_value = fn(self, *arg, **kw)

  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", 
line 1239, in commit
self._prepare_impl()
  File "", line 2, in _prepare_impl
  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/state_changes.py",
 
line 139, in _go
ret_value = fn(self, *arg, **kw)

  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", 
line 1214, in _prepare_impl
self.session.flush()
  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", 
line 4179, in flush
self._flush(objects)
  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", 
line 4314, in _flush
with util.safe_reraise():
  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", 
line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", 
line 4275, in _flush
flush_context.execute()
  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/unitofwork.py", 
line 466, in execute
rec.execute(self)
  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/unitofwork.py", 
line 642, in execute
util.preloaded.orm_persistence.save_obj(
  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/persistence.py", 
line 93, in save_obj
_emit_insert_statements(
  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/orm/persistence.py", 
line 1226, in _emit_insert_statements
result = connection.execute(
 ^^^
  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", 
line 1412, in execute
return meth(
   ^
  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", 
line 516, in _execute_on_connection
return connection._execute_clauseelement(
   ^^
  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", 
line 1635, in _execute_clauseelement
ret = self._execute_context(
  ^^
  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", 
line 1844, in _execute_context
return self._exec_single_context(
   ^^
  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", 
line 1984, in _exec_single_context
self._handle_dbapi_exception(
  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", 
line 2339, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", 
line 1965, in _exec_single_context
self.dialect.do_execute(
  File 
"/home/dev/.local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", 
line 921, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt 
type 'uuid_utils.UUID'
[SQL: INSERT INTO test (id) VALUES (%(id)s::UUID)]
[parameters: {'id': UUID('018af275-54b2-709a-9ab2-8a7492d8ee9b')}]
(Background on this error at: https://sqlalche.me/e/20/f405)



I've found a workaround to import the native uuid package and set the 
default attribute to:  lambda _: NATIVEUUID(uuid7().hex) but I wanted to 
ask if there's a more elegant way to do it?