本ページはプロモーションが含まれています

[Python] MySQLにアクセスしてデータベース操作を行う

2018.12.07
MySQLPythonデータソースと書式モジュール
mySQL

MySQL Connector/Pythonは、PythonからMySQLへ接続してDatabase操作を行うライブラリです。PEP 249 で記述されている DB-API 2.0 に準拠しており、MySQL公開元のORACLEからリリースされています。今回はこのライブラリを使って、Pythonを使ったDatabase操作方法についてまとめます。

スポンサーリンク

確認した環境

  • OS：Ubuntu16.04LTS
  • python 3.7.0
  • MySQL Server version: 8.0.13 MySQL Community Server – GPL

# MySQLのインストール方法についてはこちらの記事をご参照ください

MySQLをインストールする
MySQLは、オープンソースで公開されているリレーショナルデータベース管理システム (RDBMS) の一つです。本記事では、MySQLのインストール方法について備忘録も兼ねてまとめます。
hibiki-press.tech
2018.11.09

MySQL Connector/Python インストール方法

3rdパーティ製のライブラリなので、自分の環境に入っていない場合はインストールします。
通常通りpipを使って以下のようにインストールできます。

shell> pip install mysql-connector-python

anaconda環境の場合は、以下でインストールできます。
今回は、version 8.0.12を使います。

conda install mysql-connector-python

本記事で扱うデータベース

本記事では、以下のデータベース、テーブルの作成、レコードの追加・取得を行います。

データベース名：sample_db
テーブル名： sample_tb

テーブルの定義
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| userid   | int(5)       | NO   | PRI | NULL    | auto_increment |
| username | varchar(255) | NO   | UNI | NULL    |                |
| email    | varchar(40)  | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

MySQLサーバへの接続

まずmysql.connectorモジュールをインポートします。

import mysql.connector

MySQLへ接続するには、connect()メソッドを用いてconnectionオブジェクトを作ります。

下記のように、接続するMySQlのアカウント情報をパラメータに設定し引数として渡します。ここで、USERNAMEおよびPASSWORDは、MySQLにログインするためのユーザ名とパスワードです。

# ログインパラメータの設定
config = {
        'user': USERNAME,
        'password': PASSWORD,
        'host': 'localhost',
        }

# MySQLへ接続
cnx = mysql.connector.connect(**config)

connect()メソッドの引数の詳細は公式リファレンスのこちらをご参照ください。

接続が失敗した場合は、 errors.Error Exceptionが返ります。これを利用してtry〜except文でエラーが発生した場合にもプログラムが止まらないように処理を入れておくと良いと思います。以下に例を示します。

import mysql.connector
from mysql.connector import errorcode

config = {
        'user': USERNAME,
        'password': PASSWORD,
        'host': 'localhost'
        }
		
# try〜except文でエラー対処
try:
    cnx = mysql.connector.connect(**config)

# ER_ACCESS_DENIED_ERRORは、DBへのアクセスが拒否された場合のエラーコード
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print('Cannnot connect database.')
    else:
        print(err)
else:
    cnx.close()

尚、エラーメッセージ詳細は公式リファレンスのこちらに詳細に記載があります。

SQL statementの実行

SQL文は大まかに以下の手順で実行します。

  • cursorオブジェクトの生成
  • execute()メソッドにSQL文を渡して実行

cursorオブジェクトの生成

まず、cursor()メソッドを用いてMySQLCursor() objectを生成します。

# cursorオブジェクトの生成
cursor = cnx.cursor()

execute()メソッドによるSQL文の実行

excecute()メソッドへのSQL文の渡し方にはいくつか方法がありますので、例を挙げて見ていきたいと思います。

(1) SQL文を自分で組み立てる

SQL文を自分で組み立てて、固定の文字列として引数に設定することも可能です。
以下は、データベースを生成する例です（CREATE DATABASE）。
データベース名が固定されている場合はこれでも良いかもしれません。

cursor.execute('CREATE DATABASE sample_db')

