CODEWITHSUNDEEP
pythonpymysql
Srecko
Srecko

Reputation: 199

pymysql select in with variable number of parameters

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.

Upvotes: 3

Views: 10786

Answers (2)

Rich Andrews
Rich Andrews

Reputation: 1680

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.

  1. create parameter expansions
  2. format them into query string
  3. 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'

Upvotes: 1

KiwiMartin
KiwiMartin

Reputation: 832

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.

Upvotes: 8

Related Questions