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

I faced the same issue. I was defining models in a separate file and I had to call SQLAlchemy twice. That's why there were two different sessions were running.

I solved this by doing following: In case you are trying to remove an object from db: Just create the removeObject function inside the model

Answer 2

This error means the record you are handling is attached to 2 different session(db)!

One of the reasons is that you may define your model with one db = SQLAlchemy(app) and add/insert/modify the database with another!

My solution is UNIFORMING THE DB!

try this:

u = db.session.query(User).filter(User.username == request.form["username"]).first()

Instead of this:

u = User.query.filter(User.username == request.form["username"]).first()

Answer 3

I had this problem too. I created a test_file.py and added this code:

from app import app
from models import Tovar  
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy(app)

tovardel = Tovar.query.filter(Tovar.region == 1 and Tovar.price == 12).first()
db.session.delete(tovardel)
tovar = Tovar.query.filter(Tovar.region == 1 and Tovar.price == 12).first()
print(tovar.description)

and when I ran the code I got this error:

Object '<Tovar at 0x7f09cbf74208>' is already attached to session '1' (this is '2')

PROBLEM SOLVING:

If you have db = SQLAlchemy(app) in, for example, text_file.py, and in app.py, you get this problem all time. You should del db = SQLAlchemy(app), and import db from app from app import db

Answer 4

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()

Answer 5

This db session issue will arise if you are having server.py and model.py importing each other

server.py

from flask import Flask
import os
import models as appmod #################### importing models here in server.py<----------

app = Flask(__name__)                                  # L1
app.config.from_object(os.environ['APP_SETTINGS'])     # L2
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False   # L3
database = SQLAlchemy(app)                             # L4
db = database                                          # L5

@app.route('/item_delete/<id>', methods=['DELETE'])
def remove_method(id = None):
    data_rec = appmod.Employee.query.get(id)    
    db.session.delete(data_rec)
    db.session.commit()
    return "DELETE"

if __name__ == '__main__':

    app.run(port=5000, host='0.0.0.0',debug=True,threaded=True)

models.py

from server import db #################### importing server in models.py here <------------
from sqlalchemy.dialects.mysql import JSON


class Employee(db.Model):
    __tablename__ = 'employe_flask'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128))
    datetime = db.Column(db.DateTime)
    designation = db.Column(db.String(128))


    def __init__(self, name, datetime, designation):
        self.name = name
        self.datetime = datetime
        self.designation = designation

    @staticmethod
    def delete_rec(data_rec):
        db.session.delete(data_rec)#.delete
        db.session.commit()

    def __repr__(self):
        record = {"name":self.name,"date":self.datetime.ctime(),"designation":self.designation}.__str__()
        return record

Remove the line L1 to L5 from server.py and place it in common file like settings.py and import 'app' and 'db' to server.py and import db in models.py

like this files below

server.py

from flask import Flask
import os
import models as appmod 
from settings import app, db


@app.route('/item_delete/<id>', methods=['DELETE'])
def remove_method(id = None):
    data_rec = appmod.Employee.query.get(id)    
    db.session.delete(data_rec)
    db.session.commit()
    return "DELETE"

if __name__ == '__main__':

    app.run(port=5000, host='0.0.0.0',debug=True,threaded=True)

settings.py

import os
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)                                  # L1
app.config.from_object(os.environ['APP_SETTINGS'])     # L2
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False   # L3
database = SQLAlchemy(app)                             # L4
db = database                                          # L5

models.py

from settings import db
from sqlalchemy.dialects.mysql import JSON


class Employee(db.Model):
    __tablename__ = 'employe_flask'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128))
    datetime = db.Column(db.DateTime)
    designation = db.Column(db.String(128))


    def __init__(self, name, datetime, designation):
        self.name = name
        self.datetime = datetime
        self.designation = designation

    @staticmethod
    def delete_rec(data_rec):
        db.session.delete(data_rec)#.delete
        db.session.commit()

    def __repr__(self):
        record = {"name":self.name,"date":self.datetime.ctime(),"designation":self.designation}.__str__()
        return record

Answer 6

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)