Expunge object from SQLAlchemy session

Question

I want to pass an instance of a mapped class to a non-SQLAlchemy aware method (in another process) and only need the values of my attributes. The problem is, that an UnboundExecutionError occurs, every time the method wants to read an attribute value. I do understand, why this happens, but I would like to have a solution for this problem.

I only need the values of my defined attributes (id, name and dirty in the example) and do not need the SQLAlchemy overhead in the destination method.

Example class:

Base = declarative_base()
class Record(Base):
    __tablename__ = 'records'
    _id = Column('id', Integer, primary_key=True)
    _name = Column('name', String(50))
    _dirty = Column('dirty', Boolean, index=True)

    @synonym_for('_id')
    @property
    def id(self):
        return self._id

    @property
    def name(self):
        return self._name

    @name.setter
    def name(self, value):
        self._name = value
        self._dirty = True

    name = synonym('_name', descriptor=name)

    @synonym_for('_dirty')
    @property
    def dirty(self):
        return self._dirty

Example call:

...
def do_it(self):
    records = self.query.filter(Record.dirty == True)
    for record in records:
        pass_to_other_process(record)

I've tried using session.expunge() and copy.copy(), but without success.

Answer 1

You need to remove the SQL ALchemy object from the session aka 'expunge' it. Then you can request any already loaded attribute w/o it attempting to reuse its last known session/unbound session.

self.expunge(record)

Be aware though, that any unloaded attribute will return it's last known value or None. If you would like to later work with the object again you can just call 'add' again or 'merge'

self.add(record)

Answer 2

My guess is that you're running afoul of SQLAlchemy's lazy loading. Since I don't actually know a whole lot about SQLAlchemy's internals, here's what I recommend:

class RecordData(object):
    __slots__ = ('id', 'name', 'dirty')

    def __init__(self, rec):
        self.id = rec.id
        self.name = rec.name
        self.dirty = rec.dirty

Then later on...

def do_it(self):
    records = self.query.filter(Record.dirty == True)
    for record in records:
        pass_to_other_process(RecordData(record))

Now, I think there is a way to tell SQLAlchemy to turn your object into a 'dumb' object that has no connection to the database and looks very much like what I just made here. But I don't know what it is.