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 <module>

    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 "<string>", 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 "<string>", 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?

Thanks in advance and kind regards

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

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/8026ee8e-461e-486e-85f0-af18415d53b7n%40googlegroups.com.