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

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

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

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

利用可能なPythonデータベースインターフェースのリストを以下に示します-https://wiki.python.org/moin/DatabaseInterfaces[Python Database Interfaces and APIs]。アクセスする必要のあるデータベースごとに個別のDB APIモジュールをダウンロードする必要があります。たとえば、MySQLデータベースだけでなくOracleデータベースにもアクセスする必要がある場合、OracleとMySQLデータベースモジュールの両方をダウンロードする必要があります。

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

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

PythonにはSQLiteの組み込みサポートがあります。このセクションでは、MySQLを使用してすべての概念を学習します。 MySQLとの一般的なインターフェイスであるMySQLdbモジュールは、Python 3と互換性がありません。代わりに、http：//www.pymysql.org/[PyMySQL]モジュールを使用します。

PyMySQLとは何ですか？

PyMySQLは、PythonからMySQLデータベースサーバーに接続するためのインターフェイスです。 Python Database API v2.0を実装し、純粋なPython MySQLクライアントライブラリが含まれています。 PyMySQLの目標は、MySQLdbのドロップイン代替物になることです。

PyMySQLをインストールする方法は？

さらに先に進む前に、マシンにPyMySQLがインストールされていることを確認してください。 Pythonスクリプトに次を入力して実行するだけです-

#!/usr/bin/python3

import pymysql

それは次の結果を生成する場合、それはMySQLdbモジュールがインストールされていないことを意味します-

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

最後の安定版リリースはPyPIで利用でき、pipでインストールできます-

pip install pymysql

別の方法（例： pipが利用できない場合）、tarballはhttps://github.com/PyMySQL/PyMySQL[GitHub]からダウンロードでき、次のようにSetuptoolsでインストールできます-

$ # X.X is the desired pymysql version (e.g. 0.5 or 0.6).
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python setup.py install
$ # The folder PyMySQL* can be safely removed now.

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

データベース接続

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

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

例

以下は、MySQLデータベース「TESTDB」との接続例です-

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.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()

このスクリプトを実行すると、次の結果が生成されます。

Database version : 5.5.20-log

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

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

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

例

データベーステーブルEMPLOYEEを作成しましょう-

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.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操作

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

例

次の例では、SQL _INSERT_ステートメントを実行して、EMPLOYEEテーブルにレコードを作成します-

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.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/python3

import pymysql

# Open database connection
db = pymysql.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/python3

import pymysql

# Open database connection
db = pymysql.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 = "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 fetch data")

# disconnect from server
db.close()

出力

これは、次の結果を生成します-

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

更新操作

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

次の手順は、SEXが 'M' であるすべてのレコードを更新します。ここでは、すべての男性の年齢を1年増やします。

例

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.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/python3

import pymysql

# Open database connection
db = pymysql.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仕様を読むと、それらに関する詳細情報を取得できます。

Python3-python-database-access