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

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

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

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

使用可能なPythonデータベースインターフェイスのリストは次のとおりです：PythonデータベースインターフェイスとAPI。アクセスする必要のあるデータベースごとに個別のDBAPIモジュールをダウンロードする必要があります。たとえば、MySQLデータベースだけでなくOracleデータベースにもアクセスする必要がある場合は、OracleデータベースモジュールとMySQLデータベースモジュールの両方をダウンロードする必要があります。

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

• APIモジュールのインポート。
• データベースとの接続を取得します。
• SQLステートメントとストアード・プロシージャーの発行。
• 接続を閉じる

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

MySQLdbとは何ですか？

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

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

Note −上記のモジュールをインストールするためのroot権限があることを確認してください。

データベース接続

MySQLデータベースに接続する前に、次のことを確認してください。

• データベースTESTDBを作成しました。

• TESTDBにテーブルEMPLOYEEを作成しました。

• このテーブルには、フィールドFIRST_NAME、LAST_NAME、AGE、SEX、およびINCOMEがあります。

• TESTDBにアクセスするためのユーザーID「testuser」とパスワード「test123」が設定されています。

• PythonモジュールMySQLdbがマシンに正しくインストールされています。

• MySQLの基本を理解するためにMySQLチュートリアルを完了しました。

例

以下は、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 それ以外の場合は、さらに使用します dbNoneに設定されています。次、db オブジェクトは、を作成するために使用されます cursorオブジェクト。SQLクエリの実行に使用されます。最後に、出てくる前に、データベース接続が閉じられ、リソースが解放されていることを確認します。

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

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

例

データベーステーブル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操作

任意のデータベースでの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'。ここでは、すべての男性のAGEを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操作が必要です。以下は、AGEが20を超える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 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 −トランザクションが完了するか、まったく何も起こりません。

• Consistency −トランザクションは一貫性のある状態で開始し、システムを一貫性のある状態のままにする必要があります。

• Isolation −トランザクションの中間結果は、現在のトランザクションの外部には表示されません。

• Durability −トランザクションがコミットされると、システム障害が発生した後でも、影響は持続します。

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の下位レベルの実装の詳細に依存するのではなく、commitまたはrollbackを明示的に呼び出す方がアプリケーションに適しています。

エラーの処理

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

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

シニア番号	例外と説明
1	Warning 致命的でない問題に使用されます。StandardErrorをサブクラス化する必要があります。
2	Error エラーの基本クラス。StandardErrorをサブクラス化する必要があります。
3	InterfaceError データベース自体ではなく、データベースモジュールのエラーに使用されます。エラーをサブクラス化する必要があります。
4	DatabaseError データベースのエラーに使用されます。エラーをサブクラス化する必要があります。
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仕様を読むことで、それらに関する詳細情報を入手できます。