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 :neveropen 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', 'neveropen'engine = create_engine( 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', 'neveropen'engine = create_engine( 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:
