Open In App

How to select min and max from table by column score in SQLAchemy?

Improve
Improve
Like Article
Like
Save
Share
Report

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' , 'geeksforgeeks'
engine = create_engine(url=f'mysql+pymysql://{user}:{password}@{host}/{database}?charset=utf8')
  
connection = engine.connect()
  
table_name = 'players'
  
result = connection.execute(f'SELECT MIN(score) , MAX(score) FROM {table_name}')
  
min_value = None
max_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', 'geeksforgeeks'
engine = create_engine(
    url=f'mysql+pymysql://{user}:{password}@{host}/{database}?charset=utf8')
  
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:



Last Updated : 28 Feb, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads