python, mysql-connectorのexecutemany()のパラメータで副問合せを実行する方法

Question

前提・実現したいこと

pythonのmysql-connectorのexecutemany()のメソッドを利用して、
パラメータで副問合せを実行する方法を知りたいです。

DBのテーブルは「社員テーブル」、「顧客テーブル」の２つがあります。
「顧客テーブル」にデータを登録する際
「同じ社員ID」がある場合、「sort_no」は「最大値+1」を設定値とする

発生している問題・エラーメッセージ

Eさんを登録した時に、「sort_no」が「3」ではなく「0」になってしまいます。

-- 処理実行前
mysql> SELECT * FROM customer;
+----+-------------+---------+---------+
| id | employee_id | name    | sort_no |
+----+-------------+---------+---------+
|  1 |           1 | Aさん   |       1 |
|  2 |           1 | Cさん   |       2 |
|  3 |           2 | Bさん   |       1 |
|  4 |           3 | Dさん   |       1 |
+----+-------------+---------+---------+
4 rows in set (0.00 sec)


-- 処理実行後
mysql> SELECT * FROM customer;
+----+-------------+---------+---------+
| id | employee_id | name    | sort_no |
+----+-------------+---------+---------+
|  1 |           1 | Aさん   |       1 |
|  2 |           1 | Cさん   |       2 |
|  3 |           2 | Bさん   |       1 |
|  4 |           3 | Dさん   |       1 |
|  5 |           1 | Eさん   |       0 |
+----+-------------+---------+---------+
5 rows in set (0.00 sec)

該当のソースコード

gitにもありますが、こちらにも掲載します。

    config = {
        'host': 'sample_my'
        ,'port': '3306'
        ,'user': 'root'
        ,'password': 'root'
        ,'database': 'table1'
    }

    cnx = connector.connect(**config)

    cnx.ping(reconnect=True)
    # 自動コミットオフ
    cnx.autocommit = False
    # カーソル作成
    cur = cnx.cursor()

    '''
    顧客テーブルに新たにデータ登録
    sort_noは副問合せで取得して設定
    '''
    # 副問合せをパラメータとして設定
    values = [
        (1, 'Eさん', '(SELECT MAX(sort_no)+1 AS `max_sort_no` FROM customer WHERE employee_id = 1)')
    ]

    # TODO: 設定するも「0」が登録される
    # 最大値+1で「3」が設定される想定
    cur.executemany("""
        INSERT INTO `customer` (employee_id, name, sort_no) VALUES
        (%s, %s, %s);
    """, values)
    cnx.commit()

    exit()

試したこと

いくつか別の方法で試した際には期待してる結果になることを確認しました。

# 以下をmain.pyの38行目以降に追加
    '''
    念のため、別の方法で実施
    '''

    # 全て文字列で実行
    # 想定通りに登録される
    cur.execute("""
        INSERT INTO `customer` (employee_id, name, sort_no) VALUES
        (1, 'Fさん', (SELECT MAX(sort_no)+1 FROM customer AS `c` WHERE employee_id = 1));
    """)
    cnx.commit()

    # 先に取得して、文字列に組み込み
    cur.execute("SELECT MAX(sort_no)+1 AS `max_sort_no` FROM customer WHERE employee_id = 1;")
    sort_no = cur.fetchall()[0][0]

    # 想定通りに登録される
    cur.execute(f"""
        INSERT INTO `customer` (employee_id, name, sort_no) VALUES
        (1, 'Gさん', {sort_no});
    """)
    cnx.commit()

    # 先に取得して、パラメータとして設定
    cur.execute("SELECT MAX(sort_no)+1 AS `max_sort_no` FROM customer WHERE employee_id = 1;")
    sort_no = cur.fetchall()[0][0]
    values = [
        (1, 'Hさん', sort_no)
    ]

    # 想定通りに登録される
    cur.executemany("""
        INSERT INTO `customer` (employee_id, name, sort_no) VALUES
        (%s, %s, %s);
    """, values)
    cnx.commit()

実行結果確認

mysql> SELECT * FROM customer;
+----+-------------+---------+---------+
| id | employee_id | name    | sort_no |
+----+-------------+---------+---------+
|  1 |           1 | Aさん   |       1 |
|  2 |           1 | Cさん   |       2 |
|  3 |           2 | Bさん   |       1 |
|  4 |           3 | Dさん   |       1 |
|  5 |           1 | Eさん   |       0 |
|  6 |           1 | Eさん   |       0 |
|  7 |           1 | Fさん   |       3 |
|  8 |           1 | Gさん   |       4 |
|  9 |           1 | Hさん   |       5 |
+----+-------------+---------+---------+
9 rows in set (0.00 sec)

補足情報（FW/ツールのバージョンなど）

環境は下記になります。

Docker Engine v20.10.7
Python 3.10.0
mysql-connector 2.2.9
mysql 8.0.0

コンテナ構築、ソースコードなどのgitはこちら

Answer 1

プレースホルダは値しか受け付けませんので、サブクエリ全体を後から「値」としてセットして実行することはできません。

Comments

[k.a_teratail]

> プレースホルダは値しか受け付けませんので、サブクエリ全体を後から「値」としてセットして実行することはできません。

「name」にサブクエリを仕込むと、文字列で登録されていることが確認できました。
executemany()のパラメータでサブクエリを使用することは不可能ということですね

```sql
*************************** 10. row ***************************
id: 10
employee_id: 1
name: (SELECT MAX(sort_no)
sort_no: 1
```

サブクエリ値をパラメータとして使用するなら
先に取得して、設定するかないということですね。

ご回答いただき、ありがとうございました。
こちらをベストアンサーとして、質問をクローズします。