Why does SQLAlchemy initialize hstore field as null?

Question

I am using Flask-SQLAlchemy and have a model with the following column:

class Item(Model):
    misc = Column(MutableDict.as_mutable(postgresql.HSTORE), nullable=False,
                  server_default='',
                  default=MutableDict.as_mutable(postgresql.HSTORE))

When I try to assign fields to the model object, it seems that the misc column is None, rather than an empty dict:

my_item = Item()
my_item.misc["foo"] = "bar"
# TypeError: 'NoneType' object does not support item assignment

How can I configure the model so that new objects get initialized with an empty dictionary?

Answer 1

There are two problems here. First, default should be a Python dict, not a repeat of the column type. Second, default is not used when initializing new instances, only when committing instances with no value set for that column. So you need to specifically add a default during initialization also.

from sqlalchemy.dialects.postgresql import HSTORE
from sqlalchemy.ext.mutable import MutableDict

class Item(db.Model):
    misc = db.Column(MutableDict.as_mutable(HSTORE), nullable=False, default={}, server_default='')

    def __init__(self, **kwargs):
        kwargs.setdefault('misc', {})
        super(Item, self).__init__(**kwargs)

item = Item()
item.misc['foo'] = 'bar'

---

_{On that note, there's no point in setting both default and server_default if you're only ever going to use this from Python. There's no harm either, though.}

---

_{I assume that you know you want an HSTORE in this specific case, but I'll also point out that PostgreSQL has the more general JSON and JSONB types now.}