python - 类型错误:格式字符串 Python3-MySQL 的参数不足

Question

我编写了一个 Web 应用程序，它接受输入并将其存储在 MySQL 数据库中。我在网络应用程序上创建了一个搜索字段，以便可以输入一个字符串，它将在所有列中搜索数据输入并返回它。

最初我用于搜索的 pymysql 脚本看起来像这样。

  try:
    with connection.cursor(pymysql.cursors.DictCursor) as cursor:
        sql = """SELECT * FROM main WHERE Concat(idDirectConnect_ID,
                                         CR_Number,
                                         VPC_Name,
                                         Creator_Name,
                                         Route_Domain,
                                         OSPF_ASN,
                                         OSPF_VLAN,
                                         OSPF_Subnet,
                                         BGP_VLAN,
                                         BGP_Subnet,
                                         BGP_AuthKey is null,
                                         AWS_Expected_Subnet,
                                         AWS_Acnt,
                                         Company_Name,
                                         Lpbck_Int,
                                         Lpbck_IPAddr,
                                         Tunnel_Num1,
                                         Tunnel_Num2,
                                         VPN_Tunnel1_Dest is null,
                                         VPN_Int1_CryptoKey is null,
                                         VPN_Tun1_IP is null,
                                         VPN_Tunnel2_Dest is null,
                                         VPN_Int2_CryptoKey is null,
                                         VPN_Tun2_IP is null,
                                         AWS_VPN_ConnectionID is null)
                                         LIKE %s;"""
        cursor.execute(sql, ('%{}%'.format(sql_var)))
    result = cursor.fetchall()
    return result
finally:
    connection.close()

除非列中的数据标记为空，否则此方法有效。这些列在第一次创建时为空，并且在一段时间内保持这种状态，但其他列中有可搜索的数据。

因此，我尝试将搜索查询更新为:

    try:
    with connection.cursor() as cursor:
        sql = """SELECT * FROM main WHERE (idDirectConnect_ID LIKE %s
                                         OR CR_Number LIKE %s
                                         OR VPC_Name LIKE %s
                                         OR Creator_Name LIKE %s
                                         OR Route_Domain LIKE %s
                                         OR OSPF_ASN LIKE %s
                                         OR OSPF_VLAN LIKE %s
                                         OR OSPF_Subnet LIKE %s
                                         OR BGP_VLAN LIKE %s
                                         OR BGP_Subnet LIKE %s
                                         OR AWS_Expected_Subnet LIKE %s
                                         OR AWS_Acnt LIKE %s
                                         OR Company_Name LIKE %s
                                         OR Lpbck_Int LIKE %s
                                         OR Lpbck_IPAddr LIKE %s
                                         OR Tunnel_Num1 LIKE %s
                                         OR Tunnel_Num2 LIKE %s
                                         OR BGP_AuthKey LIKE %s
                                         OR VPN_Tunnel1_Dest LIKE %s
                                         OR VPN_Int1_CryptoKey LIKE %s
                                         OR VPN_Tun1_IP LIKE %s
                                         OR VPN_Tunnel2_Dest LIKE %s
                                         OR VPN_Int2_CryptoKey LIKE %s
                                         OR VPN_Tun2_IP LIKE %s
                                         OR AWS_VPN_ConnectionID LIKE %s);"""
        cursor.execute(sql, ", ".join(["'%{}%'".format(sql_var)]*25))
    result = cursor.fetchall()
    return result
finally:
    connection.close()

但我得到:

query = query % self._escape_args(args, conn)
TypeError: not enough arguments for format string

我最初只有:

 cursor.execute(sql, ('%{}%'.format(sql_var)))

但我意识到没有足够的参数来支持所有 %s，因此我将cursor.execute 更改为:

 cursor.execute(sql, ", ".join(["'%{}%'".format(sql_var)]*25))

现在它回来了:

"'%206%', '%206%', '%206%', '%206%', '%206%', '%206%', '%206%', '%206%', '%206%', '%206%', '%206%', '%206%', '%206%', '%206%', '%206%', '%206%', '%206%', '%206%', '%206%', '%206%', '%206%', '%206%', '%206%', '%206%', '%206%'" 

我已经数了又数了，我也数了又重新数了 %s，应该足够了。

我已经阅读了尽可能多的类似的 SO 帖子和 MySQL 文档。此时我怀疑我可能需要将光标更改为executemany，但这只是一个猜测。

Answer 1

比尔，

谢谢，这为我指明了正确的方向。

这是我的工作代码:

try:
    with connection.cursor(pymysql.cursors.DictCursor) as cursor:
        sql = """SELECT * FROM main WHERE (idDirectConnect_ID LIKE %s
                                         OR CR_Number LIKE %s
                                         OR VPC_Name LIKE %s
                                         OR Creator_Name LIKE %s
                                         OR Route_Domain LIKE %s
                                         OR OSPF_ASN LIKE %s
                                         OR OSPF_VLAN LIKE %s
                                         OR OSPF_Subnet LIKE %s
                                         OR BGP_VLAN LIKE %s
                                         OR BGP_Subnet LIKE %s
                                         OR AWS_Expected_Subnet LIKE %s
                                         OR AWS_Acnt LIKE %s
                                         OR Company_Name LIKE %s
                                         OR Lpbck_Int LIKE %s
                                         OR Lpbck_IPAddr LIKE %s
                                         OR Tunnel_Num1 LIKE %s
                                         OR Tunnel_Num2 LIKE %s
                                         OR BGP_AuthKey LIKE %s
                                         OR VPN_Tunnel1_Dest LIKE %s
                                         OR VPN_Int1_CryptoKey LIKE %s
                                         OR VPN_Tun1_IP LIKE %s
                                         OR VPN_Tunnel2_Dest LIKE %s
                                         OR VPN_Int2_CryptoKey LIKE %s
                                         OR VPN_Tun2_IP LIKE %s
                                         OR AWS_VPN_ConnectionID LIKE %s);"""
        cursor.execute(sql, list(['%{}%'.format(sql_var)]*25))
    result = cursor.fetchall()
    return result
finally:
    connection.close()

我还必须将光标更改回 DictCursor，因为其余的 python 代码需要该格式。