Introduction

MySQL is one of the most popular relational database management systems (RDBMS) used for storing and managing structured data. Python can connect to MySQL databases using libraries such as mysql-connector-python.

MySQL is widely used in:

Web Applications
Enterprise Systems
Automation Frameworks
API Backends
Data Analytics
E-Commerce Platforms
Test Data Management

In this tutorial, you will learn how to connect Python to MySQL, perform basic database operations, practical examples, automation testing use cases, common mistakes, and best practices.

What is MySQL?

MySQL is a relational database that stores data in tables consisting of rows and columns.

Unlike SQLite:

MySQL requires a database server.
Supports multiple users.
Handles large-scale applications.
Provides advanced security and performance features.

Installing MySQL Connector

Python requires a connector library to communicate with MySQL.

Installation

pip install mysql-connector-python

Importing MySQL Connector

import mysql.connector

Creating a MySQL Connection

Syntax

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password"
)

Checking Connection

Example

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password"
)

if connection.is_connected():
    print("Connected Successfully")

Output

Connected Successfully

Connection Parameters

Parameter	Description
host	Database server address
user	MySQL username
password	MySQL password
database	Database name
port	MySQL port number

Connecting to a Specific Database

Example

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="company"
)

print("Database Connected")

Creating a Cursor

A cursor is used to execute SQL statements.

Example

cursor = connection.cursor()

Creating a Database

Example

cursor.execute(
    "CREATE DATABASE company"
)

print("Database Created")

Viewing Databases

Example

cursor.execute(
    "SHOW DATABASES"
)

for database in cursor:
    print(database)

Output

('company',)
('mysql',)
('information_schema',)

Creating a Table

Example

cursor.execute("""
CREATE TABLE IF NOT EXISTS employees(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    salary DECIMAL(10,2)
)
""")

print("Table Created")

Inserting Data

Example

query = """
INSERT INTO employees
(name, salary)
VALUES (%s, %s)
"""

values = ("John", 50000)

cursor.execute(query, values)

connection.commit()

print("Record Inserted")

Output

Record Inserted

Inserting Multiple Records

Example

query = """
INSERT INTO employees
(name, salary)
VALUES (%s, %s)
"""

data = [
    ("Alice", 60000),
    ("Bob", 55000),
    ("David", 70000)
]

cursor.executemany(
    query,
    data
)

connection.commit()

Retrieving Data

Example

cursor.execute(
    "SELECT * FROM employees"
)

records = cursor.fetchall()

for record in records:
    print(record)

Output

(1, 'John', 50000.00)
(2, 'Alice', 60000.00)

Fetch Methods

fetchone()

record = cursor.fetchone()

Returns one row.

fetchall()

records = cursor.fetchall()

Returns all rows.

fetchmany()

records = cursor.fetchmany(5)

Returns a specified number of rows.

Updating Records

Example

query = """
UPDATE employees
SET salary=%s
WHERE name=%s
"""

cursor.execute(
    query,
    (75000, "John")
)

connection.commit()

print("Record Updated")

Deleting Records

Example

query = """
DELETE FROM employees
WHERE name=%s
"""

cursor.execute(
    query,
    ("John",)
)

connection.commit()

print("Record Deleted")

Using WHERE Clause

Example

cursor.execute("""
SELECT *
FROM employees
WHERE salary > 60000
""")

records = cursor.fetchall()

for record in records:
    print(record)

Closing the Connection

Always close the cursor and connection.

Example

cursor.close()
connection.close()

Complete Example

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="company"
)

cursor = connection.cursor()

cursor.execute(
    "SELECT * FROM employees"
)

records = cursor.fetchall()

for record in records:
    print(record)

cursor.close()
connection.close()

MySQL Connection in Selenium Automation

MySQL is commonly used to store:

Test Data
Test Results
Execution Logs
Environment Configurations

Example

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="automation"
)

cursor = connection.cursor()

