Zenn
🐍

MySQL環境構築とPythonからの接続方法

2025/01/10に公開
MySQL
Python
プログラミング
Python 3
tech

Pythonを使ってMySQLを操作する方法を解説します。仮想環境の作成方法やMySQLの認証方式の変更などを含めて詳しく手順を説明します。

手順概要

  1. WSLでPython仮想環境を作成
  2. 必要なパッケージのインストール
  3. MySQLサーバーのインストールと認証方式の変更
  4. PythonスクリプトでMySQLに接続し、データベース一覧を取得

ステップ1：WSL環境でPython仮想環境を作成

1.1 必要なパッケージのインストール 

sudo apt update && sudo apt upgrade -y
sudo apt install python3 python3-venv python3-pip

1.2 仮想環境の作成と有効化 

mkdir my_project && cd my_project
python3 -m venv venv_sql
source venv_sql/bin/activate

仮想環境が有効化されると、以下のようにプロンプトの前に(venv_sql)が表示されます：

(venv_sql) user@wsl:~/my_project$

1.3 pipのアップグレード 

pip install --upgrade pip

1.4 MySQL接続用ライブラリのインストール 

pip install pymysql

ステップ2：MySQLサーバーのインストールと認証方式の変更

MySQLではデフォルトでauth_socketという認証方式を使うことがあります。Pythonから接続できるように、mysql_native_passwordに変更します。

2.1 MySQLサーバーのインストール

まず、MySQLサーバーをインストールします：

sudo apt update
sudo apt install mysql-server

2.2 MySQLサービスの起動

インストール後、MySQLサービスを起動します：

sudo service mysql start

MySQLサービスのステータスを確認する場合は以下を実行します：

sudo service mysql status

2.3 MySQLシェルにログイン

MySQLシェルにログインするには以下を実行します：

sudo mysql -u root

2.2 現在の認証方式を確認 

SELECT user, host, plugin FROM mysql.user WHERE user = 'root';

plugin列がauth_socketになっている場合、以下のコマンドで認証方式を変更します。

2.3 認証方式の変更 

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';
FLUSH PRIVILEGES;
  • 'your_password'の部分にパスワードを設定してください。
  • FLUSH PRIVILEGES;で設定を反映します。

2.4 認証方式の確認

再度以下を実行して、pluginがmysql_native_passwordに変更されているか確認します。

SELECT user, host, plugin FROM mysql.user WHERE user = 'root';

MySQLシェルを終了します：

exit;

ステップ3：Pythonスクリプトで接続テスト

3.1 データベースの作成

MySQLシェルに再ログインして、新しいデータベースを作成します。

sudo mysql -u root -p

CREATE DATABASE test_db;
exit;

3.2 Pythonスクリプトの作成

仮想環境内でPythonスクリプトを作成します。

nano sql-test.py

以下の内容をsql-test.pyに記述します。

import pymysql

connection = pymysql.connect(
    host='localhost',
    user='root',
    password='',  # パスワードを設定していない場合は空文字を使用  # 設定したrootパスワードを入力
    database='test_db'
)

try:
    with connection.cursor() as cursor:
        cursor.execute("SHOW DATABASES;")
        databases = cursor.fetchall()
        print("Databases:", databases)
finally:
    connection.close()

3.3 スクリプトの実行 

python sql-test.py

以下のようにデータベース一覧が出力されれば成功です。

Databases: (("information_schema",), ("mysql",), ("performance_schema",), ("sys",), ("test_db",))

出力結果の解説

  • information_schema：データベースに関するメタ情報（テーブル構造や権限など）を格納したシステムデータベース。
  • mysql：ユーザー情報や権限設定などMySQLの内部情報を管理するためのデータベース。
  • performance_schema：クエリの実行時間やリソース使用状況を監視するためのデータベース。
  • sys：管理を簡単にするための便利なビューが格納されたシステムデータベース。
  • test_db：ユーザーが作成したテスト用のデータベース。

出力されているデータベース一覧は、MySQLサーバー上に存在しているデータベースのすべてを示しています。最後にtest_dbが表示されていることから、Pythonスクリプトを通じて正しく接続できていることがわかります。

ステップ4：セキュリティのために専用ユーザーを作成する方法（オプション）

  1. 新しいユーザーの作成
CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'password123';
  1. 権限の付与
GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'localhost';
FLUSH PRIVILEGES;
  1. Pythonスクリプトの接続情報を変更
    スクリプト内でユーザー情報を以下に変更します。
user='test_user',
password='password123',

まとめ

以上の手順で、WSL環境内でPython仮想環境を構築し、MySQLへの接続と操作ができるようになります。専用ユーザーを使うことで、セキュリティを強化することも可能です。

トラブルシューティングやエラー発生時は、mysql.logやpymysqlのエラーメッセージを確認し、適切な対処を行いましょう。

『PythonではじめるRaspberry Pi入門』を執筆しています

これからPythonやRaspberry Piを使ってアプリケーションを開発してみたいという方向けの入門書です。Pythonプログラムから実際に動くものを作ってみたい方はぜひ手に取ってみてください。

https://zenn.dev/murasanlab/books/c93b5ccaf332a9

Discussion