linuxBean14.04(69)MySQL5.6にPython3.3から接続する

前の関連記事：linuxBean14.04(68)mysql_config_editorで認証情報を難読化

サンプルデータベースworld databaseをMySQL Workbench6.3を使ってMySQL5.6にインストールし、その後Connector/Python 2.0.4を使ってPython3.3から接続します。

サンプルデータベースworld databaseのインストール

MySQL :: MySQL Documentation: Other MySQL Documentationからworld databaseのZip形式をダウンロードします。

ファイルマネージャでダウンロードしたworld.sql.zipを右クリック→ここでファイルで展開。

解凍してできたworld.sqlファイルをMySQL Workbench6.3でMySQL ConnectionからData Import/RestoreからMySQL5.6に取り込みます。


City, Country, CuntryLanguageの3つのテーブルが取り込まれました。

Python3.3からMySQL5.6に接続するためのPyCharmのプロジェクトを作成する

linuxBean14.04(4)PyCharmをSynapticパッケージマネージャからインストールでインストールしたPyCharmで操作します。


アップデートで現在PyCharm 4.5.4です。

PythonとMySQL5.6の接続にはlinuxBean14.04(64)MySQL Utilities1.5.5とConnector/Python2.0.4のインストールでインストールしたConnector/Python2.0.4を使います。

PythonはlinuxBean14.04(38)LibreOfficeバンドルPythonにパッケージを追加でインストールしたPython3.3を使うのでConnector/Python2.0.4インストール時にmysql-connector-python-py3もインストールしておきます。


PyCharm4.5の起動画面の下にあるConfigureからSettings。


Default Project→Project Interpreter。

Project Interpreterで　/usr/bin/python3.3　を選択します。

mysql-conncetor-python 2.0.4がインストールされていることがわかります。

「OK」をクリック。

元の起動画面に戻ってCreate New Projectをクリック。


Locationのuntitledをmysqlに変更しました。

Interpreterは/usr/bin/python3.3　を選択します。

「Create」をクリック。


これでmysqlプロジェクトが作成されました。

あとは右クリック、New→Python File、でpyファイルを作成します。

Connector/Python 2.0.4を使ってPython3.3でMySQL5.6に接続する

 5.1 Connecting to MySQL Using Connector/Pythonにいくつか例が載っています。

import mysql.connector
cnx = mysql.connector.connect(user='User01', password='user01_pass', host='127.0.0.1', database='world')
cnx.close()

これで接続できました。

7.1 Connector/Python Connection Argumentsによるとportはデフォルト値3306でhostもデフォルト値が127.0.0.1なのでhostも省略できます。

import mysql.connector
cnx = mysql.connector.connect(user='User01', password='user01_pass', database='world')
cnx.close()

このmysql.connector.connect()メソッドを使う方法以外にconnection.MySQLConnectionクラスを使う方法もあります。

from mysql.connector import connection
cnx = connection.MySQLConnection(user='User01', password='user01_pass', database='world')
cnx.close()

どちらでも同じ結果を得ることができますが、connect()を使う方法が好まれるそうです。

#!/usr/bin/python3.3
# -*- coding: utf-8 -*-
import mysql.connector
from mysql.connector import errorcode
try:
    cnx = mysql.connector.connect(user='User01', password='user01_pass', database='world')
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("ユーザー名かパスワードが間違っています。")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("データベースが存在しません。")
    else:
        print(err)
else:
    cnx.close()

エラー処理をつけて接続部分は完成です。

Connector/Python 2.0.4を使ってPython3.3でMySQL5.6からデータを取り出す

5.4 Querying Data Using Connector/PythonとGraph data from a MySQL database in Python | Modern Dataを参考にしました。

#!/usr/bin/python3.3
# -*- coding: utf-8 -*-
import mysql.connector
from mysql.connector import errorcode
try:
    cnx = mysql.connector.connect(user='User01', password='user01_pass', database='world')
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("ユーザー名かパスワードが間違っています。")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("データベースが存在しません。")
    else:
        print(err)
else:
    cursor = cnx.cursor()
    query = "SELECT Name, Continent, Population, LifeExpectancy, GNP FROM Country"
    cursor.execute(query)
    for (Name, Continent, Population, LifeExpectancy, GNP) in cursor:
      print("{}, {}, {}, {}, {}".format(Name, Continent, Population, LifeExpectancy, GNP))
    cursor.close()
    cnx.close() 

16行目でqueryに入れたSQLを17行目で実行しています。

worldスキーマのテーブルCountryから国、大陸、人口、平均余命、国民総生産を抜き出して出力します。

/usr/bin/python3.3 /home/pq/PycharmProjects/mysql/mysql069-2.py
Aruba, North America, 103000, 78.4, 828.0
Afghanistan, Asia, 22720000, 45.9, 5976.0
Angola, Africa, 12878000, 38.3, 6648.0
Anguilla, North America, 8000, 76.1, 63.2
Albania, Europe, 3401200, 71.6, 3205.0
Andorra, Europe, 78000, 83.5, 1630.0
Netherlands Antilles, North America, 217000, 74.7, 1941.0
United Arab Emirates, Asia, 2441000, 74.1, 37966.0
Argentina, South America, 37032000, 75.1, 340238.0

