PythonをつかってMySQLの巨大な結果を返すselect文を処理する

  • 2月 25, 2009 08:22

PythonからMySQLを扱うには一般的にMySQLdbモジュールを使います。

(または、MySQLdbを内部で使っているSQLAlchemyなどのO/Rマッパー)

MySQLdbを普通に使うと以下のようになります。

>>> import MySQLdb
>>> con = MySQLdb.connect(db="intra", host="yourhost", user="your_username", passwd="your_passwd")
>>> cur = con.cursor(MySQLdb.cursors.DictCursor)
>>> cur.execute("select * from hoge_tbl limit 90000")
90000L
>>> cur.fetchone()
{'columns1': '4', 'maintdate': datetime.date(2002, 8, 13), 'hoge': 'hoge' }
# 実際には
#for row in cur:
#    rowの処理
>>> cur.close()
>>> con.close()

ところがこの方法だとexecutメソッドでSQLを発行した時点で結果をメモリに溜め込む仕様らしく大量のデータを扱うときに問題があります。

ちなみに僕の例だと 276260k も使ってました。

Webアプリのように一画面にしか結果を表示しないものなら便利なcursorを使う方が良いですが、

大量データを扱うバッチ処理などではメモリを消費しないようにしたいものです。

<追記: 最初に書いた方法と別な方法があったので追加しておきます>

SSDictCursorを使えばexecuteの時点でメモリを消費しない模様。

しかしcloseメソッドで結局、メモリを大量に使う、、、謎です。

>>> import MySQLdb
>>> con = MySQLdb.connect(db="intra", host="yourhost", user="your_username", passwd="your_passwd")
>>> cur = con.cursor(MySQLdb.cursors.SSDictCursor) #ここでSSDictCursorを指定
>>> cur.execute("select * from hoge_tbl limit 90000") #この時点でメモリ 6936k
18446744073709551615L #limit 90000にも関わらず激しい数字が表示 意味不明
>>> cur.fetchone()
{'columns1': '4', 'maintdate': datetime.date(2002, 8, 13), 'hoge': 'hoge' }
# 実際には
#for row in cur:
#    rowの処理
>>> cur.close() #closeした途端 メモリ最大250000k に そのあと142240k
>>> con.close()

<追記おしまい>

どんな方法があるかと思ってドキュメントを見ていたら

MySQLdb User's Guide

>>> import MySQLdb
>>> con = MySQLdb.connect(db="intra", host="yourhost", user="your_username", passwd="your_passwd")
>>> con.query("select * from hoge_tbl limit 90000")
>>> r = con.use_result()
>>> print r.fetch_row(1, 1) #1行をcolumn名をkeyにした辞書型でタプルに入れて結果を返す
({'columns1': '4', 'maintdate': datetime.date(2002, 8, 13), 'hoge': 'hoge' },)
>>> con.close()

という方法が良さそうです。

これだと、con.use_result()を実行した時点で、メモリは6916k。

単純に対話コンソールを立ち上げた時点で4720kだったのでかなり少なくすみました。これはサーバー側に結果を保持して、fetch_rowを実行するたびに次の結果をもらうためです。

当然、con.close()するまで接続を保持するので他のことはできないので注意が必要です。

ちなみに fetch_rowメソッドは

fetch_row([maxrows, how]) -- Fetches up to maxrows as a tuple.
The rows are formatted according to how:

0 -- tuples (default)
1 -- dictionaries, key=column or table.column if duplicated
2 -- dictionaries, key=table.column

と、なっています。

僕は結果は辞書でもらった方がいいので第2引数に1を指定しました。

そのまんま使うにはちょっと使いづらいし、closeを忘れると面倒なのでラッパー書くのが良いでしょう。