Python MySQLデータベースアクセス

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

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

GadFly
mSQL
MySQL
PostgreSQL
Microsoft SQL Server 2000
Informix
インターベース
オラクル
Sybase

アクセスする必要があるデータベースごとに個別の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 <module>
    import MySQLdb
ImportError: No module named MySQLdb

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

For Ubuntu, use the following command -
$ sudo apt-get install python-pip python-dev libmysqlclient-dev
For Fedora, use the following command -
$ sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc
For Python command prompt, use the following command -
pip install MySQL-python

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

データベース接続

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

データベースTESTDBを作成する。
TESTDBにEMPLOYEEテーブルを登録する。
この表には、FIRST_NAME、LAST_NAME、AGE、SEX、INCOMEの各フィールドがあります。
ユーザーID「testuser」とパスワード「test123」がTESTDBにアクセスするように設定されています。
PythonモジュールMySQLdbはマシンに正しくインストールされています。
★よくわからないかたはこのホームページのSQLの欄をみてください★

例

以下は、MySQLデータベース “TESTDB”で接続する例です。

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()

print "Database version : %s " % data

# disconnect from server
db.close()

このスクリプトを実行している間、Linuxマシンではこのようになってます

Database version : 5.0.45

データソースとの接続が確立されると、接続オブジェクトが返され、後で使用するためにdbに保存されます。そうでない場合は、dbはNoneに設定されます。次に、dbオブジェクトを使用してカーソルオブジェクトを作成します。カーソルオブジェクトはSQLクエリを実行するために使用されます。最後に、出てくる前に、データベース接続が閉じられ、リソースが解放されることを保証します。

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

データベース接続が確立されると、作成されたカーソルの実行メソッドを使用してデータベーステーブルにテーブルまたはレコードを作成する準備ができた

例

データベース表EMPLOYEEを作成しましょう：

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Drop table if it already exist using execute() method.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# Create table as per requirement
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""

cursor.execute(sql)

# disconnect from server
db.close()

INSERT操作

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

例

次の例では、SQL INSERT文を実行してEMPLOYEE表にレコードを作成します。

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

上の例は、SQLクエリを動的に作成するために次のように記述することができます。

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

例

次のコードセグメントは、パラメータを直接渡すことのできる別の実行形式です。

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

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

読み取り操作

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

データベース接続が確立されると、このデータベースにクエリを行う準備が整いました。あなたはどちらかを使用することができますfetchone（）単一のレコードまたはフェッチする方法はfetchAll（）データベーステーブルから複数の値をfetechする方法を。

fetchone（）：クエリ結果セットの次の行を取得します。結果セットは、カーソル・オブジェクトを使用して表を照会するときに戻されるオブジェクトです。
fetchall（）：結果セット内のすべての行をフェッチします。結果セットからいくつかの行がすでに抽出されている場合は、結果セットから残りの行が検索されます。
rowcount：これは読み取り専用の属性で、execute（）メソッドの影響を受けた行の数を返します。

例

次の表は、給与が1000を超えるEMPLOYEE表のすべてのレコードを問い合せます。

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > '%d'" % (1000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # Now print fetched result
      print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
             (fname, lname, age, sex, income )
except:
   print "Error: unable to fecth data"

# disconnect from server
db.close()

これにより、次の結果が生成されます。

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

更新操作

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

次の手順は、SEXが‘M’であるすべてのレコードを更新します。ここでは、すべての男性の年齢を1年プラスさせます。

例

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to UPDATE required records
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
                          WHERE SEX = '%c'" % ('M')
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

DELETE操作

データベースからいくつかのレコードを削除する場合は、DELETE操作が必要です。EMPLOYEEからすべてのレコードを削除する手順は次のとおりです。AGEが20を超える場合 –

例

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

トランザクションの実行

トランザクションは、データの一貫性を保証するメカニズムです。トランザクションには、次の4つのプロパティがあります。

Atomicity：トランザクションが完了するか、まったく何も起こりません。
一貫性：トランザクションは一貫した状態で開始し、システムを一貫した状態にしておく必要があります。
分離：トランザクションの中間結果は、現在のトランザクション外では表示されません。
耐久性：トランザクションがコミットされると、システム障害が発生した後もその影響は永続的です。

Python DB API 2.0には、トランザクションのコミットまたはロールバックの 2つのメソッドがあります。

例

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

COMMIT操作

コミットは操作で、変更を確定するためにデータベースに緑色の信号を与え、この操作の後で変更を元に戻すことはできません。

ここでは、commitメソッドを呼び出す簡単な例を示します。

 db.commit()

ロールバック操作

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

ここでは、rollback（）メソッドを呼び出す簡単な例を示します。

 db.rollback()

データベースの切断

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

 db.close()

close（）メソッドを使用してデータベースへの接続をクローズすると、未処理のトランザクションはDBによってロールバックされます。しかし、DBの下位レベルの実装の詳細に依存するのではなく、明示的にコミットまたはロールバックを呼び出す方がアプリケーションのほうが良いでしょう。

エラーの処理

多くのエラーの原因があります。いくつかの例は、実行されたSQL文の構文エラー、接続の失敗、またはすでにキャンセルまたは終了した文ハンドルのfetchメソッドの呼び出しです。

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

例外	説明
警告	致命的でない問題に使用されます。StandardErrorをサブクラス化する必要があります。
エラー	エラーの基本クラス。StandardErrorをサブクラス化する必要があります。
InterfaceError	データベースモジュール自体のエラーではなく、データベースモジュール内のエラーに使用されます。エラーをサブクラス化する必要があります。
データベースエラー	データベース内のエラーに使用されます。エラーをサブクラス化する必要があります。
DataError	データ内のエラーを参照するDatabaseErrorのサブクラスです。
OperationalError	DatabaseErrorのサブクラスで、データベースへの接続の切断などのエラーを参照します。これらのエラーは一般的にPythonスクリプタの制御の範囲外です。
IntegrityError	一意制約や外部キーなど、リレーショナルの整合性を損なう可能性のある状況については、DatabaseErrorのサブクラスを参照してください。
内部エラー	カーソルがもはやアクティブではないなど、データベースモジュール内部のエラーを参照するDatabaseErrorのサブクラスです。
ProgrammingError	悪質なテーブル名や、あなたに非難される可能性のある他のものなどのエラーを参照するDatabaseErrorのサブクラスです。
NotSupportedError	サポートされていない機能の呼び出しを参照するDatabaseErrorのサブクラスです。

前のページ③PythonCGIプログラミング

次のページ⑤

④Python MySQLデータベースアクセス