Sqlalchemy : InvalidRequestError: Object '<User at 0x3a970d0>' is already attached to session '2' (this is '3')

Question

I'm working to modify a cookiecutter Flask app. I'm trying to follow https://realpython.com/blog/python/handling-email-confirmation-in-flask/ to add email authorization.

My user model is in the screenshot and is also:

class User(UserMixin, SurrogatePK, Model):

    __tablename__ = 'users'
    username = Column(db.String(80), nullable=True)
    email = Column(db.String(80), nullable=False)
    #: The hashed password
    password = Column(db.String(128), nullable=True)
    created_at = Column(db.DateTime, nullable=False, default=dt.datetime.utcnow)
    first_name = Column(db.String(30), nullable=True)
    last_name = Column(db.String(30), nullable=True)
    active = Column(db.Boolean(), default=False)
    admin = Column(db.Boolean(), default=False)
    confirmed = db.Column(db.Boolean, default=False)
    confirmed_on = db.Column(db.DateTime, nullable=True)


    def __init__(self, username=username, email=email, password=None, **kwargs):
        db.Model.__init__(self, username=username, email=email, **kwargs)
        if password:
            self.set_password(password)
        else:
            self.password = None

    def set_password(self, password):
        self.password = bcrypt.generate_password_hash(password)

As part of the signup process I receive an email from a form and process it creating a token from it and creating a User , followed by sending a confirmation email to the email using:

@blueprint.route("/get_email/", methods=['GET', 'POST'])
def get_email():
    form = EmailForm(request.form, csrf_enabled=False)
    if form.validate_on_submit():
        new_user = User.create(username=None,email=form.email.data)
        token = generate_confirmation_token(form.email.data)
        confirm_url = url_for('user.confirm_email', token=token, _external=True)
        html = render_template('users/activate.html', confirm_url=confirm_url)
        subject = "Please confirm your email"
        send_email(form.email.data, subject, html)
        return redirect(url_for("main.home"))

In this case I sent an email to:

cluemarine5@mailinator.com

The email contains a confirmation link that looks like:

http://127.0.0.1:5000/users/confirm/ImNsdWVtYXJpbmU1QG1haWxpbmF0b3IuY29tIg.CZ-urA.n5IErF0CPPG6EnIwJeSP6mPmDb4

which contains the embedded email 'cluemarine5@mailinator.com' In the token.

To confirm you click on that link which activates the following route:

@blueprint.route('/confirm/<token>')
def confirm_email(token):
    try:
        email = confirm_token(token)
    except:
        flash('The confirmation link is invalid or has expired.', 'danger')
    user = User.query.filter_by(email=email).first_or_404()
    if user.confirmed:
        flash('Account already confirmed. Please login.', 'success')
    else:
        user.confirmed = True
        user.confirmed_on = datetime.datetime.now()
        db.session.add(user)
        db.session.commit()
        flash('You have confirmed your account. Thanks!', 'success')
    return redirect(url_for('main.home'))

on debugging the code works fine down until

db.session.commit()

Then I get the error above. What am I doing wrong?

Answer 1

I finally realized that this cookiecutter is using a class called a 'CRUDMixin' for db operations, which is in database.py:

class CRUDMixin(object):
    """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."""
        instance = cls(**kwargs)
        return instance.save()

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

    def save(self, commit=True):
        """Save the record."""
        db.session.add(self)
        if commit:
            db.session.commit()
        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):
    """Base model class that includes CRUD convenience methods."""
    __abstract__ = True

I then changed:

db.session.add(user)
db.session.commit()

To:

use.save()

and it started working