【Ubuntu + Python + MySQL】おぼえがき（接続、トランザクション処理、クエリ発行）

いろんな言語を行ったりきたりしていると忘れるので、PythonからMySQLに接続してクエリを発行するまでのカンニングペーパー📝

環境
インストールしておくもの
サンプルコード
余談
参考

環境

一応、こんな感じ。

項目	バージョン
OS	Ubuntu 18.04
Python	3.6.8
MySQL	8.0.16

インストールしておくもの

Pythonは標準でMySQLをサポートしているわけじゃないので、ドライバを別途インストールする必要あり。
Ubuntuなのでaptで。

$ sudo apt install libmysqlclient-dev

また、モジュールのmysqlclientも必要。こちらはpipで。

$ pip install mysqlclient

サンプルコード

うだうだと説明するのは後回し！
接続〜トランザクション〜クエリ実行を含むサンプルコード。
個別の説明は後述でピックアップ。

-- サンプル用のテーブル作成
DROP TABLE IF EXISTS member;
CREATE TABLE member (
    id INT AUTO_INCREMENT,
    member_number INT UNIQUE,
    name TEXT NOT NULL,
    sex TEXT,
    age INT NOT NULL,
    birth_day DATE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import MySQLdb
import textwrap
import datetime

# MySQLに接続
db_config = {
    'host'    : 'localhost',
    'port'    : 3306,
    'user'    : 'user',
    'passwd'  : 'password',
    'db'      : 'db_name',
    'charset' : 'utf8'
}

conn   = MySQLdb.connect(**db_config)
cursor = conn.cursor(MySQLdb.cursors.DictCursor)

try:
    # 挿入
    query = textwrap.dedent("""\
        INSERT INTO member (
            member_number,
            name,
            sex,
            age,
            birth_day
        ) VALUES (
            %(member_number)s,
            %(name)s,
            %(sex)s,
            %(age)s,
            %(birth_day)s
        )
        """)

    ## 1件挿入
    data = {
        'member_number' : 101,
        'name'          : '田中 博士',
        'sex'           : '男',
        'age'           : 23,
        'birth_day'     : '1995-10-01'
    }

    cursor.execute(query, data)
    conn.commit()

    ## 複数件挿入
    datas = [
        {
            'member_number' : 102,
            'name'          : '佐藤 武',
            'sex'           : '男',
            'age'           : 22,
            'birth_day'     : '1996-02-23'
        },
        {
            'member_number' : 103,
            'name'          : '清水 真那',
            'sex'           : '女',
            'age'           : 32,
            'birth_day'     : '1986-12-30'
        },
        {
            'member_number' : 104,
            'name'          : '田中 アキラ',
            'sex'           : None,
            'age'           : 16,
            'birth_day'     : '1986-12-30'
        }

    ]

    cursor.executemany(query, datas)
    conn.commit()

    # 検索
    ## 全件検索
    cursor.execute("SELECT * FROM member")

    rows = cursor.fetchall()

    print("全件検索結果")
    for row in rows:
        print(row)
        #=> {'id': 1, 'member_number': 101, 'name': '田中 博士', 'sex': '男', 'age': 23, 'birth_day': datetime.date(1995, 10, 1), 'created_at': datetime.datetime(2019, 8, 17, 0, 14, 13), 'updated_at': datetime.datetime(2019, 8, 17, 0, 14, 13)}
        #=> ...

    ## 絞り込み検索（IN句のテスト）
    query = textwrap.dedent("""\
        SELECT
            *
        FROM
            member
        WHERE
            1 = 1
        AND member_number in (%(member_number)s)
    """)

    datas = {
        'member_number_1' : 102,
        'member_number_2' : 103
    }

    replace_strings = []
    for key, value in datas.items():
        replace_strings.append('%(' + key + ')s')

    query = query % {'member_number' : ', '.join(replace_strings)}
    cursor.execute(query, datas)

    rows = cursor.fetchall()

    print("絞り込み検索結果")
    for row in rows:
        print(row)
        #=> {'id': 2, 'member_number': 102, 'name': '佐藤 武', 'sex': '男', 'age': 22, 'birth_day': datetime.date(1996, 2, 23), 'created_at': datetime.datetime(2019, 8, 17, 0, 47, 1), 'updated_at': datetime.datetime(2019, 8, 17, 0, 47, 1)}
        #=> ...

    # 更新
    query = textwrap.dedent("""\
        UPDATE
            member
        SET
            updated_at = %s
        WHERE
            member_number = 103
    """)

    row_count = cursor.execute(query, [datetime.datetime.now()])
    conn.commit()
    print("更新件数")
    print(row_count) #=> 1

    # 削除
    query = textwrap.dedent("""\
        DELETE FROM
            member
        WHERE
            name like %(name)s
    """)

    row_count = cursor.execute(query, {'name' : '田中%'})
    conn.commit()
    print('削除件数')
    print(row_count) #=> 2

except MySQLdb.Error as ex:
    # 例外処理
    conn.rollback()
    print('MySQL ERROR: ', ex)

finally:
    # 処理
    cursor.close()
    conn.close()

接続部分

# MySQLに接続
db_config = {
    'host'    : 'localhost',
    'port'    : 3306,
    'user'    : 'user',
    'passwd'  : 'password',
    'db'      : 'db_name',
    'charset' : 'utf8'
}

conn   = MySQLdb.connect(**db_config)
cursor = conn.cursor(MySQLdb.cursors.DictCursor)

特に難しいことはしてないと思う。

MySQLdb.connect()でコンストラクタにMySQLの接続に必要な情報を渡してあげる。引数をうだうだ書くのもめんどかったので、辞書型にパラメータを格納して渡してあげている。
一応、例としてportも載せておいたけど、デフォルトの3306のままなら記述は不要。

また、カーソルを作る時、コンストラクタにMySQLdb.cursors.DictCursorを指定しているのは個人的にデフォ。
これをやっとくと、SELECTの結果が辞書型で返ってくるのでいろいろやりやすい。

挿入

    # 挿入
    query = textwrap.dedent("""\
        INSERT INTO member (
            member_number,
            name,
            sex,
            age,
            birth_day
        ) VALUES (
            %(member_number)s,
            %(name)s,
            %(sex)s,
            %(age)s,
            %(birth_day)s
        )
    """)

クエリの組み立て。
プレースホルダは個人的にキーと紐付けできるように名前付きで宣言するのが私的にデフォ。
まあ流石に1個の時とかはしないけど。

    ## 1件挿入
    data = {
        'member_number' : 101,
        'name'          : '田中 博士',
        'sex'           : '男',
        'age'           : 23,
        'birth_day'     : '1995-10-01'
    }

    cursor.execute(query, data)
    conn.commit()

名前付きプレースホルダの場合は、プレースホルダと紐づくキーと値を持った辞書型を渡してあげる。

    ## 複数件挿入
    datas = [
        {
            'member_number' : 102,
            'name'          : '佐藤 武',
            'sex'           : '男',
            'age'           : 22,
            'birth_day'     : '1996-02-23'
        },
 
        # 省略
    ]

    cursor.executemany(query, datas)
    conn.commit()

同じクエリに異なるパラメータで複数回実行する時は、executemany()。
この時、渡すパラメータは配列で。今回の場合は辞書型を内包した配列を渡してあげている。

検索

# 検索
    ## 全件検索
    cursor.execute("SELECT * FROM member")

    rows = cursor.fetchall()

    print("全件検索結果")
    for row in rows:
        print(row)
        #=> {'id': 1, 'member_number': 101, 'name': '田中 博士', 'sex': '男', 'age': 23, 'birth_day': datetime.date(1995, 10, 1), 'created_at': datetime.datetime(2019, 8, 17, 0, 14, 13), 'updated_at': datetime.datetime(2019, 8, 17, 0, 14, 13)}
        #=> ...

全件検索はとくに語ることもなし。
強いていうなら、全件結果取得にfetchall()だよーくらい。

## 絞り込み検索（IN句のテスト）
    query = textwrap.dedent("""\
        SELECT
            *
        FROM
            member
        WHERE
            1 = 1
        AND member_number in (%(member_number)s)
    """)

    datas = {
        'member_number_1' : 102,
        'member_number_2' : 103
    }

    replace_strings = []
    for key, value in datas.items():
        replace_strings.append('%(' + key + ')s')

    query = query % {'member_number' : ', '.join(replace_strings)}
    cursor.execute(query, datas)

    rows = cursor.fetchall()

IN句の形成テクニックはちょっと解説する。
IN句を使う場合は条件に一致させる値が増減する時というのが常なので、その分のプレースホルダをクエリに埋め込んであげないといけない。
なので、query = query % {'member_number' : ', '.join(replace_strings)}で、クエリ内の%(member_number)sを必要なプレースホルダで置き換えてあげている。
その後、通常通りクエリとパラメータを渡して実行しているという感じ。

更新

    # 更新
    query = textwrap.dedent("""\
        # 省略
    """)

    row_count = cursor.execute(query, [datetime.datetime.now()])
    conn.commit()
    print("更新件数")
    print(row_count) #=> 1

更新、削除はクエリが影響した件数が返ってくるのがポイントかな。

削除

    # 削除
    query = textwrap.dedent("""\
        DELETE FROM
            member
        WHERE
            name like %(name)s
    """)

    row_count = cursor.execute(query, {'name' : '田中%'})
    conn.commit()
    print('削除件数')
    print(row_count) #=> 2

ここは削除というよりもLIKE句についての話。
まあ、普通に意図する検索条件になるように値に%をつけてあげましょうねという話。

例外と後処理

except MySQLdb.Error as ex:
    # 例外処理
    conn.rollback()
    print('MySQL ERROR: ', ex)

finally:
    # 処理
    cursor.close()
    conn.close()

一応、クエリ実行時の例外処理と、各オブジェクトの後処理。
例外時はロールバックするのと、最終処理はカーソルも接続もちゃんとお行儀よくクローズしときましょうという感じ。

DATE型、DATETIME型とNULLの代入

DATE型、DATETIME型はMySQLが解釈できるフォーマットで指定してあげれば大丈夫。
例えば'YYYY-MM-DD'なんかが通る。
また、datetime.datetimeクラスのオブジェクトを渡してもいい感じに挿入されるし、SELECTでもdatetime.datetimeクラスのオブジェクトで返ってくるので、日時の扱いはPython上で無理にフォーマットしたりする必要はないかな。

NULLを代入する場合はNoneにしとけばOK。

余談

ひとまずこんな感じ。

1エントリで必要な情報を全部見れるカンペってことでぱぱっと書いたので汚いコードだけど、まあ知りたいことはそこそこ補完できてるのでヨシッ👈

なんか気づきがあったら追記していくかもかも。

参考

【Python3】MySQL 操作をひと通りマスター！導入方法とCRUDサンプルコード集 | ITエンジニアラボ

独学プログラマー Python言語の基本から仕事のやり方まで

作者: コーリー・アルソフ,清水川貴之監訳,清水川貴之,新木雅也
出版社/メーカー: 日経BP
発売日: 2018/02/24
メディア: 単行本
この商品を含むブログ (4件) を見る

MySQL徹底入門第3版 ?5.5新機能対応?

作者: とみたまさひろ,鶴長鎮一,舘山聖司,遠藤俊裕,坂井恵,班石悦夫,松信嘉範
出版社/メーカー: 翔泳社
発売日: 2014/03/25
メディア: Kindle版
この商品を含むブログ (2件) を見る

ぺーぱーふぇいす

雑記と備忘録。私はプログラマ。