Python MySQLデータベースアクセス：標準DB-APIの活用

Python DB-APIとは

データベースインターフェースのPython標準はPython DB-APIです。ほとんどのPythonデータベースインターフェースはこの標準に準拠しています。

アプリケーションに適したデータベースを選択できます。Python Database APIは以下のような幅広いデータベースサーバーをサポートしています。

GadFly
mSQL
MySQL
PostgreSQL
Microsoft SQL Server 2000
Informix
Interbase
Oracle
Sybase

利用可能なPythonデータベースインターフェースの一覧: Python Database Interfaces and APIs。アクセスするデータベースごとに別個のDB APIモジュールをダウンロードする必要があります。例えば、OracleデータベースとMySQLデータベースの両方にアクセスする必要がある場合、OracleとMySQLの両方のデータベースモジュールをダウンロードする必要があります。

DB APIは、可能な限りPythonの構造と構文を使用してデータベースを操作するための最小限の標準を提供します。このAPIには以下が含まれます。

APIモジュールのインポート
データベースとの接続取得
SQL文とストアドプロシージャの発行
接続のクローズ

ここではMySQLを使用してすべての概念を学習するため、MySQLdbモジュールについて説明します。

MySQLdbとは

MySQLdbは、PythonからMySQLデータベースサーバーに接続するためのインターフェースです。Python Database API v2.0を実装し、MySQL C APIの上に構築されています。

MySQLdbのインストール方法

作業を進める前に、マシンにMySQLdbがインストールされていることを確認してください。以下をPythonスクリプトに記述して実行します。

#!/usr/bin/python
import MySQLdb

以下の結果が出力される場合、MySQLdbモジュールはインストールされていません。

Traceback (most recent call last):
  File "test.py", line 3, in 
    import MySQLdb
ImportError: No module named MySQLdb

MySQLdbモジュールをインストールするには、以下のコマンドを使用します。

Ubuntuの場合: $ sudo apt-get install python-pip python-dev libmysqlclient-dev
Fedoraの場合: $ sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc
Pythonコマンドプロンプトの場合: pip install MySQL-python

注意 - 上記モジュールをインストールするにはroot権限が必要です。

データベース接続

MySQLデータベースに接続する前に、以下を確認してください。

TESTDBデータベースを作成済み
TESTDBにEMPLOYEEテーブルを作成済み
このテーブルにFIRST_NAME、LAST_NAME、AGE、SEX、INCOMEフィールドがある
ユーザーID "testuser"、パスワード "test123" でTESTDBにアクセス可能
PythonモジュールMySQLdbが正しくインストール済み
MySQLチュートリアルを通じてMySQLの基礎を理解済み

例：MySQLデータベース"TESTDB"への接続

#!/usr/bin/python
import MySQLdb

# データベース接続を開く
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")

# cursor()メソッドを使用してカーソルオブジェクトを準備
cursor = db.cursor()

# execute()メソッドを使用してSQLクエリを実行
cursor.execute("SELECT VERSION()")

# fetchone()メソッドを使用して単一行を取得
data = cursor.fetchone()
print("Database version : %s " % data)

# サーバーから切断
db.close()

このスクリプトを実行すると、私のLinuxマシンでは以下の結果が出力されます。

Database version : 5.0.45

データソースとの接続が確立されると、Connection Objectが返されdbに保存されます。接続に失敗した場合はdbはNoneに設定されます。次に、dbオブジェクトを使用してcursorオブジェクトを作成し、SQLクエリの実行に使用します。最後に、データベース接続が閉じられ、リソースが解放されることを確認してから終了します。

データベーステーブルの作成

データベース接続が確立されたら、作成したカーソルのexecuteメソッドを使用して、データベーステーブルへのテーブルやレコードの作成準備が整います。

例：EMPLOYEEテーブルの作成

#!/usr/bin/python
import MySQLdb

# データベース接続を開く
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")

# cursor()メソッドを使用してカーソルオブジェクトを準備
cursor = db.cursor()

