How to Perform Arithmetic Across Columns of a MySQL Table Using Python?
Last Updated :26 Nov, 2020
Python is a dynamic language, and Python applications can be integrated with database servers. The module used to access a MySQL database from Python is MySQL Connector Python. PyMySQL, MySQLDB and mysqlclient are other Python modules to communicate with a MySQL database server in Python. However, we will use MySQL Connector Python in this article because it is an API written purely in Python which means it does not have dependencies and only requires the standard library.
To perform arithmetic operations on column data, MySQL has Arithmetic Operators. These operators are useful for performing calculations on your data. Arithmetic operators in MySQL are as follows:
MySQL Arithmetic Operator
Syntax in MySQL
SELECT op1 + op2 FROM tablename;
SELECT op1 – op2 FROM tablename;
SELECT op1 / op2 FROM tablename;
SELECT op1 * op2 FROM tablename;
SELECT op1 % op2 FROM tablename;
In the above table, op1 and op2 can be column names or numeric values (in which case the “from” clause is not required). The following programs will help you understand the use of these operators better.
Database in use:
We will use a product information table in our programs. It consists of the product name, its cost price, selling price, tax and the quantity purchased.
Example 1: Use of addition operator
# Import required packages
# Establish connection to MySQL database
# MySQLCursorDict creates a cursor
# that returns rows as dictionaries
mycursor =mydb.cursor( dictionary =True)
# MySQL query for getting total
# sale amount (i.e. selling price + tax)
query ="SELECT Selling_price, \
concat(Selling_price +tax) AS sale_amount \
# Execute the query
mycursor.execute( query )
# Fetch result of query
# Print result of query
print(f"SP \t Tax \t Sale Amount")
# Each value printed for display purpose (you can simply print row)