PickleType gets not updated in Database in some circumstances

[Marc Schlaich]

Test:

import logging
logging.getLogger('sqlalchemy').setLevel(logging.INFO)
logging.basicConfig()

from sqlalchemy import create_engine, Column, Integer, PickleType
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Test(Base):
    __tablename__ = 'test'
    id = Column(Integer, primary_key=True)
    counters = Column(PickleType)


engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)


def test_update_dict():
    session = Session()
    session.add(Test(counters=[dict(counter=1)]))
    session.commit()

    t = session.query(Test).one()
    for d in t.counters:
        # it's obvious that this doesn't trigger a change in SQLA
        d['counter'] -= 1

    # try to force a change in SQLAlchemy, this should issue a
    # update query
    t.counters = list(t.counters)
    session.commit()

    t = session.query(Test).one()
    assert t.counters == [dict(counter=0)]

Result:

$ py.test sqla_test.py
============================= test session starts =============================
platform win32 -- Python 2.7.6 -- py-1.4.20 -- pytest-2.5.2
plugins: cov
collected 1 items / 1 skipped

sqla_test.py F

================================== FAILURES ===================================
______________________________ test_update_dict _______________________________

    def test_update_dict():
        session = Session()
        session.add(Test(counters=[dict(counter=1)]))
        session.commit()

        t = session.query(Test).one()
        for d in t.counters:
            # it's obvious that this doesn't trigger a change in SQLA
            d['counter'] -= 1

        # try to force a change in SQLAlchemy, this should issue a
        # update query
        t.counters = list(t.counters)
        print t.counters
        session.commit()

        t = session.query(Test).one()
>       assert t.counters == [dict(counter=0)]
E       assert [{'counter': 1}] == [{'counter': 0}]
E         At index 0 diff: {'counter': 1} != {'counter': 0}

sqla_test.py:43: AssertionError
------------------------------- Captured stdout -------------------------------
[{'counter': 0}]
------------------------------- Captured stderr -------------------------------
INFO:sqlalchemy.orm.mapper.Mapper:(Test|test) _post_configure_properties() started
INFO:sqlalchemy.orm.mapper.Mapper:(Test|test) initialize prop id
INFO:sqlalchemy.orm.mapper.Mapper:(Test|test) initialize prop counters
INFO:sqlalchemy.orm.mapper.Mapper:(Test|test) _post_configure_properties() complete
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO test (counters) VALUES (?)
INFO:sqlalchemy.engine.base.Engine:(<read-only buffer for 0x03971C50, size -1, offset 0 at 0x03913060>,)
INFO:sqlalchemy.engine.base.Engine:COMMIT
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:SELECT test.id AS test_id, test.counters AS test_counters
FROM test
INFO:sqlalchemy.engine.base.Engine:()
INFO:sqlalchemy.engine.base.Engine:COMMIT
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:SELECT test.id AS test_id, test.counters AS test_counters
FROM test
INFO:sqlalchemy.engine.base.Engine:()
===================== 1 failed, 1 skipped in 0.63 seconds =====================

[Marc Schlaich]

Workaround is to create new dictionaries:

t.counters = [dict(counter=d['counter'] - 1) for d in t.counters]

[Marc Schlaich]

Even more surprising, this fails, too:

t.counters[0]['counter'] -= 1
t.counters = [dict(counter=d['counter']) for d in t.counters]

[Mike Bayer]

OK hate to pop the bubble on this one but PickleType doesn't automatically track changes. To do the list of dicts here, we need to create a custom structure to track changes as follows:

from sqlalchemy.ext.mutable import MutableDict, Mutable

class MutableDictInList(MutableDict):
    parent = None

    def __init__(self, parent, value):
        self.parent = parent
        super(MutableDictInList, self).__init__(value)

    def changed(self):
        if self.parent:
            self.parent.changed()

class MutableList(Mutable, list):
    """A list type that implements :class:`.Mutable`.

    """

    def __init__(self, value):
        super(MutableList, self).__init__(self._dict(v) for v in value)

    def _dict(self, value):
        value = MutableDictInList(self, value)
        return value

    def __setitem__(self, key, value):
        """Detect dictionary set events and emit change events."""
        list.__setitem__(self, key, self._dict(value))
        self.changed()

    def append(self, value):
        list.append(self, self._dict(value))
        self.changed()

    @classmethod
    def coerce(cls, key, value):
        if not isinstance(value, MutableList):
            if isinstance(value, list):
                return MutableList(value)
            return Mutable.coerce(key, value)
        else:
            return value

    def __getstate__(self):
        return list(dict(v) for v in self)

    def __setstate__(self, state):
        self[:] = [self._dict(value) for value in state]


class Test(Base):
    __tablename__ = 'test'
    id = Column(Integer, primary_key=True)
    counters = Column(MutableList.as_mutable(PickleType))

Background on PickleType and mutability:

• Mutation Extension supersedes MutableType in 0.7
• MutableType has been removed in 0.8
• PickleType documentation
• Mutation Tracking

[Mike Bayer]

the ORM only knows things changed based on events, so a custom structure needs to emit its own events via the mutable extension.

[Marc Schlaich]

PickleType doesn't automatically track changes

I don't except that in-place changes of this data are automatically detected (as I pointed out in my first comment in the test case). But I assumed that any setattr emits a changed event, this is IMO the expected behavior from a user's point of view.

But even worse, as seen above, PickleType.__setattr__ does not behave consistently. Sometimes it does trigger a database update and sometimes not. This is IMO a behavioral bug.

[Mike Bayer]

If you have an object "x", and you have "x.foo = 3". then you persist that, then you set it again: "x.foo = 3". You get no UPDATE statement. This is because when SQLAlchemy detects a change, it sets the value of the attribute to the new value, then it stores away the old value in a dictionary (which you can see via inspect(obj).committed_state). At UPDATE time, it compares the two values. if they are the same, SQLAlchemy is smart enough not to emit a needless UPDATE when there is no net change.

In the case of this:

t.counters[0]['counter'] -= 1
t.counters = [dict(counter=d['counter']) for d in t.counters]

you are setting the value of "t.counters" to something new, however it compares as exactly the same to the value that was already there, because you are changing it in place first.

Basically, when you are ORM-persisting a value that is to receive in place mutations, you have to use Mutable to track that. Or, assign a totally new value without mutating the old one to look identical.

[Marc Schlaich]

Ah, a reasonable solution in my case is to use PickleType(comparator=lambda *a: False).

Sorry for spamming the issue tracker, I wanted to delete my previous comment but BB is unwilling to do so (upstream reported #9163).

[Mike Bayer]

Removing milestone: 0.8.xx (automated comment)