How to Compute the Average of a Column of a MySQL Table Using Python?

Computing the average of a MySQL table column is a common data analysis task. Python's mysql-connector-python library makes this straightforward by combining SQL's AVG() function with Python's database connectivity.

Installing Required Libraries

First, install the MySQL connector library using pip ?

pip install mysql-connector-python

Complete Example

Here's a complete program that connects to MySQL and computes a column average ?

import mysql.connector

# Step 1: Connect to the database
mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername", 
    password="yourpassword",
    database="mydatabase"
)

# Step 2: Create a cursor
mycursor = mydb.cursor()

# Step 3: Execute the query to get average
mycursor.execute("SELECT AVG(salary) FROM employees")

# Step 4: Fetch the result
result = mycursor.fetchone()

# Step 5: Print the result
print("Average salary:", result[0])

# Step 6: Close the connection
mydb.close()

Connection Parameters

The connect() method requires these parameters ?

• host: MySQL server location (localhost for local server)

• user: MySQL username with database access privileges

• password: Password for the MySQL user

• database: Name of the target database

Error Handling Example

Add error handling to make your code more robust ?

import mysql.connector
from mysql.connector import Error

try:
    # Connect to database
    mydb = mysql.connector.connect(
        host="localhost",
        user="yourusername",
        password="yourpassword", 
        database="mydatabase"
    )
    
    mycursor = mydb.cursor()
    mycursor.execute("SELECT AVG(price) FROM products")
    result = mycursor.fetchone()
    
    if result[0] is not None:
        print(f"Average price: ${result[0]:.2f}")
    else:
        print("No data found or all values are NULL")
        
except Error as e:
    print(f"Error: {e}")
    
finally:
    if mydb.is_connected():
        mycursor.close()
        mydb.close()

Key Methods

Common Use Cases

You can compute averages for various scenarios ?

# Average with condition
mycursor.execute("SELECT AVG(score) FROM students WHERE grade = 'A'")

# Average with GROUP BY
mycursor.execute("SELECT department, AVG(salary) FROM employees GROUP BY department")

# Average excluding NULL values (default behavior)
mycursor.execute("SELECT AVG(rating) FROM reviews")

Conclusion

Use mysql-connector-python with SQL's AVG() function to compute column averages. Always include proper error handling and close connections when finished. This approach efficiently handles large datasets directly in the database.