MySQLとPythonの入門

完全に機能するデプロイ可能なアプリケーションには、データの永続化が不可欠です。データを保存する簡単な方法は、ハードディスク内のファイルに書き込むことですが、明らかな理由により、アプリケーション固有のデータをデータベースに書き込むことを好むでしょう。Pythonは、様々なデータベースにデータを書き込むための言語サポートを提供します。

Python DB API

データベースプログラミングのための Python サポートの中心は、特定のデータベースエンジンに依存しない Python DB API (PEP – 249) です。永続化層で使用するデータベースに応じて、Python DB APIの適切な実装をインポートして、プログラムで使用する必要があります。このチュートリアルでは、Pythonを使用してMySQLデータベースに接続し、トランザクションを実行する方法を紹介します。このために、MySQLdb Python パッケージを使用します。

Pythonを使用してデータベースへの接続を行う前に、Python用のMySQLコネクタをインストールする必要があります。これは2つの方法で行うことができます。

1つは、MySQLの公式サイトからOSとビットバージョンに適したインストーラを直接ダウンロードする方法です。
もう1つの方法は、pipを使用してインストールする方法です。

$ pip install mysql-connector-python

ローカルマシンに特定のMySQLバージョンがインストールされている場合、互換性の問題が発生しないように、特定のMySQLコネクタバージョンが必要な場合があります。

$ pip install mysql-connector-python==<insert_version_number_here

最後に、クライアントとして動作するPythonアプリケーションからMySQLデータベースに接続できるようにするために、MySQLクライアントモジュールをインストールする必要があります。

$ pip install mysqlclient

データベースへの接続

コネクタのインストールが完了したら、Pythonファイルを実行する際に import MySQLdb ステートメントがエラーを吐かないようにします。

前提条件

注：読者は、一般的なデータベースと MySQL データベースに関する基本的な理解、および構造化クエリ言語（SQL）の知識を持っていることを前提としています。しかし、データベースとユーザーを作成するための基本的なプロセスは、このセクションで説明されています。以下の手順に従ってください。

MySQLサーバーが稼動していることを確認します。これは、MySQL WorkBench – Server Statusで確認することができます。
MySQL WorkBenchまたはMySQL CLIを起動します。新しいデータベースを作成します。ここでは pythondb と呼ぶことにします。

CREATE DATABASE pythondb;
USE pythondb;

新規ユーザー pythonuser を作成し、パスワード pythonpwd123 で pythondb にアクセス権を付与します。

CREATE USER 'pythonuser'@'localhost' IDENTIFIED BY 'pythonpwd123'
GRANT ALL PRIVILEGES ON pythondb.* To 'pythonuser'@'localhost'
FLUSH PRIVILEGES

pythondbへの接続を確認する

新しく作成されたデータベースへの接続をプログラムでテストするための簡単なスクリプトを紹介します。

#!/usr/bin/python


import MySQLdb


dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb")


cursor = dbconnect.cursor()
cursor.execute("SELECT VERSION()")


data = cursor.fetchone()
if data:
  print('Version retrieved: ', data)
else:
  print('Version not retrieved.')


dbconnect.close()

出力

Version retrieved: 5.7.19

上に表示されているバージョン番号は単なるダミー番号です。インストールされたMySQLサーバーのバージョンと一致するはずです。

上記のサンプルプログラムがどのように動作するのか、詳しく見ていきましょう。まず、import MySQLdb は必要な Python モジュールをインポートするために使用されます。

MySQLdb.connect() メソッドは、ホスト名、ユーザー名、パスワード、データベーススキーマ名を受け取り、データベース接続を作成します。データベースとの接続に成功すると、接続オブジェクトが返されます（ここでは dbconnect と呼びます）。

接続オブジェクトを使うと、接続を閉じる前にクエリを実行したり、トランザクションをコミットしたり、トランザクションをロールバックしたりすることができる。

接続オブジェクトを取得したら、 execute メソッドを使ってクエリを実行するために MySQLCursor オブジェクトを取得する必要があります。トランザクションの結果セットは fetchall、fetchone または fetchmany メソッドを使用して取得することができますが、これについてはこのチュートリアルで後ほど説明します。

execute メソッドとは別に、データベーストランザクションに関連する重要なメソッドが3つあります。これらのメソッドについて、これから簡単に学びます。

