python - 如何在 Python 的 SQL 语句中使用变量和 OR 连接？

Question

我在名为 res 的列表中有一个 ID 列表，我想在将结果保存在数组中之前逐行用作 SQL 查询的 WHERE 条件:

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

这是我尝试将其插入到 SQL 查询中的内容:

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]

可重现示例的数据

提供进一步的数据信息:

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

这里的输出应该是这样的:

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

当前错误信息

错误:标记化输入时出现意外错误以下回溯可能已损坏或无效错误信息是:('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: 并非所有参数都在字符串格式化期间转换

Answer 1

看看tuples adaptation :

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

将 id 作为元组查询参数传递，因此要执行的参数是 id 元组的 1 元组，并删除 %s 周围的手动括号。目前你的 (res.iloc[i]['ids']) 只不过是冗余括号中的序列表达式，所以 execute() 将它用作参数序列，这会导致您的 TypeError 异常；您的参数序列包含的参数多于查询包含的占位符。

试试 (tuple(res.iloc[i]['ids']),) 代替。注意逗号，它是 very common error to omit it .总而言之:

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

您的 for 循环有点奇怪，因为您迭代了 2 元组 (0, len(res))。也许你的意思是 range(len(res))。您也可以只遍历 Pandas 系列:

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