Build SQL query string using IN with a python list

Question

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.?

This question is more about how to include a list in an SQL query built with python. The fact that you're using pandas is orthogonal to the problem you've encountered. — Paul H
– Paul H, Commented Jan 23, 2018 at 0:21
ok I dont know why this worked .. but it did. I converted the list to tuple, like cipTup = tuple(cips). Then in my query I did .. where c_ip in "+str(cipTup)" and it worked. — chowpay
– chowpay, Commented Jan 23, 2018 at 3:34

MaxU - stand with Ukraine · Accepted Answer · 2018-01-23 09:34:37Z

5

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)

edited Jan 23, 2018 at 9:34

answered Jan 23, 2018 at 0:28

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

6 Comments

cs95 Over a year ago

Oh, this is a fantastic idea!

Paul H Over a year ago

certainly more secure

chowpay Over a year ago

Do I need to have an additional module for this? I get AttributeError: 'numpy.ndarray' object has no attribute 'to_frame'

Paul H Over a year ago

@chowpay unique() returns an array, not a series (common mistake). So if you wrap it in a series, it should work (see the edit)

chowpay Over a year ago

When I try to wrap it in a series I get this error , but the solution here didnt work for me stackoverflow.com/questions/18295314/…

|

Collectives™ on Stack Overflow

Build SQL query string using IN with a python list

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related