dbconnect.commit() メソッドは、この関数を呼び出す前に実行された変更を確定し、トランザクションが成功したら以前の状態にロールバックする余地がないことをデータベースに通知します。

トランザクションに失敗した場合、データが失われたり破損したりしないように、失敗前の状態にデータベースを変更する必要があることがあります。そのような場合は、dbconnect.rollback() を使ってデータベースを以前の状態にロールバックする必要があります。

最後に、データベースへの接続を閉じるために dbconnect.close() メソッドを使用します。さらにトランザクションを実行するには、新しい接続を作成する必要があります。

新規テーブルの作成

pythondb`との接続に成功したら、次のステップに進みます。その中に新しいテーブルを作成してみましょう。

import MySQLdb


dbconnect = MySQLdb.connect("localhost","pythonuser","pythonpwd123","pythondb" )


cursor = dbconnect.cursor()
cursor.execute("DROP TABLE IF EXISTS MOVIE")


query = "CREATE TABLE MOVIE(  
          id int(11) NOT NULL,
          name varchar(20),
          year int(11),
          director varchar(20),
          genre varchar(20),
          PRIMARY KEY (id))"


cursor.execute(query)


dbconnect.close()

上記のスクリプトを実行すると、スキーマ pythondb に新しいテーブル movie が作成されるはずである。これは MySQL WorkBench を使って見ることができます。

CRUD操作の実行

では、Pythonスクリプトを使って、新しく作成したデータベーステーブルに対して、挿入、読み込み、変更、削除の操作を行ってみましょう。

新規レコードの作成

次のスクリプトは、Python スクリプトを使用して MySQL データベースに新しいレコードを挿入する方法を示しています。

#!/usr/bin/python


import MySQLdb


dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb")


cursor = dbconnect.cursor()


query = 'insert into movie(id, name, year, director, genre)  
       values (1, "Bruce Almighty", 2003, "Tom Shaydac", "Comedy")'
try:
   cursor.execute(query)
   dbconnect.commit()
except:
   dbconnect.rollback()
finally:
   dbconnect.close()

テーブルから行を読み取る

新しい行がデータベースに挿入されると、カーソルオブジェクトを使用して、3つの方法でデータを取得することができます。

cursor.fetchall() – すべての行を取得するために使用することができます。
cursor.fetchmany() – 選択した数の行を取得するために使用することができます。
cursor.fetchone() – 結果セットから最初の行だけを取得するために使用することができます。

単純化するために、”select all” SQL クエリを使用し、fetchall メソッドの結果セットに対して for ループを使用して、個々のレコードを表示することにします。

#!/usr/bin/python


import MySQLdb


dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb")


cursor = dbconnect.cursor()


query = "SELECT * FROM movie"
try:
   cursor.execute(query)
   resultList = cursor.fetchall()
   for row in resultList:
      print ("Movie ID =", row[0])
      print ("Name =", row[1])
      print ("Year =", row[2])
      print ("Director = ", row[3])
      print ('Genre = ', row[4])
except:
   print ("Encountered error while retrieving data from database")
finally:
   dbconnect.close()

出力します。

Movie ID = 1
Name = Bruce Almighty
Year = 2003
Director = Tom Shaydac
Genre = Comedy

行の更新

ブルース・オールマイティ」のジャンルをコメディーから風刺に更新してみましょう。

import MySQLdb


dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb")


# The cursor object obtained below allows SQL queries to be executed in the database session.
cursor = dbconnect.cursor()


updatequery = "update movie set genre = 'Satire' where id = 1"


cursor.execute(updatequery)


dbconnect.commit()


print(cursor.rowcount, "record(s) affected")

出力

1 record(s) affected

レコードの削除

以下は、データベースの行を削除する方法を示すPythonスクリプトです。

import MySQLdb


dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb")


# The cursor object obtained below allows SQL queries to be executed in the database session.
cursor = dbconnect.cursor()


updatequery = "DELETE FROM movie WHERE id = 1"


cursor.execute(updatequery)


dbconnect.commit()


print(cursor.rowcount, "record(s) deleted")

上記のスクリプトを実行した後、すべてがうまくいけば次のような出力が得られるはずである。

出力

1 record(s) deleted

結論

この記事では、Python DB API を使用してデータベースに接続する方法を学びました。具体的には、Python DB APIのMySQLdb実装を使用して、MySQLデータベースに接続する方法を学びました。また、データベースとのトランザクションを実行する方法も学びました。

を参照してください。