How to Compute the Average of All Rows of a Column of a MySQL Table Using Python



Python


In this article, we show how to compute the average of all rows of a column of a MySQL table using Python.

This has application for a lot of things.

One thing is a shopping cart.

A user puts items in the shopping cart. These items get loaded to a MySQL table. There is a price column in this table, so the average of all of the prices of these rows can be represented.

If you need to know how to install MySQL, see How to Install MySQL in Python 3.

So, the general code to find the average of all rows of a column in a MySQL table is shown below.



So, the code above finds the average of the column, column_name, of the table, Table_name.

If you save the average in a variable so that you can use it in other areas of the script, the code would be as that shown below.



Now, the average of the column is in the variable average.

So, this is how the code can be written.

We will now go through a full, complete example of finding the average of all rows of a column.

So let's say we have a table called CustomerOrders and the table, shown below, represents all the items that the user has put in his or her shopping cart for purchase.

MySQL CustomerOrders Table

We want to write a Python script so that we find the average of the price column. Thus, this will represent the average price of each item in a customer's shopping cart.

So the code below is the full script to get the average of the price column of this CustomerOrders table.



So, this is all the code that is needed to find the average of the price column of the CustomerOrders table.

So we first must import MySQLdb. Once that is imported, we gain connection to the MySQL database using the MySQLdb.connect() function. To see a full-length article on connecting to a MySQL database, see How to Connect to a MySQL Database in Python.

We then have to create a cursor for the table.

Next, we execute our function to find the average of the price column of the CustomerOrders table using the cursor.execute() function. Inside of this function, we place in the line, "SELECT AVG(price) AS average FROM CustomerOrders". So the whole line of code, is, cursor.execute("SELECT AVG(price) AS average FROM CustomerOrders")

We then create a variable named result and set it equal to cursor.fetchall().

We then use a for loop and print out i[0], which represents the average of the price column.

We then close the database once we've done what we've needed to.

Again, you can use the average variable and save the mathematical average in this variable.

The program prints out 5.5, because this is the average of the table shown above.

And this is we can find the average of all of the rows of a column in a MySQL table using Python.


Related Resources

How to Show All Tables of a MySQL Database in Python

How to Count the Number of Rows in a MySQL Table in Python



HTML Comment Box is loading comments...