I have multiple Pytest files. The problem is that the database data is not being cleared after each file is done running.

DB_URL = "sqlite:///:memory:"

engine = create_engine(DB_URL, connect_args={"check_same_thread": False}, poolclass=StaticPool)
TestingSessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base.metadata.drop_all(bind=engine)
Base.metadata.create_all(bind=engine)

def override_get_db():
    try:
        db = TestingSessionLocal()
        yield db
    finally:
        db.close()

app.dependency_overrides[get_db] = override_get_db

I have this piece of code in each file, but the data is not being dropped

Example:
I have a test file test_product, which creates a new CategoryModel for testing a product.

test_product.py:

def test_create_category_for_test():
    payload = {
        "name": "camisa_time"
    }
    response = product.post("/categories", json=payload, headers={"Content-Type": "application/json", "Authorization": f"Bearer {ACCESS_TOKEN}"})
    return response.json()["id"]

CATEGORY_ID = test_create_category_for_test()

In my category test file, I create a new category

test_category.py:

def test_create_category():
    payload = {
        "name": "Eletrônicos"
    }
    response = category.post("/categories", json=payload, headers={"Content-Type": "application/json", "Authorization": f"Bearer {ACCESS_TOKEN}"})
    assert response.status_code == 201
    assert response.json()["name"] == "Eletrônicos"
    assert response.json()["id"] is not None

However, when, in my category_test, I try to retrieve my newly created category ‘Eletrônicos’, it returns as the 0 index the category created at my test_order.py ‘camisa_time’

def test_get_category_by_id():
    response = category.get("/categories", headers={"Content-Type": "application/json", "Authorization": f"Bearer {ACCESS_TOKEN}"})
    category_id = response.json()[0]["id"]
    response = category.get(f"/categories/{category_id}", headers={"Content-Type": "application/json", "Authorization": f"Bearer {ACCESS_TOKEN}"})
    assert response.status_code == 200
    assert response.json()["id"] == category_id
    assert response.json()["name"] == "Eletrônicos"

The file structure

tests/
├── test_category.py
└── test_product.py

Error:

app/tests/test_category.py::test_get_category_by_id - AssertionError: assert 'camisa_time' == 'Eletrônicos'