测试SQLAlchemy（mssql+pyodbc引擎）是否因密码错误抛出异常

Let's walk through how to build a solid test case for verifying that your application correctly raises a connection rejection error when using an invalid password with SQLAlchemy's mssql+pyodbc engine. Here's a step-by-step implementation tailored to your scenario:

Core Test Logic #

The test needs to follow these key steps to validate the password failure scenario:

• Override your database config with a random, invalid password
• Initialize your db_interaction instance using this bad credential set
• Attempt to access the database for user xxx
• Verify the resulting error is specifically due to password authentication failure

Example Test Code (Using pytest) #

Assuming you're using pytest for testing, here's a concrete, maintainable implementation:

import pytest
import random
import string
from sqlalchemy.exc import OperationalError
# Import your db_interaction class/module here
from your_app_module import db_interaction

# Assume your base config dictionary is defined/imported here
config = {
    "server": "your-sql-server",
    "database": "target-db",
    "username": "db-username",
    "password": "original-valid-password"
}

def test_connection_rejected_on_invalid_password():
    # 1. Create a copy of the config to avoid modifying the original (prevents test side effects)
    test_config = config.copy()
    
    # Generate a random 12-character string as the invalid password
    invalid_password = ''.join(
        random.choices(string.ascii_letters + string.digits + string.punctuation, k=12)
    )
    test_config["password"] = invalid_password
    
    # 2. Initialize the db_interaction instance with bad credentials
    db = db_interaction(test_config)
    
    # 3. Attempt to access user xxx's database and catch the connection error
    with pytest.raises(OperationalError) as exc_context:
        # Replace this with your actual method that triggers a DB connection
        # e.g., fetching user xxx's data or accessing their schema
        db.access_user_database("xxx")
    
    # 4. Validate the error is explicitly a password/login failure
    error_details = str(exc_context.value)
    # MSSQL returns error code 18456 for login failures, or clear message text
    assert "18456" in error_details or "Login failed for user" in error_details, \
           f"Expected password rejection error, got unexpected message: {error_details}"

Key Notes for Accuracy #

• Isolate test config: Always copy the original config instead of modifying it directly—this prevents polluting your application's real configuration or breaking other tests.
• Random invalid password: Using a random string ensures you're not relying on a hardcoded bad password that might accidentally match a valid one (unlikely, but removes any edge case risk).
• Target the right exception: SQLAlchemy throws OperationalError for connection-related issues, which is exactly what we want to catch here.
• Validate error specifics: MSSQL's login failure error code 18456 is a reliable marker for authentication issues. Alternatively, checking for the phrase "Login failed for user" works across most SQL Server versions and pyodbc setups.

Edge Case Adjustment #

If your db_interaction class establishes a connection during initialization (instead of lazy-loading it when accessing data), adjust the test to catch the exception during instance creation:

def test_connection_rejected_on_invalid_password():
    test_config = config.copy()
    test_config["password"] = ''.join(random.choices(string.ascii_letters, k=12))
    
    # Catch exception during instance initialization
    with pytest.raises(OperationalError) as exc_context:
        db_interaction(test_config)
    
    error_details = str(exc_context.value)
    assert "18456" in error_details or "Login failed for user" in error_details

内容的提问来源于stack exchange，提问作者jan-seins