1.9Connecting from Python -- Your First Automated DB Test

Connecting to the database from your test code lets you verify persistence directly:

import psycopg2
import pytest

@pytest.fixture(scope="session")
def db():
    conn = psycopg2.connect(
        host="localhost",
        dbname="testdb",
        user="testuser",
        password="testpass"
    )
    yield conn
    conn.close()

def test_user_creation_persists(api_client, db):
    """Verify that creating a user via API persists to database."""
    api_client.post("/users", json={
        "name": "Alice",
        "email": "alice@test.com"
    })

    cursor = db.cursor()
    cursor.execute(
        "SELECT name, email, role FROM users WHERE email = %s",
        ("alice@test.com",)
    )
    row = cursor.fetchone()
    assert row is not None, "User was not persisted to database"
    assert row[0] == "Alice"
    assert row[2] == "viewer"  # Verify default role applied