Sql Alchemy: Child object doesn't load during before_commit event listener

user_11672615 0
Viewed 245

I am working on configuring my app to automatically update an elasticsearch (es) index anytime specified database tables/columns are updated. I'm basing it off Michael Grinberg's Flask Megatutorial. I am using a SQLAlchemy session event listeners (before_commit and after_commit) to collect the changes and determine when to update the es index.

The table I am indexing is a user table that has an address table as its child. Besides indexing the user's name, I also want to index their city, which comes from the address table. My issue is that address field populates as None, instead of loading the address. When creating a new user (along with its address), I receive the following error: 'This session is in 'committed' state; no further SQL can be emitted within this transaction.' when I try to index the city value.

I have also tried using the after_flush_postexec event, with the same results. Do you have any suggestions for configurations to load the child relationship in either the 'after_commit' or the 'after_flush_postexec' events?

models.py


class SearchableMixin(object):
    @classmethod
    def before_commit(cls, session):
        session._changes = {
            'add': list(session.new),
            'update': list(session.dirty),
            'delete': list(session.deleted)
        }

    @classmethod
    def after_commit(cls, session):
        for obj in session._changes['add']:
            if isinstance(obj, SearchableMixin):
                #do stuff to add User name and city to index
        for obj in session._changes['update']:
            if isinstance(obj, SearchableMixin):
                # do stuff to update User name and city in index
        for obj in session._changes['delete']:
            if isinstance(obj, SearchableMixin):
                # do stuff to delete User name and city from index
        session._changes = None

class User(SearchableMixin, db.model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    address = db.relationship("Address", backref="user", lazy='joined', uselist=False)

    @hybrid_property
    def city(self):
        return self.address.city


class Address(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    city = db.Column(db.String(50))
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
flask-sqlalchemy sqlalchemy python
1 Answers

Here's the solution I came up with. While solving it, it presented another issue for which I've asked a [separate question][1]. However, while far from perfect, it does seem to work.

Instead of using sqlalchemy event hooks, I first subclassed db.model, adding in a CRUD mixin that adds create, update, save, delete functions. Then in the Searchable Mixin, I overrode those create, update, and delete functions to call the appropriate indexing function.

models.py

class CRUDMixin(object):
    @classmethod
    def create(cls, **kwargs):
        """Create a new record and save it the database."""
        instance = cls(**kwargs)
        saved = instance.save()
        return saved
        
    def update(self, commit=True, **kwargs):
        for attr, value in kwargs.items():
            if value != getattr(self, attr):
                setattr(self, attr, value)
        return commit and self.save() or self

    def save(self, commit=True):
        db.session.add(self)
        if commit:
            try:
                db.session.commit()
            except Exception:
                db.session.rollback()
                raise
        return self

    def delete(self, commit=True):
        """Remove the record from the database."""
        db.session.delete(self)
        return commit and db.session.commit()


class Model(CRUDMixin, db.Model):

    __abstract__ = True

class SearchableMixin(object):

    @classmethod
    def create(cls, **kwargs):
        try:
            new = super().create(**kwargs)
            add_to_index(new)
            db.session.commit()
            return new
        except Exception:
            raise

    def update(self, commit=True, **kwargs):
        try:
            super().update(commit, **kwargs)
            add_to_index(self)
            db.session.commit()
            return self
        except Exception:
            raise

    def delete(self, commit=True):
        try:
            super().delete(commit)
            remove_from_index(self)
            db.session.commit()
            return None
        except Exception:
            raise

class User(SearchableMixin, Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    address = db.relationship("Address", backref="user", lazy='joined', uselist=False)

    @hybrid_property
    def city(self):
        return self.address.city


class Address(Model):
    id = db.Column(db.Integer, primary_key=True)
    city = db.Column(db.String(50))
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))


  [1]: https://stackoverflow.com/questions/62722027/sql-alchemy-wont-drop-tables-at-end-of-test-due-to-metadata-lock-on-db
user_116726150
Related