How to round a number in SQLAlchemy and MySQL?

In this article, we are going to round a number using SQLAlchemy and MySQL.

Database related information :

We have used a mysql database along with a table named “student”.

Database used : geeksforgeeks
Table used : students

Requirements :

We need MySQL installed along the with “SQLAlchemy” module and “pymysql” module ( a dependency of SQLAlchemy we need for this post ) in the system.

Syntax to install required libraries:

pip install sqlalchemy pymysql

There are 2 ways through which we can round a number using SQLAlchemy and MySQL:

• Using the “ROUND” function of sql.
• Using the “round” function of python.

Method 1: Using round function of SQL :

SQL provides us with a “ROUND” function which we can use in our queries and SQL will return us the rounded output.

The syntax of the “ROUND” function is :

ROUND( value / column_name , n )

Here the first argument is the name of the column or any constant value. The second argument (n) is the no of decimals up to which you want to round off. By default, the value of n is equal to 0 which means there will 0 digits after the decimal point. 

Few examples of the output of the round function :

ROUND(13.533)       --> 14
ROUND(13.565 , 2)   --> 13.57
ROUND(13.134 , -1)  --> 10

Syntax of the sql query is :

SELECT ROUND(column_name , n) FROM table ;

And our python code for creating the query will look like :

Python3

query = f'SELECT ROUND({col_to_round}) FROM {table_name}'

Note: We have not mentioned the value of n in the above ROUND function as we want to round up to 0 decimal places. You can obviously specify that if you want by adding a “,” and then the value.

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 = 'students'
col_to_round = 'marks'

query = f'SELECT ROUND({col_to_round}) FROM {table_name}'

result = connection.execute(query)
for elem in result:
    print(elem[0])

Output :

Method 2: Using round function of python

In this method, we will form a normal “SELECT” query and execute it. Then we will get the result on which we will run a for loop and then get all the values. And finally, we will round off the value using the “round” function of python and hence achieve our task.

The syntax of sql query is :

SELECT col_name FROM table_name;

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 = 'students'
col_to_round = 'marks'

query = f'SELECT {col_to_round} FROM {table_name}'

result = connection.execute(query)
for elem in result:
    value = elem[0]
    rounded_value = round(value)
    print("Value :", value, "\t Rounded value :", rounded_value)

Output: