使用IN和python列表构建SQL查询字符串 [英] Build SQL query string using IN with a python list

问题描述

我建立了熊猫感兴趣的值列表.

I built a list of values of interest in pandas.

table1 = pd.read_csv("logswithIPs.csv")
cips = data_dash['ip'].unique().tolist()
print(cips[:10])
['111.111.111.111', '123.123.123.123', '122.122.122.122', '2.2.2.2', '3.3.3.3', '4.4.4.4', '5.5.5.5'...'']

现在我有了上面的列表，我想查看这些IP是否存在于SQL数据库的表中.

Now that I have the list above I want to see if those IPs exist in a table in my SQL Database.

filterIPs = pd.read_sql("select count(*) as count, url from "+table2+" where c_ip in "+cips+" group by url",conn)

具体来说，我的问题是我的语法在这里c_ip in "+cips+":

Specifically my problem is in my syntax here c_ip in "+cips+":

TypeError: Can't convert 'list' object to str implicitly

如何在SQL查询中正确包含列表?

How can I properly include the list in my SQL query?

***编辑

所以我终于让它工作了，看起来熊猫不想要它想要一个字符串的列表.

So I finally got it to work it looks like pandas doesnt want a list it wants a string.

所以我 cipTup = tuple(cips). 然后在我的查询中，我做了..

So I cipTup = tuple(cips). Then in my query I did ..

where c_ip in "+str(cipTup)" 

它奏效了.

我的猜测是，大熊猫知道如何将这样的字符串作为列表来对待??

My guess is that pandas knows how to treat a string like that as a list.?

推荐答案

我会将data_dash['ip'].unique()导出/保存为SQL表，以便可以将其有效地用于子查询:

I would export/save data_dash['ip'].unique() as an SQL table, so that it could be efficiently used for subqueries:

pd.DataFrame({'ip':data_dash['ip'].unique()}).to_sql('tmp_ip', conn, if_exists='replace')

现在您可以在SQL DB端使用它:

now you can use it on the SQL DB side:

qry = """
select count(*) as count, url
from tab_name
where c_ip in (select ip from tmp_ip)
group by url
"""

filterIPs = pd.read_sql(qry, conn)

这篇关于使用IN和python列表构建SQL查询字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！