pytest-mysql

4.0.0 · active · verified Thu Apr 16

pytest-mysql is a pytest plugin, version 4.0.0, that facilitates testing code reliant on a running MySQL/MariaDB database. It provides pytest fixtures for managing MySQL process lifecycle (`mysql_proc`) and client connections (`mysql`, `mysql_noproc`), ensuring isolated and repeatable database environments for tests. It automatically handles starting and stopping MySQL instances for test sessions and can clean up test databases per function. The project is actively maintained, with the latest version released in December 2024.

Common errors

```
pymysql.err.OperationalError: (2002, "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)") OR pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'localhost' (111 Connection refused)")
```
cause The `pytest-mysql` plugin failed to start the MySQL process, or the path to MySQL executables (`mysqld`, `mysqladmin`) is incorrect, or a port conflict exists.

fix Ensure MySQL/MariaDB server is properly installed and its executables are in your system's PATH. Check the `pytest` output for startup errors. You can manually specify executable paths and ports in `pytest.ini` or when creating custom fixtures using `factories.mysql_proc(executable='/path/to/mysqld', port=3307)`.
```
Failed: Database 'test_...' already exists / Table '...' already exists (when running multiple tests)
```
cause Database state is not being correctly isolated or reset between test runs. This typically happens if a session-scoped fixture (`mysql_proc`, `mysql_noproc`) is used, but a test modifies the database in a way that affects subsequent tests without proper cleanup, or if the `mysql` fixture isn't effectively cleaning up.

fix For individual test function isolation, ensure you are using the `mysql` fixture, which ensures the test database is dropped after each test. If you need a session-scoped database for performance, implement explicit database cleanup (e.g., `TRUNCATE TABLE` or schema recreation) in setup/teardown fixtures within your tests.
```
ModuleNotFoundError: No module named 'pytest_mysql'
```
cause The `pytest-mysql` library is not installed in your current Python environment, or the environment where `pytest` is being run is not the one where `pytest-mysql` was installed.

fix Activate the correct Python virtual environment (if used) and run `pip install pytest-mysql`. If `pymysql` is also needed for database interaction, install it with `pip install pymysql`.

Warnings

breaking pytest-mysql versions 3.0.0 and above (including 4.0.0) only support MySQL/MariaDB server versions 5.7.6 and up. If you are using an older MySQL server, you must use `pytest-mysql` version 2.0.3.
Fix: Upgrade your MySQL/MariaDB server to version 5.7.6+ or downgrade `pytest-mysql` to version 2.0.3 (`pip install 'pytest-mysql<3'`).
gotcha Configuration options for MySQL instances (e.g., port, user, executable path) follow a specific precedence: Fixture factory arguments override command-line options, which in turn override settings in `pytest.ini`.
Fix: Always check the fixture factory arguments first, then command-line options (`--mysql-port=...`), and finally `pytest.ini` (`[pytest] mysql_port = ...`) to understand which setting is active.
gotcha Relying on external or pre-existing MySQL instances without proper fixture usage can lead to test failures due to stale data. The `mysql_noproc` fixture connects to an existing instance but relies on external cleanup if not configured otherwise.
Fix: For function-level database isolation, use the `mysql` fixture, which automatically drops the test database after each test. If connecting to a pre-existing instance with `mysql_noproc`, ensure your test setup/teardown explicitly manages database state (e.g., truncating tables, recreating schema) for repeatability.

Install

pip install pytest-mysql Install pytest-mysql
pip install pymysql pytest Install with a common MySQL connector and pytest

Imports

factories
```
from pytest_mysql import factories
```
Used to create custom MySQL process or client fixtures with specific configurations.

Quickstart

This quickstart demonstrates using the function-scoped `mysql` fixture provided by `pytest-mysql`. This fixture provides a PyMySQL connection to a temporary database, which is created and dropped for each test function, ensuring test isolation. The `mysql_proc` fixture (session-scoped) is also available for starting and stopping the MySQL server once per test session. You might need to install `pymysql` to interact with the connection object.

import pytest
import pymysql # Often used to interact with the database fixture

def test_mysql_connection(mysql):
    """
    Demonstrates a basic connection and a simple query using the 'mysql' fixture.
    The 'mysql' fixture provides a PyMySQL connection object and drops the 
    test database after each test function.
    """
    assert isinstance(mysql, pymysql.connections.Connection)
    cursor = mysql.cursor()
    cursor.execute("SELECT 1 + 1")
    result = cursor.fetchone()[0]
    assert result == 2
    cursor.close()

def test_create_table_and_insert(mysql):
    """
    Shows how to create a table and insert data. The database will be 
    cleaned up after this test completes due to the 'mysql' fixture's scope.
    """
    cursor = mysql.cursor()
    cursor.execute("CREATE TABLE IF NOT EXISTS my_table (id INT AUTO_INCREMENT PRIMARY KEY, value VARCHAR(255))")
    cursor.execute("INSERT INTO my_table (value) VALUES (%s)", ("test_value",))
    mysql.commit() # Commit changes to the temporary database

    cursor.execute("SELECT value FROM my_table WHERE id = 1")
    fetched_value = cursor.fetchone()[0]
    assert fetched_value == 'test_value'
    cursor.close()

# To run these tests:
# 1. Ensure MySQL/MariaDB server is installed and accessible (pytest-mysql will start it).
# 2. Save the above code as e.g., `test_database.py`.
# 3. Run `pytest` in your terminal in the same directory.

view raw JSON →