Recently, I used the sqlalchemy of flask, because it made some encapsulation for sqlalchemy, and I am not familiar with sqlalchemy myself, so I went a lot of detours in usage.

Because there is no time to study the source code of sqlalchemy, we can only test its usage.

1. Flash Sqlalchemy is thread safe

Please refer to the article for details https://blog.csdn.net/luffyser/article/details/89380186

2. After each query, remember to commit, otherwise the connection pool will be occupied

I made a simple test locally. If a single query request is completed without a commit, the request will be made several times in a row, and then the request will not respond. Presumably, the database connection pool is not released and occupied. Causes the request database to hang, resulting in no response.

Directly commit after each request can solve this problem.   

    def queryLast(cls):
        try:
            ret = db.session.query(cls).order_by(cls.version.desc()).first()
　　　　　　　#db.session.expunge(ret)
            db.session.commit()
        except:
            db.session.rollback()
            ret = None

        return ret

3. However, if you Commit, the query results may be cleared from the cache. If you reuse the objects of the query results, the connection query will be established again. Therefore, the above connection pool exhaustion problem will occur.

From the log with yellow icon below, we can see that after commit, when using the returned query result, another query task is executed and the result is returned, my god!

2019-12-10 12:31:21,650 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-12-10 12:31:21,651 INFO sqlalchemy.engine.base.Engine SELECT appversion.id AS appversion_id, appversion.version AS appversion_version, appversion.`appUrl` AS `appversion_appUrl`, appversion.des AS appversion_des, appversion.`createTime` AS `appversion_createTime`, appversion.`lastModiftyTime` AS `appversion_lastModiftyTime`, appversion.type AS appversion_type 
FROM appversion ORDER BY appversion.version DESC 
 LIMIT %s
2019-12-10 12:31:21,651 INFO sqlalchemy.engine.base.Engine (1,)
2019-12-10 12:31:21,661 INFO sqlalchemy.engine.base.Engine COMMIT
2019-12-10 12:31:21,680 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-12-10 12:31:21,681 INFO sqlalchemy.engine.base.Engine SELECT appversion.id AS appversion_id, appversion.version AS appversion_version, appversion.`appUrl` AS `appversion_appUrl`, appversion.des AS appversion_des, appversion.`createTime` AS `appversion_createTime`, appversion.`lastModiftyTime` AS `appversion_lastModiftyTime`, appversion.type AS appversion_type 
FROM appversion 
WHERE appversion.id = %s
2019-12-10 12:31:21,681 INFO sqlalchemy.engine.base.Engine (1,)

4. However, sometimes (maybe for a little longer) after the commit, an error may be reported when the query result object is used again: instance < user at 0x32768d0 > is not bound to a session

This may be because the bound session has been recycled, resulting in no more queries, so there is an error.

5. To sum up, for the sake of security, you need to add db.session.expand (RET) after the query result to disconnect the relationship between the query result and session. Make it a local entity. It will not be cleared from the cache. When it is used, it will not be queried again.

6. During the test, I found a strange problem. The other interface to get the user is almost the same as the above code, and it will automatically roll back, which makes me puzzled.

My logic is to get the query result (i.e. the user), judge whether the status field of the user is 1. If it is 1, modify the user property, and then commit. If it is not 1, do not operate or call rollback.

But when I tested it, I found that if it was not 1, it would automatically roll back.

Maybe this is an advanced function of sqlalchemy?

7. Summary:

1. sqlalchemy's object entity (model) has established a connection with the session. When you get and set these models, even if you have already commit ted, it will automatically re establish a connection with the database (when you get, it will re select and set, it will re establish the connection, waiting for you to submit. If you do not submit, the connection will always exist, and eventually it will be exhausted.) , so be careful with model fields, unless you really know what you're doing and what's going to happen.

2. Using db.session.expand will cut off the relationship between the entity and session. This is a good use.

3. However, I strongly recommend that I build another model to do business level logic. sqlalchemy's object entities are only used for database operations. In this way, it will avoid many times that the pit is not released due to careless connection.