How to apply Column defaults before a commit in sqlalchemy

Question

I have a declarative-base model:

class User(Base):     id = Column(Integer, primary_key=True)     money = Column(Integer, default=100) 

and then I run

>>> u = User() >>> u.money None 

How can I populate the defaults using sqlalchemy without writing anything to the database?

Answer 1

The column default only applies to INSERT and UPDATE statements, and thus isn't being applied until you .flush() the session.

To see the same value on new instances before you flush, you need to apply the default when a new instance is being created; in the __init__ method of the User object:

class User(Base):     __tablename__ = 'users'      def __init__(self, **kwargs):         if 'money' not in kwargs:              kwargs['money'] = self.__table__.c.money.default.arg         super(User, self).__init__(**kwargs)      id = Column(Integer, primary_key=True)     money = Column(Integer, default=100) 

If no money attribute is being set, we add one directly based on the default configured for the column.

Note that the defaults are SQL expressions, not Python values, so you may have to map those to Python objects first. For example, a boolean field will have a default 'false' or 'true' string value, not a False or True Python boolean object.

Answer 2

I have discovered a mechanism for populating some defaults automatically when an instance is created. I'm migrating from an in-house ORM that set defaults at instance creation, so I needed to preserve this behaviour and I didn't want to touch every model definition.

BaseModel = declarative_base()  class Base(BaseModel):     __abstract__ = True      def __init__(self, **kwargs):         for attr in self.__mapper__.column_attrs:             if attr.key in kwargs:                 continue              # TODO: Support more than one value in columns?             assert len(attr.columns) == 1             col = attr.columns[0]              if col.default and not callable(col.default.arg):                 kwargs[attr.key] = col.default.arg          super(Base, self).__init__(**kwargs) 

The major limitation is that only non-callable defaults are supported. Callable defaults and those that use a database query require an execution context that isn't available at instance creation time. Also, I have not discovered a case where len(ColumnProperty.columns) != 1 but that isn't supported either.