使用Python进行SQL多次插入

Question

更新
在按照Nathan的建议将行列表execute（）传递给execute（）之后，下面的代码将进一步执行，但仍然停留在execute函数上。错误消息如下：

    query = query % db.literal(args)
TypeError: not all arguments converted during string formatting

所以还是不行。有人知道为什么现在会有类型错误吗？
结束更新

我有一个.xls格式的大邮件列表。我使用python和xlrd将xls文件中的名称和电子邮件检索到两个列表中。现在我想把每个名字和电子邮件放到一个mysql数据库中。我用MySQLdb来做这部分。显然，我不想为每个列表项都做insert语句。
这是我目前所掌握的信息。

from xlrd import open_workbook, cellname
import MySQLdb

dbname = 'h4h'
host = 'localhost'
pwd = 'P@ssw0rd'
user = 'root'

book = open_workbook('h4hlist.xls')
sheet = book.sheet_by_index(0)
mailing_list = {}
name_list = []
email_list = []

for row in range(sheet.nrows):
    """name is in the 0th col. email is the 4th col."""
    name = sheet.cell(row, 0).value  
    email =  sheet.cell(row, 4).value
    if name and email:
        mailing_list[name] = email

for n, e in sorted(mailing_list.iteritems()):
    name_list.append(n)
    email_list.append(e)

db = MySQLdb.connect(host=host, user=user, db=dbname, passwd=pwd)
cursor = db.cursor()
cursor.execute("""INSERT INTO mailing_list (name,email) VALUES (%s,%s)""",
              (name_list, email_list))

光标执行时的问题。这是一个错误：_mysql_exceptions.OperationalError: (1241, 'Operand should contain 1 column(s)')我最初尝试将查询放入一个var中，但随后它只是弹出了一条关于将元组传递给execute（）的消息。

我做错什么了？这可能吗？

这个列表非常庞大，我绝对负担不起把insert放入循环中。我看了使用加载数据填充，但我真的不知道如何格式化文件或查询，当我不得不阅读MySQL文档时，我的眼睛流血了。我知道我可以使用一些在线xls-to-mysql转换器，但这对我来说也是一个学习练习。有更好的方法吗？

Answer 1

要修复TypeError: not all arguments converted during string formatting-您需要使用cursor.executemany(...)方法，因为它接受一个元组的iterable（多行），而cursor.execute(...)期望参数是一个单行值。

执行命令后，需要使用db.commit()确保事务已提交，以使更改在数据库中处于活动状态。

Answer 2

您需要给^{}一个行列表。你不需要把名字和电子邮件分成单独的列表，只需要创建一个包含两个值的列表。

rows = []

for row in range(sheet.nrows):
    """name is in the 0th col. email is the 4th col."""
    name = sheet.cell(row, 0).value  
    email =  sheet.cell(row, 4).value
    rows.append((name, email))

db = MySQLdb.connect(host=host, user=user, db=dbname, passwd=pwd)
cursor = db.cursor()
cursor.executemany("""INSERT INTO mailing_list (name,email) VALUES (%s,%s)""", rows)

更新：正如@JonClements提到的，应该是executemany()而不是execute()。

Answer 3

如果您对代码的高性能感兴趣，这个答案可能更好。

与excutemany方法相比，下面的execute将快得多：

INSERT INTO mailing_list (name,email) VALUES ('Jim','jim@yahoo.com'),('Lucy','Lucy@gmail.com')

您可以很容易地修改@Nathan Villaescusa的答案并获得新代码。

cursor.execute("""INSERT INTO mailing_list (name,email) VALUES (%s)""".format(",".join(str(i) for i in rows))

以下是我自己的测试结果：

excutemany:10000 runs takes 220 seconds

execute:10000 runs takes 12 seconds.

速度差大约是15倍。