The crux of the issue relating to __mro__ causing the AttributeError is that CampaignStatusType is not a class, but rather an instance variable of type sqlalchemy.dialects.postgresql.ENUM (using pyright may verify this - given that it complains about Mapped[CampaignStatusType] being an "Illegal type annotation: variable not allowed unless it is a type alias"). As a test, replacing the type annotation for status with Mapped[CampaignStatus] does resolve the issue (and pyright reports no errors), but that does not hook the column type to the enum with postgresql dialect that is desired.
So the only way around this while using the dialect specific enum type is to use the non-annotated construct:
status = mapped_column(CampaignStatusType, nullable=False)
However, if type annotation is still desired, i.e. whatever being Mapped must be a type, and that sqlalchemy.dialects.postgresql.ENUM (which was imported as pgEnum) is the underlying type for the instance CampaignStatusType, it may be thought that the following might be a solution
# don't do this erroneous example despite it does run
status: Mapped[sqlalchemy.dialects.postgresql.ENUM] = mapped_column(
CampaignStatusType,
nullable=False,
)
While it works, it does NOT actually reflect what will be represented by the data, so DO NOT actually do that. Moreover, it only works because the type annotation is ignored when the specific column type is passed, so putting anything in there will work while having an invalid type.
Now, given that SQLAlchemy is now 2.0 (as the question explicitly want this newer version), perhaps reviewing the documentation and see now native enums should be handled now.
Adapting the examples in the documentation, the following MVCE may now be derived, using all the intended keyword arguments that was passed to the PostgreSQL dialect specific ENUM type passed generic sqlalchemy.Enum instead (aside from metadata=Base.metadata as that's completely superfluous):
from typing import Literal
from typing import get_args
from sqlalchemy import Enum
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
CampaignStatus = Literal["activated", "deactivated"]
class Base(DeclarativeBase):
pass
class Campaign(Base):
__tablename__ = "campaign"
id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
status: Mapped[CampaignStatus] = mapped_column(Enum(
*get_args(CampaignStatus),
name="campaignstatus",
create_constraint=True,
validate_strings=True,
))
Note the use of typing.get_args on CampaignStatus and splat it to the Enum here as opposed to what the official examples have done in repeating themselves. Now to include the usage:
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
def main():
engine = create_engine('postgresql://postgres@localhost/postgres')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
session.add(Campaign(status='activated'))
session.add(Campaign(status='deactivated'))
session.commit()
s = 'some_unvalidated_string'
try:
session.add(Campaign(status=s))
session.commit()
except Exception:
print("failed to insert with %r" % s)
if __name__ == '__main__':
main()
The above will produce failed to insert with 'some_unvalidated_string' as the output, showing that unvalidated strings will not be inserted, while validated strings that are mapped to some enum are inserted without issues. Moreover, pyright will not produce errors (though honestly, this is not necessarily a good metric because type hinting in Python is still fairly half-baked, as pyright did not detect the erroneous example as an error in the very beginning no matter what went inside Mapped, but I digress).
Viewing the newly created entities using psql
postgres=# select * from campaign;
id | status
----+-------------
1 | activated
2 | deactivated
(2 rows)
postgres=# \dt campaign;
Table "public.campaign"
Column | Type | Collation | Nullable | Default
--------+----------------+-----------+----------+--------------------------------------
id | integer | | not null | nextval('campaign_id_seq'::regclass)
status | campaignstatus | | not null |
Indexes:
"campaign_pkey" PRIMARY KEY, btree (id)
postgres=# \dT+ campaignstatus;
List of data types
Schema | Name | Internal name | Size | Elements | Owner | Access privileges | Description
--------+----------------+----------------+------+-------------+----------+-------------------+-------------
public | campaignstatus | campaignstatus | 4 | activated +| postgres | |
| | | | deactivated | | |
(1 row)
The enum of course cannot be dropped without dropping the campaign table:
postgres=# drop type campaignstatus;
ERROR: cannot drop type campaignstatus because other objects depend on it
DETAIL: column status of table campaign depends on type campaignstatus
HINT: Use DROP ... CASCADE to drop the dependent objects too.
So the enum more or less behaves as expected despite only using generic SQLAlchemy types, without needing dialect specific imports.
