[專案踩坑] py executemany 的使用報錯：Not all parameters were used in the SQL

wangchunbo發表於2020-05-29

使用 executemany 來執行批量操作

我的執行程式碼如下：

with closing(ds.get_connection()) as conn, closing(conn.cursor()) as cur:

            cur.executemany(sql, tup_list)
            # cur.execute(sql, tup_list[0])
            conn.commit()

其中資料為：
sql：
INSERT INTOfootball_player_top_data(id,competition_id,season,team,goals,penalties,assists,created_at) VALUES (%s,%s,%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATEteam=%s,goals=%s,penalties=%s,assists=%s,created_at=%s

tuplist:

[(63861, 2, '2019-2020', 1612, 1, 0, 0, '2020-05-29 15:01:32', 1612, 1, 0, 0, '2020-05-29 15:01:32'), (63978, 2, '2019-2020', 188, 1, 0, 0, '2020-05-29 15:01:32', 188, 1, 0, 0, '2020-05-29 15:01:32'), (64119, 2, '2019-2020', 207, 1, 0, 0, '2020-05-29 15:01:32', 207, 1, 0, 0, '2020-05-29 15:01:32'), (64153, 2, '2019-2020', 1612, 1, 0, 0, '2020-05-29 15:01:32', 1612, 1, 0, 0, '2020-05-29 15:01:32'), (68935, 2, '2019-2020', 3193, 1, 0, 0, '2020-05-29 15:01:32', 3193, 1, 0, 0, '2020-05-29 15:01:32'), (79870, 2, '2019-2020', 206, 1, 0, 0, '2020-05-29 15:01:32', 206, 1, 0, 0, '2020-05-29 15:01:32'), (81138, 2, '2019-2020', 201, 1, 0, 0, '2020-05-29 15:01:32', 201, 1, 0, 0, '2020-05-29 15:01:32'), (84144, 2, '2019-2020', 201, 0, 1, 0, '2020-05-29 15:01:32', 201, 0, 1, 0, '2020-05-29 15:01:32'), (90162, 2, '2019-2020', 204, 1, 0, 0, '2020-05-29 15:01:32', 204, 1, 0, 0, '2020-05-29 15:01:32'), (97156, 2, '2019-2020', 3195, 1, 0, 0, '2020-05-29 15:01:32', 3195, 1, 0, 0, '2020-05-29 15:01:32'), (97236, 2, '2019-2020', 207, 1, 0, 0, '2020-05-29 15:01:32', 207, 1, 0, 0, '2020-05-29 15:01:32'), (97401, 2, '2019-2020', 181, 1, 0, 0, '2020-05-29 15:01:32', 181, 1, 0, 0, '2020-05-29 15:01:32'), (97645, 2, '2019-2020', 3195, 1, 0, 0, '2020-05-29 15:01:32', 3195, 1, 0, 0, '2020-05-29 15:01:32'), (107413, 2, '2019-2020', 193, 1, 0, 0, '2020-05-29 15:01:32', 193, 1, 0, 0, '2020-05-29 15:01:32'), (120408, 2, '2019-2020', 206, 1, 0, 0, '2020-05-29 15:01:32', 206, 1, 0, 0, '2020-05-29 15:01:32'), (121546, 2, '2019-2020', 188, 1, 0, 0, '2020-05-29 15:01:32', 188, 1, 0, 0, '2020-05-29 15:01:32'), (127225, 2, '2019-2020', 201, 0, 1, 0, '2020-05-29 15:01:32', 201, 0, 1, 0, '2020-05-29 15:01:32'), (130167, 2, '2019-2020', 209, 2, 0, 0, '2020-05-29 15:01:32', 209, 2, 0, 0, '2020-05-29 15:01:32'), (139192, 2, '2019-2020', 209, 2, 0, 0, '2020-05-29 15:01:32', 209, 2, 0, 0, '2020-05-29 15:01:32'), (142059, 2, '2019-2020', 206, 1, 0, 0, '2020-05-29 15:01:32', 206, 1, 0, 0, '2020-05-29 15:01:32'), (142654, 2, '2019-2020', 196, 1, 0, 0, '2020-05-29 15:01:32', 196, 3415, 1, 0, 0, '2020-05-29 15:01:32', 3415, 1, 0, 0, '2020-05-29 15:01:32')]

Not all parameters were used in the SQL statement

翻譯：為引數不夠？

通過仔細對比，仔細排查，發現是一樣的

那我該用 execute 來試試？就插入一條。

with closing(ds.get_connection()) as conn, closing(conn.cursor()) as cur:

            cur.execute(sql, tup_list[0])
            conn.commit()

發現直接成功。。。。

python executemany的使用報錯：Not all parameters were used in the SQL statement

排除引數不夠的情況

去百度搜尋看看

發現都是一毛一樣的文章！！！我草。

而且告訴我：

在這裡executemany和ON DUPLICATE KEY UPDATE聯合使用的時候如果按照sql常規模式，即：sql=”insert into myTable (created_day,name,count) values(%s,%s,%s) ON DUPLICATE KEY UPDATE count=count+%s”會報bug：not all arguments converted during string formatting

python executemany的使用報錯：Not all parameters were used in the SQL statement

不理解 UPDATE count=count+%s的寫法是什麼意思？

去google 看看，要輸入英文的才行。

搜尋詞：

Executemany and duplicate

找到下面這個文章：

python executemany的使用報錯：Not all parameters were used in the SQL statement

Executemany insert on duplicate key update error: Not all parameters were used

最終發現，

原理

在pymysql的 executemany不能使用位置引數來表示實際的列名。出於多種原因，列名需要在語句中進行硬編碼。但是，在您的情況下，我認為這樣做沒有任何問題：

程式碼

INSERT INTO updates (ID, insert_datetime, egroup, job_state)
VALUES (%s,%s,%s,%s)
ON DUPLICATE KEY UPDATE
    insert_datetime = VALUES(insert_datetime),
    egroup = VALUES(egroup),
    job_state = VALUES(job_state);

或者，作為Python程式碼：

sql = "INSERT INTO updates (ID, insert_datetime, egroup, job_state) VALUES (%s,%s,%s,%s) ON DUPLICATE KEY UPDATE insert_datetime = VALUES(insert_datetime), egroup = VALUES(egroup), job_state = VALUES(job_state);"
mycursor.executemany(sql, jobUpdatesList)

分析

在程式碼

ON DUPLICATE KEY UPDATE insert_datetime = VALUES(insert_datetime), egroup = VALUES(egroup),

這裡， insert_datetime = VALUES(insert_datetime) 意思是我們不需要指定 %s 這麼去替換。

我們直接給一個名字，pymysql 自己會去找到對應的值。
不需要重複的寫。

程式碼


with closing(ds.get_connection()) as conn, closing(conn.cursor()) as cur:

            cur.executemany(sql, tup_list)
            # cur.execute(sql, tup_list[0])
            conn.commit()

資料

tuplist:

[(63861, 2, '2019-2020', 1612, 1, 0, 0, '2020-05-29 15:12:35'), (63978, 2, '2019-2020', 188, 1, 0, 0, '2020-05-29 15:12:35'), (64119, 2, '2019-2020', 207, 1, 0, 0, '2020-05-29 15:12:35'), (64153, 2, '2019-2020', 1612, 1, 0, 0, '2020-05-29 15:12:35'), (68935, 2, '2019-2020', 3193, 1, 0, 0, '2020-05-29 15:12:35'), (79870, 2, '2019-2020', 206, 1, 0, 0, '2020-05-29 15:12:35'), (81138, 2, '2019-2020', 201, 1, 0, 0, '2020-05-29 15:12:35'), (84144, 2, '2019-2020', 201, 0, 1, 0, '2020-05-29 15:12:35'), (90162, 2, '2019-2020', 204, 1, 0, 0, '2020-05-29 15:12:35'), (97156, 2, '2019-2020', 3195, 1, 0, 0, '2020-05-29 15:12:35'), (97236, 2, '2019-2020', 207, 1, 0, 0, '2020-05-29 15:12:35'), (97401, 2, '2019-2020', 181, 1, 0, 0, '2020-05-29 15:12:35'), (97645, 2, '2019-2020', 3195, 1, 0, 0, '2020-05-29 15:12:35'), (107413, 2, '2019-2020', 193, 1, 0, 0, '2020-05-29 15:12:35'), (120408, 2, '2019-2020', 206, 1, 0, 0, '2020-05-29 15:12:35'), (121546, 2, '2019-2020', 188, 1, 0, 0, '2020-05-29 15:12:35'), (127225, 2, '2019-2020', 201, 0, 1, 0, '2020-05-29 15:12:35'), (130167, 2, '2019-2020', 209, 2, 0, 0, '2020-05-29 15:12:35'), (139192, 2, '2019-2020', 209, 2, 0, 0, '2020-05-29 15:12:35'), (142059, 2, '2019-2020', 206, 1, 0, 0, '2020-05-29 15:12:35'), (142654, 2, '2019-2020', 196, 1, 0, 0, '2020-05-29 15:12:35'), (142934, 2, '2019-2020', 188, 1, 0, 0, '2020-05-29 15:12:35'), (151438, 2, '2019-2020', 203, 1, 0, 0, '2020-05-29 15:12:35'), (184119, 2, '2019-2020', 3415, 1, 0, 0, '2020-05-29 15:12:35')]

sql:

INSERT INTO `football_player_top_data` (`id`,`competition_id`,`season`,`team`,`goals`,`penalties`,`assists`,`created_at`)  VALUES  (%s,%s,%s,%s,%s,%s,%s,%s)  ON DUPLICATE KEY UPDATE `team`=values(team),`goals`=values(goals),`penalties`=values(penalties),`assists`=values(assists),`created_at`=values(created_at)