Python3でMySQLを操作する
Pythonを書き始めて結構経つのですが、最近初めてDBを使いたくなり、はて?どうすればよいのやら…と調べたあれこれを書き残しておきます。PythonでDBを操るには、標準モジュールでは事足りず、各DB向けの追加モジュールが必要です。
実行環境
Python 3.5.2 Mysql 5.7.17
PyMySQLのインストール
「MySQL-python」を使おうと思ったのですが、自分の環境ではエラーとなりうまくインストールできませんでした。 Python3なら「PyMySQL」がサクッと接続できていいよ との情報を目にしてこちらにしてみました。
$ sudo pip3 install PyMySQL $ python3 > import pymysql.cursors
エラーがでないので無事インストール完了です。
テーブル・データ
testというDBに作成しました。
CREATE TABLE `post` ( `id` int(11) NOT NULL AUTO_INCREMENT, `content` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `post` (`id`, `content`) VALUES (1, 'おはようございます。いい天気ですね!!'), (2, '太郎さん、新聞が届いていますよ。'), (3, '天気がいいのは良いですが、ちと暑いですね'), (4, '今日もお仕事ご苦労さまです');
Pythonコード
GitHubのサンプルコードを参考に、PythonでMySQLを操作するコードを書いていきます。
PyMySQL/PyMySQL: Pure Python MySQL Client
#! /usr/bin/python3 # -*- coding: utf-8 -*- import pymysql.cursors connection = pymysql.connect(host='localhost', user='root', password='******', db='test', charset='utf8', # Selectの結果をdictionary形式で受け取る cursorclass=pymysql.cursors.DictCursor) try: with connection.cursor() as cursor: sql = "SELECT * FROM post" cursor.execute(sql) dbdata = cursor.fetchall() for rows in dbdata: print(rows) finally: connection.close()
実行結果
{'content': 'おはようございます。いい天気ですね!!', 'id': 1} {'content': '太郎さん、新聞が届いていますよ。', 'id': 2} {'content': '天気がいいのは良いですが、ちと暑いですね', 'id': 3} {'content': '今日もお仕事ご苦労さまです', 'id': 4}
気になった点の調査
実行したSQL文を知りたい
意図しない動作となったとき早期解決できるように実行したSQL文が見たいです。上のテストコードように単純なら良いのですが、動的に値を変えるとなると頭の中だけでは追いづらいです。
先ほどのGitHubのページから、executeの実処理である、cursors.py の executeメソッドを追ってみました。すると、self.executed に実行したSQL文を保持していました。このプロパティにアクセスすれば実行SQL文が取得できそうです。
先ほどのコードのSQL文にWHERE句を追加して、idを置換するように変更します。実行後に self.executed を出力するとid置換後のSQL文が取得できました。
with connection.cursor() as cursor: sql = "SELECT * FROM post WHERE id = %s" cursor.execute(sql, (1)) print(cursor._executed) dbdata = cursor.fetchall() for rows in dbdata: print(rows)
SELECT * FROM post WHERE id = 1 {'content': 'おはようございます。いい天気ですね!!', 'id': 1}
特殊文字がエスケープがされるか
文字置換するときに、SQLインジェクション対策がなされるかを調べました。
with connection.cursor() as cursor: sql = "SELECT * FROM post WHERE id = %s" cursor.execute(sql, ("';DELETE FROM post;'")) print(cursor._executed) dbdata = cursor.fetchall() for rows in dbdata: print(rows)
SELECT * FROM post WHERE id = '\';DELETE FROM post;\''
適切にエスケープされました。
DELETE文は文字列として処理され、\‘;DELETE FROM post;\’ という id は存在しないので何も取得されません。
置換文字を名前ベースで指定
GitHubのサンプルコードでは、置換文字列をタプルで渡しています。
このままだと、置換する件数が後から増えたとき前から順番を合わせなければならず大変です。
名前ベースでアクセスをしたいもの。
こちらも、cursors.py を追っていくと mogrifyメソッドで 渡した値を % 演算子で置換していました。
最終的には、次の形で実行しています。タプル部分が、executeの第2引数です。
'SELECT * FROM post WHERE id = %s' % (1)
Pythonの文法的に次のように変えても問題ないはずです。
'SELECT * FROM post WHERE id = %(id)s' % {'id':1}
SQL文とexecuteの引数を変えてみます。
with connection.cursor() as cursor: sql = "SELECT * FROM post WHERE id = %(id)s" cursor.execute(sql, {'id':1}) print(cursor._executed) dbdata = cursor.fetchall() for rows in dbdata: print(rows)
SELECT * FROM post WHERE id = 1 {'content': 'おはようございます。いい天気ですね!!', 'id': 1}
問題ないですね!!!
ふぅ、実際にコードを追ってみるって大切ですね。
使いやすくしてみる
今までの内容をまとめて、もう少し使いやすくするため、クラス化してみました。