ここで、データベース名やテーブル名を設定する上で注意点です。
データベース名やテーブル名（識別子：identifierと呼びます）はMySQL内部処理でUnicodeに変換されますが、アポストロフィー（＇）等の特殊文字については識別子を引用符（backtick (`)）で囲む必要があります。例えば、データベース「a’b」を作成する場合は、以下のように記載します。

# 引用符で囲みます。
CREATE DATABASE `a'b`;

# 以下は正しく動作しません。
CREATE DATABASE a'b;

# この場合、以下のようなエラーが発生します。
>>> cursor.execute("CREATE DATABASE a'b")
Traceback (most recent call last):
（中略）
_mysql_connector.MySQLInterfaceError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''b' at line 1

During handling of the above exception, another exception occurred:
（中略）
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''b' at line 1

詳細は、公式リファレンスのこちらを参照ください。

(2) format()形式で記載

例えばデータベース名やテーブル名を場面ごとに切り替えたい場合があるかもしれません。そのような場合は、format()記法を使って記述することも可能です。

以下の例は、使用するデータベースを選択して（USE database）、そこにテーブルを作成する例です。(CREATE TABLE)

# データベース名を定義
DB_NAME = 'sample_db'

# テーブル名の定義
TBL_NAME = 'sample_tbl'

# データベース（DB_NAME）の選択 
cursor.execute('USE {}'.format(DB_NAME))

# テーブル作成のSQL文
SAMPLE_TABLE = ("CREATE TABLE {} ("
            "userid int(5) PRIMARY KEY AUTO_INCREMENT,"
            "username varchar(255) NOT NULL UNIQUE,"
            "email varchar(40)"
            ")"
            )

# SQL文の実行
cursor.execute(SAMPLE_TABLE.format(TBL_NAME))

(3) タプルまたは辞書型でパラメータを設定する

パラメータをタプルや辞書形式で表して、引数に設定する方法があります。
以下は、レコードを追加する処理です。(INSERT INTO *)

● タプルでパラメータを設定する

# レコード追加のSQL文
add_userdata =("INSERT INTO {} "
               "(userid, username, email) "
               "VALUES (%s, %s, %s)"
               ).format(TBL_NAME)
			   
# パラメータの設定
userdata = (10, 'Alice', 'alice@example.com')

# SQL文の実行
cursor.execute(add_userdata, userdata)

# commitを忘れずに
cnx.commit()

● 辞書でパラメータを設定

# レコード追加のSQL文
add_userdata2 =("INSERT INTO {} "
               "(userid, username, email) "
               "VALUES (%(userid)s, %(username)s, %(email)s)"
               ).format(TBL_NAME)

# パラメータの設定
userdata2 = {
            'userid': 12,
            'username': 'Jim',
            'email': 'jim@example.com',
            }

# SQL文の実行
cursor.execute(add_userdata2, userdata2)

# commitを忘れずに
cnx.commit()

尚、レコード追加や更新時は必ずcommit()メソッドを実行すること。これをしないとデータベースに反映されません。筆者はこれを忘れてかなり悩みました。

また、レコードの読み出し（SELECT column,.. FROM table_name）も同様に実行できます。

ここで、execute()メソッドに設定するパラメータにタプルを使う場合、タプルは要素が1つの場合は要素の後にカンマ(,)を付けるのを忘れないようにしましょう。

# レコード読み出しのSQL文
query = ("SELECT userid, username, email FROM {} "
         "WHERE username = %s").format(TBL_NAME)

# SQL文の実行
cursor.execute(query, ('Jim', ))

# 読みだしたレコードを表示
for (userid, username, email) in cursor:
    print("userid:{}, username:{}, email:{}".format(userid, username, email))

# 出力結果
userid:12, username:Jim, email:jim@example.com

読み出されたレコードはcursorオブジェクトに格納されます。上記例ではこれをイテレーターとして用いています。

まとめ

pythonからMySQLに接続して、データベース操作をする方法についてまとめました。

参考書籍

書籍でもう少し詳しく学びたい場合はこちらもどうぞ。筆者もかなり参考にさせてもらっています!

シェアする
ひびきをフォローする
ひびき
スポンサーリンク