How are fixtures used for interacting with the database when writing tests?

Fixtures in Python tests (e.g., with pytest) are used to prepare and clean up the database state before and after tests. This helps isolate tests from each other and ensures predictable results.

Main ways to use fixtures for working with the database:

• Creating a connection and session: the fixture opens a connection to the test database and returns a session or cursor object used in tests.
• Data preparation: the fixture can create necessary records so that tests work with a known set of data.
• Transactions and rollback: often, fixtures start a transaction before the test and rollback after, to avoid saving changes to the database.
• Cleanup: if transactions are not used, the fixture can manually delete created data.

Example of a fixture with SQLAlchemy and pytest:

import pytest
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from myapp.models import Base

@pytest.fixture(scope='function')
def db_session():
    engine = create_engine('sqlite:///:memory:')
    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()

    yield session  # tests use the session

    session.rollback()  # rollback all changes
    session.close()

In the test:

def test_add_user(db_session):
    user = User(name='Alice')
    db_session.add(user)
    db_session.commit()

    assert db_session.query(User).filter_by(name='Alice').one()

This approach ensures test isolation and data cleanliness.