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

In this article, we show how to get the minimum 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 lowest price of all of the searches for the most affordable 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 minimum value of a column of a MySQL table using Python is shown below.

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

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

If you save the minimum 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 MIN(column_name) AS minimum FROM Table_name") result = cursor.fetchall() for i in result: minimum= float(i[0])

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

So, this is how the code can be written.

We will now go through a full, complete example of finding the minimum 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 minimum value of the price column. Thus, this will represent the lowest-priced item in a customer's shopping cart.

So the code below is the full script to get the minimum 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 MIN(price) AS minimum 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 lowest 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 minimum value of the price column of the CustomerOrders table using the cursor.execute() function. Inside of this function, we place in the line, "SELECT MIN(price) AS minimum FROM CustomerOrders". So the whole line of code, is, cursor.execute("SELECT MIN(price) AS minimum 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 minimum value of the price column.

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

Again, you can use the minimum variable and save the lowest value in this variable.

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

And this is how we can find the minimum 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