python - pymysql select in 具有可变数量的参数

Question

我阅读了几个示例，这些示例展示了 pymysql“select in”应该如何工作。所以，这个例子工作得很好:

 sql_select = 'SELECT a.user_id, AVG(a.rcount) AS \'average\' ' \
                 'FROM (SELECT user_id, item_id, count(*) AS rcount ' \
                 'FROM submission AS qsm ' \
                 'JOIN metadata as qm   ' \
                 'ON qsm.item_id = qm.id ' \
                 'WHERE qsm.item_id NOT IN (1, 2, 5, 6, 7, 147, 148) ' \
                 'AND DATE(FROM_UNIXTIME(submission_time)) BETWEEN %s AND %s ' \
                 'AND qm.type != \'survey\' ' \
                 'GROUP BY user_id, item_id ' \
                 'ORDER BY user_id) a ' \
                 'GROUP BY a.user_id'
    args = [course_start, course_end]
    cur.execute(sql_select, args)

但是，我还想为这个“NOT IN”部分添加另一个论点。这里的问题是这个列表是可变的，所以不太确定如何处理这个列表。

Answer 1

使用 PyMySQL 0.7.9 版:

cells = ('cell_1', 'cell_2')
cursor.execute('select count(*) from instance where cell_name in %(cell_names)s;', {'cell_names': cells})
# or alternately
cursor.execute('select count(*) from instance where cell_name in %s;', [cells])

PyMySQL execute 文档描述了两种可能的形式:

If args is a list or tuple, %s can be used as a placeholder in the query. 
If args is a dict, %(name)s can be used as a placeholder in the query.