How to perform arithmetic across columns of a MySQL table using Python?

PythonServer Side ProgrammingProgramming

The arithmetic operations as the name suggests are used to perform the operations such as additon, subtraction,division, multiplication or modulus.

The arithmetic operations are operated on the numeric data in your table.

Syntax

To perform addition

SELECT op1+op2 FROM table_name

Here, the op1 and op2 are the column names or the numeric values. If the op1 and op2 are the numeric values, then FROM clause are not required.

The + in the above syntax can be replaced by -,*,%,/ to perform other arithmetic operations.

Steps to perform arithmetic operations in a table using MySQL in python

  • import MySQL connector

  • establish connection with the connector using connect()

  • create the cursor object using cursor() method

  • create a query using the appropriate mysql statements

  • execute the SQL query using execute() method

  • close the connection

Suppose we have the following table named “Sales”

+------------+---------+
| sale_price |    tax  |
+------------+---------+
|    1000    | 200     |
|    500     | 100     |
|    50      | 50      |
|    180     | 180     |
+------------+---------+

Example

We need to calculate amount adding both the column values including sale_price and the tax.

import mysql.connector
db=mysql.connector.connect(host="your host", user="your username", password="your
password",database="database_name")

cursor=db.cursor()

query="SELECT sale_price,tax, concat(sale_price+tax) AS amount FROM Sales"
cursor.execute(query)

rows=cursor.fetchall()

for row in rows:
   print(row)

db.close()

Output

( ‘sale_price’ , ‘tax’ , ‘amount’ )
(1000,200,1200)
(500,100,600)
(100,50,150)
(700,180,880)

The arithmetic addition is operated on the two columns in the table. Similarly, other arithmetic operations can be performed based on need.

raja
Published on 10-Jun-2021 13:22:13
Advertisements