Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to perform arithmetic across columns of a MySQL table using Python?
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.
519 Views
