Executemany SELECT queries with psycopg2

Question

I have a large postgresql DB of users that I connect with using psycopg2. I need to retrieve (SELECT) the information of a specific large subset of users (>200). I am provided with a list of ids and I need to return the age of each of those users. I put down a working solution:

conn = psycopg2.connect("dbname= bla bla bla")
cur = conn.cursor()

for user_id in interesting_users:
    qr =  "SELECT age FROM users WHERE country_code = {0} AND user_id = {1}".format(1, user_id)
    cur.execute(qr)
    fetched_row = cur.fetchall()
    #parse results

This solution works fine, however it is not ideal when the length of interesting_users is large. I am looking for a more efficient approach than executing multiple queries. One solution would be to create a single query by appending all the user ids:

for user_id in interesting_users:
    query += "OR user_id {0}".format(user_id)

But I was hoping for a more elegant solution.

I found that psycopg2 provides the executemany() method. So, I tried to apply to my problem. However, I can't manage to make it work. This:

cur.executemany("SELECT age FROM users WHERE country_code = %s AND user_id = %s",[(1, user_id) for user_id in interesting_users])
r = cur.fetchall()

returns:

r = cur.fetchall()
psycopg2.ProgrammingError: no results to fetch

So, can executemany() be used for a SELECT statement? If yes, what's wrong with my code? If no, how can I perform multiple SELECT queries at once? Note: ids in interesting_users have no order so I can't use something like WHERE id < ...

SOLUTION:

query =  "SELECT age FROM users WHERE country_code = {0} AND user_id IN ({1});".format(1, ",".join(map(str, interesting_users)))
cur.execute(query)
fetched_rows = cur.fetchall()

Never format parameters into a SQL statement, use parameters, as in your second example. — Daniel, May 14 '18 at 16:28

score 3 · Answer 1 · answered May 14 '18 at 16:32

3

executemany works only with INSERT, not SELECT. Use IN:

cur.executemany("SELECT age FROM users WHERE country_code = %s AND user_id IN ({})".format(','.join(['%s'] * len(interesting_users)),
    [1] + interesting_users)
r = cur.fetchall()

answered May 14 '18 at 16:32

Daniel

42,087
4
55
81

Thanks for the answer, but I get `TypeError: Required argument 'vars_list' (pos 2) not found` even if the first argument is "'%s,%s,%s,%s'" and the second is a list [1, 1526081542, 1526309772, 1526151777, 1526254283]. Any idea why? – Titus Pullo May 14 '18 at 16:39
Check your parens, sounds like a parentheses in the wrong place – Michael Robellard May 14 '18 at 20:06
I fixed the parenthesis error, and I have to pass the list as a tuple, otherwise I get `"TypeError: 'int' object does not support indexing"`. However, I still get `psycopg2.ProgrammingError: no results to fetch"` when using `cur.executemany("SELECT age FROM users WHERE country_code = %s AND user_id IN ({})".format(','.join(['%s'] * len(interesting_users))), ([1] + interesting_users,))`. Any idea why? – Titus Pullo May 15 '18 at 11:20

Executemany SELECT queries with psycopg2

1 Answers1