【 Python 】mysql-connector-pythonでMySQLにアクセスしよう！

2024年12月30日

当ページのリンクには広告が含まれています。

今回はPythonからMySQLデータベースに接続し、データを更新したり取得する方法について解説します。

MySQL は無料で使える本格的なデータベースであり、商用利用も盛んにされていますので、仕事で必要に迫られるケースも多いと思います。

少し前までは mysqlclient が有名でしたが、Windowsではサポートされなくなっており、2024年9月においては
mysql-connector-python がよく使われています。

そこで、本記事では Windows上で mysql-connector-python を使って PythonからMySQLに接続する方法を、サンプルソース付きで解説致します。

「【コピペでOK】９割の機能を網羅！PytonからMySQLを扱うクラスを作ってみました。」では、本記事の内容をもとに、MySQLで便利な自作クラスを紹介していますので、興味のある方は併せてご覧ください。

mysql-connector-python インストール

コマンドプロンプトから、pipコマンドで mysqlclientをインストールします。

pip install mysql-connector-python

mysql-connector-pythonの概要

SQLやDDLを実行するための手順は、SQLite、Oracle、PostgreSQL など多くのデータベースと同様です。

尚、インストールは mysqlclient ですが、 import は MySQLdb なのでご注意ください。

import MySQLdb

mysql-connector-pythonの使い方

MySQL への接続

MySQLへの接続には、mysql.connector.connect メソッドに接続に必要な接続情報を引数として渡します。
また、処理が終れば close() メソッドを使って接続を切断しておきます。

# モジュールのインポート
import mysql.connector

# MySQLへの接続
conn = mysql.connector.connect(host='dbserver',user='mining', database='mining',password='mining')

# ～ここに処理を書く～

# MySQLへの接続を切断
conn.close()

引数名	内容	補足説明
host	サーバー名	IPアドレス（例：192.168.0,110）又はホスト名（例：'hoge'）
database	データベース名
user	ユーザー名
password	パスワード
port	ポート番号	省略時は3306が設定される

他のDatabaseでは、接続文字列（接続情報を繋げて１つの文字列にしたもの）が使えるものも多いですが、mysql-connector-pythonは接続文字列をサポートしていません。

テーブルの作成と削除

テーブルの作成や削除、インデックスの追加、カラム名の変更など、いわゆる定義系のSQL（DDL＝Data Definition Language）は、execute メソッドを使います。

execute メソッドを使うには、DBに接続後にカーソルを取得する必要があるため、結果的に下記の様になります。

# MySQLへの接続
conn = mysql.connector.connect(host='dbserver',user='mining', database='mining',password='mining')

# カーソルを取得
cur = conn.cursor()

#任意のDDL(Createなど）を実行
cur.execute('～任意のDDL文～')

# カーソルとコネクションをクローズ（解放）
cur.close()
conn.close()

例えば、新しいテーブルを作成する場合は、次のように記述します。

# モジュールのインポート
import mysql.connector

# MySQLへの接続
conn = mysql.connector.connect(host='dbserver',user='mining', database='mining',password='mining')

# create 文の作成
sql_statement = """
    CREATE TABLE hoge (
        id VARCHAR(5) NOT NULL,
        name VARCHAR(20),
        age INT,
        create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id)
    );
"""

# カーソルを取得し、Select文を実行後、結果を取得する
cur = conn.cursor()
cur.execute(create_statement )

# カーソルとコネクションをクローズ（解放）
cur.close()
conn.close()

テーブルを削除する場合は、sql_statement を次の内容に変更します。

sql_statement = """
    DROP TABLE IF EXISTS hoge;
"""

データの挿入と更新

データを挿入する場合も基本的にはDDLと同じく、execute メソッドを使います。
異なるのは、データの更新を確定させるために、commitメソッドを呼ぶ必要があることです。これをしないと、いくらテーブルを更新しても結果が反映されません。

# モジュールのインポート
import mysql.connector

# MySQLへの接続
conn = mysql.connector.connect(host='dbserver',user='mining', database='mining',password='mining')
cur = conn.cursor()

# インサート文の実行
cur.execute("insert into hoge(id,name,age) values('A1001','山田',30)")
cur.execute("insert into hoge(id,name,age) values('A1002','鈴木',45)")
cur.execute("insert into hoge(id,name,age) values('A1003','斉藤',29)")

# 更新内容の確定（コミット）
conn.commit()

# カーソルとコネクションをクローズ（解放）
cur.close()
conn.close()

executeの引数を update文に換えると、指定した条件のデータを更新（アップデート）できます。

cur.execute("update hoge set age=50 where id='A1001'")

検索(Select）と結果の取得

データを検索する場合も同様、execute メソッドを使います。
また、fetchall メソッドを使って検索結果を受け取ります。

# モジュールのインポート
mport mysql.connector

# MySQLへの接続
conn = mysql.connector.connect(host='dbserver',user='mining', database='mining',password='mining')

# カーソルを取得し、Select文を実行後、結果を取得する
cur = conn.cursor()
cur.execute('select * from hoge')
res = cur.fetchall()

# 取得した結果をプリント
print(res)

# カーソルとコネクションをクローズ（解放）
cur.close()
conn.close()

fetchall は検索結果を全てメモリに読み込むため、大量のデータを受け取る場合はメモリ不足のエラーが発生します。
そのため、検索結果を１件づつ読み出す fetchoneメソッドや、指定した件数毎に結果を受け取れる fetchmanyメソッドが用意されています。

機能	メソッド	備考
全件取得	fetchall()	リスト形式で全件を返す。
1件づつ取得	fetchone()	データがあれば1件をタプル形式で返す。データが無くなれば None を返す。
指定件数づつ取得	fetchmany(件数)	データがあれば指定件数づつタプル形式で返す。データが無くなれば None を返す。

以下は、fetchmany を使ったサンプルです。２件づつデータを読み取りながらループしていますが、書き方が２通りあるため、参考に載せておきます。

# モジュールのインポート
import mysql.connector

# MySQLへの接続
conn = mysql.connector.connect(
    host='dbserver',
    user='mining', 
    database='mining',
    password='mining')

# カーソルを取得し、Select文を実行後、結果を取得する
cur = conn.cursor()
cur.execute('select * from hoge')

res = cur.fetchmany(2)
while res:
    print(res)
    res = cur.fetchmany(2)
    
# カーソルとコネクションをクローズ（解放）
cur.close()
conn.close()

# モジュールのインポート
import mysql.connector

# MySQLへの接続
conn = mysql.connector.connect(
    host='dbserver',
    user='mining', 
    database='mining',
    password='mining')

# カーソルを取得し、Select文を実行後、結果を取得する
cur = conn.cursor()
cur.execute('select * from hoge')

while True:
    res = cur.fetchmany(2)
    if res == []:
        break 
    print(res)
    
# カーソルとコネクションをクローズ（解放）
cur.close()
conn.close()