在数据库表中遍历所有行的最佳方法

Question

我经常写一些小的Python脚本，用来遍历数据库表中的所有行。比如说，给所有订阅者发送电子邮件。

我通常是这样做的：

conn = MySQLdb.connect(host = hst, user = usr, passwd = pw, db = db)
cursor = conn.cursor()
subscribers = cursor.execute("SELECT * FROM tbl_subscriber;")

for subscriber in subscribers:
 ...

conn.close()

我在想，是否有更好的方法来实现这个，因为我的代码可能会把成千上万的行加载到内存中。

我考虑过使用LIMIT来做得更好。也许可以像这样：

"SELECT * FROM tbl_subscriber LIMIT %d,%d;" % (actualLimit,steps)    

那么，最好的方法是什么呢？你会怎么做呢？

Answer 1

大多数基于libmysqlclient的MySQL连接器默认会把所有的查询结果都存储在客户端的内存中，这是为了提高性能（假设你不会读取很大的结果集）。

当你需要读取一个很大的结果集时，可以使用SSCursor，这样就可以避免把整个大的结果集都缓存在内存里。

http://mysql-python.sourceforge.net/MySQLdb.html#using-and-extending

SSCursor - 这是一个“服务器端”的游标。它和普通的游标类似，但使用了CursorUseResultMixIn。只有在处理可能很大的结果集时才使用它。

不过，这样做会带来一些复杂性，你需要小心。如果你没有从游标中读取所有的结果，第二次查询会引发一个ProgrammingError错误：

>>> import MySQLdb
>>> import MySQLdb.cursors
>>> conn = MySQLdb.connect(read_default_file='~/.my.cnf')
>>> curs = conn.cursor(MySQLdb.cursors.SSCursor)
>>> curs.execute('SELECT * FROM big_table')
18446744073709551615L
>>> curs.fetchone()
(1L, '2c57b425f0de896fcf5b2e2f28c93f66')
>>> curs.execute('SELECT NOW()')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib64/python2.6/site-packages/MySQLdb/cursors.py", line 173, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.6/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (2014, "Commands out of sync; you can't run this command now")

这意味着在发起另一个查询之前，你必须始终从游标中读取所有内容（可能还有多个结果集），MySQLdb不会为你自动处理这些。

Answer 2

你不需要修改查询语句，可以使用游标的fetchmany方法。下面是我怎么做的：

def fetchsome(cursor, some=1000):
    fetch = cursor.fetchmany
    while True:
        rows = fetch(some)
        if not rows: break
        for row in rows:
            yield row  

这样你可以执行“SELECT * FROM tbl_subscriber;”，但每次只会获取一些数据。

Answer 3

除非你的数据库里有大对象（BLOB），否则几千行数据应该不会有什么问题。你知道这是什么意思吗？

另外，为什么要让自己和家人感到羞愧，去做这样的事情呢

"SELECT * FROM tbl_subscriber LIMIT %d,%d;" % (actualLimit,steps)

而使用游标可以帮你自动处理这些替换，避免SQL注入的风险呢？

c.execute("SELECT * FROM tbl_subscriber LIMIT %i,%i;", (actualLimit,steps))