# 既存テーブルがあればDROP TABLEで削除
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# 要件に合わせてテーブルを作成
sql = """CREATE TABLE EMPLOYEE (
    FIRST_NAME CHAR(20) NOT NULL,
    LAST_NAME CHAR(20),
    AGE INT,
    SEX CHAR(1),
    INCOME FLOAT
)"""
cursor.execute(sql)

# サーバーから切断
db.close()

INSERT操作

データベーステーブルにレコードを作成する場合に必要です。

例：EMPLOYEEテーブルへのレコード挿入

#!/usr/bin/python
import MySQLdb

# データベース接続を開く
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")

# cursor()メソッドを使用してカーソルオブジェクトを準備
cursor = db.cursor()

# データベースにレコードをINSERTするSQLクエリを準備
sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""

try:
    # SQLコマンドを実行
    cursor.execute(sql)
    # データベースに変更をコミット
    db.commit()
except:
    # エラーが発生した場合はロールバック
    db.rollback()

# サーバーから切断
db.close()

上記の例は、SQLクエリを動的に作成する形でも書けます。

#!/usr/bin/python
import MySQLdb

# データベース接続を開く
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")

# cursor()メソッドを使用してカーソルオブジェクトを準備
cursor = db.cursor()

# データベースにレコードをINSERTするSQLクエリを準備
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
       ('Mac', 'Mohan', 20, 'M', 2000)

try:
    # SQLコマンドを実行
    cursor.execute(sql)
    # データベースに変更をコミット
    db.commit()
except:
    # エラーが発生した場合はロールバック
    db.rollback()

# サーバーから切断
db.close()

パラメータを直接渡す別の形式

..................................
user_id = "test123"
password = "password"
con.execute('insert into Login values("%s", "%s")' % \
            (user_id, password))
..................................

READ操作

データベースでのREAD操作とは、データベースから有用な情報を取得することを意味します。

データベース接続が確立されたら、このデータベースへのクエリを実行できます。fetchone()メソッドで単一レコードを取得するか、fetchall()メソッドでデータベーステーブルから複数の値を取得できます。

fetchone() - クエリ結果セットの次の行を取得。結果セットは、カーソルオブジェクトを使用してテーブルをクエリしたときに返されるオブジェクト
fetchall() - 結果セットのすべての行を取得。一部の行がすでに結果セットから抽出されている場合、残りの行を取得
rowcount - 読み取り専用属性。execute()メソッドで影響を受けた行数を返す

例：給与が1000より大きいEMPLOYEEレコードをすべて取得

#!/usr/bin/python
import MySQLdb

# データベース接続を開く
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")

# cursor()メソッドを使用してカーソルオブジェクトを準備
cursor = db.cursor()

sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > '%d'" % (1000)

try:
    # SQLコマンドを実行
    cursor.execute(sql)
    # すべての行をリストのリストとして取得
    results = cursor.fetchall()
    for row in results:
        fname = row[0]
        lname = row[1]
        age = row[2]
        sex = row[3]
        income = row[4]
        # 取得結果を出力
        print("fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
              (fname, lname, age, sex, income))
except:
    print("Error: unable to fetch data")

# サーバーから切断
db.close()

実行結果：

fname=Mac, lname=Mohan, age=20, sex=M, income=2000

UPDATE操作

データベースでのUPDATE操作とは、データベースにすでに存在する1つ以上のレコードを更新することを意味します。

以下の手順では、SEXが'M'のすべてのレコードを更新します。ここでは、すべての男性のAGEを1歳増やします。

例

#!/usr/bin/python
import MySQLdb

# データベース接続を開く
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")

# cursor()メソッドを使用してカーソルオブジェクトを準備
cursor = db.cursor()

# 必要なレコードをUPDATEするSQLクエリを準備
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')

try:
    # SQLコマンドを実行
    cursor.execute(sql)
    # データベースに変更をコミット
    db.commit()
except:
    # エラーが発生した場合はロールバック
    db.rollback()

# サーバーから切断
db.close()

DELETE操作

