SQLAlchemy sessions error

Question

Background: Flask / Flask-SQLAlchemy / Flask-WTF, using declarative and scoped session

Simple POST operation:

@tas.route('/order_add', methods=['GET', 'POST'])  
def tas_order_add():  
    if request.method == 'POST':
        order_form = OrderForm()
        if order_form.validate_on_submit():
            order = Order()
            order_form.populate_obj(order)
            db_session.add(order)
            db_session.commit()

Now trying to run it I get an error:

InvalidRequestError: Object '' is already attached to session '1' (this is '2')

Changing add to merge solves the problem, but:

• I don't know why do I have to merge an object while I just initiated it

• If I do change add to merge and try to define one of the properties something in line

  order = Order()
  order_form.populate_obj(order)
  order.order_status = OrderStatus.query.filter(OrderStatus.code=='PLACED').first()
  db_session.merge(order)
  db_session.commit()
  

  I get the same error, just now on OrderStatus object

  InvalidRequestError: Object '' is already attached to session '2' (this is '1')

Can someone point me where I'm doing something wrong because it's driving me nuts. I do have some experience with SQLAlchemy but it's the first time I see such a behaviour and I can't pinpoint the problem.

Searching all I found was a problem with double database session initialization but I don't belive it's this case.

EDIT

db_session is defined in separate file database.py with following content

from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm.scoping import scoped_session
from sqlalchemy.orm.session import sessionmaker

engine = create_engine('sqlite:///fundmanager_devel.db', convert_unicode=True)
db_session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()

Answer 1

seems a problem with session mix up, I used it as follows and it works:

from <project> import db
from <project.models import Category

category = QuerySelectField('category', query_factory=lambda: db.session.query(Category), get_pk=lambda a: a.id, get_label=lambda a: a.name)

Answer 2

I had a similar problem with QuerySelectField:

forms.py :

class SurgeryForm(Form):
    study = QuerySelectField('Study',
                             query_factory=StudyGroup.query.all,
                             get_label='name')

models.py

class Animal(db.Model):
    study_id = db.Column(db.Integer, db.ForeignKey('study_group.id'))

class StudyGroup(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    animals = db.relationship('Animal', backref='group', lazy='dynamic')

views.py:

def do_surgery():
    form = SurgeryForm(request.form)

    if form.validate_on_submit():
        a = models.Animal()
        form.populate_obj(a)  # Gather the easy stuff automagically
        #a.group = form.data['study']  #FAILS!
        a.study_id = form.data['study'].id  #WORKS!

It seems that SQLAlchemy (or possibly Flask-SQLAlchemy or Flask-WTF) uses one session to gather the values in the QuerySelectField and another session to create the new Animal object.

If you try to attach the StudyGroup object to the Animal using the backref (Animal.group) you'll run into this problem (since the objects associated with different sessions). The workaround that I'm using is to set the foreign key (Animal.study_id) directly.

Clearly I'm a little late to the party with this answer, but I hope it helps someone!