Issues

Issue #2994 invalid

PickleType gets not updated in Database in some circumstances

Marc Schlaich
created an issue

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 =====================

Comments (11)

  1. Mike Bayer repo owner

    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:

  2. Marc Schlaich reporter

    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.

  3. Mike Bayer repo owner

    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.

  4. Marc Schlaich reporter

    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).

  5. Log in to comment