PyMySQLを使って、PythonからMySQLに接続してみる

これは、なにをしたくて書いたもの？

Pythonでデータベースアクセスをあまりやってきていないので、情報収集がてら軽く練習にということで。

PythonからMySQLにアクセスする

PythonからMySQLにアクセスするには、まずはドライバーを探すことになります。

以前、Pythonにおけるデータベースアクセスのための標準APIであるDB API（PEP 249）について簡単に調べました。

PythonのDB API 2.0（PEP 249）って？ - CLOVER🍀

この時に、MySQLのドライバーがWikiにリストアップされているのも見ています。

MySQL - Python Wiki

ですが、数が多いですね…。

調べてみると、実質的な選択肢は次の3つのようです。

• mysqlclient
• MySQL Connector/Python
• PyMySQL

ちなみに以前こういうエントリーを書いたことがあって、この時にはなんとなくMySQL Connector/Pythonを使っています。

MySQL 8.0のCharset utf8mb4での日本語環境で使うCollationで文字比較をしてみる - CLOVER🍀

それぞれ違いを見てみます。

mysqlclient

mysqlclientのGitHubリポジトリーはこちら。

GitHub - PyMySQL/mysqlclient: MySQL/MariaDB connector for Python

ドキュメントはこちら。

Welcome to MySQLdb’s documentation! — mysqlclient 1.2.4b4 documentation

mysqlclientは、MySQLdb1というプロジェクトをフォークしたもののようです。

インストール手順を見る限り、MySQLのクライアントライブラリー（libmysqlclient）を使うドライバーのようです。

MySQL Connector/Python

MySQL Connector/PythonのGitHubリポジトリーはこちら。

https://github.com/mysql/mysql-connector-python

ドキュメントはこちら。

MySQL :: MySQL Connector/Python Developer Guide

MySQL Connector/Pythonは、MySQL自体が提供するドライバーです。

DB API 2.0以外にも、X DevAPIという専用のAPIを使うことができます。

以前こちらを使ったエントリーを書いたことがありますが、たぶんMySQLが提供しているドライバーだからという理由で他を調べずに
使った気がしますね…。

インストールすると、ネイティブライブラリーもついてきます。

PyMySQL

最後はPyMySQLです。GitHubリポジトリーはこちら。

GitHub - PyMySQL/PyMySQL: MySQL client library for Python

実はmysqlclientとGitHub organizationが同じです。

ドキュメントはこちら。

PyMySQL documentation — PyMySQL 0.7.2 documentation

こちらはPure Pythonのドライバーのようです。

今回紹介している3つのドライバーで、GitHubのスター数が1番多いのがこのPyMySQLになります。

MySQL Connector/Pythonは前に1度試しているので、今回はPyMySQLを使ってみることにします。

環境

今回の環境はこちら。

$ python3 --version
Python 3.10.12


$ pip3 --version
pip 22.0.2 from /usr/lib/python3/dist-packages/pip (python 3.10)

MySQLは8.4.3を使い、172.17.0.2で動作しているものとします。

 MySQL  localhost:3306 ssl  practice  SQL > select version();
+-----------+
| version() |
+-----------+
| 8.4.3     |
+-----------+
1 row in set (0.0003 sec)

準備

まずはMySQLにテーブルを作成します。お題は書籍にしましょう。

create table book(
  isbn varchar(14),
  title varchar(100),
  price int,
  primary key(isbn)
);

次に、PyMySQLをインストール。今のMySQLのデフォルトの認証方式はcaching_sha2_passwordなので、PyMySQL[rsa]をインストールします。

$ pip3 install PyMySQL[rsa]

PyMySQL / Installation

動作確認はテストコードで行うことにするのでpytest、型チェックもしておきたいのでMypyをインストールしておきます。

$ pip3 install pytest mypy

PyMySQLの型情報もインストール。

$ pip3 install types-PyMySQL

インストールされたライブラリーの一覧。

$ pip3 list
Package           Version
----------------- --------------
cffi              1.17.1
cryptography      43.0.3
exceptiongroup    1.2.2
iniconfig         2.0.0
mypy              1.13.0
mypy-extensions   1.0.0
packaging         24.1
pip               22.0.2
pluggy            1.5.0
pycparser         2.22
PyMySQL           1.1.1
pytest            8.3.3
setuptools        59.6.0
tomli             2.0.2
types-PyMySQL     1.1.0.20241103
typing_extensions 4.12.2

現時点のPyMySQLのバージョンは、1.1.1ですね。

PyMySQLを使ってみる

それでは、PyMySQLを使ってみましょう。

ドキュメントのコード例を見ると…実はこれくらいしかありません。

Examples — PyMySQL 0.7.2 documentation

あとはConnectionとCursorのリファレンスになります。

Connection Object — PyMySQL 0.7.2 documentation

Cursor Objects — PyMySQL 0.7.2 documentation

ちなみにページのタイトルを見ると、バージョンが0.7.2になっていてちょっと驚くのですが。

GitHubリポジトリー側のdocsã‚„README.mdの更新履歴を見ていると、ドキュメントとしてこれ以上の情報はなさそうです。

pymysql.connections.Connectionの説明はおよそ見ればわかる気はするのですが、Cursorについてはデフォルトがどういう動きをするのか
よくわからないので合わせて確認してみたいと思います。なお、ExamplesではDictCursorを使用しています。

とりあえず進めてみましょう。

テストコードのimportにはこちらのみを記述。

import pymysql

最低限の情報でMySQLに接続してみます。

