如何用@pytest.mark.parametrize测试SQLAlchemy权限校验函数？

Hey there! Let's work through your permission check function and testing setup together. First, let's fix a few small issues in your current function that could cause problems, then we'll look at two solid testing approaches: using a temporary SQLite database (no mocks needed) and mocking the database queries if you prefer that route.

First: Fixes to Your Permission Function #

Your core logic is on the right track, but there are a couple of edge cases and structural issues to address first:

• if has_permission == "": → query.all() returns a list of tuples (never an empty string). Use if not has_permission instead to check for no permissions.
• The data list approach is flawed: appending every element from permission tuples creates a flat list that doesn't preserve resource-action pairs. Instead, iterate directly over the permission tuples to check for matches.

Here's a cleaned-up version of your function:

def check_if_user_has_permissions(user=None, resource=None, action=None):
    # Validate required inputs first
    if not all([user, resource, action]):
        return False

    # Fetch all resource-action pairs for the user
    user_permissions = db.session.query(Permission.resource, Permission.action).\
        join(RoleMember, RoleMember.role == Permission.role).\
        filter(RoleMember.user_uid == user).all()

    # Define super permission rules
    SUPER_RESOURCE = "machines/*"
    SUPER_ACTION = "*"

    # Check for super permission first
    for perm_resource, perm_action in user_permissions:
        if perm_resource == SUPER_RESOURCE and perm_action == SUPER_ACTION:
            return True

    # Check if the specific resource-action pair exists
    return (resource, action) in user_permissions

Testing Approach 1: Use a Temporary SQLite Database (No Mocks) #

Since you're already using SQLite, we can spin up an in-memory database for each test. This is great because it mirrors real database behavior without needing to mock anything.

Step 1: Create a Pytest Fixture for Database Setup

This fixture will create fresh tables before each test and clean them up afterward:

import pytest
from your_app_module import db, Role, Permission, RoleMember, check_if_user_has_permissions

# Replace 'app' with your actual Flask app instance if using Flask-SQLAlchemy
@pytest.fixture(scope="function")
def test_database():
    # Initialize in-memory SQLite database
    db.init_engine("sqlite:///:memory:")
    db.create_all()
    yield db
    db.drop_all()

Step 2: Write Parameterized Tests

We'll use @pytest.mark.parametrize to cover all key scenarios:

@pytest.mark.parametrize(
    "user, resource, action, expected_result",
    [
        # Super user: can perform any action on any machine resource
        ("super_admin", "machines/123", "edit", True),
        ("super_admin", "machines/456", "delete", True),
        # Regular user: has permission for specific resource-action
        ("machine_viewer", "machines/123", "view", True),
        # Regular user: wrong resource
        ("machine_viewer", "machines/456", "view", False),
        # Regular user: wrong action
        ("machine_viewer", "machines/123", "edit", False),
        # User with no permissions
        ("no_perm_user", "machines/123", "view", False),
        # Missing required parameters
        (None, "machines/123", "view", False),
        ("user1", None, "view", False),
        ("user1", "machines/123", None, False),
    ]
)
def test_permission_check(test_database, user, resource, action, expected_result):
    # Populate test data based on user type
    if user == "super_admin":
        # Create super role and permission
        super_role = Role(name="super_role")
        test_database.session.add(super_role)
        test_database.session.commit()

        super_perm = Permission(
            resource="machines/*",
            action="*",
            role=super_role.id
        )
        test_database.session.add(super_perm)
        test_database.session.add(RoleMember(user_uid="super_admin", role=super_role.id))
        test_database.session.commit()
    elif user == "machine_viewer":
        # Create regular role and permission
        viewer_role = Role(name="viewer_role")
        test_database.session.add(viewer_role)
        test_database.session.commit()

        viewer_perm = Permission(
            resource="machines/123",
            action="view",
            role=viewer_role.id
        )
        test_database.session.add(viewer_perm)
        test_database.session.add(RoleMember(user_uid="machine_viewer", role=viewer_role.id))
        test_database.session.commit()

    # Run the function and assert the result
    assert check_if_user_has_permissions(user, resource, action) == expected_result

Testing Approach 2: Mock the Database Query #

If you want faster tests without a real database, you can mock the SQLAlchemy query using unittest.mock. This is less representative of real behavior but useful for isolated unit tests.

from unittest.mock import patch, MagicMock
from your_app_module import check_if_user_has_permissions

@pytest.mark.parametrize(
    "user, resource, action, mock_permissions, expected_result",
    [
        ("super_admin", "machines/123", "edit", [("machines/*", "*")], True),
        ("machine_viewer", "machines/123", "view", [("machines/123", "view")], True),
        ("machine_viewer", "machines/456", "view", [("machines/123", "view")], False),
        ("no_perm_user", "machines/123", "view", [], False),
        ("user1", "machines/123", None, [("machines/123", "view")], False),
    ]
)
def test_permission_check_mocked(user, resource, action, mock_permissions, expected_result):
    # Mock the SQLAlchemy query chain
    mock_query = MagicMock()
    mock_join = MagicMock()
    mock_filter = MagicMock()
    mock_filter.join.return_value = mock_join
    mock_join.all.return_value = mock_permissions

    with patch("your_app_module.db.session.query", return_value=mock_query):
        mock_query.filter.return_value = mock_filter
        assert check_if_user_has_permissions(user, resource, action) == expected_result

内容的提问来源于stack exchange，提问作者Patrick José