query = """
INSERT INTO test_results
(test_name, status)
VALUES (%s, %s)
"""

cursor.execute(
    query,
    ("Login Test", "PASS")
)

connection.commit()

Selenium Automation Use Cases

Store:

Test execution status
Browser information
Failed test details
Screenshot paths
Execution timestamps

Example:

cursor.execute("""
INSERT INTO test_results
VALUES (%s, %s)
""", ("Checkout Test", "FAIL"))

MySQL Connection in API Automation

Store API execution logs.

Example

query = """
INSERT INTO api_logs
(endpoint, status_code)
VALUES (%s, %s)
"""

cursor.execute(
    query,
    ("/users", 200)
)

connection.commit()

Useful for:

API monitoring
Audit logs
Response tracking

Parameterized Queries

Always use placeholders.

Correct

cursor.execute(
    "SELECT * FROM employees WHERE name=%s",
    ("John",)
)

Incorrect

cursor.execute(
    f"SELECT * FROM employees WHERE name='{name}'"
)

This can cause SQL Injection vulnerabilities.

Handling Connection Errors

Example

import mysql.connector

try:

    connection = mysql.connector.connect(
        host="localhost",
        user="root",
        password="wrong"
    )

except mysql.connector.Error as error:

    print("Error:", error)

Output

Access denied...

Common Mistakes Beginners Make

Forgetting commit()

Incorrect

cursor.execute(query)

Changes won’t be saved.

Correct

connection.commit()

Forgetting to Close Connection

Incorrect

connection = mysql.connector.connect(...)

Connection remains open.

Correct

connection.close()

Using String Formatting in SQL

Incorrect

query = f"""
SELECT *
FROM employees
WHERE name='{name}'
"""

Correct

query = """
SELECT *
FROM employees
WHERE name=%s
"""

Not Handling Exceptions

Always wrap connection code in try-except.

Best Practices

Use Parameterized Queries

WHERE name=%s

Always Close Connections

cursor.close()
connection.close()

Handle Exceptions

try:
    pass

except mysql.connector.Error:
    pass

Use Connection Pooling for Large Applications

Improves performance.

Store Credentials Securely

Avoid hardcoding:

password = "root123"

Use:

Environment Variables
Configuration Files
Secret Managers

Advantages of MySQL

High performance
Multi-user support
Scalable
Secure
Widely used
Enterprise ready

Limitations of MySQL

Requires server installation
More complex than SQLite
Additional administration needed

MySQL vs SQLite

Feature	MySQL	SQLite
Server Required	Yes	No
Multi-User Support	Yes	Limited
Scalability	High	Moderate
Installation	Required	Built-in
Enterprise Ready	Yes	Limited

Conclusion

MySQL is one of the most widely used relational databases and integrates seamlessly with Python using the mysql-connector-python library. It provides powerful features for storing, retrieving, and managing data in applications and automation frameworks.

Whether you’re managing Selenium test results, API logs, user information, or business data, MySQL offers a scalable and reliable database solution.

Learning how to connect Python with MySQL is an essential skill for automation engineers, testers, backend developers, and data professionals.

Frequently Asked Questions (FAQs)

Which module is used to connect Python to MySQL?

import mysql.connector

How do I install the MySQL connector?

pip install mysql-connector-python

How do I save changes?

connection.commit()

How do I retrieve records?

cursor.fetchall()

cursor.fetchone()

How do I close a connection?

cursor.close()
connection.close()

Key Takeaways

MySQL is a relational database management system.
Python connects to MySQL using mysql-connector-python.
Use connect() to establish a database connection.
Use cursors to execute SQL queries.
Always call commit() after INSERT, UPDATE, or DELETE operations.
Use parameterized queries to prevent SQL injection.
Close cursors and connections after use.
Useful in Selenium automation for test result storage.
Useful in API automation for logging and tracking responses.
MySQL is scalable and suitable for enterprise applications.