游标的作用
现在经过层层封装的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))
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。