Python预备语句. SELECT IN的问题 [英] Python Prepared Statements. Problems with SELECT IN

问题描述

我在使用Python编写的语句存在问题，到目前为止我还无法解决.

I'm having an issue with a prepared statement in Python I can't solve so far.

应执行的查询例如:

 SELECT md5 FROM software WHERE software_id IN (1, 2, 4)

所以我试图执行这样的查询:

So I tried to execute a Query like this:

software_id_string = "(2, 3, 4)"
cursor.execute("SELECT md5 FROM software WHERE software_id IN %s", 
                software_id_string)

问题是在字符串->'(2，3，4)'中添加了''，因此查询将是:

The Problem is that there are '' added to the string --> '(2, 3, 4)', so that the Query will be:

SELECT md5 FROM software WHERE software_id IN ''(2, 3, 4)''

我也试图像这样重建脚本:

I've also tried to rebuild the Script like this:

software_id_string = " 1 OR software_id = 2"
cursor.execute("SELECT md5 FROm software WHERE software_id = %s", 
              software_id_string)

这仅适用于将要提交的第一个ID(在本例中为1)，因为OR部分不会插入为SQL语句...

This works only for the first id, which will be submitted (in this case 1), because the OR-part won't be interpeted as an SQL Statement...

是否有可能用准备好的语句解决问题?

Is there any possibility to fix the issues with the prepared statements?

推荐答案

参数列表中的每个项目都需要一个占位符.
您可以使用字符串操作来完成该部分:

You need one placeholder for each item in your parameter list.
You can use string operations to get that part done:

1. 为每个参数创建一个%s，然后
2. 用逗号将它们连起来.

1. Create one %s for each parameter, and
2. Join those together with a comma.

下一步，您可以按照 DB-API文档.

software_id_string = (1,2,4)
qry = '''SELECT md5 
           FROM software 
          WHERE software_id IN (%s)''' % ','.join(['%s']*len(software_id_string))
# // 'SELECT md5 FROM software WHERE software_id IN (%s,%s,%s)'
cursor.execute(qry, software_id_string)

这篇关于Python预备语句. SELECT IN的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！