Repository navigation
How to make enum column to work with SQLModel? #96
Description
Activity
- changed the title
[-]How to make enum column to work in with SQLModel?[/-][+]How to make enum column to work with SQLModel?[/+]on Sep 14, 2021 training_status: TrainingStatus = Field(sa_column=Column(Enum(TrainingStatus)))Just make sure that if you're using Alembic migration autogeneration and you require values to be stored as
Enumin the database and notString, you modify the column type in the generated migration script.Reacted by Lance Hasson, haja-k, Javad Miraftabzadeh, mohammed , Winrey, Navaneethan, Arnau, Muhammad Yaseen, Miguel Sebastián Frausto Zapata and Abdullah TahirReacted by haja-k, Jonathan Vargas, Rodrigo Medina, mohammed , Alejandro Latorre, Winrey, Tran Dong, danielaRiesgo and Rida Zougatraining_status: TrainingStatus = Field(sa_column=Column(Enum(TrainingStatus)))Just make sure that if you're using Alembic migration autogeneration and you require values to be stored as
Enumin the database and notString, you modify the column type in the generated migration script.This worked for me. However, I think it would be a bit nicer if we could just specify
training_status: Enum[TrainingStatus]or similar as the type.
Reacted by Matt Morris, mrh1997, Winrey, David McQueen, Alq and Giorgi MerebashviliReacted by haja-k, Winrey, Mohamed Martini and chattershuts- added a commit that references this issue
on Nov 24, 2021 Thank you!
training_status: TrainingStatus = Field(sa_column=Column(Enum(TrainingStatus)))Just make sure that if you're using Alembic migration autogeneration and you require values to be stored as
Enumin the database and notString, you modify the column type in the generated migration script.Thanks @yasamoka This solution worked for me. Just one thing Enum on training_status: TrainingStatus = Field(sa_column=Column(Enum(TrainingStatus))) is imported from sqlmodel and Enum on TrainingStatus is imported from enum.Enum I lost some time until I realized this.
Reacted by Ramzi Sabra, Sean Rapp, Tharinda Seth Wijesekera, Noam Friedman, norayr-im, Joel Berkeley, Ahmad Sajid, Sagit Khaliullin, lexi, Daniel Gafni and 13 moreReacted by norayr-im, Ahmad Sajid, Tanmay patil and danielaRiesgoReacted by Joel Berkeleytraining_status: TrainingStatus = Field(sa_column=Column(Enum(TrainingStatus)))Just make sure that if you're using Alembic migration autogeneration and you require values to be stored as
Enumin the database and notString, you modify the column type in the generated migration script.I do not get how I should "require values to be stored as
Enum" when using postgresql engine for example. Is there a flag somewhere or how does that work?I'm getting an error which looks like sqlmodel is trying to save the enum as a string:
sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input value for enum objecttypes: "type1"I do not get how I should "require values to be stored as
Enum" when using postgresql engine for example. Is there a flag somewhere or how does that work?I'm getting an error which looks like sqlmodel is trying to save the enum as a string:
sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input value for enum objecttypes: "type1"Here is a sample migration from a project of mine that shows how this is done. This is a migration script that changes a column from String to Enum.
"""language_fluency Revision ID: 5342a2459462 Revises: 919490ea9497 Create Date: 2021-09-17 03:49:14.251234 """ from alembic import op import sqlalchemy as sa import sqlmodel from sqlalchemy.dialects import postgresql # revision identifiers, used by Alembic. revision = '5342a2459462' down_revision = '919490ea9497' branch_labels = None depends_on = None fluency = sa.Enum('WEAK', 'MODERATE', 'GOOD', name='fluency') def upgrade(): fluency.create(op.get_bind()) op.alter_column('language', 'fluency', type_=fluency, postgresql_using='fluency::text::fluency') def downgrade(): op.alter_column('language', 'fluency', type_=sqlmodel.sql.sqltypes.AutoString()) fluency.drop(op.get_bind())Reacted by haja-kdo I really need a working alembic setup if I start from scratch? I can see that the table is created correctly but for some reason sqlmodel / psycopg2==2.9.3 are creating wrong queries. This is what the db looks like:
Table "public.items" Column | Type | Collation | Nullable | Default --------+-------------------+-----------+----------+--------- type | itemtypes | | | id | uuid | | not null | name | character varying | | not null |Does alembic change how sqlmodel/sqlalchemy generates statements and passes the parameters to that?
What do you mean by starting from scratch? Do you mean that you have an existing database and you have yet to setup Alembic for migrations? If so, then no, you do not need a working alembic setup if you can manually define your column type correctly on the database schema itself directly.
Alembic doesn't change anything related to how SQLModel / SQLAlchemy generates statements. Alembic compares your existing database against the schema it reads from your code and generates schema migrations based on that. Regardless of whether those migrations are applied, it is SQLAlchemy that ultimately generates statements based on your models and tables as defined in your code. If there is still a mismatch between your actual database and the models / tables you have defined, then you get an error.
From scratch I mean that I create an empty postgresql server and use the
SQLModel.metadata.create_all(engine)to create tables. I got this working withmysqlso I guess the main issue I'm having is thatsqlmodeldoes not create the postgresql enum correctly. Postgres showsdocker compose exec db psql -U postgres -c "select enum_range(null::itemtypes);" enum_range ------------ {} (1 row)Maybe alembic works by correcting that enum.
From scratch I mean that I create an empty postgresql server and use the
SQLModel.metadata.create_all(engine)to create tables. I got this working withmysqlso I guess the main issue I'm having is thatsqlmodeldoes not create the postgresql enum correctly. Postgres showsdocker compose exec db psql -U postgres -c "select enum_range(null::itemtypes);" enum_range ------------ {} (1 row)Maybe alembic works by correcting that enum.
The Alembic migration I showed you was not autogenerated. It was written manually (
alembic revision -m "message").Hello, @alhoo I had the same problem and I noticed that the error happens if the table you are adding the new enum column already exists. In my case, I deleted the table and created a new migration and it worked, new types were created. I am not sure why alembic does not create a new enum type automatically on existing tables. This is not the best solution but works.
The following solution worked fine for me:
import enum from sqlalchemy import (Column, Integer) from sqlalchemy_utils import ChoiceType from sqlmodel import SQLModel, Field class LightType(enum.IntEnum): off = 0 on = 1 flashing = 2 class Events(SQLModel, table=True): green: LightType = Field(sa_column=Column(ChoiceType(LightType, impl=Integer()), nullable=False))Reacted by haja-k, Eloy Adonis Colell, Honglei, Khyn Antoque, Adrián Constante, hirousto, eshine996, rysson, Humphrey Ahn and Priyansh2001hereReacted by bashtian-fr2 remaining items
Thanks for the help everyone :)
@jonra1993 Using this approach how would you set a default value, either/both at the database level and sqlmodel level
EDIT: Got it, you can just set a default inside the
Field(), ie,Field(default=SomeEnum.foo)Reacted by Jonathan Vargas and Adrián ConstanteThe solution provided here doesn't automatically create the enum type in case it is missing with postgresql, at least with my setting:
sqlmodel: 0.0.8 python: 3.10.11 macOS: Ventura 13.3.1 (22E261)I am calling
createmanually (Enum(TrainingStatus).create(engine)) to fix this@turangojayev could you please provide more details for your proposed fix? E.g. when and where exactly do you call
create?sorry for late response, well, I basically do
sa_TrainingStatus = Enum(TrainingStatus) def init_db(): sa_TrainingStatus.create(engine, checkfirst=True) SQLModel.metadata.create_all(engine) @asynccontextmanager async def lifespan(app: FastAPI): create_db_and_tables() yield app = FastAPI(lifespan=lifespan)If you want enum types to be recognized by alembic, do the following.
InstallationSQLModel: 0.0.18 Python: 3.11pip install alembic-postgresql-enum or poetry add alembic-postgresql-enumAdd import to envy.py
import alembic_postgresql_enumWhen you update enum types, alembic will detect the changes and automatically generate migrations.
def upgrade() -> None: # ### commands auto-generated by Alembic - please adjust! ### op.sync_enum_values('public', 'channel', ['USSD', 'WHATSAPP'], [('subscription', 'channel')], enum_values_to_rename=[]) # ### end Alembic commands ### def downgrade() -> None: # ### commands auto generated by Alembic - please adjust! ### op.sync_enum_values('public', 'channel', ['WHATSAPP'], [('subscription', 'channel')], enum_values_to_rename=[]) # ### end Alembic commands ###Reacted by Adeyanju Farinnako, SoulEater and Haran RajkumarCan we change the scheme of where the Enum type is stored?
I prefre to use
sa_type:training_status: TrainingStatus = Field(sa_type=Enum(TrainingStatus))
Reacted by Alexandr Makurin, Evgeny Pogrebnyak, worldworm and Marián HlaváčReacted by bashtian-fr, Gilles Jacobs and Andrew LiangI prefre to use
sa_type:training_status: TrainingStatus = Field(sa_type=Enum(TrainingStatus))
this create the following error for me:
class NotificationPriority(Enum): LOW = "LOW" MEDIUM = "MEDIUM" HIGH = "HIGH" CRITICAL = "CRITICAL" .... File "/workspaces/api/src/n0tifs/models.py", line 255, in Notification priority: NotificationPriority = Field(sa_type=Enum(NotificationPriority)) ^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/vscode/.local/lib/python3.12/site-packages/sqlalchemy/sql/sqltypes.py", line 1397, in __init__ self._enum_init(enums, kw) File "/home/vscode/.local/lib/python3.12/site-packages/sqlalchemy/sql/sqltypes.py", line 1427, in _enum_init self._default_length = length = max(len(x) for x in self.enums) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/vscode/.local/lib/python3.12/site-packages/sqlalchemy/sql/sqltypes.py", line 1427, in <genexpr> self._default_length = length = max(len(x) for x in self.enums) ^^^^^^ TypeError: object of type 'type' has no len()Reacted by Anis Mekacher@bashtian-fr I have the same issue SQLModel version 0.0.21
@bashtian-fr I have the same issue SQLModel version 0.0.21
Hey there, I solved it by doing the following:
import enum from sqlmodel import Column, Enum, Field class NotificationPriority(str, enum.Enum): LOW = "LOW" MEDIUM = "MEDIUM" HIGH = "HIGH" CRITICAL = "CRITICAL" class Notification(TableMixin, NotificationBase, table=True): id: int | None = Field(default=None, primary_key=True) priority: NotificationPriority = Field(sa_column=Column(Enum(NotificationPriority)))Note, that the enum class extend str AND Enum, which is from the
enummodule, while I use SQLModelEnumfor the columnReacted by Dean McDonald, Urjit Singh Bhatia, Alan Jumeaucourt and Olivier Nguyen QuocTo summarize what helped me:
UseCase: I want to have an enum in python and the keys to use in Python should be UPPERCASE. The values should be different in the database and I want to reference them. Should be done with SQLModel and alembic.
Solution
import enum from sqlmodel import INTEGER, Column, Enum, Field, SQLModel def enum_values(enum_class: type[enum.Enum]) -> list: """Get values for enum.""" return [status.value for status in enum_class] # watch out which enum you use. This should be the python enum class Alive(enum.Enum): NO = "no" YES = "yes" # here the database representation, where a column `alive` will be created with - the values: [no, yes] of the enum, # instead of the keys `NO,YES` class AliveDB(SQLModel, table=True): __tablename__ = "Alive" id: int | None = Field( sa_column=Column( name="alive_id", nullable=False, type_=INTEGER, primary_key=True, default=None, ) ) alive: Alive = Field( sa_column=Column( name="alive", nullable=False, type_= Enum(Alive, values_callable=enum_values), ) )
Reacted by Christian-Manuel Butzke, Can Burak Sofyalıoğlu, Ted Li, Precious , Mai Chân Chính, keijay and Iksfrom sqlmodel import Field, SQLModel, Enum from sqlalchemy import Enum as SAEnum import enum class Statuses(str, enum.Enum): published = "published" draft = "draft" archived = "archived" status: Statuses = Field( sa_type=SAEnum(Statuses, name="statuses_enum"), sa_column_kwargs={"server_default": text(f"'{Statuses.published.value}'")}, default=Statuses.published, nullable=False, )
I prefre to use
sa_type:training_status: TrainingStatus = Field(sa_type=Enum(TrainingStatus))
Yes! I forgot to mention, use
Enumfrom sqlalchemy, not from pythonenum.I also have an advance solution! That what I am doing in my project. Sqlalchemy has
TypeDecoratorwhich you can use to customize a Enum Type.See my pr related to IntEnum column: #1337
Also, there is another pr about how to have a JSON type column: #1324You can easily have your own StrEnum by using sqlalchemy TypeDecorator
First Check
Commit to Help
Example Code
Description
I am trying to create an enum column and use the value in the database. But I am getting this error:
Does anyone knows how to help me? I tried
but it doesn't seem to work as I don't understand where the 'value' should be coming from 😓 I would really appreciate any input
Operating System
macOS
Operating System Details
No response
SQLModel Version
0.0.4
Python Version
3.7.9
Additional Context
No response