PythonでMySQLデータベースを操作する

Python標準データベースインターフェースはPython DB-APIです。Python DB-APIは開発者にデータベースアプリケーションプログラミングインターフェースを提供します。

Pythonデータベースインターフェースは非常に多くのデータベースをサポートしており、プロジェクトに適したデータベースを選択できます：

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

アクセスできます：PythonデータベースインターフェースおよびAPI詳細なサポート対象データベースの一覧を確認してください。

データベースごとに異なるDB APIモジュールをダウンロードする必要があります。例えば、OracleデータベースとMySQLデータにアクセスする必要がある場合は、OracleおよびMySQLデータベースモジュールをダウンロードする必要があります。

DB-APIは仕様です。さまざまな基盤データベースシステムと多様なデータベースインターフェースプログラムに対して一貫したアクセスインターフェースを提供するために、一連の必須オブジェクトとデータベースアクセス方法を定義しています。

PythonのDB-APIはほとんどのデータベース向けにインターフェースを実装しています。これを使って各データベースに接続すれば、同じ方法で各データベースを操作できます。

Python DB-APIの使用手順：

APIモジュールをインポートします。
データベースへの接続を取得します。
SQL文とストアドプロシージャを実行します。
データベース接続を閉じます。

MySQLdbとは何ですか？

MySQLdbはPythonからMySQLデータベースへ接続するためのインターフェースで、PythonデータベースAPI仕様V2.0を実装しており、MySQL C APIに基づいて構築されています。

MySQLdbをインストールするには？

DB-APIを使用してMySQLスクリプトを作成するには、MySQLがインストールされていることを確認する必要があります。次のコードをコピーして実行してください：

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

実行後の出力結果が次のようであれば、MySQLdbモジュールがインストールされていないことを意味します：

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

MySQLdbをインストールするには、次を参照してください：http://sourceforge.net/projects/mysql-python、（Linuxプラットフォームでは次にアクセスできます：https://pypi.python.org/pypi/MySQL-python）ここからお使いのプラットフォームに適したインストールパッケージを選択できます。プリコンパイル済みバイナリファイルとソースコードインストールパッケージに分かれています。

バイナリファイルのリリース版を選択した場合、インストールは基本的にインストールの指示に従うだけで完了できます。ソースコードからインストールする場合は、MySQLdbリリース版のトップディレクトリに移動し、次のコマンドを入力する必要があります:

$ gunzip MySQL-python-1.2.2.tar.gz
$ tar -xvf MySQL-python-1.2.2.tar
$ cd MySQL-python-1.2.2
$ python setup.py build
$ python setup.py install

注意：上記のモジュールをインストールするにはroot権限があることを確認してください。

データベース接続

データベースに接続する前に、次の事項を確認してください：

データベースTESTDBを作成していること。
TESTDBデータベース内にテーブルEMPLOYEEを作成していること。
EMPLOYEEテーブルのフィールドはFIRST_NAME、LAST_NAME、AGE、SEX、INCOMEです。
データベースTESTDBへの接続に使用するユーザー名は"testuser"、パスワードは"test123"です。自分で設定するか、rootユーザー名とそのパスワードを直接使用してもかまいません。MySQLデータベースのユーザー権限付与にはGrantコマンドを使用してください。
お使いのコンピュータにPython MySQLdbモジュールがインストールされていること。
SQL文に詳しくない場合は、次のサイトを参照できます：SQL基礎チュートリアル

例：

次の例はMySQLのTESTDBデータベースに接続します：

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# 使用execute方法执行SQL语句
cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取一条数据
data = cursor.fetchone()

print "Database version : %s " % data

# 关闭数据库连接
db.close()

上記のスクリプトを実行すると、出力結果は次のようになります：

Database version : 5.0.45

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

データベース接続が存在する場合、execute()メソッドを使用してデータベースにテーブルを作成できます。次に示すようにテーブルEMPLOYEEを作成します：

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# 如果数据表已经存在使用 execute() 方法删除表。
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# 创建数据表SQL语句
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()

データベース挿入操作

次の例では、SQL INSERT文を実行してテーブルEMPLOYEEにレコードを挿入します：

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# 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:
   # Rollback in case there is any error
   db.rollback()

# 关闭数据库连接
db.close()

上記の例は次のように書くこともできます：

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 插入语句
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES (%s, %s, %s, %s, %s )" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # 执行sql语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

# 关闭数据库连接
db.close()

例：

次のコードは変数を使用してSQL文にパラメータを渡します:

..................................
user_id = "test123"
password = "password"

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

データベース問い合わせ操作

PythonでMySQLを問い合わせるには、fetchone()メソッドで単一のデータを取得し、fetchall()メソッドで複数のデータを取得します。

