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

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

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

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

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

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

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

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

PyMySQLとは何ですか？

PyMySQLは、PythonからMySQLデータベースサーバーに接続するためのインターフェイスです。Python Database API v2.0を実装し、純粋なPythonMySQLクライアントライブラリが含まれています。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を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.

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

データベース接続

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

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

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

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

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

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

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

例

以下は、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 それ以外の場合は、さらに使用します dbNoneに設定されています。次、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操作

任意のデータベースでの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操作

データベースから一部のレコードを削除する場合は、DELETE操作が必要です。以下は、AGEが20を超える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 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仕様を読むことで、それらに関する詳細情報を入手できます。