Enum validation causes problems with values not in Enum

[Christian Groschupp]

After upgrading from 1.0 to 1.1.3, i get this error:

  File "/home/xxx/.venv/xxx/lib/python2.7/site-packages/sqlalchemy/sql/sqltypes.py", line 1317, in _object_value_for_elem
    '"%s" is not among the defined enum values' % elem)
LookupError: "" is not among the defined enum values

MySQL allows to insert data, that is not in the Enum.

Test Case

import unittest
from sqlalchemy import (Column, Integer, Enum)
from sqlalchemy.ext.declarative import declarative_base
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = ("mysql://root:develop@localhost/test123")
db = SQLAlchemy(app)

Base = declarative_base()

class Test(db.Model):
    __tablename__ = 'test'
    id = Column(Integer, primary_key=True, nullable=False)

    default = Column(Enum('VALUE1','VALUE2'))

db.drop_all()
db.create_all()

class TestSelfEnum(unittest.TestCase):

    def test_enum(self):
        db.session.add(Test(default="VALUE1"))
        db.engine.execute('INSERT INTO test(`default`) VALUES("")')
        db.session.commit()
        result = db.session.query(Test).all()

[Mike Bayer]

question. Why do you want to insert a blank string into a column that does not support it? Also why not create ENUM("one", "two", "") ?

[Mike Bayer]

if you put a value that's not part of the enum in, then select it, you get blank back. So the type isn't storing arbitrary data, only "blank".

[Mike Bayer]

https://gerrit.sqlalchemy.org/224

[Mike Bayer]

Add check for blank string coming from MySQL's enum

MySQL's native ENUM type supports any non-valid value being sent, and in response will return a blank string. A hardcoded rule to check for "is returning the blank string" has been added to the MySQL implementation for ENUM so that this blank string is returned to the application rather than being rejected as a non-valid value. Note that if your MySQL enum is linking values to objects, you still get the blank string back.

Change-Id: I61f85c20293a48b0c11a31f2a19f6756c206bd20 Fixes: #3841

→ <<cset dd4e09d38729>>