Debugging why Flask-SQLAlchemy won't update database

[chbr...@gmail.com]

Hello,

I'm hoping someone might have advice on how to track down this problem or what might be causing it.  I don't have code that I can use to recreate the issue as I'm not able to recreate it outside of one instance of the same codebase (I've got a local development environment, and two test development environments that UI/UX developers can have access to use and make changes to the UI/UX which I then push to the servers, but I'm the only developer making/pushing changes and all 3 have identical code apart from some contents in the database).

The issue I'm having is that Flask-SQLAlchemy will create a new row in a table/model without any problems. 

However, when I try to update a row anywhere it just simply doesn't.  If I flush before the commit it acts as if the changes have been made even when they aren't actually committed (ie if I print object.column the changed value is shown.. but then when I look in the DB or query again the original value is still there).

So as an example:

user = Users.query.filter_by(username='foo').first()

print(user.first_name)

# as an example let's say the current value is Yoshi

user.first_name = 'Tom'

db.session.flush()

db.session.commit()

print(user.first_name)

# it would output 'Tom' here, though when I look in the MySQL database, or run the initial query again the original value persists.

Is there any way to try and track down what's going on behind the scenes here to narrow down what could be causing the problem?  Is there a way to see if the db.session.commit() is actually executing, and if so what the SQL it's generating is?  When it's in a try/except block it doesn't throw any errors, when it isn't it doesn't either. 

The only other thing I've tried and noticed threw an error was explicitly adding the row to the session, such as this:

db.session.add(user)

When I do this I receive the following:

2018-06-27 08:35:32,072:   File "./app2/routes.py", line 4450, in change_info
2018-06-27 08:35:32,072:     db.session.add(user)
2018-06-27 08:35:32,072:   File "/home/werdenaz/.virtualenvs/lenv/lib/python3.6/site-packages/sqlalchemy/orm/scoping.py", line 153, in do
2018-06-27 08:35:32,072:     return getattr(self.registry(), name)(*args, **kwargs)
2018-06-27 08:35:32,072:   File "/home/werdenaz/.virtualenvs/lenv/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 1776, in add
2018-06-27 08:35:32,072:     self._save_or_update_state(state)
2018-06-27 08:35:32,072:   File "/home/werdenaz/.virtualenvs/lenv/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 1789, in _save_or_update_state
2018-06-27 08:35:32,072:     self._save_or_update_impl(state)
2018-06-27 08:35:32,072:   File "/home/werdenaz/.virtualenvs/lenv/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2106, in _save_or_update_impl
2018-06-27 08:35:32,072:     self._update_impl(state)
2018-06-27 08:35:32,073:   File "/home/werdenaz/.virtualenvs/lenv/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2089, in _update_impl
2018-06-27 08:35:32,073:     to_attach = self._before_attach(state, obj)
2018-06-27 08:35:32,073:   File "/home/werdenaz/.virtualenvs/lenv/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2179, in _before_attach
2018-06-27 08:35:32,073:     state.session_id, self.hash_key))
2018-06-27 08:35:32,073: sqlalchemy.exc.InvalidRequestError: Object '<UserInfo at 0x7f3bf01b3d30>' is already attached to session '1' (this is '2')

I'm not sure if this is helpful or not in this situation.
Thanks!

[Mike Bayer]

you want to log SQL output using echo=True or similar, which will also
show the word COMMIT when it commits the transaction:

http://docs.sqlalchemy.org/en/latest/core/engines.html#configuring-logging

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Jonathan Vanasco]

Adding to Mike's response...

I think you're using two sessions...

you select this:

    user = Users.query.filter_by(username='foo').first()

and save this

    db.session.add(user)

when you select, i think that's using some flask-sqlalchemy syntactic sugar to select the session.  a 'raw' sqlalchemy approach would typically be:

    db.session.query(Users).filter_by(username='foo').first()

Your error when saving made this stick out:

  sqlalchemy.exc.InvalidRequestError: Object '<UserInfo at 0x7f3bf01b3d30>' is already attached to session '1' (this is '2')

If that's the case, these aren't being issued against the session you queried with

    db.session.flush()

    db.session.commit()

so when you do this...

    print(user.first_name)

this never hit sql.  you're just printing the object state that has not been flushed or committed.

All that being said, You should repost this on the Flask community list/slack channel. The above code is *generally correct* and your issue is most likely in the integration layer of flask-sqlalchemy -- and not your usage of sqlalchemy. They can probably solve it faster there, as most people on this group don't really know flask/flask-sqlalchemy (but everyone here would love to know your solution).