时间戳

首页 > 解决方案 > SQLAlchemy session.query(Table) 命令太慢了。我的代码有什么问题？

python-3.x - SQLAlchemy session.query(Table) 命令太慢了。我的代码有什么问题？

问题描述

尝试选择大约 300,000 行的数据库表中的所有行

（表名='test_table'，我用的是Aurora Mysql）

from sqlalchemy.engine import create_engine, url
from sqlalchemy.orm import Session, sessionmaker

# Create a connection pool and get a session object as a property.
class Pool(object):

    def __init__(self, database, database_config):
        self.database_config = self.set_config_with_database_name(
            database, database_config)
        self.url_ = url.URL(**self.database_config)
        self.engine = create_engine(
            self.url_, echo=True, echo_pool=True, pool_size=2, max_overflow=5)
        self.Session_ = sessionmaker(bind=self.engine)

    @property
    def session(self) -> Session:
        return self.Session_()

    def set_config_with_database_name(self, database, database_config):
        conf_with_database = database_config.copy()
        conf_with_database['database'] = database
        return conf_with_database

我尝试通过会话访问表以带来数据。

# Table class generated by sqlacodegen
from models import TestTable 

# config helper
config = get_config('./config/database.ini', 'backend') 

# get a session from Pool class
connection_pool = Pool('test', config) 
session = connection_pool.session

# It takes an long time in this part.
result = session.query(TestTable.id, TestTable.name).filter(
TestTable.is_use.is_(True)).all()

下面是 sqlalchemy 回显日志。

2018-04-27 15:03:10,932 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2018-04-27 15:03:10,933 INFO sqlalchemy.engine.base.Engine {}
2018-04-27 15:03:11,017 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2018-04-27 15:03:11,018 INFO sqlalchemy.engine.base.Engine {}
2018-04-27 15:03:11,095 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'
2018-04-27 15:03:11,096 INFO sqlalchemy.engine.base.Engine {}
2018-04-27 15:03:11,143 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2018-04-27 15:03:11,144 INFO sqlalchemy.engine.base.Engine {}
2018-04-27 15:03:11,191 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2018-04-27 15:03:11,194 INFO sqlalchemy.engine.base.Engine {}
2018-04-27 15:03:11,236 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1
2018-04-27 15:03:11,237 INFO sqlalchemy.engine.base.Engine {}
2018-04-27 15:03:11,319 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-04-27 15:03:11,323 INFO sqlalchemy.engine.base.Engine SELECT test_table.id AS test_table_id, test_table.name AS test_table_name FROM test_table WHERE test_table.is_use IS true
2018-04-27 15:03:11,324 INFO sqlalchemy.engine.base.Engine {} // stucked here

老实说，我不觉得有什么问题。请让我知道我缺少什么部分

标签： python-3.xsqlalchemy

解决方案

终于，我知道问题出在哪里了。这是因为我执行了一个选择查询来一次获取太多数据。

我通过使用 sqlalchemy.orm.query.Query 解决了这个问题。yield_per

yield_per下面是有无比较的代码yield_per

# Assume that there are about 600,000 rows in the TestTable

rows = session.query(TestTable).filter(TestTable.is_use.is_(True))
yield_rows = session.query(TestTable).filter(TestTable.is_use.is_(True)).yield_per(200)

@timer
def print_rows(result_rows):
    for row in result_rows:
        if row.id > 300: # print only 300 rows
            break

print_rows(rows) # Elapsed: 21.57
print_rows(yield_rows) # Elapsed: 0.68

如果您有其他更好的方法，请评论或回答！

推荐阅读