python 列表输入sql_使用IN和python列表构建SQL查询字符串

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'...'']

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)

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

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

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

***EDIT

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

So I

cipTup = tuple(cips).

Then in my query I did ..

where c_ip in "+str(cipTup)"

and it worked.

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

解决方案

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

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)