Python-database-access

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

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

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

• ガドフライ
• mSQL
• MySQL
• PostgreSQL
• Microsoft SQL Server 2000
• Informix
• Interbase
• オラクル
• Sybase

利用可能なPythonデータベースインターフェイスのリストは次のとおりです。http：//wiki.python.org/moin/DatabaseInterfaces [PythonデータベースインターフェイスとAPI]。 アクセスする必要のあるデータベースごとに個別のDB APIモジュールをダウンロードする必要があります。 たとえば、MySQLデータベースだけでなくOracleデータベースにもアクセスする必要がある場合、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

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

データベース接続

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

• データベースTESTDBを作成しました。
• TESTDBにテーブルEMPLOYEEを作成しました。
• このテーブルには、FIRST_NAME、LAST_NAME、AGE、SEX、およびINCOMEフィールドがあります。
• ユーザーID「testuser」とパスワード「test123」は、TESTDBにアクセスするように設定されています。
• PythonモジュールMySQLdbがマシンに適切にインストールされます。
• リンクを理解するためにMySQLチュートリアルを完了しました：/mysql/index [MySQL Basics。]

例

以下は、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 オブジェクトを使用して 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操作は、データベースからいくつかの有用な情報を取得することを意味します。

データベース接続が確立されると、このデータベースにクエリを実行する準備が整います。 * fetchone（）メソッドを使用して単一レコードをフェッチするか、 fetchall（）*メソッドを使用してデータベーステーブルから複数の値をフェテックできます。

• * 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操作が必要です。 以下は、年齢が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は、トランザクションを_commit_または_rollback_する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* メソッドを呼び出す簡単な例を次に示します。

db.commit()

ロールバック操作

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

• rollback（）*メソッドを呼び出す簡単な例を次に示します。

db.rollback()

データベースの切断

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

db.close()

データベースへの接続がclose（）メソッドを使用してユーザーによって閉じられた場合、未処理のトランザクションはすべてDBによってロールバックされます。 ただし、DBの下位レベルの実装の詳細に依存する代わりに、アプリケーションは明示的にcommitまたはrollbackを呼び出す方が適切です。

エラー処理

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

DB APIは、各データベースモジュールに存在する必要があるエラーの数を定義します。 次の表に、これらの例外をリストします。

Sr.No.	Exception & Description
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仕様を読むと、それらに関する詳細情報を取得できます。