Sqlalchemy返回SELECT命令的不同结果(query.all) [英] Sqlalchemy returns different results of the SELECT command (query.all)

问题描述

我的Web服务器(512 RAM)具有: FLASK + SQLAlchemy(SQLite)-> uWSGI-> Nginx

I have web server (512 RAM) with: FLASK + SQLAlchemy (SQLite) -> uWSGI -> Nginx

问题:Sqlalchemy返回SELECT命令(query.all)的不同结果.

PROBLEM: Sqlalchemy returns different results of the SELECT command (query.all).

示例:

• 在数据库中添加了一些记录.
• 我重新加载页面:新记录未返回(但旧记录已返回).
• 重新加载页面:返回所有记录.很棒.
• 重新加载页面:再次没有返回新记录. (但老回来了.)

只要我不重新启动Flask应用，就会发生这种情况.

This happens as long as I do not restart Flask app.

以下代码:

DECLARATIVE_BASE = declarative_base()
engine = create_engine('sqlite:///database.db')
Session = sessionmaker(bind=engine)
session = Session()

class Order(DECLARATIVE_BASE):
    __tablename__ = 'orders'
    __table_args__ = (
        {'mysql_engine': 'InnoDB', 'sqlite_autoincrement': True, 'mysql_charset': 'utf8'}
    )

    id = Column(INTEGER, autoincrement=True, primary_key=True, nullable=False)  # pylint: disable=invalid-name
    name = Column(TEXT, nullable=False)
    address = Column(TEXT)
    phone = Column(TEXT, nullable=False)
    email = Column(TEXT)
    comment = Column(TEXT)
    totalPrice = Column(DECIMAL(asdecimal=False))
    orderItems = relationship(Orderitem)
    time = Column(TEXT, default=time.strftime("%H:%m %d.%m.%y"))

    def __repr__(self):
        return self.__str__()

    def __str__(self):
        return "<Order(%s)>" % self.__dict__

@app.route('/api/orders', methods=['GET'])
def getAllOrders():
    allOrders = session.query(Order).all()
    return json.dumps(allOrders, cls=new_alchemy_encoder(False, ['orderItems', 'product']), check_circular=False, ensure_ascii=False) #ensure_ascii=False -- for rigth out cyrlic;

推荐答案

每个工人有一个SQLAlchemy会话，并且可能在uwsgi中使用2个工人. SQLAlchemy缓存每个会话的结果，因此工作程序1的会话将返回新结果，因为您已向该工作程序添加了记录，但工作程序2的会话未更新，仅返回了旧记录.

You have one SQLAlchemy session per Worker and probably use 2 Workers with uwsgi. SQLAlchemy caches results per session, so session of worker 1 returns the new results, because you have added the records with this worker, but the session of worker 2 is not updated and returns only the old records.

解决方案:不创建全局会话，而是为每个请求创建一个新会话.

Solution: don't create global sessions, but a new session for each request.

@app.route('/api/orders', methods=['GET'])
def getAllOrders():
    session = Session()
    allOrders = session.query(Order).all()
    return json.dumps(allOrders, cls=new_alchemy_encoder(False, ['orderItems', 'product']), check_circular=False, ensure_ascii=False) #ensure_ascii=False -- for rigth out cyrlic;

这篇关于Sqlalchemy返回SELECT命令的不同结果(query.all)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！