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

Performing arithmetic operations across columns in a MySQL table using Python is a common task in data analysis. You can add, subtract, multiply, divide, or find the modulus of numeric columns using SQL queries executed through Python's MySQL connector.

Syntax

The basic syntax for arithmetic operations in SQL is:

SELECT column1 + column2 AS result FROM table_name;

Replace + with -, *, /, or % for subtraction, multiplication, division, or modulus operations respectively.

Steps to Perform Arithmetic Operations

• Import MySQL connector

• Establish connection using connect()

• Create cursor object using cursor() method

• Write SQL query with arithmetic operations

• Execute query using execute() method

• Fetch and process results

• Close the connection

Sample Table

Let's work with a "Sales" table:

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

Example: Addition Operation

Calculate the total amount by adding sale_price and tax columns:

import mysql.connector

# Establish connection
db = mysql.connector.connect(
    host="localhost",
    user="your_username", 
    password="your_password",
    database="your_database"
)

cursor = db.cursor()

# Query to add two columns
query = "SELECT sale_price, tax, sale_price + tax AS total_amount FROM Sales"
cursor.execute(query)

# Fetch all results
rows = cursor.fetchall()

# Display results
print("Sale Price | Tax | Total Amount")
print("-----------|-----|-------------")
for row in rows:
    print(f"{row[0]:10} | {row[1]:3} | {row[2]:11}")

# Close connection
db.close()

Multiple Arithmetic Operations

You can perform multiple operations in a single query:

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password", 
    database="your_database"
)

cursor = db.cursor()

# Multiple arithmetic operations
query = """
SELECT 
    sale_price,
    tax,
    sale_price + tax AS total_amount,
    sale_price - tax AS difference,
    sale_price * tax AS product,
    sale_price / tax AS ratio
FROM Sales
"""

cursor.execute(query)
results = cursor.fetchall()

for row in results:
    print(f"Price: {row[0]}, Tax: {row[1]}")
    print(f"Total: {row[2]}, Difference: {row[3]}")
    print(f"Product: {row[4]}, Ratio: {row[5]:.2f}")
    print("-" * 30)

db.close()

Common Use Cases

Conclusion

Arithmetic operations across MySQL columns using Python are straightforward with proper SQL syntax. Use appropriate operators (+, -, *, /, %) in SELECT statements and handle results using cursor methods for effective data processing.