Sqlalchemy: Iterators should automatically be marked for an update whenever next is called on them

Created on 1 Jan 2021 · 3Comments · Source: sqlalchemy/sqlalchemy

Describe the solution you'd like

The title pretty much describes the feature. Whenever an iterator is iterated through via the next function they should be marked as needing to be updated in the database.

Example

thing = session.get(Thing, 123)
# thing.cycler is an itertools.cycle object
value_i_need = next(thing.cycler)
# thing.cycler should be updated in the
# SQL database when session.commit()
# is called
session.commit()

Thing Class for reference

Base = declarative_base()
class Thing(Base):
  id = Column(Integer, primary_key=True)
  cycler = Column(PickleType)

Describe alternatives you've considered

The current work around I am using is this

thing = session.get(Thing, 123)
cycler = thing.cycler
value_i_need = next(cycler)
# Since SQLAlchemy checks for changes
# in the __setattr__ method
# we reassign cycler to thing.cycler
thing.cycler = cycler
# This will update the thing.cycler
# in the database
session.commit()

Thing Class needed to make this work

Base = declarative_base()
class Thing(Base):
  id = Column(Integer, primary_key=True)
  # In comparator assume any change
  # in this attribute should be written
  # to the database
  cycler = Column(PickleType(comparator=lambda a, b: False))

Have a nice day!

Andres 🙃

question

Source

Ahsoka

All 3 comments

hey there -

PickleType values don't detect in-place mutation, as documented at PickleType you need to build your own mutation-detection scheme using the sqlalchemy.ext.mutable extension. This involves building subclasses of Python types such as dict, list, which are provided already, and in this case the itertools.cycle object, such that .changed() method is called.

The notion of pickling iterables not to mention storing them in a database is kind of out there, so just to make sure it works, here's a POC

import itertools

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import Integer
from sqlalchemy import PickleType
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.mutable import Mutable
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import Session


class MutableCycler(Mutable, itertools.cycle):
    def __next__(self):
        val = super(MutableCycler, self).__next__()
        self.changed()
        return val

    @classmethod
    def coerce(cls, key, value):
        # receive plain itertools.cycle objects and convert.
        # only needed if the application works with the itertools.cycle()
        # class directly instead of MutableCycler
        if not isinstance(value, MutableCycler):
            value = MutableCycler(*value.__reduce__()[1])
        return value


Base = declarative_base()


class Thing(Base):
    __tablename__ = "thing"

    id = Column(Integer, primary_key=True)
    cycler = Column(MutableCycler.as_mutable(PickleType))


e = create_engine("sqlite://", echo=True)

Base.metadata.create_all(e)

with Session(e) as session:

    session.add(Thing(id=123, cycler=itertools.cycle([1, 2, 3, 4, 5])))
    session.commit()


for i in range(15):
    session = Session(e)
    thing = session.get(Thing, 123)
    value_i_need = next(thing.cycler)
    print(value_i_need)
    session.commit()