Python3 MySQL データベース接続 - PyMySQL ドライバ

この記事では、Python3 を使用してPyMySQLデータベースに接続し、簡単な追加・削除・更新・検索（CRUD）の実装について紹介します。

PyMySQL とは？

PyMySQL は、Python3.x で MySQL サーバーに接続するためのライブラリです。Python2 では mysqldb を使用します。

PyMySQL は Python データベース API v2.0 仕様に準拠しており、pure-Python MySQL クライアントライブラリを含んでいます。

PyMySQL のインストール

PyMySQL を使用する前に、PyMySQL がインストールされていることを確認する必要があります。

PyMySQL ダウンロード先：https://github.com/PyMySQL/PyMySQL。

まだインストールしていない場合は、以下のコマンドで最新版の PyMySQL をインストールできます。

$ pip3 install PyMySQL

お使いのシステムが pip コマンドに対応していない場合は、以下の方法でインストールできます。

1. git コマンドを使用してインストールパッケージをダウンロードしてインストールします（手動でダウンロードすることもできます）。

$ git clone https://github.com/PyMySQL/PyMySQL
$ cd PyMySQL/
$ python3 setup.py install

2. バージョン番号を指定する必要がある場合は、curl コマンドを使用してインストールできます。

$ # X.X 为 PyMySQL 的版本号
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python3 setup.py install
$ # 现在你可以删除 PyMySQL* 目录

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

インストール中に "ImportError: No module named setuptools" というエラーが表示される場合があります。これは setuptools がインストールされていないことを意味します。次のサイトにアクセスできます：https://pypi.python.org/pypi/setuptools各システムのインストール方法を確認できます。

Linux システムでのインストール例：

$ wget https://bootstrap.pypa.io/ez_setup.py
$ python3 ez_setup.py

データベース接続

データベースに接続する前に、以下の項目を確認してください。

  • データベース TESTDB を作成していること。
  • TESTDB データベースにテーブル EMPLOYEE を作成していること。
  • EMPLOYEE テーブルのフィールドは FIRST_NAME, LAST_NAME, AGE, SEX, INCOME であること。
  • データベース TESTDB に接続する際のユーザー名は "testuser"、パスワードは "test123" です。自分で設定するか、root ユーザー名とそのパスワードを直接使用しても構いません。MySQL データベースのユーザー認証には Grant コマンドを使用してください。
  • お使いのマシンには Pythonpymysqlモジュールがインストールされていること。
  • SQL 文に詳しくない場合は、次のページにアクセスしてください。SQL 基礎チュートリアル

例：

以下の例では、Mysql の TESTDB データベースに接続します。

例(Python 3.0+)

#!/usr/bin/python3 import pymysql # データベース接続を開く db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # cursor() メソッドを使用してカーソルオブジェクト cursor を作成する cursor = db.cursor() # execute() メソッドを使用して SQL クエリを実行する cursor.execute("SELECT VERSION()") # fetchone() メソッドを使用して単一のデータを取得する data = cursor.fetchone() print ("Database version : %s " % data) # データベース接続を閉じる db.close()

上記のスクリプトを実行すると、出力結果は次のようになります。

Database version : 5.5.20-log

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

データベース接続が存在する場合、execute() メソッドを使用してデータベースにテーブルを作成できます。以下に示すように、EMPLOYEE テーブルを作成します。

例(Python 3.0+)

#!/usr/bin/python3 import pymysql # データベース接続を開く db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # cursor() メソッドを使用してカーソルオブジェクト cursor を作成する cursor = db.cursor() # execute() メソッドを使用して SQL を実行する。テーブルが存在する場合は削除する cursor.execute("DROP TABLE IF EXISTS EMPLOYEE") # プリペアドステートメントを使用してテーブルを作成する sql = """CREATE TABLE EMPLOYEE ( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT )""" cursor.execute(sql) # データベース接続を閉じる db.close()

データベース挿入操作

