Help us understand the problem. What is going on with this article?

Python+MySQL で dict で結果が返ってくるカーソルを使う

(この記事は私の blog の http://umezawa.dyndns.info/wordpress/?p=7308 の転載です)

ちょっと Python + MySQL をいじってるんですが、デフォルトではクエリの結果はタプルorリストで返ってくるので、雑に SELECT * FROM table とかやるとどんな順番で列が返ってくるのか SQL 文を見ただけでは不明で扱いづらいため、 dict (連想配列)で取れると嬉しいです。

何とかならないのかなぁと思いつつ PyMySQLMySQL Connector/Pythonmysqlclient のドキュメントを読むと、 connection.cursor() の引数になんか渡すと結果を dict で返してくるカーソルが得られるようなので、これを使ってみます。

準備

まず、以下のようなテーブルを作っておきます。

mysql> SELECT * FROM table1;
+------+------+
| hoge | fuga |
+------+------+
|    1 |    2 |
|    3 |    4 |
+------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM table2;
+------+------+
| fuga | piyo |
+------+------+
|    4 |    5 |
+------+------+
1 row in set (0.00 sec)

PyMySQL の場合

そして以下のように実行してみます。

#!/usr/bin/python3

mysql_kwargs = {
    "host":     "localhost",
    "port":     3306,
    "user":     "test",
    "password": "hogehoge",
    "database": "testdb",
}


import pymysql
print("PyMySQL")
print()

conn = pymysql.connect(**mysql_kwargs)

def get_cursor():
    return conn.cursor()

def get_dict_cursor():
    return conn.cursor(pymysql.cursors.DictCursor)

cur = get_cursor()
cur.execute("SELECT * FROM table1")
print(cur.description)
print(cur.fetchall())
cur.close()

cur = get_dict_cursor()
cur.execute("SELECT * FROM table1")
print(cur.fetchall())
cur.close()

print()

cur = get_cursor()
cur.execute("SELECT * FROM table1, table2 WHERE table1.fuga = table2.fuga")
print(cur.description)
print(cur.fetchall())
cur.close()

cur = get_dict_cursor()
cur.execute("SELECT * FROM table1, table2 WHERE table1.fuga = table2.fuga")
print(cur.fetchall())
cur.close()

print()

cur = get_cursor()
cur.execute("SELECT * FROM table1 INNER JOIN table2 USING(fuga)")
print(cur.description)
print(cur.fetchall())
cur.close()

cur = get_dict_cursor()
cur.execute("SELECT * FROM table1 INNER JOIN table2 USING(fuga)")
print(cur.fetchall())
cur.close()
[umezawa@devubuntu:pts/1 ~]$ ./cursortest.py
PyMySQL

(('hoge', 3, None, 11, 11, 0, False), ('fuga', 3, None, 11, 11, 0, False))
((1, 2), (3, 4))
[{'hoge': 1, 'fuga': 2}, {'hoge': 3, 'fuga': 4}]

(('hoge', 3, None, 11, 11, 0, False), ('fuga', 3, None, 11, 11, 0, False), ('fuga', 3, None, 11, 11, 0, False), ('piyo', 3, None, 11, 11, 0, False))
((3, 4, 4, 5),)
[{'hoge': 3, 'fuga': 4, 'table2.fuga': 4, 'piyo': 5}]

(('fuga', 3, None, 11, 11, 0, False), ('hoge', 3, None, 11, 11, 0, False), ('piyo', 3, None, 11, 11, 0, False))
((4, 3, 5),)
[{'fuga': 4, 'hoge': 3, 'piyo': 5}]

MySQL Connector/Python の場合

スクリプトの先頭の部分を以下のように変えて MySQL Connector/Python を使ってみます。

import mysql.connector
print("MySQL Connector/Python")
print()

conn = mysql.connector.connect(**mysql_kwargs)

def get_cursor():
    return conn.cursor()

def get_dict_cursor():
    return conn.cursor(dictionary=True)
[umezawa@devubuntu:pts/1 ~]$ ./cursortest.py
MySQL Connector/Python

[('hoge', 3, None, None, None, None, 0, 4097), ('fuga', 3, None, None, None, None, 0, 4097)]
[(1, 2), (3, 4)]
[{'hoge': 1, 'fuga': 2}, {'hoge': 3, 'fuga': 4}]

[('hoge', 3, None, None, None, None, 0, 4097), ('fuga', 3, None, None, None, None, 0, 4097), ('fuga', 3, None, None, None, None, 0, 4097), ('piyo', 3, None, None, None, None, 0, 4097)]
[(3, 4, 4, 5)]
[{'hoge': 3, 'fuga': 4, 'piyo': 5}]

[('fuga', 3, None, None, None, None, 0, 4097), ('hoge', 3, None, None, None, None, 0, 4097), ('piyo', 3, None, None, None, None, 0, 4097)]
[(4, 3, 5)]
[{'fuga': 4, 'hoge': 3, 'piyo': 5}]

mysqlclient の場合

今度はこう書き換えて mysqlclient を使います。

import MySQLdb
print("mysqlclient")
print()

conn = MySQLdb.connect(**mysql_kwargs)

def get_cursor():
    return conn.cursor()

def get_dict_cursor():
    return conn.cursor(MySQLdb.cursors.DictCursor)
[umezawa@devubuntu:pts/1 ~]$ ./cursortest.py
mysqlclient

(('hoge', 3, 1, 11, 11, 0, 0), ('fuga', 3, 1, 11, 11, 0, 0))
((1, 2), (3, 4))
({'hoge': 1, 'fuga': 2}, {'hoge': 3, 'fuga': 4})

(('hoge', 3, 1, 11, 11, 0, 0), ('fuga', 3, 1, 11, 11, 0, 0), ('fuga', 3, 1, 11, 11, 0, 0), ('piyo', 3, 1, 11, 11, 0, 0))
((3, 4, 4, 5),)
({'hoge': 3, 'fuga': 4, 'table2.fuga': 4, 'piyo': 5},)

(('fuga', 3, 1, 11, 11, 0, 0), ('hoge', 3, 1, 11, 11, 0, 0), ('piyo', 3, 1, 11, 11, 0, 0))
((4, 3, 5),)
({'fuga': 4, 'hoge': 3, 'piyo': 5},)

というわけで何となく期待したような結果になっています。

注意点とか

MySQL Connector/Python は、同じ名前の列が複数出現するときの挙動が他の2つと異なる(他の2つは2つ目の出現にはテーブル名が付くので見分けがつくが、MySQL Connector/Python はテーブル名は付かずに上書きされるっぽい?)ので、その点だけ注意でしょうか。

ちなみに dict なカーソルが使えない場合は、以下のような idiom で同様の結果を得ることができます。(最初は全部こう書いててダサいなぁと思ってた)

dict_results= [dict((cursor.description[i][0], value) for i, value in enumerate(row)) for row in cursor.fetchall()]

使った環境

  • Ubuntu 18.04 LTS
  • Python 3.6.8
  • PyMySQL 0.9.3
  • MySQL Connector/Python 2.1.6
  • mysqlclient 1.3.10
umezawatakeshi
AVX-512 が実装されたパフォーマンスデスクトップ向けプロセッサを待ち続けて早幾年月
http://umezawa.dyndns.info/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした