MySQL のクエリ結果を JSON で取得したい Python 編

tl;dr

SELECT * FROM table WHERE id = N の結果を JSON で取得したい. ただ, それだけ.

こんなテーブルがあったとする

mysql> SELECT * FROM users;
+------+------+--------+
| id   | old  | name   |
+------+------+--------+
|    1 |   18 | Satou  |
|    2 |   20 | Yamada |
+------+------+--------+
2 rows in set (0.00 sec)

この結果を JSON で欲しい.

実装例

環境

$ python --version
Python 3.6.4
$ pip freeze
mysqlclient==1.3.12
pep8==1.7.1

mysqlclient は Python3.4 以降では動かない MySQLdb1 を fork したもの.

1 件取得する

SELECT * FROM users LIMIT 1;

SQL を実行すると...

mysql> SELECT * FROM users LIMIT 1;
+------+------+-------+
| id   | old  | name  |
+------+------+-------+
|    1 |   18 | Satou |
+------+------+-------+
1 row in set (0.00 sec)

以下のように書いた.

import MySQLdb
import json

conn = MySQLdb.connect(user='root',
                       password='your_password',
                       host='127.0.0.1',
                       db='sample')
query = 'SELECT * FROM users;'
cursor = conn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute(query)
data = cursor.fetchone()
conn.close()
print(json.dumps(data, indent=4))

実行.

$ python sample2.py 
{
    "id": 1,
    "old": 18,
    "name": "Satou"
}

肝となるのは Cursor クラスの MySQLdb.cursors.DictCursor で, これを用することでクエリの結果を辞書形式で取得することが出来る. 詳細についてはドキュメントの Using and extending に書かれている.

全件を取得する

SELECT * FROM users

SQL を実行すると, 以下のような結果となる.

mysql> SELECT * FROM users;
+------+------+--------+
| id   | old  | name   |
+------+------+--------+
|    1 |   18 | Satou  |
|    2 |   20 | Yamada |
+------+------+--------+
2 rows in set (0.00 sec)

以下のように書いた.

import MySQLdb
import json

conn = MySQLdb.connect(user='root',
                       password='your_password',
                       host='127.0.0.1',
                       db='sample')
query = "SELECT * FROM users;"
with conn.cursor(MySQLdb.cursors.DictCursor) as cursor:
    cursor.execute(query)
    data = cursor.fetchall()
print(json.dumps(data, indent=4))

以下のように出力される. with ~ as ステートメントを使うことで conn.close() が不要になる.

$ python sample.py 
[
    {
        "id": 1,
        "old": 18,
        "name": "Satou"
    },
    {
        "id": 2,
        "old": 20,
        "name": "Yamada"
    }
]

指定した条件のレコードを取得

SELECT * FROM users WHERE id = 2;

SQL を実行すると, 以下のような結果となる.

mysql> SELECT * FROM users WHERE id = 2;
+------+------+--------+
| id   | old  | name   |
+------+------+--------+
|    2 |   20 | Yamada |
+------+------+--------+
1 row in set (0.00 sec)

以下のように書いた.

import MySQLdb
import json

conn = MySQLdb.connect(user='root',
                       password='your_password',
                       host='127.0.0.1',
                       db='sample')
query = "SELECT * FROM users;"
with conn.cursor(MySQLdb.cursors.DictCursor) as cursor:
    cursor.execute(query)
    all_data = cursor.fetchall()

for data in all_data:
    if data['id'] == 2:
        print(json.dumps(data, indent=4))

以下のように出力される.

$ python sample.py 
{
    "id": 2,
    "old": 20,
    "name": "Yamada"
}

辞書形式になってしまえばこっちの物だってことで...

以上

思ったよりも簡単にクエリの結果を JSON で得ることが出来た.