SQLAlchemy Object already attached to session

Question

I'm trying to get a server for an app working, but I'm getting an error upon login:

[!] Object '<User at 0x7f12bc185a90>' is already attached to session '2' (this is '3')

It seems the session I'm adding is already on the database. This is the snippet of code that is causing the problem:

@app.route('/login', methods=['POST'])
def login():
    u = User.query.filter(User.username == request.form["username"]).first()
    if not u or u.password != request.form["password"]:
        return error("E1")

    s = Session.get_by_user(u)
    if s is not None:
         db_session.delete(s)
         db_session.commit()

     print db_session.execute("SELECT * FROM sessions").fetchall()

     s = Session(u)
     db_session.add(s)
     db_session.commit()

     return jsonify(s.values)

As you can see, I'm printing the content from the sessions table before trying to add anything, and it is empty! ([])

What else could be causing this?

Here is the 'Session' implementation:

class Session(Base):
__tablename__ = "sessions"
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey('users.id'), unique=True)
user = relationship(User)
key = Column(String(50), unique=True)
created = Column(DateTime)

def __init__(self, user=None):
    self.user = user
    self.key = base64.encodestring(os.urandom(24)).strip()
    self.created = datetime.now()

def __repr__(self):
    return '<Session %r>' % (self.key)

@property
def values(self):
    return {"username" : self.user.username,
            "key" : self.key,
            "created" : str(self.created),
            }
@classmethod
def get_by_key(cls, key):
    s = cls.query.filter(cls.key == key).first()
    #print datetime.now() - s.created
    if s and datetime.now() - s.created > settings.SESSION_LIFETIME:
        s = None
    return s

@classmethod
def get_by_user(cls, user):
    s = cls.query.filter(cls.user == user).first()
    if s and datetime.now() - s.created > settings.SESSION_LIFETIME:
        s.query.delete()
        db_session.commit()
        s = None
    return s

Answer 1

Object you're trying to modify is already attached to another session. Maybe you have wrong imports, and db_session is a new instance.

A good workaround to this is to extract the current bound session and use it:

Instead of:

db_session.add(s)

Do:

current_db_sessions = db_session.object_session(s)
current_db_sessions.add(s)

Answer 2

As @marcinkuzminski mentioned, you can't add an object that is already attached to another session. Just pulling in the original session from the object with object_session() is risky, though, if you aren't sure that session originated in the same thread context you're currently operating in. A thread-safe method is to use merge():

    local_object = db_session.merge(original_object)
    db_session.add(local_object)
    db_session.commit()