WHERE IN clause in pymysql returns last row

Question

Consider a table users of 6 rows

   +_______________________+
   |     userid |  name    |
   +-----------------------+
   |       1    | john     |
   |       2    | steve    |
   |       3    | joe      |
   |       4    | jason    |
   |       5    | abraham  |
   |       6    | leonard  |
   +-----------------------+

I am using the below SQL query:

SELECT userid,name FROM users where userid IN (2,3,4,5);

which returns 4 rows -

   |       2    | steve    |
   |       3    | joe      |
   |       4    | jason    |
   |       5    | abraham  |

Pymysql equivalent code is as below:

def get_username(user_ids):
    data=[]
    conn = init_db()
    cur = conn.cursor(pymysql.cursors.DictCursor)
    cur.executemany("SELECT userid,name from users WHERE userid IN (%s)",user_ids)
    rows=cur.fetchall()
    for row in rows:
        data.append([row['userid'],row['name']])
    cur.close()
    conn.close()
    return data

user_ids=[2,3,4,5]
get_usernames(user_ids)

This code just returns the last row [[5,abraham]] . How can I fetch all those rows?.

Answer 1

That's the (partly documented) behaviour of .executemany():

Help on method executemany in module pymysql.cursors:

executemany(self, query, args) method of pymysql.cursors.Cursor instance

Run several data against one query

:param query: query to execute on server

:param args: Sequence of sequences or mappings. It is used as parameter.

:return: Number of rows affected, if any.

This method improves performance on multiple-row INSERT and REPLACE. Otherwise it is equivalent to looping over args with execute().

So what you want here is cursor.execute() - but then, you have a bit more work to build your SQL query:

user_ids = (2, 3, 4, 5)
placeholders = ", ".join(["%s"] * len(user_ids)) 
sql = "SELECT userid,name from users WHERE userid IN ({})".format(placeholders)
cursor.execute(sql, user_ids)
data = list(cursor)

Note that cursors are iterables, so you don't need to explicitely call cursor.fetchall() then iterate on the result, you can iterate directly on the cursor. Also note that if you want a list of (id, name) tuples, using a DictCursor is just a double waste of CPU cycles (once for building the dicts and once for rebuilding tuples out of them), you could just use a default cursor and return list(cursor) instead.