CRUDMixin to handle errors

[some-crab]

The CRUDMixin class assumes valid inputs to its methods/classmethods but this may not be the case. I added this class to my own example and when interacting in the python shell I noticed that this leads to some issues. First, using the .create classmethod without providing any arguments to the specific model (e.g. User.create()) throws an error like this:

sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) null value in column "username" of relation "users" violates not-null constraint 
DETAIL:  Failing row contains (17, null, null, null).

That might be ok, but the problem arises when, after attempting and failing with the above, I then use User.create(username='john', email='john@john', password='password'). I receive another error which suggests that the previous attempt was somehow stored and is interfering with writing new entries:

sqlalchemy.exc.PendingRollbackError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (psycopg2.errors.NotNullViolation) null value in column "username" of relation "users" violates not-null constraint
DETAIL:  Failing row contains (17, null, null, null).

I think it might be useful to handle such cases to avoid these problems, but maybe I'm overlooking something? I understand it isn't a typical way of interacting with the model classes, but it is sometimes useful for testing purposes to interact via the python shell. My suggestion is simply this:

class CRUDMixin:
    """Mixin that adds convenience methods for CRUD (create, read, update, delete) operations."""

    @classmethod
    def create(cls, **kwargs):
        """Create a new record and save it the database."""
        if not kwargs:
            raise ValueError(f"Must provide valid inputs to the {cls.__name__} model.")
        instance = cls(**kwargs)
        return instance.save()

    # @classmethod
    # def read(cls, **kwargs):
    #     return cls.query.filter_by(**kwargs)

    def update(self, commit=True, **kwargs):
        """Update specific fields of a record."""
        if not kwargs:
            raise ValueError(f"Must provide valid inputs to the {cls.__name__} model.")

        for attr, value in kwargs.items():
            setattr(self, attr, value)
        if commit:
            return self.save()
        return self

    def save(self, commit=True):
        """Save the record."""
        db.session.add(self)
        if commit:
            # Try it and rollback if there's an error ================ #
            try:
                db.session.commit()
            except Exception as e:
                print(e)
                print("Rolling back...")
                db.session.rollback()

        return self

    def delete(self, commit: bool = True) -> None:
        """Remove the record from the database."""
        db.session.delete(self)
        if commit:
            try:
                committed = db.session.commit()
                return committed
            except Exception as e:
                print(e)
                print("Rolling back...")
                db.session.rollback()
        return

On a side note, for what reason is there no .read classmethod in the CRUDMixin, since it is a CRUD operation? (e.g. as commented in the above)

[tgross35]

I think it's reasonable to not define exception handling in CRUDMixin since the handling varies tremendously. Do you try to resend the transaction a couple times in case of network errors? Do you immediately return a 502 gateway error if you can't connect?

Perhaps one way to do it would be to implement an exception handler callback.

I have something similar to you, except I broke out a flush() method before committing since that's the likely failure (not necessarily CRUD but useful nonetheless). Flush just explicitly rolls back (like you have) then reraises the exception:

class CRUDMixin:
    """Mixin that adds convenience methods for CRUD (create, read, update, delete) operations."""

    @classmethod
    def create(cls: Type[T], commit=True, **kwargs) -> T:
        """Create a new record and save it the database."""
        instance = cls(**kwargs)
        return instance.save(commit)

    def update(self, commit=True, **kwargs):
        """Update specific fields of a record."""
        for attr, value in kwargs.items():
            setattr(self, attr, value)
        return commit and self.save() or self

    def save(self, commit=True, **kwargs):
        """Save the record. Allow kwargs to be handed in case flush gets overwritten."""
        db.session.add(self)
        if commit:
            self.flush(**kwargs)
            db.session.commit()
        return self

    def delete(self, commit: bool = True) -> None:
        """Remove the record from the database."""
        db.session.delete(self)
        if commit:
            self.flush()
            return db.session.commit()
        return

    def flush(self) -> None:
        """Flush the session"""
        try:
            db.session.flush()
        except Exception:
            db.session.rollback()
            # possible exception callback here
            raise

Then I just have a global error handler on sqlalchemy.exc.OperationalError and pymysql.err.OperationalError that will display a 502 error. Obviously this is just another example of how to handle things, but the possibilities are endless.

Regarding read(), what would this method do? Reading is just getting a row object (from query or get_by_id()) and accessing its members. You could maybe wrap query or get_by_id() (or getattr(), depending on what you consider a read) in a read method, but I don't think you're adding any clarity in doing that.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.