DELETE操作は、データベースから一部のレコードを削除する場合に必要です。以下は、AGEが20より大きいEMPLOYEEのすべてのレコードを削除する手順です。

例

#!/usr/bin/python
import MySQLdb

# データベース接続を開く
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")

# cursor()メソッドを使用してカーソルオブジェクトを準備
cursor = db.cursor()

# 必要なレコードをDELETEするSQLクエリを準備
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)

try:
    # SQLコマンドを実行
    cursor.execute(sql)
    # データベースに変更をコミット
    db.commit()
except:
    # エラーが発生した場合はロールバック
    db.rollback()

# サーバーから切断
db.close()

トランザクションの実行

トランザクションはデータの一貫性を保証するメカニズムです。トランザクションには以下の4つの特性（ACID特性）があります。

原子性 - トランザクションが完了するか、何も起こらないかのいずれか
一貫性 - トランザクションは一貫した状態で開始し、システムを一貫した状態に残す必要がある
分離性 - トランザクションの中間結果は現在のトランザクションの外部からは見えない
耐久性 - トランザクションがコミットされると、システム障害後も効果は持続

Python DB API 2.0は、トランザクションをコミットまたはロールバックするための2つのメソッドを提供します。

例

# 必要なレコードをDELETEするSQLクエリを準備
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)

try:
    # SQLコマンドを実行
    cursor.execute(sql)
    # データベースに変更をコミット
    db.commit()
except:
    # エラーが発生した場合はロールバック
    db.rollback()

COMMIT操作

Commitは、データベースに変更を確定させる操作であり、この操作後は変更を元に戻せません。

db.commit()

ROLLBACK操作

1つ以上の変更に満足できず、それらを完全に元に戻したい場合は、rollback()メソッドを使用します。

db.rollback()

データベースの切断

データベース接続を切断するには、close()メソッドを使用します。

db.close()

ユーザーがclose()メソッドでデータベース接続を閉じた場合、未処理のトランザクションはDBによってロールバックされます。ただし、DBの低レベル実装の詳細に依存するのではなく、アプリケーションで明示的にcommitまたはrollbackを呼び出す方が良いでしょう。

エラー処理

エラーの原因は多岐にわたります。実行されたSQL文の構文エラー、接続失敗、すでにキャンセルまたは完了したステートメントハンドルに対するfetchメソッドの呼び出しなどが例です。

DB APIは、各データベースモジュールに存在しなければならない多数のエラーを定義しています。以下の表にこれらの例外を示します。

No.	例外と説明
1	Warning - 致命的でない問題に使用。StandardErrorをサブクラス化必須
2	Error - エラーの基底クラス。StandardErrorをサブクラス化必須
3	InterfaceError - データベース自体ではなく、データベースモジュールのエラーに使用。Errorをサブクラス化必須
4	DatabaseError - データベースのエラーに使用。Errorをサブクラス化必須
5	DataError - データのエラーを指すDatabaseErrorのサブクラス
6	OperationalError - データベースへの接続喪失などのエラーを指すDatabaseErrorのサブクラス。一般的にPythonスクリプト作成者の制御外
7	IntegrityError - 一意制約や外部キーなど、関係の整合性を損なう状況を指すDatabaseErrorのサブクラス
8	InternalError - カーソルがアクティブでなくなるなど、データベースモジュール内部のエラーを指すDatabaseErrorのサブクラス
9	ProgrammingError - 不正なテーブル名など、安全にユーザーの責任とできるエラーを指すDatabaseErrorのサブクラス
10	NotSupportedError - サポートされていない機能を呼び出そうとした場合を指すDatabaseErrorのサブクラス

Pythonスクリプトではこれらのエラーを処理すべきですが、上記の例外を使用する前に、MySQLdbがその例外をサポートしていることを確認してください。詳細についてはDB API 2.0仕様を参照してください。

Python正規表現マスターガイド：実践的パターンマッチング Pythonネットワークプログラミング：ソケットと高度なプロトコルをマスター

Python