fetchone():このメソッドは次のクエリ結果セットを取得します。結果セットはオブジェクトです。
fetchall():返されたすべての結果行を受け取ります。
rowcount:これは読み取り専用の属性で、execute()メソッド実行後に影響を受けた行数を返します。

例：

EMPLOYEEテーブルのsalary（給与）フィールドが1000より大きいすべてのデータを問い合わせます：

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > %s" % (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=%s,sex=%s,income=%s" % \
             (fname, lname, age, sex, income )
except:
   print "Error: unable to fetch data"

# 关闭数据库连接
db.close()

上記のスクリプトの実行結果は次のとおりです：

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

データベース更新操作

更新操作はデータテーブルのデータを更新するために使用します。次の例では、EMPLOYEEテーブルのSEXフィールドが'M'の行のAGEフィールドを1増やします：

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 更新语句
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

# 关闭数据库连接
db.close()

削除操作

削除操作はデータテーブル内のデータを削除するために使用します。次の例では、データテーブルEMPLOYEE内のAGEが20より大きいすべてのデータを削除します：

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 删除语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交修改
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

# 关闭连接
db.close()

トランザクションの実行

トランザクション機構はデータの一貫性を保証できます。

トランザクションには4つの属性が必要です：原子性、一貫性、分離性、永続性です。これらの4つの属性は通常、ACID特性と呼ばれます。

原子性（atomicity）。トランザクションは分割できない作業単位であり、トランザクションに含まれるすべての操作は、すべて実行されるか、すべて実行されないかのどちらかです。
一貫性（consistency）。トランザクションは、データベースをある一貫性のある状態から別の一貫性のある状態へ変化させるものでなければなりません。一貫性は原子性と密接に関連しています。
分離性（isolation）。あるトランザクションの実行は他のトランザクションによって妨害されてはなりません。つまり、トランザクション内部の操作と使用するデータは、並行して実行される他のトランザクションから分離されており、並行実行される各トランザクションは互いに干渉できません。
永続性（durability）。持続性は永久性（permanence）とも呼ばれ、トランザクションがいったんコミットされると、データベース内のデータに対する変更は永続的であるべきことを指します。その後の他の操作や障害がそれに影響を与えるべきではありません。

Python DB API 2.0のトランザクションには、commit と rollback の2つのメソッドがあります。

例：

# SQL删除记录语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 向数据库提交
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

トランザクションをサポートするデータベースでは、Pythonデータベースプログラミングにおいて、カーソルが作成された時点で、自動的に暗黙のデータベーストランザクションが開始されます。

commit()メソッドはカーソルのすべての更新操作をコミットし、rollback()メソッドは現在のカーソルのすべての操作をロールバックします。それぞれのメソッドは新しいトランザクションを開始します。

エラー処理

DB APIにはデータベース操作のエラーと例外がいくつか定義されています。次の表にこれらのエラーと例外を示します:

例外	説明
Warning	重大な警告があるときに発生します。例えば、データ挿入時にデータが切り詰められる場合などです。StandardErrorのサブクラスである必要があります。
Error	警告以外のすべてのその他のエラークラス。StandardErrorのサブクラスである必要があります。
InterfaceError	データベースインターフェースモジュール自体のエラー（データベースのエラーではなく）が発生したときに発生します。Errorのサブクラスである必要があります。
DatabaseError	データベース関連のエラーが発生したときに発生します。Errorのサブクラスである必要があります。
DataError	データ処理中のエラーが発生したときに発生します。例：ゼロ除算エラー、データ範囲外など。 DatabaseErrorのサブクラスである必要があります。
OperationalError	ユーザーが制御できない、データベース操作中に発生するエラーを指します。例：接続が予期せず切断された、データベース名が見つからない、トランザクション処理の失敗、メモリ割り当てエラーなど、データベース操作中に発生するエラーです。 DatabaseErrorのサブクラスである必要があります。
IntegrityError	整合性に関するエラー。例：外部キー検査の失敗など。DatabaseErrorのサブクラスである必要があります。
InternalError	データベースの内部エラー。例：カーソル（cursor）が無効になる、トランザクションの同期に失敗するなど。 DatabaseErrorのサブクラスである必要があります。
ProgrammingError	プログラムエラー。例：データテーブル（table）が見つからない、または既に存在する、SQL文の構文エラー、パラメータ数のエラーなど。DatabaseErrorのサブクラスである必要があります。
NotSupportedError	サポートされていないエラー。データベースがサポートしていない関数やAPIなどを使用したことを指します。例えば、接続オブジェクトに対して .rollback()関数を使用するが、データベースがトランザクションをサポートしていない、またはトランザクションが閉じられている場合です。 DatabaseErrorのサブクラスである必要があります。

その他の拡張

Python上級チュートリアル