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: stackoverflow.com/questions/589284/…. You cannot parameterize lists as is. — alecxe
– alecxe, Commented Sep 5, 2016 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
– Srecko, Commented Sep 5, 2016 at 18:08
For some reason, still getting an error: TypeError: not enough arguments for format string — Srecko
– Srecko, Commented Sep 5, 2016 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
– Srecko, Commented Sep 5, 2016 at 21:37

KiwiMartin · Accepted Answer · 2017-01-03 04:32:18Z

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

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 3, 2017 at 4:32

answered Jan 3, 2017 at 0:03

KiwiMartin

84211 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rich Andrews Over a year ago

PyMySQL 1.0 works this way and I think this is preferable to my answer below on creating expansion parameters.

Rich Andrews · Accepted Answer · 2021-02-24 20:51:04Z

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'

Collectives™ on Stack Overflow

pymysql select in with variable number of parameters

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related