How to Get the Maximum Value of a Column of a MySQL Table Using Python

In this article, we show how to get the maximum value of a column of a MySQL table using Python.

This has application for a lot of things.

For example, think of an online price searcher, such as finding airline tickets. This can be used to find the most expensive price of all of the searches for airline tickets.

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

So, the general code to get the maximum value of a column of a MySQL table using Python is shown below.

cursor.execute("SELECT MAX(column_name) AS maximum FROM Table_name") result = cursor.fetchall() for i in result: print(i[0])

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

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

cursor.execute("SELECT MAX(column_name) AS maximum FROM Table_name") result = cursor.fetchall() for i in result: maximum= float(i[0])

Now, the maximum value of the column is in the maximum variable.

So, this is how the code can be written.

We will now go through a full, complete example of finding the maximum value 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.

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

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

import pymysql pymysql.install_as_MySQLdb() import MySQLdb db= MySQLdb.connect("hostname", "username", "password", "database_name") cursor= db.cursor() cursor.execute("SELECT MAX(price) AS maximum FROM CustomerOrders") result = cursor.fetchall() for i in result: print(i[0]) db.close()

So, this is all the code that is needed to find the highest value of the price column.

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 maximum value of the price column of the CustomerOrders table using the cursor.execute() function. Inside of this function, we place in the line, "SELECT MAX(price) AS maximum FROM CustomerOrders". So the whole line of code, is, cursor.execute("SELECT MAX(price) AS maximum 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 maximum value of the price column.

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

Again, you can use the maximum variable and save the highest value in this variable.

The program prints out 9.00, because this is the highest value of the price column shown in the table above.

And this is how we can find the maximum value of all 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