def test_connect_mysql() -> None:
    with pymysql.connect(
            host="172.17.0.2",
            port=3306,
            user="kazuhira",
            password="password",
            database="practice",
    ) as connection:
        with connection.cursor() as cursor:
            cursor.execute("select version() as version")

            result = cursor.fetchone()
            assert result == ("8.4.3", )

接続できましたね。Cursor#fetchoneの結果はタプルで返ってきました。

というか、SQLの実行も結果の取得もCursorを使って行うんですね。

cursorclassã‚’DictCursorにしてみましょう。

def test_connect_mysql_dictcursor() -> None:
    with pymysql.connect(
            host="172.17.0.2",
            port=3306,
            user="kazuhira",
            password="password",
            database="practice",
            cursorclass=pymysql.cursors.DictCursor
    ) as connection:
        with connection.cursor() as cursor:
            cursor.execute("select version() as version")

            result = cursor.fetchone()
            assert result == { "version": "8.4.3", }

結果が辞書になりました。こちらの方が使いやすそうですね。以後はDictCursorを使うことにします。

次に、データを登録したり取得したりしてみます。

プレースホルダーを使った例がドキュメントにまったくなかったのですが、DB API 2.0（PEP 249）のドキュメントを見ると指定方法が
書かれていたのと

PEP 249 – Python Database API Specification v2.0 / Module Interface / paramstyle

あとはテストコードを参考にしました。

https://github.com/PyMySQL/PyMySQL/blob/v1.1.1/pymysql/tests/test_basic.py

テストコードもそうですが、実際に使ってみた感じだとプレースホルダーのスタイルとしてはformatとpyformatが使えるみたいです。

insertやselect、あとはデータの削除にtruncateなど。

def test_insert_select_with_placeholder() -> None:
    with pymysql.connect(
            host="172.17.0.2",
            port=3306,
            user="kazuhira",
            password="password",
            database="practice",
            cursorclass=pymysql.cursors.DictCursor
    ) as connection:
        with connection.cursor() as cursor:
            # format, tuple
            cursor.execute(
                "insert into book(isbn, title, price) values(%s, %s, %s)",
                ("978-4873119328", "入門 Python 3 第2版", 4180, )
            )

            cursor.execute("select isbn, title, price from book where isbn = %s", ("978-4873119328", ))
            assert cursor.fetchone() == { "isbn": "978-4873119328", "title": "入門 Python 3 第2版", "price": 4180 }

            # pyformat
            cursor.execute(
                "insert into book(isbn, title, price) values(%(isbn)s, %(title)s, %(price)s)",
                { "isbn": "978-4297111113", "title": "Python実践入門 ── 言語の力を引き出し、開発効率を高める (WEB+DB PRESS plusシリーズ)", "price": 2980 }
            )

            cursor.execute("select isbn, title, price from book where isbn = %(isbn)s", { "isbn": "978-4297111113" })
            assert cursor.fetchone() == { "isbn": "978-4297111113", "title": "Python実践入門 ── 言語の力を引き出し、開発効率を高める (WEB+DB PRESS plusシリーズ)", "price": 2980 }

            # format, list
            cursor.execute(
                "insert into book(isbn, title, price) values(%s, %s, %s)",
                ["978-4048930840", "エキスパートPythonプログラミング 改訂3版", 4180]
            )

            cursor.execute("select isbn, title, price from book where isbn = %s", ["978-4048930840"])
            assert cursor.fetchone() == { "isbn": "978-4048930840", "title": "エキスパートPythonプログラミング 改訂3版", "price": 4180 }
            
            cursor.execute("truncate table book")

            cursor.execute("select count(*) as count from book")
            assert cursor.fetchone() == { "count": 0 }

最後はトランザクションです。Connectionに対してコミットやロールバックを指示するようですね。

def test_transaction() -> None:
    with pymysql.connect(
            host="172.17.0.2",
            port=3306,
            user="kazuhira",
            password="password",
            database="practice",
            cursorclass=pymysql.cursors.DictCursor
    ) as connection:
        assert connection.get_autocommit() == False

        with connection.cursor() as cursor:
            # commit
            cursor.executemany(
                "insert into book(isbn, title, price) values(%(isbn)s, %(title)s, %(price)s)",
                [
                    { "isbn": "978-4873119328", "title": "入門 Python 3 第2版", "price": 4180 },
                    { "isbn": "978-4297111113", "title": "Python実践入門 ── 言語の力を引き出し、開発効率を高める (WEB+DB PRESS plusシリーズ)", "price": 2980 }
                ]
            )

            cursor.execute("select count(*) as count from book")
            assert cursor.fetchone() == { "count": 2 }
            
            connection.commit()

            cursor.execute("select count(*) as count from book")
            assert cursor.fetchone() == { "count": 2 }

            # rollback
            cursor.execute(
                "insert into book(isbn, title, price) values(%(isbn)s, %(title)s, %(price)s)",
                { "isbn": "978-4048930840", "title": "エキスパートPythonプログラミング 改訂3版", "price": 4180 }
            )

            cursor.execute("select count(*) as count from book")
            assert cursor.fetchone() == { "count": 3 }

            connection.rollback()

            cursor.execute("select count(*) as count from book")
            assert cursor.fetchone() == { "count": 2 }

            cursor.execute("truncate table book")

            cursor.execute("select count(*) as count from book")
            assert cursor.fetchone() == { "count": 0 }

こんなところでしょうか。

おわりに

PyMySQLを使って、PythonからMySQLに接続してみました。

PyMySQLが人気のように見えるのですが、ドキュメントが全然ないので最初はちょっと困りました。

やっぱりORMを使うものなのでしょうか？