MySQLdb doesn’t return all the arguments converted using "upon re-updating the key",

Question

With the MySQLdb package in python, I want to insert records that validate some unique keys. The method I used is execution. The arguments are sql clause and tuple. But when I executed it, he raised an error, which said that "not all arguments are converted." The codes are as follows:

dData = [[u'Daniel', u'00-50-56-C0-00-12', u'Daniel']]
sql = "INSERT INTO app_network_white_black_list (biz_id, shop_id, type, mac_phone, remarks, create_time) " \
      "VALUES ({bsid}, {shop_id}, {type}, %s, %s, NOW()) " \
      "ON DUPLICATE KEY UPDATE type={type}, remarks=%s, create_time=NOW()".format(bsid=bsid, shop_id=shop_id, type=dType)
cur.executemany(sql, tuple(dData))

Someone said that this is a mistake. But they did not give me a way to jump over it. Provide a method if this is an error.

Answer 1

What's going wrong

MySQLdb 1.2.4b4 1.2.5, unubtu answer, , cursors.py. , , , , , , .

, , VALUES ( ... ) INSERT . , executemany , . I.e., , :

INSERT INTO table
  (foo, bar, ...)
VALUES
  (%s, %s, ...);

, :

INSERT INTO table
  (foo, bar, ...)
VALUES
  (1, 2, ...),
  (3, 4, ...),
  (5, 6, ...),
  ...;

, , , executemany , , VALUES. , :

INSERT INTO table
  (foo, bar, ...)
VALUES
  (%s, %s, ...)
ON DUPLICATE KEY UPDATE baz=%s;

:

INSERT INTO table
  (foo, bar, ...)
VALUES
  (1, 2, ...),
  (3, 4, ...),
  (5, 6, ...),
  ...
ON DUPLICATE KEY UPDATE baz=%s;

, MySQLdb . VALUES ( ... ), MySQLdb (%s, %s, ...), , UPDATE.

VALUES executemany, TypeError, . , INSERT ... ON DUPLICATE UPDATE VALUES, UPDATE %s placeholder. MySQL.

, MySQLdb 1.2.3c1 . , , , , . , executemany , , execute .

, 1.2.3c1, . , .

, unubtu Q & A, , , .

, VALUES() UPDATE. , , ( MySQL docs).

, :

dData = [[u'Daniel', u'00-50-56-C0-00-12', u'Daniel']]  # exact input you gave

sql = """
INSERT INTO app_network_white_black_list
  (biz_id, shop_id, type, mac_phone, remarks, create_time)
VALUES
  (%s, %s, %s, %s, %s, NOW())
ON DUPLICATE KEY UPDATE
  type=VALUES(type), remarks=VALUES(remarks), create_time=VALUES(create_time);
"""  # keep parameters in one part of the statement

# generator expression takes care of the repeated values
cur.executemany(sql, ((bsid, shop_id, dType, mac, rem) for mac, rem in dData))

, UPDATE , MySQLdb insert .

:

• executemany; .
• SQL Python, ; , .
• , ? , .
• , NOW(). , CURRENT_TIMESTAMP DEFAULT . . , , .
• UPDATE - , (t212) UPDATE VALUES - 'll execute executemany.

Answer 2

dData, %s .