出力は239個続きます。

クエリーをPython の文字列操作を使って構築することは安全ではない

#!/usr/bin/python3.3
# -*- coding: utf-8 -*-
import mysql.connector
from mysql.connector import errorcode
config = {
    'user': 'User01',
    'password': 'user01_pass',
    'database': 'world'
}
try:
    cnx = mysql.connector.connect(**config)
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("ユーザー名かパスワードが間違っています。")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("データベース「{}」が存在しません。".format(config['database']))
    else:
        print(err)
else:
    cursor = cnx.cursor()
    cols = 'Name', 'Continent', 'Population', 'LifeExpectancy', 'GNP'
    query = 'SELECT {}, {}, {}, {}, {} FROM Country'.format(*cols)
    cursor.execute(query)
    for (Name, Continent, Population, LifeExpectancy, GNP) in cursor:
        print("{}, {}, {}, {}, {}".format(Name, Continent, Population, LifeExpectancy, GNP))
    cursor.close()
    cnx.close()

コードが読みやすいと思ってPythonの文字列操作を使って22行目でクエリーを作るようにしましたが、これは安全ではないそうです。

 SQL インジェクション攻撃に対し脆弱になるそうで、具体的にはIPA ISEC　セキュア・プログラミング講座：Webアプリケーション編　第6章 入力対策：SQL注入: #1 実装における対策が詳しいです。

12.6. sqlite3 — SQLite データベースに対する DB-API 2.0 インタフェース — Python 3.3.6 ドキュメントにはPython文字列操作の代わりに、DB-APIパラメータの割当を使います、と書いてあります。

MySQLではクエリーで変数の値を使いたいところに%sと書いておいて、cursor.execute()の第2引数のタプルで%sに入れる値を渡せばよいわけです。

#!/usr/bin/python3.3
# -*- coding: utf-8 -*-
import mysql.connector
from mysql.connector import errorcode
config = {
    'user': 'User01',
    'password': 'user01_pass',
    'database': 'world'
}
try:
    cnx = mysql.connector.connect(**config)
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("ユーザー名かパスワードが間違っています。")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("データベース「{}」が存在しません。".format(config['database']))
    else:
        print(err)
else:
    cursor = cnx.cursor()
    cols = 'Name', 'Continent', 'Population', 'LifeExpectancy', 'GNP'
    query = 'SELECT %s, %s, %s, %s, %s FROM Country'
    cursor.execute(query, cols)
    for (Name, Continent, Population, LifeExpectancy, GNP) in cursor:
        print("{}, {}, {}, {}, {}".format(Name, Continent, Population, LifeExpectancy, GNP))
    cursor.close()
    cnx.close()

22行目で%sとおいたところに23行目でタプルcolsの値が入るはずですが、これはうまくいきません。

23行目でcursor.execute()によって作られるクエリが、

SELECT Name, Continent, Population, LifeExpectancy, GNP FROM Country

ではなく、

SELECT 'Name', 'Continent', 'Population', 'LifeExpectancy', 'GNP' FROM Country

となっているからです。

第2引数の値が文字列として認識されるのでSQLインジェクションができないメリットはあるのですが、実行できるSQLを代入できないのでクエリに文字列か数値を入れることしかできません。

#!/usr/bin/python3.3
# -*- coding: utf-8 -*-
import mysql.connector
from mysql.connector import errorcode
config = {
    'user': 'User01',
    'password': 'user01_pass',
    'database': 'world'
}
try:
    cnx = mysql.connector.connect(**config)
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("ユーザー名かパスワードが間違っています。")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("データベース「{}」が存在しません。".format(config['database']))
    else:
        print(err)
else:
    cursor = cnx.cursor()
    query = 'SELECT Name, Continent, Population, LifeExpectancy, GNP FROM Country'
    cursor.execute(query)
    for (Name, Continent, Population, LifeExpectancy, GNP) in cursor:
        print("{}, {}, {}, {}, {}".format(Name, Continent, Population, LifeExpectancy, GNP))
    cursor.close()
    cnx.close()

ということで今回はこれで落ち着きました。

動的にSQLを作らなければならなくなったときにまた考えることにします。

参考にしたサイト

MySQL :: MySQL Documentation: Other MySQL Documentation
サンプルデータベースworld database

MySQL :: MySQL Connector/Python Developer Guide
Connector/Python 2.0.4のマニュアル。

MySQL :: MySQL Connector/Python Developer Guide :: 5 Connector/Python Coding Examples
上記マニュアル中のコード例集。

MySQL :: MySQL Connector/Python Developer Guide :: 10 Connector/Python API Reference
Connector/PythonのAPIリファレンス。

Graph data from a MySQL database in Python | Modern Data
MySQLdbとplotlyを使ってMySQLからPythonでグラフを出力しています。

次の関連記事:linuxBean14.04(70)Jupyter NotebookをPyCharm4.5で使う