Python => 值错误 : unsupported format character 'Y' (0x59)

Question

我不理解 Y 的 ValueError。我用 %... 转义...

table = town+"_history"
db.execute("SELECT DATE_FORMAT(snapdate,'%%Y-%%m-%%d') AS date, SUM( population ) AS accountpopulation, count( blockid ) AS number_block FROM %s WHERE blockid =%%s GROUP BY snapdate ORDER BY snapdate DESC LIMIT 7" % table, (blockid))

Answer 1

您将 %% 转义，然后将字符串用作格式化程序 first:

"...." % table,

返回一个新字符串，其中 %% 转义百分比被单个 % 字符替换。 MySQL 数据库适配器 (ab) 使用带有 % too 的字符串格式，因此它将获取该输出并期望能够填充 %s 带有转义 SQL 文字的插槽。在那里，您的 SQL 语句的 '%Y-%m-%d' 部分再次被解释为字符串格式并引发错误。

解决办法是加倍加倍:

db.execute("SELECT DATE_FORMAT(snapdate,'%%%%Y-%%%%m-%%%%d') AS date, SUM( population ) AS accountpopulation, count( blockid ) AS number_block FROM %s WHERE blockid = %%s GROUP BY snapdate ORDER BY snapdate DESC LIMIT 7" % table, (blockid,))

或者使用 str.format() 来避免双重转义:

db.execute("SELECT DATE_FORMAT(snapdate,'%%Y-%%m-%%d') AS date, SUM( population ) AS accountpopulation, count( blockid ) AS number_block FROM {0} WHERE blockid = %s GROUP BY snapdate ORDER BY snapdate DESC LIMIT 7".format(table), (blockid,))

这里 {0} 被表名替换，%% 转义保持不变；数据库适配器将使用 %s 槽填充 blockid 参数并返回一个 SQL 语句，其中 %% 转义变成单个 % 个字符。