SQLAlchemy Session Error

Question

Background: Flask / Flask-SQLAlchemy / Flask-WTF using declarative and restricted 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 start it, I get an error message:

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

Changing add to merge solves the problem, but:

• I do not know why I need to combine the object while I initiated it.

• If I change add to merge and try to define one of the properties of something on the 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, only now on the OrderStatus object

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

Can someone point me where I am doing something wrong, because it makes me go crazy. I have some experience with SQLAlchemy, but this is the first time I see this behavior and I can’t pinpoint the problem.

Finding everything I found was a problem with initializing a dual database session, but I do not believe this case.

EDIT

db_session is defined in a separate database.py file with the following contents

 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

There seems to be a problem with shuffling the session, I used it like this: 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 perhaps Flask-SQLAlchemy or Flask-WTF) uses one session to collect values ​​in QuerySelectField and another session to create a new Animal object.

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

It’s clear that I was a little late to the party with this answer, but I hope this helps someone!

Answer 3

This is strange. Why do you create the engine and declarative database explicitly if you use the sqlalchemy bottle? Probably your problem. It looks like you have two engines and sessions running at the same time, so you got an error.

Instead of explicitly creating the engine, you should use only:

 from flask.ext.sqlalchemy import SQLAlchemy db = SQLAlchemy() 

And inside your factory application:

 app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///fundmanager_devel.db' db.init_app(app) 

Then your basic declarative model is db.Model , your session is in db.session , and you must let the checkbox request context control the creation of the session.

Check out the minimal application example in Flask-SQLAlchemy docs:

http://pythonhosted.org/Flask-SQLAlchemy/quickstart.html#a-minimal-application

So SQLAlchemy is recommended:

Most web frameworks include an infrastructure for creating a single session associated with a request, which is correctly constructed and demolished appropriately torn down at the end of the request. Such infrastructure components include products such as Flask-SQLAlchemy, for use in conjunction with the framework, and Zope-SQLAlchemy for use in conjunction with the Pyramid and Zope platforms. SQLAlchemy strongly recommends using these products as affordable.

http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html