Accepting multiple types on an SQLAlchemy model field

Question

Consider an SQLAlchemy model with a String field:

class MyModel(Base):
    name = Column(String(100))
    # ...

What is the right way to have this field accept e.g. int values and convert them to strings before saving and querying - essentially str(val) ?

Eventually I want all of these to be equivalent:

m = MyModel(name='123')
m = MyModel(name=123)
q = session.query(MyModel).filter_by(name='123')
q = session.query(MyModel).filter_by(name=123)

Have you read this section in the docs? [Use Descriptors and Hybrids](https://docs.sqlalchemy.org/en/latest/orm/mapped_attributes.html#using-descriptors-and-hybrids). — SuperShoot, Nov 05 '18 at 09:19

score 2 · Accepted Answer · answered Nov 05 '18 at 10:13

I've tested on MySQL and your original code works as is:

class MyModel(Base):

    __tablename__ = 'mymodel'

    id = Column(Integer, primary_key=True)
    name = Column("name", String(100))

if __name__ == '__main__':
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)
    s = Session()
    new_obj = MyModel(name=123)
    print(type(new_obj.name))  # <class 'int'>
    s.add(new_obj)
    s.commit()
    s.close()

    s = Session()
    obj = s.query(MyModel).filter(MyModel.name == 123).one()  # query with integer value
    print(obj.name, type(obj.name))  # 123 <class 'str'>

In This Answer, relating to validation and coercion of data types @zzzeek says that sqlalchemy:

...defers to the DBAPI/database as the best and most efficient source of validation and coercion of values.

That answer also details one way you could use the SQLAlchemy Event system to intercept data as it's set on instrumented attributes and do stuff.

Another way would be to use a hybrid_property decorator and an associated setter to do stuff to attribute values as they are set. It handles both creating new objects and querying:

from sqlalchemy.ext.hybrid import hybrid_property

class MyModel(Base):

    __tablename__ = 'mymodel'

    id = Column(Integer, primary_key=True)
    name_ = Column("name", String(100))

    @hybrid_property
    def name(self):
        return self.name_

    @name.setter
    def name(self, val):
        self.name_ = str(val)

if __name__ == '__main__':
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)
    s = Session()
    new_obj = MyModel(name=123)
    print(type(new_obj.name))  # <class 'str'>
    s.add(new_obj)
    s.commit()
    s.close()

    s = Session()
    obj = s.query(MyModel).filter(MyModel.name == 123).one()  # query with integer value
    print(obj.name, type(obj.name))  # 123 <class 'str'>

All of those methods are well documented and so could be considered 'right'.

Accepting multiple types on an SQLAlchemy model field

1 Answers1