SQLAlchemy Query returning None

Question

I have a web application using SQLAlchemy and MySQL-server on my ubuntu 16 & Nginx + uwsgi.

while creating the engine, i put

echo=True

to get tracing of the query. I have problem registering user, everytime user_loader is called on flask login, I execute:

dbsession.query(User).filter_by(id=user_id).first()

The result i get is :

INFO sqlalchemy.engine.base.Engine SELECT user.id AS user_id, user.name AS user_name, user.email AS user_email, user.pass$
Mar 29 23:48:56 ubuntu-512mb-sgp1-01 uwsgi[26160]: FROM user
Mar 29 23:48:56 ubuntu-512mb-sgp1-01 uwsgi[26160]: WHERE user.id = %s
Mar 29 23:48:56 ubuntu-512mb-sgp1-01 uwsgi[26160]:  LIMIT %s
Mar 29 23:48:56 ubuntu-512mb-sgp1-01 uwsgi[26160]: 2017-03-29 23:48:56,517 INFO sqlalchemy.engine.base.Engine ('61', 1)

The result is None. However, the on the above example, using user_id = 61 , , I can find the user on mysql shell in ubuntu to get user id 61.

When i refresh the page a few times, then the result will come out. Once user finished registering, they will be redirected to login page, upon completing the form, it shows error "Please register", which is triggered if user is now found using this query:

dbsession.query(User).filter_by(id=user_id).first()

On registration my code is:

user = User(name=name, email=email, password=password)
dbsession.add(user)
dbsession.commit()
return redirect(url_for('login'))

checking has been done to ensure name, email password is valid.

Thanks

Please provide a [minimal, *complete*, and *verifiable* example](http://stackoverflow.com/help/mcve). Your issues might stem from a multitude of reasons. For example MySQL's default transaction isolation level is [REPEATABLE READ](https://en.wikipedia.org/wiki/Isolation_(database_systems)#Repeatable_reads), so if for any reason your transactions linger, you will not be able to read fresh data. And what does "However, the user id 61 is valid and can be called." mean exactly. How can it be "called"? — Ilja Everilä, Mar 30 '17 at 06:56
@IljaEverilä if you see the user.id is passed in as 61 (does not matter '61' or 61). I tried querying the db using session.execute and it returns back the correct user. however, this session.query().first() keeps returing None and sometimes a User object. How to ensure that fresh data is alr inside ? — Andree Wijaya, Mar 30 '17 at 09:54
Create a coherent example that replicates the observed behaviour in your question. Don't drop single lines of code in comments. You tried querying it using `session.execute()` where? In a shell? What do you mean by sometimes? On different requests to your web server? If you want proper answers, provide proper steps to *reproduce the issue*. More often than not these types of questions turn out to be about accessing the wrong DB, not committing in time, etc. — Ilja Everilä, Mar 30 '17 at 10:44
So you've created a user in 1 view, but still my best bet would be that you have concurrent workers servicing requests and for some reason you're able to keep a transaction going between requests. And so it happens that when you try to read the created user, you happen to get a worker with a stale transaction open, in REPEATABLE READ isolation level, and cannot read the new data. — Ilja Everilä, Mar 30 '17 at 12:02
I get the gist of your explaination. Is there any way i can handle this ? — Andree Wijaya, Mar 30 '17 at 12:05
@IljaEverilä I have solved it thanks for the guide. managed to get everything smoothly now — Andree Wijaya, Mar 30 '17 at 12:23

score 3 · Answer 1 · answered Mar 30 '17 at 12:25

3

Solved the problem.

Thanks to @iljaEverila.

Basically I need to make sure the dbsession from SQLAlchemy has persisted the data and there is no pending transaction. So that once user registered and user_loader is invoke, i just need to call:

dbsession.commit()

Then calling this:

dbsession.query(User).filter_by(id=user_id).first()

will be successful.

Thanks

answered Mar 30 '17 at 12:25

Andree Wijaya

425
1
5
13

2

Good that you got it going, but this is band-aid over a bigger problem: why are your transactions "leaking" between requests. Probably a configuration issue, or perhaps something related to this: http://stackoverflow.com/questions/23301968/invalid-transaction-persisting-across-requests – Ilja Everilä Mar 30 '17 at 12:31
1

It might also be a bit bad idea to commit state that you don't control in a view. Rollback would be the "safer" option. – Ilja Everilä Mar 30 '17 at 12:38
1

@IljaEverilä updated my code to rollback and it works fine. Thanks! Btw, what kind of config issue may cause leaking ? Cos this is my first time setting up ubuntu server. Need to pick up the pieces – Andree Wijaya Mar 30 '17 at 16:03

SQLAlchemy Query returning None

1 Answers1