pandas query with list variables

Question

I made a list of id's each value is is an type int

temp_id = [7922, 7018, 5650, 209, 21928, 2294, 10507, 3623]

type(tempasn) 
list

This works:

Temp = pd.read_sql("select count(*) as count\
                    from "+db+"\
                    where ids in (7922, 7018, 5650, 209, 21928, 2294, 10507, 3623)\
                    order by count desc limit 10", conn)

I want to take the variable of the list add it to my query

This causes errors

Temp = pd.read_sql("select count(*) as count\
                    from "+db+"\
                    where ids in "+temp_id+"\
                    order by count desc limit 10", conn)


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

I don't understand what I need to change here

Answer

Ended up taking the advise of the comments below and modified my loop which generates the temp_ids:

temp_id = []
for id in df['ID']:
    if id and id not in temp_id:
        temp_id.append(str(ID))        

        #Convert to list format
temp_id = ", ".join(temp_id)

2 things made this work :

str(ID) & temp_id = ", ".join(temp_id)

You could also use something like this: "("+str(temp_id).strip('[]')+")" — suvayu
– suvayu, Commented Nov 14, 2018 at 10:02

Ankur Patel · Accepted Answer · 2018-07-07 07:24:24Z

1

You need to store the variables as a comma separated string instead of an array.

So declare temp_id as string.

temp_id = "7922, 7018, 5650, 209, 21928, 2294, 10507, 3623"

Temp = pd.read_sql("select count(*) as count\
                    from "+db+"\
                    where ids in ("+temp_id+")\
                    order by count desc limit 10", conn)

answered Jul 7, 2018 at 7:24

Ankur Patel

1,4338 silver badges15 bronze badges

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

Comments

ScaisEdge · Accepted Answer · 2018-07-07 07:21:56Z

1

You need a string so you should implode your array

temp_id = [7922, 7018, 5650, 209, 21928, 2294, 10507, 3623]


my_string = ",".join(temp_id )

Temp = pd.read_sql("select count(*) as count\
                    from "+db+"\
                    where ids in ("+my_string+")\
                    order by count desc limit 10", conn)

answered Jul 7, 2018 at 7:21

ScaisEdge

134k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

pandas query with list variables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related