以下の例では、SQL INSERT 文を実行してテーブル EMPLOYEE にレコードを挿入します。

例(Python 3.0+)

#!/usr/bin/python3 import pymysql # データベース接続を開く db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # cursor() メソッドを使用して操作カーソルを取得する cursor = db.cursor() # SQL 挿入文 sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Mac', 'Mohan', 20, 'M', 2000)""" try: # SQL 文を実行する cursor.execute(sql) # データベースにコミットして実行する db.commit() except: # エラーが発生した場合はロールバックする db.rollback() # データベース接続を閉じる db.close()

上記の例は、次のように書くこともできます。

例(Python 3.0+)

#!/usr/bin/python3 import pymysql # データベース接続を開く db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # cursor() メソッドを使用して操作カーソルを取得する cursor = db.cursor() # SQL 挿入文 sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \ LAST_NAME, AGE, SEX, INCOME) \ VALUES ('%s', '%s', %s, '%s', %s)" % \ ('Mac', 'Mohan', 20, 'M', 2000) try: # SQL 文を実行する cursor.execute(sql) # SQL 文を実行する db.commit() except: # エラーが発生した場合はロールバックする db.rollback() # データベース接続を閉じる db.close()

以下のコードでは、変数を使用して SQL 文にパラメータを渡します。

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

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

データベース検索操作

Python で Mysql を検索する場合、fetchone() メソッドで単一のデータを取得し、fetchall() メソッドで複数のデータを取得します。

  • fetchone():このメソッドは、次のクエリ結果セットを取得します。結果セットはオブジェクトです。
  • fetchall(): 返されたすべての結果行を受け取ります。
  • rowcount:これは読み取り専用属性で、execute() メソッドの実行後に影響を受けた行数を返します。

例：

EMPLOYEE テーブルで salary（給与）フィールドが 1000 より大きいすべてのデータを検索します：

例(Python 3.0+)

#!/usr/bin/python3 import pymysql # データベース接続を開く db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # cursor() メソッドを使用して操作カーソルを取得する cursor = db.cursor() # SQL クエリ文 sql = "SELECT * FROM EMPLOYEE \ WHERE INCOME > %s" % (1000) try: # SQL 文を実行する cursor.execute(sql) # すべてのレコードリストを取得する results = cursor.fetchall() for row in results: fname = row[0] lname = row[1] age = row[2] sex = row[3] income = row[4] # 結果を出力する print ("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \ (fname, lname, age, sex, income )) except: print ("Error: unable to fetch data") # データベース接続を閉じる db.close()

上記のスクリプトの実行結果は次のとおりです。

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

データベース更新操作

更新操作はデータテーブルのデータを更新するために使用します。以下の例では、TESTDB テーブルの SEX が 'M' である行の AGE フィールドを 1 増やします。

例(Python 3.0+)

#!/usr/bin/python3 import pymysql # データベース接続を開く db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # cursor() メソッドを使用して操作カーソルを取得する cursor = db.cursor() # SQL 更新文 sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M') try: # SQL 文を実行する cursor.execute(sql) # データベースにコミットして実行する db.commit() except: # エラーが発生した場合はロールバックする db.rollback() # データベース接続を閉じる db.close()

削除操作

削除操作はデータテーブル内のデータを削除するために使用します。以下の例では、データテーブル EMPLOYEE から AGE が 20 より大きいすべてのデータを削除します。

例(Python 3.0+)

#!/usr/bin/python3 import pymysql # データベース接続を開く db = pymysql.connect(host='localhost', user='testuser', password='test123', database='TESTDB') # cursor() メソッドを使用して操作カーソルを取得する cursor = db.cursor() # SQL 削除文 sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20) try: # SQL 文を実行する cursor.execute(sql) # 変更をコミットする db.commit() except: # エラーが発生した場合はロールバックする db.rollback() # 接続を閉じる db.close()

トランザクションの実行

トランザクション機構により、データの一貫性を確保できます。

