Mysql/python fetchall（）无法处理结果，因为它太大

Question

我有一个2760000行的表。在mysqlworkbench中，从原始表中选择*需要36秒。在

我想用python中的这个现有表创建另一个表（使用myfunc（）来转换值）。在

但是，当我在命令行中运行它时，它似乎永远不会结束。在

sql = "SELECT ID, Eye, Values FROM my_original_table"
curQuery.execute(sql)

for row in curQuery.fetchall():        
    dat = list(row)
    id = dat.pop(0)
    eye = dat.pop(0)
    values = dat.pop(0)     
    v = my_func(values)
    if v != None :
        sql = "INSERT INTO new_table VALUES ( '%s', '%s', %d );" % (id, eye, v)
    print(sql)
    curExe.execute(sql)

db.commit()

但是，如果我在第一个select sql中添加了LIMIT 0,10（如下所示），它运行良好。所以，这意味着我的计划是正确的。但这是否意味着没有“限制”，数据太多，我的电脑无法处理？我怎么解决这个问题？在

^{pr2}$

Answer 1

根据documentation：

db.store_result() returns the entire result set to the client immediately. If your result set is really large, this could be a problem. One way around this is to add a LIMIT clause to your query, to limit the number of rows returned. The other is to use use_result(), which keeps the result set in the server and sends it row-by-row when you fetch. This does, however, tie up server resources, and it ties up the connection: You cannot do any more queries until you have fetched all the rows. Generally I recommend using store_result() unless your result set is really huge and you can't use LIMIT for some reason.

db = MySQLdb.connect(yourhost,yourname,yourpw,yourdb)

db.query("SELECT ID, Eye, Values FROM my_original_table")

r=db.use_result()

>>> r.fetch_row()
(('3','2','0'),)

Answer 2

使用游标作为迭代器（不调用fetchall）：

sql = "SELECT ID, Eye, Values FROM my_original_table"
curQuery.execute(sql)

for row in curQuery:
    # ...

以上相当于使用while循环处理查询，其中包含fetchone：

^{pr2}$