Storing Python 3 Enum in MySQL using SQLAlchemy

Question

When I try to store a Animals object with a field species containing a Python Enum object SpeciesType.Cat, I get the error

TypeError: object of type 'VisitableType' has no len()

What is the correct way to store a Python 3 Enum in MySQL using SQLAlchemy?

# SQLALCHEMY CODE ########################

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class SpeciesType(Enum):
    Cat = 1
    Dog = 2


class Animals(Base):
    __tablename__ = 'animals'
    id  = Column(String(16), primary_key=True)
    species = Column(Enum(SpeciesType))


# REGULAR CODE ########################

import enum

class SpeciesType(enum.Enum):
    Cat = 1
    Dog = 2


class MyClass:
    @property
    def session(self):
        ....

    def insertToDB(self):
        kitty = Animals(id='kitty', species=SpeciesType.Cat)
        self.session.add(kitty)
        self.session.commit()

c = MyClass()
c.insertToDB()

Update

The following new error occurs after fixing the name collision between sqlalchemy.enum and Enum.enum.

sqlalchemy.exc.StatementError: (builtins.LookupError) "SpeciesType.Cat" is not among the defined enum values [SQL: 'INSERT INTO animals (id, species) VALUES (%(id)s, %(species)s)'] [parameters: [{'species': , 'id': 'kitty'}]]

Are you sure you don't have a name collision between enum.Enum and sqlalchemy.Enum? — univerio
– univerio, Commented Jan 4, 2018 at 20:58
@univerio Fixed the name collision, now I get a new error that I cant solve sqlalchemy.exc.StatementError: (builtins.LookupError) "SpeciesType.Cat" is not among the defined enum values [SQL: 'INSERT INTO animals (id, species) VALUES (%(id)s, %(species)s)'] [parameters: [{'species': <SpeciesType.Cat: 1>, 'id': 'kitty'}]]. Updated question — Nyxynyx
– Nyxynyx, Commented Jan 4, 2018 at 21:37

Ethan Furman · Accepted Answer · 2018-01-04 23:22:08Z

2

Looks like you are creating the Animals enumeration twice, which means you will have two different enums that happen to have the same names.

Define it only once, and import it anywhere else you need it.

answered Jan 4, 2018 at 23:22

Ethan Furman

70.1k21 gold badges174 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Caner · Accepted Answer · 2020-01-27 15:51:42Z

1

Your enum model should be a subclass of Python's enum.Enum and your models column definitions should use sqlalchemy.Enum

class SpeciesType(enum.Enum):
...

class Animals(Base):
    ...
    species = Column(sqlalchemy.Enum(SpeciesType))

answered Jan 27, 2020 at 15:51

Caner

59.7k37 gold badges184 silver badges185 bronze badges

1 Comment

Ivan Camilito Ramirez Verdes Over a year ago

with this configuration, I get "Boolean value of this clause is not defined"

Collectives™ on Stack Overflow

Storing Python 3 Enum in MySQL using SQLAlchemy

Update

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Update

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related