MySQL + Python: Not all arguments converted during string formatting

时间:2022-10-07 23:14:55

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


3  

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())

#1


3  

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())