python中多变量MySQL select

Question

我想使用以下类型的python脚本中的SELECT，包含2个变量：

• 带有ID的列表
• 计数

类似这样的事情：

SELECT * 
  FROM servers 
 WHERE id IN (32, 48, 49, 68) 
 GROUP 
    BY serial 
 ORDER 
    BY first_seen 
 LIMIT 4;

“in”中的ID的数量将会改变，“limit”也会根据服务的数量改变。（在本例中，我有4个服务器id，因此限制也是4个）

我尝试了以下（这是一种场景）：

list_serial_client = (24,347,359,360,362,363,365,418,388,23)


my_string = ','.join(map(str, list_serial_client)) 
count = len(list_serial_client)

tuple = ((my_string), count)
print(tuple)

q="""SELECT \
i.id,s.serial, i.persistent_uuid, b.id, u.patchnum, u.first_seen \
FROM  installs i LEFT JOIN serials s ON  s.hardware_id = i.hardware_id \
LEFT JOIN updates u ON u.install_id = i.id \
LEFT JOIN buildnumbers b ON b.image_uuid = u.image_uuid \
WHERE s.serial IN (%s) \
AND i.invalidated IS NULL GROUP BY s.serial, u.first_seen \
ORDER BY first_seen DESC LIMIT %s"""

mycursor.execute(q, tuple)

for x in mycursor:
    print(" >>  " + str(x))

不幸的是，它仅使用第一个ID的行（本例中为24)，并且忽略了限制

 >>  (44, 24, 'cdc9fd8d-6899-4d9c-89c3-3b71b2dd88c5', 144, 831)
 >>  (44, 24, 'cdc9fd8d-6899-4d9c-89c3-3b71b2dd88c5', 124, 448)
 >>  (44, 24, 'cdc9fd8d-6899-4d9c-89c3-3b71b2dd88c5', 106, 448)
 >>  (44, 24, 'cdc9fd8d-6899-4d9c-89c3-3b71b2dd88c5', 7, 448)

打印（元组）的输出如下:('24,347,359,360,362,363,365,418,388,23',10)

我想在in（）部分中有“24,347,359,360,362,363,365,418,388,23”，而10在极限处

谢谢

Answer 1

您可以使用String.Format或f-strings，如下所示：

>>> q = 'SELECT * FROM servers WHERE id IN {} GROUP BY serial ORDER BY first_seen LIMIT {};'
>>> list_serial_client =  (32, 48, 49, 68) 
>>> limit = len(list_serial_client)
>>> mysql_query = q.format(list_serial_client, limit)
>>> mysql_query
'SELECT * FROM servers WHERE id IN (32, 48, 49, 68) GROUP BY serial ORDER BY first_seen LIMIT 4;'
>>> 
>>> 
>>> list_serial_client =  (32, 48, 49, 68) 
>>> limit = len(list_serial_client)
>>> mysql_query = f'SELECT * FROM servers WHERE id IN {list_serial_client} GROUP BY serial ORDER BY first_seen LIMIT {limit};'
>>> mysql_query
'SELECT * FROM servers WHERE id IN (32, 48, 49, 68) GROUP BY serial ORDER BY first_seen LIMIT 4;'
>>>