How do I pass a function result to pandas data frame using mysql.connector-CodePudding

I have a function which selects a list_id from a database and and returns the average time phone numbers from the list_id was called. where I am struggling is how to use this function to input into a dataframe or a data frame column. I want the script to iterate over a list of list_id's and return the average in a pandas dataframe

var = [8113,8114,8112]

def avg():
    for i in range (len(var)):
        mycursor.execute("SELECT list_id, avg(called_count) FROM list WHERE list_id ={}".format(var[i]))
        myresult = mycursor.fetchall()
        for x in myresult:
            print(x[1])
avg()

If I use print(x) then it will return:

(8113, Decimal('1.5814')) (8114, Decimal('0.0928')) (8112, Decimal('1.9127'))

If I just use print(x[1]) then it will return just the result

1.5816 0.0934 1.9128

I am trying to pass the output to a dataframe with 2 columns being "list_id" and "result"

CodePudding user response：

You could use pd.read_sql for this. Also, it would be possible to run just one query, rather than one for each element in the list, which would be faster.

id_list_string = "','".join(var)
sql_query = f"SELECT list_id, avg(called_count) FROM list WHERE list_id IN ('{id_list_string}');"
df = pd.read_sql(
    sql=sql_query,
    con=mycursor
)

The above solution also uses f-strings.