MySQL + Python: Not all arguments converted during string formatting

I am trying to do an INSERT into MySQL using the cursor.execute(sql, args) syntax. This is what I currently have:

我试图使用cursor.execute（sql，args）语法对MySQL进行INSERT。这就是我目前所拥有的：

# data = {...}
qmarks = ', '.join('?' * len(data))   
query = "INSERT INTO title (%s) VALUES (%s)" %(qmarks, qmarks) 
args = data.keys() + data.values()
print len(args), print len(data)
cursor.execute(query, args)

TypeError: not all arguments converted during string formatting

I checked the length of args (22) and quarks (11 x 2 = 22), and they seem to be the same. What is causing this error, and what do I need to change?

我检查了args（22）和夸克（11 x 2 = 22）的长度，它们看起来是一样的。导致此错误的原因是什么，我需要更改什么？

Update: When I try and do the string formatting using %s, it also throws an error:

更新：当我尝试使用％s进行字符串格式化时，它也会抛出一个错误：

>>> data={'provider':'asdf', 'vendor_id': '1234'}
>>> format = ', '.join(['%s'] * len(data)) 
>>> query = "INSERT INTO title (%s) VALUES (%s)" %(format, format)
>>> query
'INSERT INTO title (%s, %s) VALUES (%s, %s)'
>>> cursor.execute(query, args)
Traceback (most recent call last):
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax 
to use near ''vendor_id', 'provider') VALUES ('1234', 'asdf')' at line 1")

I think this has to do with the fact that the column definitions are strings:

我认为这与列定义是字符串这一事实有关：

INSERT INTO title ('vendor_id', 'provider') VALUES ('1234', 'asdf')

instead of:

代替：

INSERT INTO title (vendor_id, provider) VALUES ('1234', 'asdf')

Is there a way to use string formatting here but pass a variable instead of a string?

有没有办法在这里使用字符串格式，但传递变量而不是字符串？

1 个解决方案

#1

Assuming you are using MySQLdb, you want to have %s in the final query, not ?, you will also need to enter the column names directly into the query instead of using the %s replacement for them:

假设您正在使用MySQLdb，您希望在最终查询中使用％s，而不是？，您还需要在查询中直接输入列名，而不是使用％s替换它们：

# data = {...}
columns = ', '.join(data.keys())
format = ', '.join(['%s'] * len(data))
query = "INSERT INTO title (%s) VALUES (%s)" % (columns, format)
cursor.execute(query, data.values())