mysql-connector-pythonのexecuteとexecutemany

今回は、pythonでMySQLを扱う際に利用するドライバである、mysql-connector-pythonモジュールのexecuteメソッドとexecutemanyメソッドについて、その特徴や概要について学習したのでまとめようと思います。mysql-connector-pythonモジュールはMySQLの公式pythonドライバらしく、各種メソッド等の使い方などは公式ドキュメントに記載されています。今回の記事は、その公式ドキュメントを主に参考にしています。

MySQL Connector/Python Developer Guide

また以前この公式ドキュメントに記載されているチュートリアルについて、その意訳をアップしていますので、参考になれば幸いです。

mysql-connector-pythonの使い方(Python Developer Guideの意訳)

mysql-connector-pythonはMySQL公式のPythonドライバです。DB-API2.0やPythonの最新バージョンにも対応しており、安定して使うことができます。この記事では、公式ドキュメントを基礎に、mysql-connector-pythonの使い方をまとめています。

1. 構築環境

今回の構築環境は以下になります。

PC: Macbook
MySQL: ver8.0.13
mysql-connector-python: 8.0.13
python: 3.7

2. executeメソッドとexecutemanyメソッドの概要と特徴

executeメソッドとexecutemanyメソッドの概要と特徴は以下になります。どちらもCursorオブジェクトのメソッドのになります。

executeメソッド,executemanyメソッドは共にCursorオブジェクトのメソッド
execute() -> データベースへの指令(SQL文やコマンド)を実行する
- 引数: operation(SQL文,コマンド), params=None(dictかtuple, operationの引数になるもの), multi=Flase
  - multi -> operationで指定される複数のSQL文を個別に実行できるようにし、かつイテレータを返す
excutemany() -> for文を回す時のように、一つの指令に対し複数のデータセットを実行したい場合に利用する。複数の指令は指定できない。
- INSERT文向けに最適化されているメソッド
- 引数: operation(SQL文,コマンド), seq_of_params(operationの引数になるparamsのセット,タプルやリストなど)

3. executeメソッドとexecutemanyメソッドのそれぞれの特徴を知るための実験コード

ここでは、executeメソッドのmulti引数の使い方と、executemanyメソッドの使い方についてのコードを記載しています。

3-1. executeメソッドのmulti引数

executeメソッドのmulti引数がTrueにセットされた場合、引数のoperationで指定された複数のSQL文をそれぞれ個別に実行し、イテレータのジェネレータオブジェクトが返されます。

CREATE DATABASE TEST; CREATE TABLE TEST.test(columns...); INSERT INTO TEST.test VALUES (values...);

上記のようなSQL文があった場合にmulti=Falseと設定されている場合(デフォルト)は、一括実行のようになりますが、multi=Trueと設定すると ①CREATE DATABASE ...、②CREATE TABLE ...、③INSERT INTO ...と一つずつ実行してくれます。コード例は下記になります。

# 基本設定部分
import mysql.connector

db_config = {"user": <username>,"password":<password>}  #設定情報

#operation -> drop,create,createの3つの実行文が含まれている
operation = (
    "DROP DATABASE IF EXISTS TEST;"
    "CREATE DATABASE TEST;"
    "CREATE TABLE TEST.test ("
    "no int AUTO_INCREMENT UNIQUE NOT NULL,"
    "id int NOT NULL,"
    "price DOUBLE(9,1) NOT NULL,"
    "timestamp DOUBLE(13,3) NOT NULL,"
    "primary key(id),"
    "index(no));")


#multi=False(デフォルト)
con_obj1 = mysql.connector.connect(**db_config)
cur_obj1 = con_obj1.cursor()
test1 = cur_obj1.execute(operation, multi=False)
print("multi=False: "+ str(type(test1)), "\n")

#multi=True
con_obj2 = mysql.connector.connect(**db_config)
cur_obj2 = con_obj2.cursor()
test2 = cur_obj2.execute(operation, multi=True)
print("multi=True: " + str(type(test2)))
for i in test2:
    print(i)

戻り値

multi=False: < class 'NoneType' >

multi=True: < class 'generator' >
CMySQLCursor: DROP DATABASE IF EXISTS TEST
CMySQLCursor: CREATE DATABASE TEST
CMySQLCursor: CREATE TABLE TEST.test (no int AUTO_INCR..

multi=Trueと設定された方では、ジェネレータオブジェクトが返されており、SQL文がそれぞれ個別に実行されていることがわかります。multi引数は、複数のSQL文を実行し、かつそれぞれのSQL文に対し追加で何らかの処理をおこなっていく場合などに有用なのかなと思います。

3-2. executemanyメソッドの使い方

次は、executemanyメソッドの使い方についてです。最初の特徴の説明部分でも記載したように、SQLのINSERT文に最適化されています。一方で、executeメソッドのように複数の異なるSQL文をまとめて実行することはできません。下記がコード例です。テーブルは上記で作成したものを利用しています。

# 基本設定部分
import mysql.connector

db_config = {"user": <username>,"password":<password>}  #設定情報

operation = ("INSERT INTO TEST.test (id, price, timestamp) VALUES (%s, %s, %s);")
seqs = [
    (1, 2520, 40000000),
    (2, 3890, 40000048)
]

まずは、executemanyメソッドを利用した場合です。

#executemany()を使う場合
con_obj1 = mysql.connector.connect(**db_config)
cur_obj1 = con_obj1.cursor()
cur_obj1.executemany(operation, seqs)
con_obj1.commit()

次はどのようの処理をexecuteメソッドで行なった場合です。

#execute()でexecutemany()と同様の処理をする場合
con_obj2 = mysql.connector.connect(**db_config)
cur_obj2 = con_obj2.cursor()

for i in seqs:
    cur_obj2.execute(operation, i)
con_obj2.commit()

上記の2つのコードを見比べると、executemanyの方が簡単にかけることがわかります。メモリの使用量などのその他の部分でも、より最適化されているんじゃないかと思います。

4. まとめ

以上が今回の学習内容のまとめです。個人的にDB操作はわかりやすいようで、じつはややこしい、そんな印象があります。アプリ制作などを通してこれからもっと理解を深めていきたいと思います。

読んで頂き、ありがとうございました。