通过Python批量从MySQL检索数据 [英] Retrieving Data from MySQL in batches via Python
本文介绍了通过Python批量从MySQL检索数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
由于数量多,我想分批进行此过程.
I would like to make this process in batches, because of the volume.
这是我的代码:
getconn = conexiones()
con = getconn.mysqlDWconnect()
with con:
cur = con.cursor(mdb.cursors.DictCursor)
cur.execute("SELECT id, date, product_id, sales FROM sales")
rows = cur.fetchall()
如何实现索引以批量获取数据?
How can I implement an index to fetch the data in batches?
推荐答案
第一点:python db-api.cursor
是迭代器,因此除非您真的需要立即将整个批处理加载到内存中,您可以从使用此功能开始,即代替:
First point: a python db-api.cursor
is an iterator, so unless you really need to load a whole batch in memory at once, you can just start with using this feature, ie instead of:
cursor.execute("SELECT * FROM mytable")
rows = cursor.fetchall()
for row in rows:
do_something_with(row)
您可以:
cursor.execute("SELECT * FROM mytable")
for row in cursor:
do_something_with(row)
然后,如果您的数据库连接器的实现仍未正确使用此功能,则是时候将LIMIT和OFFSET添加到混合中了:
Then if your db connector's implementation still doesn't make proper use of this feature, it will be time to add LIMIT and OFFSET to the mix:
# py2 / py3 compat
try:
# xrange is defined in py2 only
xrange
except NameError:
# py3 range is actually p2 xrange
xrange = range
cursor.execute("SELECT count(*) FROM mytable")
count = cursor.fetchone()[0]
batch_size = 42 # whatever
for offset in xrange(0, count, batch_size):
cursor.execute(
"SELECT * FROM mytable LIMIT %s OFFSET %s",
(batch_size, offset))
for row in cursor:
do_something_with(row)
这篇关于通过Python批量从MySQL检索数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文