pymysql select in with variable number of parameters

Question

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)

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.

I think this is very much related: http://stackoverflow.com/questions/589284/imploding-a-list-for-use-in-a-python-mysqldb-in-clause. You cannot parameterize lists as is. — alecxe, Sep 05 '16 at 16:31
Not sure why haven't tried that one... I was concerned about the second part, adding those dates. But, guess it should work. — Srecko, Sep 05 '16 at 18:08
For some reason, still getting an error: TypeError: not enough arguments for format string — Srecko, Sep 05 '16 at 21:10
Also tried this cur.execute(sql_select % (args, course_start, course_end)), but it seems like this approach completely skips the first argument (the one obtained from list). — Srecko, Sep 05 '16 at 21:37

KiwiMartin · Answer 1 · 2017-01-03T04:32:18.733

6

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])

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.

edited Jan 03 '17 at 04:32

answered Jan 03 '17 at 00:03

KiwiMartin

804
9
15

PyMySQL 1.0 works this way and I think this is preferable to my answer below on creating expansion parameters. – Rich Andrews Feb 24 '21 at 20:56

Rich Andrews · Answer 2 · 2021-02-24T20:51:04.417

Although this question is old, it is easy to get confused given the variety of current and now old MySQL libraries and how they do parameter handling. The above question is for PyMySQL, not MySQLConnector. Although the reference in the comments of the question provides good guidance, a PyMySQL==1.0.2 strategy is addressed below that should also work with MySQLConnector.

create parameter expansions
format them into query string
pass unpacked values to cursor.execute() for sql injection checking

It looks like:

    in_params = [1,2,3,4]
    with closing(db.cursor()) as c:
        expansions = ",".join(["%s"] * len(in_params))
        q = "select 1 from information_schema.processlist where id in ({}) and host = %s".format(expansions)
        c.execute(q, (*in_params, 'localhost'))
        print(c._last_executed)

And this will provide:

select 1 from information_schema.processlist where id in (1,2,3,4) and host = 'localhost'

pymysql select in with variable number of parameters

2 Answers2

Linked