トランザクションには、原子性、一貫性、分離性、永続性の4つの属性が必要です。これら4つの属性は通常、ACID特性と呼ばれます。

  • 原子性（atomicity）。トランザクションは分割できない作業単位であり、トランザクションに含まれるすべての操作は、すべて実行されるか、まったく実行されないかのどちらかです。
  • 一貫性（consistency）。トランザクションは、データベースをある一貫性のある状態から別の一貫性のある状態へと移行させなければなりません。一貫性は原子性と密接に関連しています。
  • 分離性（isolation）。あるトランザクションの実行は他のトランザクションによって妨害されてはなりません。つまり、トランザクション内部の操作と使用するデータは、並行して実行される他のトランザクションから分離されており、並行実行される各トランザクションは互いに干渉できません。
  • 永続性（durability）。持続性は永久性（permanence）とも呼ばれ、トランザクションが一度コミットされると、データベース内のデータに対するその変更は永久的であるべきであることを指します。その後の他の操作や障害はそれに影響を与えるべきではありません。

Python DB API 2.0 のトランザクションは、commit または rollback の2つのメソッドを提供します。

例

例(Python 3.0+)

# SQLレコード削除文 sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20) try: # SQL文を実行 cursor.execute(sql) # データベースにコミット db.commit() except: # エラー発生時にロールバック db.rollback()

トランザクションをサポートするデータベースの場合、Pythonデータベースプログラミングでは、カーソルが作成された時点で、自動的に暗黙のデータベーストランザクションが開始されます。

commit() メソッドはカーソルのすべての更新操作をコミットし、rollback() メソッドは現在のカーソルのすべての操作をロールバックします。各メソッドは新しいトランザクションを開始します。

エラーハンドリング

DB API にはデータベース操作のエラーと例外がいくつか定義されており、次の表にこれらのエラーと例外を示します。

例外説明
Warning重大な警告があるときに発生します。例えば、データ挿入時に切り詰められるなどです。StandardError のサブクラスである必要があります。
Error警告以外のその他すべてのエラークラス。StandardError のサブクラスである必要があります。
InterfaceErrorデータベースインターフェースモジュール自体のエラー（データベースのエラーではなく）が発生したときに発生します。Error のサブクラスである必要があります。
DatabaseErrorデータベースに関連するエラーが発生したときに発生します。Error のサブクラスである必要があります。
DataErrorデータ処理中のエラーが発生したときに発生します。例えば、ゼロ除算エラー、データ範囲外などです。DatabaseError のサブクラスである必要があります。
OperationalErrorユーザーが制御できない、データベース操作中に発生するエラーを指します。例えば、接続が予期せず切断される、データベース名が見つからない、トランザクション処理が失敗する、メモリ割り当てエラーなど、データベース操作中に発生するエラーです。DatabaseError のサブクラスである必要があります。
IntegrityError整合性に関連するエラー、例えば外部キー検査の失敗など。DatabaseError のサブクラスである必要があります。
InternalErrorデータベースの内部エラー、例えばカーソル（cursor）が無効になる、トランザクションの同期に失敗するなど。DatabaseError のサブクラスである必要があります。
ProgrammingErrorプログラムエラー、例えばデータテーブル（table）が見つからない、または既に存在する、SQL文の構文エラー、パラメータ数のエラーなど。DatabaseError のサブクラスである必要があります。
NotSupportedErrorサポートされていないエラー。データベースがサポートしていない関数やAPIなどを使用したことを指します。例えば、接続オブジェクトで .rollback() 関数を使用するが、データベースがトランザクションをサポートしていない、またはトランザクションが閉じられている場合です。DatabaseError のサブクラスである必要があります。

以下は例外の継承構造です：

Exception
|__Warning
|__Error
   |__InterfaceError
   |__DatabaseError
      |__DataError
      |__OperationalError
      |__IntegrityError
      |__InternalError
      |__ProgrammingError
      |__NotSupportedError
その他の拡張