Python MySQLdb 不会插入所有记录

Question

我被困在Python脚本上了。此脚本应该填充从 select 语句获取值的 MySQL 表。

问题是当我从 select 语句中获取值时，插入语句无法正常工作并且没有插入所有记录。我已经在MySQL WorkBench中检查了相同的选择语句，并返回了我要放入目标表中的正确数量的记录。select 语句是这样的，预期的行数超过 100.000(是一个大数据库(：

SELECT MAX(c1),MAX(c2),MAX(c3),MAX(c4),c5,c6,MAX(c7)
FROM Table1 as t1
INNER JOIN Table2 as t2 ON t1.Id = t2.Id
INNER JOIN Table3 as t3 ON t1.Id = t3.Id
WHERE c4 NOT LIKE '%A%'
GROUP BY t1.c1;

此 select 语句应返回不包含"A"字符的记录。

这是我的Python代码：

import MySQLdb
db = MySQLdb.connect(host,user,pass,dbName)
cursor = db.cursor()
cursor.execute("SELECT MAX(c1),MAX(c2),MAX(c3),MAX(c4),c5,c6,MAX(c7)
FROM Table1 as t1
INNER JOIN Table2 as t2 ON t1.Id = t2.Id
INNER JOIN Table3 as t3 ON t1.Id = t3.Id
WHERE c4 NOT LIKE '%A%'
GROUP BY t1.c1")
results = cursor.fetchall()
for row in results:
cursor.execute("INSERT INTO DestinationTable(col1,col2,col3,col4,col5,col6)VALUES('%s','%s','%s','%s','%s','%s')"
%(str(row[1]),str(row[2]),str(row[3]),str(row[4]),str(row[5]),str(row[6]))
db.commit()

我认为问题出在插入语句中，但我不确定。

Answer 1

只需运行一个INSERT INTO ... SELECT而不是不需要循环或SQL字符串插值的...VALUES(无论如何都应该参数化(。

下面的MAX(c1)从语句中删除SELECT因为您在尝试查询中跳过它：

sql = "INSERT INTO DestinationTable(col1, col2, col3, col4, col5, col6)
SELECT MAX(c2), MAX(c3), MAX(c4), c5, c6, MAX(c7)
FROM Table1 as t1
INNER JOIN Table2 as t2 ON t1.Id = t2.Id
INNER JOIN Table3 as t3 ON t1.Id = t3.Id
WHERE c4 NOT LIKE '%A%'
GROUP BY t1.c1"
cursor.execute(sql)
db.commit()