In this article, we are going to fetch min and max values from column scores in the SQL table using the SQLAlchemy module.
SQLAlchemy is an awesome and easy-to-use python module to connect with SQL and use the features and powers of SQL in python. Here, we are provided with a table that has a column named “score” and our task is to find out the max and min value stored in that column.
Installation
For the task you need to install the SQLAlchemy module which you can install by the following command:
pip install SQLAlchemy pymysql
Note: pymysql is a dependency of SQLAlchemy which we need to install for this post.
Database used:
As you can see, in our player’s table the min and max value of the score is 52 and 100 respectively and our task is to get these values in our python code. In this post we will do the task by 2 methods :
- Using min and max of SQL
- Using min and max of python
Both of the above methods are superbly easy and useful to perform our task. But first, let us start by connecting to the database.
Method 1: Using min and max of SQL
Let us run a select query using the connection object (created above) which runs the min and max function in it.
Example:
Python3
from sqlalchemy import create_engine user , password , host , database = 'root' , '123' , 'localhost' , 'neveropen' connection = engine.connect() table_name = 'players' result = connection.execute(f'SELECT MIN(score) , MAX(score) FROM {table_name}') min_value = Nonemax_value = None for elem in result: min_value = elem[0] max_value = elem[1] print('Min value : ',min_value) print('Max value : ',max_value) |
The element in the result is storing two values. The first value is minimum and the second value is the maximum value from the column score.
Output:
Output:
Method 2: Using min and max of python
For this approach, we will first fetch all the values through the below code, store them in a list, and run the min and max function of the python on that list.
Python3
from sqlalchemy import create_engine user, password, host, database = 'root', '123', 'localhost', 'neveropen'engine = create_engine( connection = engine.connect() table_name = 'players' result = connection.execute(f'SELECT score FROM {table_name}') all_values = [] for elem in result: all_values.append(elem[0]) min_value = min(all_values) max_value = max(all_values) print('Min value : ', min_value) print('Max value : ', max_value) |
Output:
Output:
