将表从主数据库迁移到test_db以执行测试，然后删除测试DB ( Migrating和pytest)

Question

我开始按照正式文档编写测试模块，并编写了以下代码：

from ..app.user.model import User
from fastapi.testclient import TestClient
from .temp_app import app

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from app.server.database import get_db, Base

SQLALCHEMY_DATABASE_URL = "sqlite:///./test.db"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)
TestingSessionLocal = sessionmaker(autocommit=False, autoflush=False, 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

client = TestClient(app)

#perform tests imported from different modules

Base.metadata.drop_all(bind=engine)
#attempt to delete the database after the tests are over

SQLALCHEMY_DATABASE_URL应该在test.db中执行测试，而override_get_db方法应该得到test.db而不是主数据库。

当我在SQLALCHEMY_DATABASE_URL指向主SQL数据库的情况下运行测试时，测试运行良好，但是使用我的代码我得到：

app\test\test_db.py (sqlite3.OperationalError)没有这样的表:用户

它表示我的数据库中的表尚未迁移到test.db。

我在正式文件中找不到这方面的任何信息，所以我有点困惑是否做错了什么，或者只是需要一些代码将表迁移到test.db。

请有人帮助我理解这一点，并对测试数据库中的所有表运行测试吗？

而且，在完成pytest运行测试之后，test.db文件不会被删除，所以我可能不明白Base.metadata.drop_all(bind=engine)是如何工作的。

谢谢您抽时间见我。

编辑:导入所有模型并设置echo=True之后的日志

2022-02-04 17:43:37,691 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-02-04 17:43:37,691 INFO sqlalchemy.engine.Engine SELECT users.id AS users_id, users."mobileNo" AS "users_mobileNo", users.email AS users_email, users."fullName" AS "users_fullName", users.role AS users_role, users."referralCode" AS "users_referralCode", users.status AS users_status
FROM users
WHERE users."mobileNo" = ? OR users.email = ?
 LIMIT ? OFFSET ?
2022-02-04 17:43:37,691 INFO sqlalchemy.engine.Engine [cached since 9.304s ago] ('1234567890', 'johndoe@gmail.com', 1, 0)
(sqlite3.OperationalError) no such table: users
[SQL: SELECT users.id AS users_id, users."mobileNo" AS "users_mobileNo", users.email AS users_email, users."fullName" AS "users_fullName", users.role AS users_role, users."referralCode" AS "users_referralCode", users.status AS users_status
FROM users
WHERE users."mobileNo" = ? OR users.email = ?
 LIMIT ? OFFSET ?]
[parameters: ('1234567890', 'johndoe@gmail.com', 1, 0)]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
2022-02-04 17:43:37,692 INFO sqlalchemy.engine.Engine ROLLBACK
------------------------------------------------------------------------------------------------------------ Captured log call -------------------------------------------------------------------------------------------------------------
INFO     sqlalchemy.engine.Engine:log.py:117 BEGIN (implicit)
INFO     sqlalchemy.engine.Engine:log.py:117 SELECT users.id AS users_id, users."mobileNo" AS "users_mobileNo", users.email AS users_email, users."fullName" AS "users_fullName", users.role AS users_role, users."referralCode" AS "users_referralCode", users.status AS users_status
FROM users
WHERE users."mobileNo" = ? OR users.email = ?
 LIMIT ? OFFSET ?
INFO     sqlalchemy.engine.Engine:log.py:117 [cached since 9.304s ago] ('1234567890', 'johndoe@gmail.com', 1, 0)
INFO     sqlalchemy.engine.Engine:log.py:117 ROLLBACK
============================================================================================================= warnings summary =============================================================================================================
app\testOptions\model.py:4
  C:\Users\shrid\OneDrive\Documents\backend\app\testOptions\model.py:4: PytestCollectionWarning: cannot collect test class 'TestOptions' because it has a __init__ constructor (from: app/test/test_db.py)
    class TestOptions(Base):
 
app\testResponse\model.py:9
  C:\Users\shrid\OneDrive\Documents\backend\app\testResponse\model.py:9: PytestCollectionWarning: cannot collect test class 'TestResult' because it has a __init__ constructor (from: app/test/test_db.py)
    class TestResult(Base):
 
app\testResponse\model.py:25
  C:\Users\shrid\OneDrive\Documents\backend\app\testResponse\model.py:25: PytestCollectionWarning: cannot collect test class 'TestQuestions' because it has a __init__ constructor (from: app/test/test_db.py)
    class TestQuestions(Base):
 
-- Docs: https://docs.pytest.org/en/stable/warnings.html
========================================================================================================= short test summary info ==========================================================================================================
FAILED app/test/test_db.py::test_create_user - AssertionError: {"detail":"Something went wrong!"}

编辑2:用户模型：

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    mobileNo = Column(String, index=True, nullable=False, default="")
    email = Column(String, index=True, nullable=False, default="")
    fullName = Column(String, nullable=False, default="")
    role = Column(String, default='user')
    referralCode = Column(String, default='1234567890')
    status = Column(String, default='active')

Answer 1

我无法给你一个“确定”的答案，除非我看到了你设置ORM模型的整个模块，但是阅读了您所写的内容，我认为如果您从模型的模块中导入Base (这与您在所有应用程序中使用的相同)，Base将有一个名为元数据( Base.metadata)的对象，它将包含您的所有模型，所以当您调用create_all()时，它会看到您定义的模型并创建它。做一个这样的进口：

from my_app_path.models.main_module_that_has_all_models import Base

如果您的模型分散在应用程序结构中，您应该有一个模块来导入它们，因为如果您将ORM模型到处传播，那么处理ORM模型将是一件痛苦的事情。