pymysql选择可变数量的参数 [英] pymysql select in with variable number of parameters

问题描述

我阅读了几个示例，这些示例展示了 pymysql选择输入"应该如何工作.所以，这个例子工作得很好:

I read several examples that show how pymysql "select in" should work. So, this example works just fine:

 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"部分添加另一个参数.这里的问题是这个列表是可变的，所以不太确定如何处理这个列表.

But, I would also like to add another argument for this "NOT IN" part. The problem here is that this list is variable, so not quite sure how to handle this one.

推荐答案

使用 PyMySQL 0.7.9 版:

With PyMySQL version 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 文档描述了两种可能的形式:

The PyMySQL execute documentation describes the two possible forms:

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.

这篇关于pymysql选择可变数量的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！