MySQL Connector/PythonからSQLを投げる

quiver

2017.07.30

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

RDBを操作する際には、SQL文の組み立てが発生します。自前で文字列操作を駆使してSQL文を組み立てると、バグやセキュリティホールの温床となりかねません。

今回はMySQL Connector/Pythonからお行儀よくSQLを投げる方法を紹介します。

事前準備:MySQL Connector/Python のインストール

MySQL Connector/Python のインストールは次の記事を参照して下さい。

MySQL Connector/Python 2.1をAmazon Linuxにインストールする

今回は 2.1.6 を利用します

(非推奨)自前でSQLを組み立てる

ナイーブにSQL文を自分で組み立て、データベースに投げてみましょう

import mysql.connector
config = {
    'user': 'USER',
    'password': 'PASSWORD',
    'host': 'HOST',
    'database' : 'DBNAME',
}
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
stmt = "select * from company where name = '%s'"
cursor.execute(stmt % "classmethod")

... # read data

cursor.close()
cnx.close()

name が "classmethod" だと正しく実行出来ました。 "McDonald's" だとどうなるでしょうか?

>>> "select * from company where name = '%s'" % "McDonald's"
"select * from company where name = 'McDonald's'"

セッションからわかるように、生成された SQL はシングルクオートのシンタックスエラーがあります。

エスケープ処理を入れ忘れたために、今回の様なことが発生します。

様々な入力パターンに対しても正しくSQLが実行されるように、以降で紹介するようにプレースホールダーを使ってSQLを実行します。

プレースホルダーを使ってSQLを投げる

Python には DB エンジンや DB ドライバーによらずにシームレスかついい感じにDB操作するための仕様「DB-API 2.0」があります。

PEP 249 -- Python Database API Specification v2.0

MySQL Connector/Python もこの仕様に準拠しているため、この API を素直に活用します。

まずは DB-API 2.0 に準拠していることを確認します。

>>> import mysql.connector
>>> mysql.connector.apilevel
'2.0'
>>> mysql.connector.threadsafety
1
>>> mysql.connector.paramstyle
'pyformat'

先程のようにSQL文を自前で組み立てず、テンプレートとパラメーターを分離して、API に渡します

import mysql.connector
config = {
    'user': 'USER',
    'password': 'PASSWORD',
    'host': 'HOST',
    'database' : 'DBNAME',
}
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()

stmt = "select * from company where name = %s"
cursor.execute(stmt, ("McDonald's", ))

... # read data

cursor.close()
cnx.close()

SQLのテンプレートではプレースホールダー %s の前後でクオートしません
テンプレートに渡すパラメーターはタプルで渡します

このようにするだけで、文字列をクオートで囲んだり、エスケープ処理した SQL文("select * from company where name = "McDonald\\'s")が DB サーバーに送信されます

プリペアドステートメントを利用する

MySQL Connector/Pythonはプリペアドステートメントにも対応しています。カーソル作成時に引数(prepared=True)を追加するだけです。

import mysql.connector
config = {
    'user': 'USER',
    'password': 'PASSWORD',
    'host': 'HOST',
    'database' : 'DBNAME',
}
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor(prepared=True)            # 修正

stmt = "select * from company where name = ?" # 修正
cursor.execute(stmt, ("McDonald's", ))

... # read data

cursor.close()
cnx.close()

プリペアドステートメントの違いは以下です

同じSQL文の場合、2回目以降のSQL文の解析が不要
%s(format/pyformat) だけでなく ?(qmark) もパラメーターのプレースホルダーとして利用可能

動的要素のないSQLに対しては有用です。

エスケープ関数を使う

様々な理由により、自前でSQLを組み立てざるを得ないケースもあります。そのような場合は、エスケープ処理は自前で実装せず、コネクションオブジェクトの converter.escape 関数を利用します。

>>> cnx.converter.escape("a'b")
"a\\'b"

カーソルではなくコネクションからSQLを投げる

コネクションオブジェクトには cmd_query というメソッドが存在し、SQL を直接投げることが出来ます。

import mysql.connector
config = {
    'user': 'USER',
    'password': 'PASSWORD',
    'host': 'HOST',
    'database' : 'DBNAME',
}
cnx = mysql.connector.connect(**config)

stmt = "select * from company where name = '%s'"
cnx.cmd_query(stmt, (cnx.converter.escape("McDonald's"), ))

... # read data

cnx.close()

カーソルオブジェクトとは異なり