pandas sql using var containing list

Question

I created a list of locations by doing this:

list_NA = []
for x in df['place']:
    if x and x not in list_NA:
        list_NA.append(x)

This gives me a list like this:

print(list_NA)

['DEN', 'BOS', 'DAB', 'MIB', 'SAA', 'LAB', 'NYB', 'AGA', 'QRO', 'DCC', 'PBC', 'MIC', 'MDW', 'SAB', 'LAA', 'NYA', 'PHL', 'DCB', 'CHA', 'CHB', 'SEB', 'AGB', 'SEC', 'DAA', 'MEX']

I want to use this list in my where clause like this:

df2 = pd.read_sql("select airport from "+db+" where airport in "+list_NA+"", conn)

But I keep getting this error :

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

I tried to do str(list_NA) or tuple(list_NA) but

Dickster · Accepted Answer · 2018-06-21 23:15:33Z

1

you will want to convert list_NA to a comma separated string with single quotes.

"','".join(list_NA)

but you'll also need to wrap that in single quotes on either end as well.

df2 = pd.read_sql("select airport from "+db+" where airport in ('"+ "','".join(list_NA) +"')", conn)

answered Jun 21, 2018 at 23:15

Dickster

3,0193 gold badges25 silver badges29 bronze badges

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

Collectives™ on Stack Overflow

pandas sql using var containing list

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related