Guide to Setting Up Pytest for API and Database Testing in Python

1. Install pytest Ensure pytest and related plugins are installed:

pip install pytest pytest-cov pytest-mock pytest-xdist

2. Project directory structure Example layout:

project/
│   ├── tests/
│   │   ├── __init__.py
│   │   ├── conftest.py
│   │   ├── test_api.py
│   │   └── test_database.py
│   ├── utils/
│   │   ├── __init__.py
│   │   ├── config.py
│   │   └── request_utils.py
│   ├── db_utils.py
│   └── requirements.txt

3. Configuration file (utils/config.py) Define API base URL and MySQL connection settings:

# utils/config.py
API_BASE_URL = "https://api.example.com"
DB_CONFIG = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "password",
    "database": "testdb"
}

4. Request wrapper (utils/request_utils.py) Provide simple GET and POST helpers:

# utils/request_utils.py
import requests

def get(url, params=None, headers=None):
    response = requests.get(url, params=params, headers=headers)
    return response

def post(url, data=None, json=None, headers=None):
    response = requests.post(url, data=data, json=json, headers=headers)
    return response

5. MySQL wrapper (db_utils.py) Functions for connecting, querying, and closing the database:

# db_utils.py
import pymysql

def connect_db(config):
    connection = pymysql.connect(
        host=config['host'],
        port=config['port'],
        user=config['user'],
        password=config['password'],
        database=config['database']
    )
    return connection

def execute_query(connection, query):
    with connection.cursor() as cursor:
        cursor.execute(query)
        result = cursor.fetchall()
    return result

def close_connection(connection):
    connection.close()

6. Fixtures (tests/conftest.py) Define session‑scoped database fixture and a cleanup fixture:

# tests/conftest.py
import pytest
from utils.config import DB_CONFIG
from db_utils import connect_db, close_connection

@pytest.fixture(scope="session")
def db_connection():
    connection = connect_db(DB_CONFIG)
    yield connection
    close_connection(connection)

@pytest.fixture
def clean_db(db_connection):
    # clean database before test
    execute_query(db_connection, "DELETE FROM users")
    yield
    # clean database after test
    execute_query(db_connection, "DELETE FROM users")

7. API test cases (tests/test_api.py) Verify GET and POST endpoints:

# tests/test_api.py
import pytest
from utils.config import API_BASE_URL
from utils.request_utils import get, post

def test_get_users():
    url = f"{API_BASE_URL}/users"
    response = get(url)
    assert response.status_code == 200
    assert len(response.json()) > 0

def test_create_user():
    url = f"{API_BASE_URL}/users"
    payload = {"name": "Alice", "email": "[email protected]"}
    response = post(url, json=payload)
    assert response.status_code == 201
    assert response.json()["name"] == "Alice"
    assert response.json()["email"] == "[email protected]"

8. Database test cases (tests/test_database.py) Insert and verify a user record:

# tests/test_database.py
import pytest
from db_utils import execute_query
from utils.config import DB_CONFIG

def test_create_user_in_db(clean_db, db_connection):
    execute_query(db_connection, "INSERT INTO users (name, email) VALUES ('Alice', '[email protected]')")
    result = execute_query(db_connection, "SELECT * FROM users WHERE name = 'Alice'")
    assert len(result) == 1
    assert result[0]["name"] == "Alice"
    assert result[0]["email"] == "[email protected]"

9. Run tests Execute all tests with verbose output:

pytest -v

10. Generate test report Produce an HTML coverage report using pytest‑cov:

pytest --cov=project --cov-report=html

11. Test summary The guide covered configuration files, request and MySQL wrappers, fixtures, API and database test cases, and how to run tests and generate coverage reports.