How to calculate the average of the N most recent entries in SQLite using the sqlite3 module?-CodePudding

I'm coding in Python 3.11, using the tkinter and sqlite3 packages. I've generated a database with four columns, one of them is called weight and its values are defined as real (aka decimals/floats). What I want to do is write a function using cursor.execute that "selects" the 7 most recent entries in the weight column, calculates and returns those 7 values' average.

I understand SQLite3 has the in-built function AVG() and I've tried to use it, but that function is taking the average of all entries in the weight column, and I haven't been able to research a way to direct it to only take the N most recent entries.

I also understand SqLite3 has the ability to cursor.fetchmany(7), but Sqlite3 makes all data into tuples. So when I fetchmany(7) and hardcode it to produce the average, it throws errors about tuples being unable to interact with int/str/floats. Here's what my function looks like so far. What I actually get when I execute this function is the average of all entries in the column, rather than the last 7.

def average_query():
    
    #Create a database or connect to one
    conn = sqlite3.connect('weight_tracker.db')
    #Create cursor
    c = conn.cursor()
    
    my_average = c.execute("SELECT round(avg(weight)) FROM weights ORDER BY oid DESC LIMIT 7")
    my_average = c.fetchall()
    my_average = my_average[0][0]
    
    #Create labels on screen
    average_label = Label(root,text=f"Your average 7-day rolling weight is {my_average} pounds.")
    average_label.grid(row=9, column=0, columnspan=2)

    #Commit changes
    conn.commit()
    #Close connection
    conn.close()

CodePudding user response：

You can either extract the top 5 and then do the average in Python:

res = c.execute('SELECT weight FROM weights ORDER BY oid DESC LIMIT 7')
rows = res.fetchall()
# E.G. [(40,), (0,), (0,), (2500,), (1500,), (144,), (999,)]

avg = sum(r[0] for r in rows) / len(rows)
# 740.4285714285714

Or you can use a nested query to perform the average:

res = c.execute('SELECT ROUND(AVG(*)) FROM ( SELECT weight FROM weights ORDER BY oid DESC LIMIT 7 );')

rows = res.fetchall()
# [(740.4285714285714,)]

avg = rows[0][0]
# 740.4285714285714

CodePudding user response：

Hey