TypeError：在使用参数插入时，并非在字符串格式化期间转换所有参数

I'm trying to insert an interger, a string and a list as a single record into a database, but am striking an error.

我正在尝试将一个整数，一个字符串和一个列表作为单个记录插入到数据库中，但是我发现了一个错误。

Here is my code:

这是我的代码：

values=nprnd.randint(10, size=48) #NUMPY ARRAY
valuelist= map(None, values); #Convert to list
guid=''.join(random.choice(string.ascii_uppercase + string.digits) for x in range(20)) #Generate guid
for x in range(4):
    var_string = ', '.join('?' * 48)
    valuelist.insert(0,x)
    valuelist.insert(0,ent_guid)
    #50 coloums in table, guid, x and 48 randomly generated values
    query_string = 'INSERT INTO schema1 VALUES (%s,%d,%s);' % (guid, x, var_string) 
    cursor.execute(query_string, valuelist)

I keep getting an error saying:

我一直收到错误说：

Traceback (most recent call last):
  File "script.py", line 19, in <module>
    cursor.execute(query_string, valuelist)
  File "/Library/Python/2.7/site-packages/MySQL_python-1.2.4b4-py2.7-macosx-10.8-intel.egg/MySQLdb/cursors.py", line 184, in execute
    query = query % db.literal(args)

I know the cause of this error (Even SO has a few questions on the same error), but none of the solutions I've tried have solved this error for me

我知道这个错误的原因（即使SO对同一个错误也有一些问题），但我尝试过的解决方案都没有为我解决这个错误

Any help would be appreciated

任何帮助，将不胜感激

4 个解决方案

#1

I believe MySQL-python uses printf format codes ('%s' for example) instead of '?' for placeholders. Try setting var_string like this:

我相信MySQL-python使用printf格式代码（例如'％s'）而不是'？'对于占位符。尝试像这样设置var_string：

    var_string = ', '.join(['%s'] * 48)

I also agree with Endophage that valuelist appears to be too long. I don't think you need to insert al and ent_guid.

我同意Endophage的观点，即估价师似乎太长了。我认为你不需要插入al和ent_guid。

You also need to put quotes around the guid when you insert it:

插入时还需要在guid周围加上引号：

    query_string = 'INSERT INTO schema1 VALUES (\'%s\',%d,%s);' % (guid, x, var_string)

#2

After you generate your 48 '?'s you insert 2 more elements into valuelist, have you taken those into account? The following code will be more robust:

在生成48'后，您将2个元素添加到评估者中，是否考虑过这些元素？以下代码将更加健壮：

values=nprnd.randint(10, size=48) #NUMPY ARRAY
valuelist= map(None, values); #Convert to list
guid=''.join(random.choice(string.ascii_uppercase + string.digits) for x in range(20)) #Generate guid
for x in range(4):
    valuelist.insert(0,al)
    valuelist.insert(0,ent_guid)

    # moved this line and using len(valuelist)
    var_string = ', '.join('?' * len(valuelist)) 

    #50 coloums in table, guid, x and 48 randomly generated values
    query_string = 'INSERT INTO schema1 VALUES (%s,%d,%s);' % (guid, x, var_string) 
    cursor.execute(query_string, valuelist)

Update:

更新：

From you comment below, it sounds like you're trying to double insert the guid and x values, therefore, change the query_string assignment (with the other changes I also made above) to:

从你的评论下面，听起来你正试图双重插入guid和x值，因此，将query_string赋值（与我上面做的其他更改）更改为：

query_string = 'INSERT INTO schema1 VALUES (%s);' % (var_string)

This is safer than your current string interpolation as cursor.execute will ensure the values are appropriately escaped.

这比当前的字符串插值更安全，因为cursor.execute将确保适当地转义值。

#3

It looks like you're increasing the size of valuelist by 2 each time you go through the loop.

看起来你每次循环时都会将值列表的大小增加2。

valuelist.insert adds a new item to the start of an existing list, so first time through the loop, valuelist contains 50 elements, then 52 the next, 54 the next, etc.

valuelist.insert将一个新项添加到现有列表的开头，因此第一次循环时，valuelist包含50个元素，然后是52个元素，54个是下一个元素，等等。

query_string only contains placeholders for 48 items though, so this will cause an error.

query_string只包含48个项目的占位符，因此这将导致错误。

If you remove those lines, and change the '?' to '%s', you should have better results, e.g.:

如果删除这些行，并更改“？”到'％s'，你应该有更好的结果，例如：

for x in range(4):
    var_string = ', '.join('%s' * 48) 
    query_string = "INSERT INTO schema1 VALUES ('%s',%d,%s);" % (guid, x, var_string) 
    cursor.execute(query_string, valuelist)

#4

-1

Try surrounding your values in single quotes in the query.

尝试在查询中用单引号括起您的值。

query_string = "INSERT INTO schema1 VALUES ('%s','%d','%s');" % (guid, x, var_string)

#1