Usage of sqlalchemy Uuid type in declarative mapping

6,932 views
Skip to first unread message

Pierre Massé

unread,
Jul 6, 2023, 1:21:38 PM7/6/23
to sqlal...@googlegroups.com
Dear all,

I am currently working on implementing UUID as primary keys in my database.

I have come across an issue that I cannot find an explanation for: using the `sqlalchemy.Uuid` type in declarative mapping yields different results at initialization. I sometimes get the following error:

"The type provided inside the 'id' attribute Mapped annotation is the SQLAlchemy type <class 'sqlalchemy.sql.sqltypes.Uuid'>. Expected a Python type instead"

I have a Caregiver table, for which id is a PostgreSQL UUID, and a Match Event table holding 2 fields which use the Caregiver id as foreign key. Those 2 fields are PostgreSQL UUID as well.

If my classes definitions are as follow, the initialization runs smoothly, as well as inserts in the Match Event table:

from sqlalchemy import (
ForeignKey,
Uuid,
)
from sqlalchemy.orm import (
Mapped,
mapped_column,
relationship,
)

class MatchEventOrm(Base):
__tablename__ = "match_event"

id: Mapped[int] = mapped_column(primary_key=True)
subject_caregiver_id: Mapped[Uuid] = mapped_column(
ForeignKey("caregiver.id")
)
object_caregiver_id: Mapped[Uuid] = mapped_column(
ForeignKey("caregiver.id")
)
subject_caregiver: Mapped["CaregiverOrm"] = relationship(
back_populates="match_event_subject_of",
foreign_keys=[subject_caregiver_id],
)
object_caregiver: Mapped["CaregiverOrm"] = relationship(
back_populates="match_event_object_of",
foreign_keys=[object_caregiver_id],
)

class CaregiverOrm(Base):
__tablename__ = "caregiver"

id: Mapped[str] = mapped_column(primary_key=True)
uid: Mapped[str]
user_email: Mapped[str]
...



However, if I change to the following:
from sqlalchemy import (
ForeignKey,
Uuid,
)
from sqlalchemy.orm import (
Mapped,
mapped_column,
relationship,
)

class MatchEventOrm(Base):
__tablename__ = "match_event"

id: Mapped[int] = mapped_column(primary_key=True)
subject_caregiver_id: Mapped[Uuid] = mapped_column(
ForeignKey("caregiver.id")
)
object_caregiver_id: Mapped[Uuid] = mapped_column(
ForeignKey("caregiver.id")
)
subject_caregiver: Mapped["CaregiverOrm"] = relationship(
back_populates="match_event_subject_of",
foreign_keys=[subject_caregiver_id],
)
object_caregiver: Mapped["CaregiverOrm"] = relationship(
back_populates="match_event_object_of",
foreign_keys=[object_caregiver_id],
)

class CaregiverOrm(Base):
__tablename__ = "caregiver"

id: Mapped[Uuid] = mapped_column(primary_key=True)
uid: Mapped[str]
user_email: Mapped[str]
...


I get the aforementioned error message.

I may be using type annotations wrong, any insight on what I am doing wrong would be welcome.

Thanks a lot for your support.

Regards,

Pierre


Mike Bayer

unread,
Jul 6, 2023, 1:27:44 PM7/6/23
to noreply-spamdigest via sqlalchemy


On Thu, Jul 6, 2023, at 1:21 PM, Pierre Massé wrote:
Dear all,

I am currently working on implementing UUID as primary keys in my database.

I have come across an issue that I cannot find an explanation for: using the `sqlalchemy.Uuid` type in declarative mapping yields different results at initialization. I sometimes get the following error:

"The type provided inside the 'id' attribute Mapped annotation is the SQLAlchemy type <class 'sqlalchemy.sql.sqltypes.Uuid'>. Expected a Python type instead"

this message is referring to the difference between *Python* types and *SQLAlchemy* types.  A SQLAlchemy type is like "sqlalchemy.Uuid" as you are using.  The Python type is the Python UUID type: https://docs.python.org/3/library/uuid.html#uuid.UUID


so you would want

import uuid

class XYZ(...):
    # ...

   id: Mapped[uuid.UUID] = mapped_column(...)







--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
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.

Pierre Massé

unread,
Jul 6, 2023, 1:30:47 PM7/6/23
to sqlal...@googlegroups.com
You are f****ing awesome Mike. 

Thanks for the answer and the links, I will dive into it tomorrow first thing in the morning. 

Reply all
Reply to author
Forward
0 new messages