头图

游标的作用

现在经过层层封装的web端业务系统,游标已经不太使用了,不过在处理大规模数据的时候,还是用游标速度快,省内存,可以大幅提升查询效率。

假设某数据表有1亿条记录,都需要导出,或者做业务数据处理,用OR Mapping一次性读入内存显然是不可能的,那么显然要分段处理。常见的分段方式,是通过offset+limit来指定返回的记录窗口(flask-sqlalchemy直接提供了paginate对象实现分页,应该是对offset+limit的封装)。
这种方式很直观,但缺点也很明显:每一段的都重新执行了一遍庞大的查询,只返回其中很小一部分数据集,对数据库性能是巨大的浪费。

此时就应该是游标发挥作用的时候:Cursor的价值在于,可以只执行一次查询,缓存结果位置索引,cursor记录当前位置,之后每次通过fetch获取n条记录,cursor就移动n个位置,从而节省大量磁盘IO。

在实现原理上,Cursor又分为服务器端游标和客户端游标,区别在于在哪里缓存结果集索引,服务器端缓存可以让多个连接共享结果集,而且数据库的内存配置往往很高;不过呢,现在的数据库客户端,通常也是应用服务器,内存配置也不低,做客户端缓存,有助于分担服务器的压力。Sqlalchemy的缺省连接,都是客户端缓存。

Sqlalchemy获取游标

从网上搜索的话,通常搜出来的代码都是这样的:

engine = create_engine('sqlite:///file.db')
connection = engine.connect()
try:
    cursor_obj = connection.cursor()
    cursor_obj.execute("select * from table1")
    results_one = cursor_obj.fetchall()
    cursor_obj.close()
finally:
    connection.close()

或者用raw_connection代替connection,但如果我们是在一个web环境中,使用类似flask-sqlalchemy环境,显然不能这样自己管理数据库连接,而且所有的transaction也要统一管理,所以要从session中获取连接:

    db = SQLAlchemy()
    db.init_app(app)

    session = db.session()
    cursor = session.execute('select * from user limit 10').cursor
    result = cursor.fetchall()
    print(result)

使用游标获取数据

得到游标以后,就可以用游标来获取数据了,注意游标只能从前往后顺序移动,不可能倒回去重新获取了,所以fetch的动作,即是获取数据,又是移动游标。一共有三个fetch方法:fetchone(), fetchmany(size) 和 fetchall(),顾名思义,分别为获取一条记录,多条记录和全部记录。
fetchmany和fetchall获取的结果集为嵌套的tuple,第一层是行,第二层是列;而fetchone因为摆明了只有1条记录,结果集就是单层tuple。例如假设user表有四个字段
id | account | name | comment

下面这段代码:

        cursor = session.execute('select * from user limit 10').cursor
        result = cursor.fetchmany(2)
        print(result)
        result = cursor.fetchmany(2)
        print(result)
        result = cursor.fetchone()
        print(result)
        result = cursor.fetchall()

输出内容类似下面的样子:

((1, 'acc1', 'acc1', None), (2, 'acc2', 'acc2', None))
((3, 'acc3', 'acc3', None), (4, 'acc4', 'acc4', None))
(5, 'acc6', 'acc6', None)
((6, 'acc7', 'acc7', None), (7, 'acc8', 'acc8', None), (8, 'acc9', 'acc9', None), (9, 'acc10', 'acc10', None), (10, 'acc11', 'acc11', None))

songofhawk
303 声望24 粉丝