SQLAlchemy session error: InvalidRequestError

Question

I am trying to commit as well as query the results at the same time from DB and i ended up with this error.

sqlalchemy.exc.InvalidRequestError: This session is in 'committed' state; no further SQL can be emitted within this transaction.

Full Traceback:

Traceback (most recent call last):
  File "C:\Python34\lib\site-packages\flask\app.py", line 1836, in __call__
    return self.wsgi_app(environ, start_response)
  File "C:\Python34\lib\site-packages\flask\app.py", line 1820, in wsgi_app
    response = self.make_response(self.handle_exception(e))
  File "C:\Python34\lib\site-packages\flask\app.py", line 1403, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "C:\Python34\lib\site-packages\flask\_compat.py", line 33, in reraise
    raise value
  File "C:\Python34\lib\site-packages\flask\app.py", line 1817, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\Python34\lib\site-packages\flask\app.py", line 1477, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "C:\Python34\lib\site-packages\flask\app.py", line 1381, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "C:\Python34\lib\site-packages\flask\_compat.py", line 33, in reraise
    raise value
  File "C:\Python34\lib\site-packages\flask\app.py", line 1473, in full_dispatch_request
    rv = self.preprocess_request()
  File "C:\Python34\lib\site-packages\flask\app.py", line 1666, in preprocess_request
    rv = func()
  File "C:\Users\Ajay\PycharmProjects\Pypix-Flask\codehackr\auth\views.py", line 14, in before_request
    if current_user.is_authenticated():
  File "C:\Python34\lib\site-packages\werkzeug\local.py", line 338, in __getattr__
    return getattr(self._get_current_object(), name)
  File "C:\Python34\lib\site-packages\werkzeug\local.py", line 297, in _get_current_object
    return self.__local()
  File "C:\Python34\lib\site-packages\flask_login.py", line 46, in <lambda>
    current_user = LocalProxy(lambda: _get_user())
  File "C:\Python34\lib\site-packages\flask_login.py", line 794, in _get_user
    current_app.login_manager._load_user()
  File "C:\Python34\lib\site-packages\flask_login.py", line 363, in _load_user
    return self.reload_user()
  File "C:\Python34\lib\site-packages\flask_login.py", line 325, in reload_user
    user = self.user_callback(user_id)
  File "C:\Users\Ajay\PycharmProjects\Pypix-Flask\codehackr\models.py", line 296, in load_user
    u = db.session.query(User, unread_count).filter(User.id == user_id).first()
  File "C:\Python34\lib\site-packages\sqlalchemy\orm\query.py", line 2333, in first
    ret = list(self[0:1])
  File "C:\Python34\lib\site-packages\sqlalchemy\orm\query.py", line 2200, in __getitem__
    return list(res)
  File "C:\Python34\lib\site-packages\sqlalchemy\orm\query.py", line 2404, in __iter__
    return self._execute_and_instances(context)
  File "C:\Python34\lib\site-packages\sqlalchemy\orm\query.py", line 2417, in _execute_and_instances
    close_with_result=True)
  File "C:\Python34\lib\site-packages\sqlalchemy\orm\query.py", line 2408, in _connection_from_session
    **kw)
  File "C:\Python34\lib\site-packages\sqlalchemy\orm\session.py", line 843, in connection
    close_with_result=close_with_result)
  File "C:\Python34\lib\site-packages\sqlalchemy\orm\session.py", line 847, in _connection_for_bind
    return self.transaction._connection_for_bind(engine)
  File "C:\Python34\lib\site-packages\sqlalchemy\orm\session.py", line 298, in _connection_for_bind
    self._assert_active()
  File "C:\Python34\lib\site-packages\sqlalchemy\orm\session.py", line 192, in _assert_active
    "This session is in 'committed' state; no further "
sqlalchemy.exc.InvalidRequestError: This session is in 'committed' state; no further SQL can be emitted within this transaction.

views.py

@main.route('/user/<username>', methods=['GET','POST'])
def user(username):
    form = SkillForm()
    if form.validate_on_submit():
        skill_add = Skill(skill=form.skill.data, author=current_user._get_current_object())
        db.session.add(skill_add)
        return redirect(url_for('main.index'))
    user = User.query.filter_by(username=username).first_or_404()
    page = request.args.get('page', 1, type=int)
    pagination = user.posts.order_by(Post.timestamp.desc()).paginate(
        page, per_page=current_app.config['POSTS_PER_PAGE'],
        error_out=False)
    posts = pagination.items
    skills = user.skills.all()
    return render_template('user/user.html', user=user, posts=posts,
                           pagination=pagination, form=form, skills=skills)

How to solve this error. Thanks

Answer 1

Since you have added some objects to the session via db.session.add(), you must first commit those changes, or roll them back, in order to be able to make queries using this same session.

db.session.add(skill_add)
db.session.commit() # add this line
return redirect(url_for('main.index'))

Adding the line specified would probably result in your code working as you expect.

Also, although you're adding a skill, querying a user, this error is raised because those two are bound by a relationship, so when you query for a user you also query for the skills this user has.