Use Enums with SQLAlchemy

Convenient Enum type However, if you want to use it in a web application, you want to use it as it is in O / R Mapper.

SQLAlchemy makes it easy to define your own types that wrap existing types using TypeDecorator. Try wrapping the Integer and saving the Enum.

from __future__ import print_function, division, absolute_import
from sqlalchemy import Integer
from sqlalchemy.types import TypeDecorator


class EnumType(TypeDecorator):
    """Store IntEnum as Integer"""

    impl = Integer

    def __init__(self, *args, **kwargs):
        self.enum_class = kwargs.pop('enum_class')
        TypeDecorator.__init__(self, *args, **kwargs)

    def process_bind_param(self, value, dialect):
        if value is not None:
            if not isinstance(value, self.enum_class):
                raise TypeError("Value should %s type" % self.enum_class)
            return value.value

    def process_result_value(self, value, dialect):
        if value is not None:
            if not isinstance(value, int):
                raise TypeError("value should have int type")
            return self.enum_class(value)

We now have a dedicated column type to store the Enum specified by the keyword argument ʻenum_class`. Let's use it.

import enum
from sqlalchemy import Integer, Column, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker


Base = declarative_base()

Color = enum.Enum("Color", "red green yellow")

class Light(Base):
    __tablename__ = 'some_table'
    id = Column(Integer, primary_key=True)
    color = Column(EnumType(enum_class=Color))


engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

session.add(Light(color=Color.red))
session.commit()

for L in session.query(Light).all():
    print(L.id, L.color)

session.add(Light(color=42))
session.commit()

The execution result will be like this

1 Color.red
Traceback (most recent call last):
...
  File "enum_column_sample.py", line 19, in process_bind_param
    raise TypeError("Value should %s type" % self.enum_class)
sqlalchemy.exc.StatementError: Value should <enum 'Color'> type (original cause: TypeError: Value should <enum 'Color'> type) u'INSERT INTO some_table (color) VALUES (?)' [{'color': 42}]

In this way, the value obtained from the DB is properly returned to the Enum, and the non-Enum integer value is now kicked by Insert.

Recommended Posts

Use Enums with SQLAlchemy
Use DATE_FORMAT with SQLAlchemy filter
Use Azure SQL Database with SQLAlchemy
How to use SQLAlchemy / Connect with aiomysql
Use mecab-ipadic-neologd with igo-python
Use RTX 3090 with PyTorch
Use ansible with cygwin
Use pipdeptree with virtualenv
[Python] Use JSON with Python
Use Mock with pytest
Use indicator with pd.merge
Use Gentelella with django
Use mecab with Python3
Use tensorboard with Chainer
Use DynamoDB with Python
Use SQLAlchemy and multiprocessing
Use pip with MSYS2
Use Python 3.8 with Anaconda
Use pyright with Spacemacs
Use TypeScript with django-compressor
Use MySQL with Django
Use tensorboard with NNabla
Use GPS with Edison
Use nim with Jupyter
Get table dynamically with sqlalchemy
Use shared memory with shared libraries
Use "$ in" operator with mongo-go-driver
Use custom tags with PyYAML
Use directional graphs with networkx
Use TensorFlow with Intellij IDEA
Use Twitter API with Python
Use pip with Jupyter Notebook
Use TUN / TAP with Python
Use sqlite3 with NAO (Pepper)
Use sqlite load_extensions with Pyramid
Introduction to RDB with sqlalchemy Ⅰ
Use Windows 10 fonts with WSL
How to update with SQLAlchemy?
Use chainer with Jetson TK1
Use SSL with Celery + Redis
Use Cython with Jupyter Notebook
Browse columns encrypted with sqlalchemy
Use Maxout + CNN with Pylearn2
How to Alter with SQLAlchemy?
Use WDC-433SU2M2 with Manjaro Linux
Use OpenBLAS with numpy, scipy
group_by with sqlalchemy and sum
Use subsonic API with python3
Support multi session with SQLAlchemy
Use Sonicwall NetExtener with Systemd
Use prefetch_related conveniently with Django
How to Delete with SQLAlchemy?
Use AWS interpreter with Pycharm
Use Bokeh with IPython Notebook
Use Python-like range with Rust
Use MLflow with Databricks ④ --Call model -
Use pyright with CentOS7, emacs lsp-mode
Python: How to use async with
Use vl53l0x with Raspberry Pi (python)
Connect to multiple databases with SQLAlchemy
Use PX-S1UD / PX-Q1UD with Jetson nano