【Python】MySQLに接続してデータベース操作する方法（mysqlclient）

2021.10.27 / 2021.10.27

本記事ではPythonでDBMS（データベース管理システム）の一つ、MySQLに接続してデータベース操作する方法について解説していきます。

MySQLは最も普及しているDBMSの1つで、無料で公開されています。PHP+MｙSQLやJava+MySQLという組み合わせで、多くのWebシステムが構築されます。

データベースについて詳しく知りたい方は以下記事をご参照ください。

ここではPythonのmysqlclientパッケージを使って、MySQLに接続してデータベース操作します。

MySQLはサーバーにインストールして利用するアプリケーションです。そのためすでにMySQLが稼働していることを想定して解説を進めます。

まだMySQLを稼働させてない方は、以下記事を参考にしてパソコンにMySQLをインストールしてデータベースを稼働させてください。

MySQLはローカルでも外部サーバーで起動していても、問題なくPythonで接続できます。

mysqlclient

mysqlclientとは

Pythonでmysqlclientを利用すると簡単にMySQLへ接続・操作することができます。

Python2系で利用されていたMySQLdb1をPython3系にフォークしたものがmysqlclientです。2021年10月現在ではmysqlclient2.0.3が最新バージョンです。

IT用語の確認

フォークとは、ある時点までの開発したプログラムを元に新たに開発プロジェクトを立ち上げること

PyPIは以下のリンクから確認できます。

PyPI - mysqlclient

mysqlclientを利用するために、pipでインストールする必要があります。

pip install mysqlclient

Pythonスクリプトで利用するために、次のようにMySQLdbをインポートします。

import MySQLdb

注意点

pipでインストールするのはmysqlclientですが、インポートではMySQLdbを指定します

mysqlclientによるMySQLへの接続・操作の流れ

mysqlclientによるMySQLへの接続や操作はそのほかのデータベース操作（Oracle、SQLite3など）と同じです。

基本的には以下の流れに沿ってデータベース操作を行います。

データベースへの接続
カーソルの作成
SQLによるデータの取得及び更新
データ更新の場合はトランザクションを確定
カーソルを閉じる
データベースへの接続を閉じる

それぞれの方法などに関しては次項から詳しく説明していきます。

MySQLへの接続・操作方法

データベースへの接続：connect()

最初にデータベースのMySQLに接続する必要があります。
MySQLへの接続にはconnect()メソッドを使用します。

構文

MySQLdb.connect(host, port, db, user, passwd)

引数	説明
host	MySQLを持つ接続先サーバー名（IPアドレス）
port	MySQLのポート番号（デフォルト3306）
db	接続するデータベース名
user	ユーザー名
passwd	パスワード

ポート番号はデフォルトの3306から変更していなければ省略してください。
接続先サーバーがローカル（自端末）の場合はlocalhostを指定します。

connect()メソッドによりConnectionオブジェクトが生成されます。Connectionオブジェクトは変数に代入してその後も利用します。

# connect()メソッドの使用例
conn = mydb.connect(user='user1', passwd="user1", host='192.168.1.54', db='office54')

Connectionオブジェクトには以下のメソッドが存在します。

メソッド名	説明
commit()	トランザクションを確定させる
rollback()	トランザクションをロールバック（キャンセル）する
cursor()	カーソルを作成する

カーソルの作成：cursor()

MySQLデータベースへの接続ができたら、次はカーソル（cursor）を作成します。

IT用語の確認

カーソルとは、selectで抽出したデータを１行ずつ取り出せる仕組み

カーソルの作成にはcursor()メソッドを使用します。

構文

Connectionオブジェクト.cursor()

生成されたCursorオブジェクトを利用して、クエリの実行を行います。

cur = conn.cursor()

SQLによるデータの取得及び更新：execute()

SQLを実行するためには、Cursorオブジェクトのexecute()メソッドを利用します。

構文

Cursorオブジェクト.execute(sql)

引数sqlには実行したいSQLを指定します。
例えばuserテーブルからname列を取得したい場合は次のようにします。

cur.execute("SELECT name FROM user")

execute()メソッドにSQLを指定して実行することで、データの取得や更新ができます。

データを取得（SELECT文）した場合は、以下のメソッドを使用することで結果を実際に取得できます。

メソッド名	説明
fetchone()	タプルでデータを１行返す
fetchall()	リストでデータを返す

例えばfetchall()で取得したデータは以下のようにfor文を使ってデータを一つずつ取り出せます。

for result in cur.fetchall():
    print(result)

データ更新の場合はトランザクションを確定：commit()

データベース内のデータを更新した場合は、トランザクションを確定させる必要があります。トランザクション自体は最初のSQLが実行された時点で開始します。

トランザクションの確定には、Connectionオブジェクトのcommit()メソッドを使用します。トランザクションを無効にするには、rollback()メソッドを使用します。

conn.commit()

カーソルを閉じる：close()

データベース操作が終わったら、まずはカーソルを閉じます。
カーソルを閉じるにはCursorオブジェクトのclose()メソッドを使用します。

cur.close()

データベースへの接続を閉じる：close()

カーソルを閉じたら最後にデータベースの接続も確実に閉じます。
データベースへの接続を閉じるにはConnectionオブジェクトのclose()メソッドを使用します。

conn.close()

以上の流れでMySQLに接続とデータベース操作が可能となります。

MySQLへの接続ができない場合

コネクション確立時のエラー

connect()メソッドを実行時に次のエラーが発生することがあります。

エラー

OperationalError: (1045, "Access denied for user ~)

もしconnect()メソッドで指定したhostやuser、passwordに問題がなければユーザーのアクセス権限に問題がある可能性があります。

確認するためにサーバー側で以下のコマンドを実行してください。

# mysql -u root -p password

mysql> SELECT user, host FROM mysql.user;

+---------+-----------+
| User    | Host      |
+---------+-----------+
| user2   | %         |
| user1   | localhost |
| root    | localhost |
+---------+-----------+

ここで対象のユーザーがlocalhostのみ接続可能となっていた場合は、外部からの接続が不可ということです。

そこで次のように、アクセスできるIPアドレスの範囲を設定しましょう。

mysql> GRANT all on データベース名.* TO ユーザー名@’IPアドレスの範囲’ identified by ‘パスワード’;

新たにuser1を作成し、データベースuserへの192.168.*.*からのアクセスを許容させる場合は次のコマンドを実行します。

CREATE USER user1@'192.168.%' IDENTIFIED BY 'user1';
GRANT all on user.* TO user1@'192.168.%' identified by 'user1';

XAMPP：MySQLAdmin

XAMPPでは外部からのphpMyAdminへのアクセスが制限されています。

外部からアクセスすると次の画面が表示されてアクセスができません。

この場合は「httpd-xampp.conf」の設定を変更することでアクセスできるようになります。

ファイルはXAMPPをインストールした場所によって変わります。ここではCドライブ直下にインストールした場合のパスを記します。

C:/xampp/apache/conf/extra/httpd-xampp.conf

上記のファイルを開き、94行目の「Require local」をコメントアウトして、「Require all granted」を追加します。

<Directory "C:/xampp/phpMyAdmin">
    AllowOverride AuthConfig
    #Require local
    Require all granted
    ErrorDocument 403 /error/XAMPP_FORBIDDEN.html.var
</Directory>