类型错误: %d 格式:数字是必需的，而不是 str语言 - DB 字段是字符串，传递的变量是字符串格式的数字

Question

我有一个查询，该查询基于字符串字段进行过滤，其中包含前导为零的电话号码。我的查询使用%s作为占位符，变量在执行时传递给查询，如下所示：

rows = (('01234567891',), ('01234567892',), ('01234567893',))
dbQuery2 = """
SELECT DATE_FORMAT(DATE(ch.start), '%d/%m/%Y') As CallDate,
ch.did AS InboundNo,
COUNT(*) AS DayTotal
FROM call_history ch LEFT JOIN
ast_queue_log aql
ON aql.event = 'ENTERQUEUE' AND aql.callid = ch.callid
WHERE ch.did = %s AND
ch.start BETWEEN DATE_FORMAT(NOW(), '%Y-%m-01') AND NOW()
GROUP BY ch.did, DATE(ch.start)
ORDER BY ch.did, DATE(ch.start)
;
"""
for row in rows:
cur2.execute(dbQuery2, row)
subrows = cur.fetchall()

导致问题的数据库字段是：

`did` varchar(32) NOT NULL DEFAULT '',`

错误是：

TypeError: %d format: a number is required, not str

Answer 1

这里的问题是 SQL 查询中的DATE_FORMAT字符串。%d、%m和%Y被解释为变量占位符，MySQLdb引擎试图替换变量，而不是WHERE子句中的变量。

通过在查询中使用以下格式进行修复：

dbQuery2 = """
SELECT DATE_FORMAT(DATE(ch.start), '%%d/%%m/%%Y') As CallDate,
ch.did AS InboundNo,
COUNT(*) AS DayTotal
FROM call_history ch LEFT JOIN
ast_queue_log aql
ON aql.event = 'ENTERQUEUE' AND aql.callid = ch.callid
WHERE ch.did = %s AND
ch.start BETWEEN DATE_FORMAT(NOW(), '%%Y-%%m-01') AND NOW()
GROUP BY ch.did, DATE(ch.start)
ORDER BY ch.did, DATE(ch.start)
;

"">