How to use variables and OR cunjunctions in SQL statement in Python?

Question

I have a list of Ids in a list named res that I want to use line by line as WHERE conditions on a SQL query before saving the results in an array :

                              ids
grupos                           
0       [160, 161, 365, 386, 471]
1                      [296, 306]

Here is what I tried to insert it in a SQL query :

listado = [None]*len(res)
# We store the hashtags that describes the best the groups
# We iterate on the people of a group to construct the WHERE condition
print "res : ", res
for i in (0,len(res)):        

conn = psycopg2.connect(**params)
cur = conn.cursor()

listado = [None]*len(res)
for i in (0,len(res)):        
    print "res[i:p] : ", res.iloc[i]['ids']
    cur.execute("""SELECT COUNT(swipe.eclipse_id), subscriber_hashtag.hashtag_id  FROM subscriber_hashtag
    -- join para que las publicidades/eclipses que gusta un usarios estan vinculadas con las de la tabla de correspondencia con los hashtag
    INNER JOIN eclipse_hashtag ON eclipse_hashtag.hashtag_id = subscriber_hashtag.hashtag_id
    -- join para que los usarios  estan vinculados con los de la tabla de correspondencia con los hashtag
    LEFT OUTER JOIN swipe ON subscriber_hashtag.subscriber_id = swipe.subscriber_id
    -- recobremos los "me gusta"
    WHERE subscriber_hastag.subscriber_id in (%s)
    GROUP BY subscriber_hashtag.hashtag_id
        ORDER BY COUNT(swipe.eclipse_id) DESC;""",(res.iloc[i]['ids']))

    n = cur.fetchall()
    listado[i] = [{"count": elem[0], "eclipse_id": elem[1]} for elem in n]

Data for a reproducible example

Providing the further data informations :

subscriber_id hashtag_id
160           345
160           347
161           345
160           334
161           347
306           325
296           362
306           324
296           326
161           322
160           322

The output should, here, be like :

{0:[324,1],[325,1],[326,1],[362,1], 1 : [345,2],[347,2],[334,1]}

Current error message

ERROR: An unexpected error occurred while tokenizing input The following traceback may be corrupted or invalid The error message is: ('EOF in multi-line string', (1, 50))

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-44-f7c3c5b81303> in <module>()
     39         WHERE subscriber_hastag.subscriber_id in (%s)
     40         GROUP BY subscriber_hashtag.hashtag_id
---> 41             ORDER BY COUNT(swipe.eclipse_id) DESC;""",(res.iloc[i]['ids']))
     42 
     43         n = cur.fetchall()

TypeError: not all arguments converted during string formatting

@ArpitSolanki I just added the error message, but the query was known to be not working as far as I don't know how many conditions I will have with the WHERE statement — Antoine Coppin
– Antoine Coppin, Commented Jul 3, 2017 at 14:20
` "me gusta"` check on this line. You sure you are using the raw string correctly? — Arpit Solanki
– Arpit Solanki, Commented Jul 3, 2017 at 14:23
@ArpitSolanki I am not sure to understand very well the question but everything is okay on this line, it is a comment, the one which is not is the WHERE as far as it dosn't know where to stop. — Antoine Coppin
– Antoine Coppin, Commented Jul 3, 2017 at 14:34

Ilja Everilä · Accepted Answer · 2017-07-04 18:50:37Z

3

Have a look at tuples adaptation:

Python tuples are converted into a syntax suitable for the SQL IN operator and to represent a composite type:

Pass ids as a tuple query argument, so your argument to execute is a 1-tuple of tuple of ids, and drop the manual parentheses around %s. At the moment your (res.iloc[i]['ids']) is nothing but a sequence expression in redundant parentheses, so execute() uses it as the argument sequence, which causes your TypeError exception; your argument sequence has more arguments than the query has placeholders.

Try (tuple(res.iloc[i]['ids']),) instead. Note the comma, it is a very common error to omit it. All in all:

cur.execute("""SELECT COUNT(swipe.eclipse_id), 
subscriber_hashtag.hashtag_id
FROM subscriber_hashtag
INNER JOIN eclipse_hashtag ON eclipse_hashtag.hashtag_id = subscriber_hashtag.hashtag_id
LEFT OUTER JOIN swipe ON subscriber_hashtag.subscriber_id = swipe.subscriber_id
WHERE subscriber_hashtag.subscriber_id in %s
GROUP BY subscriber_hashtag.hashtag_id
    ORDER BY COUNT(swipe.eclipse_id) DESC;""",
(tuple(res.iloc[i]['ids']),))

Your for-loop is a bit strange, since you iterate over a 2-tuple (0, len(res)). Perhaps you meant range(len(res)). You could also just iterate over the Pandas Series:

for i, ids in enumerate(res['ids']):
    ...
    cur.execute(..., (tuple(ids),))

edited Jul 4, 2017 at 18:50

answered Jul 3, 2017 at 20:10

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

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

5 Comments

Antoine Coppin Over a year ago

Thank you for the response. However, I think there is an error as far as it tells me there is a missing FROM-clause entry for table "subscriber_hastag" on WHERE subscriber_hastag.subscriber_id in (160, 161, ...

Ilja Everilä Over a year ago

That's a typo. "hastag" vs. "hashtag".

Antoine Coppin Over a year ago

There is an issue : Te pure SQL query and the python nested query don't give the same output ...

Ilja Everilä Over a year ago

There are no nested queries in the given SQL. Since the question was not about the correctness of the given SQL statement and logic, but how to use a list of values in a WHERE-clause with the IN operator, this is proving off topic. Please ask a new question about that.

Antoine Coppin Over a year ago

Okay, I just did. But how can I print out the result of the different groups still ?

Collectives™ on Stack Overflow

How to use variables and OR cunjunctions in SQL statement in Python?

Data for a reproducible example

Current error message

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Data for a reproducible example

Current error message

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related