使用Python进行SQL多次插入 [英] SQL multiple inserts with Python
问题描述
更新
根据Nathan的建议，在将execute()传递给行的列表之后，下面的代码将进一步执行，但仍会卡在execute函数上.错误消息显示为:
UPDATE
After passing execute() a list of rows as per Nathan's suggestion, below, the code executes further but still gets stuck on the execute function. The error message reads:
query = query % db.literal(args)
TypeError: not all arguments converted during string formatting
因此它仍然无法正常工作.有人知道为什么现在出现类型错误吗?
END UPDATE
So it still isn't working. Does anybody know why there is a type error now?
END UPDATE
我有一个很大的.xls格式的邮件列表.我在xlrd中使用python从xls文件中检索名称和电子邮件到两个列表中.现在，我想将每个名称和电子邮件都放入mysql数据库中.我正在为这部分使用MySQLdb.显然，我不想为每个列表项都执行插入语句.
这是我到目前为止所拥有的.
I have a large mailing list in .xls format. I am using python with xlrd to retrieve the name and email from the xls file into two lists. Now I want to put each name and email into a mysql database. I'm using MySQLdb for this part. Obviously I don't want to do an insert statement for every list item.
Here's what I have so far.
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()的消息而被拒绝.
The problem when the cursor executes. This is the error: _mysql_exceptions.OperationalError: (1241, 'Operand should contain 1 column(s)') I tried putting my query into a var initially, but then it just barfed up a message about passing a tuple to execute().
我在做什么错?甚至有可能吗?
列表很大，我绝对负担不起将插入内容放入循环中.我看着使用LOAD DATA INFILE，但是我真的不明白如何格式化文件或查询，并且当我不得不阅读MySQL文档时我的眼睛流血了.我知道我可能会使用一些在线xls到mysql转换器，但这也是我的一项学习练习. 有没有更好的方法?
The list is huge and I definitely can't afford to put the insert into a loop. I looked at using LOAD DATA INFILE, but I really don't understand how to format the file or the query and my eyes bleed when I have to read MySQL docs. I know I could probably use some online xls to mysql converter, but this is a learning exercise for me as well. Is there a better way?
推荐答案
要修复TypeError: not all arguments converted during string formatting-您需要使用cursor.executemany(...)方法，因为它接受一个可迭代的元组(多于一行)，而
To fix TypeError: not all arguments converted during string formatting - you need to use the cursor.executemany(...) method, as this accepts an iterable of tuples (more than one row), while cursor.execute(...) expects the parameter to be a single row value.
执行命令后，您需要确保使用db.commit()提交事务以使更改在数据库中处于活动状态.
After the command is executed, you need to ensure that the transaction is committed to make the changes active in the database by using db.commit().
这篇关于使用Python进行SQL多次插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
