如何在SQLAlchemy中编写这种关系？

Question

我对SQLAlchemy（和SQL）还不熟悉。我想不出我脑子里的想法是怎么编码的。在

我正在创建性能测试结果的数据库。在

测试运行由测试类型和数字组成（下面是TestRun类）

测试套件由被测试软件的版本字符串和一个或多个TestRun对象（下面是TestSuite类）组成。在

测试版本由具有给定版本名的所有测试套件组成。在

下面是我的代码，尽可能简单：

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref, sessionmaker

Base = declarative_base()

class TestVersion (Base):
    __tablename__ = 'versions'
    id = Column (Integer, primary_key=True)
    version_name = Column (String)

 def __init__ (self, version_name):
     self.version_name = version_name


class TestRun (Base):
    __tablename__ = 'runs'
    id = Column (Integer, primary_key=True)
    suite_directory = Column (String, ForeignKey ('suites.directory'))
    suite = relationship ('TestSuite', backref=backref ('runs', order_by=id))
    test_type = Column (String)
    rate = Column (Integer)

    def __init__ (self, test_type, rate):
        self.test_type = test_type
        self.rate = rate


class TestSuite (Base):
    __tablename__ = 'suites'
    directory = Column (String, primary_key=True)
    version_id = Column (Integer, ForeignKey ('versions.id'))
    version_ref = relationship ('TestVersion', backref=backref ('suites', order_by=directory))
    version_name = Column (String)

    def __init__ (self, directory, version_name):
        self.directory = directory
        self.version_name = version_name


# Create a v1.0 suite
suite1 = TestSuite ('dir1', 'v1.0')
suite1.runs.append (TestRun ('test1', 100))
suite1.runs.append (TestRun ('test2', 200))

# Create a another v1.0 suite
suite2 = TestSuite ('dir2', 'v1.0')
suite2.runs.append (TestRun ('test1', 101))
suite2.runs.append (TestRun ('test2', 201))

# Create another suite
suite3 = TestSuite ('dir3', 'v2.0')
suite3.runs.append (TestRun ('test1', 102))
suite3.runs.append (TestRun ('test2', 202))

# Create the in-memory database
engine = create_engine ('sqlite://')
Session = sessionmaker (bind=engine)
session = Session()
Base.metadata.create_all (engine)

# Add the suites in
version1 = TestVersion (suite1.version_name)
version1.suites.append (suite1)
session.add (suite1)

version2 = TestVersion (suite2.version_name)
version2.suites.append (suite2)
session.add (suite2)

version3 = TestVersion (suite3.version_name)
version3.suites.append (suite3)
session.add (suite3)

session.commit()

# Query the suites
for suite in session.query (TestSuite).order_by (TestSuite.directory):
    print "\nSuite directory %s, version %s has %d test runs:" % (suite.directory, suite.version_name, len (suite.runs))
    for run in suite.runs:
        print "  Test '%s', result %d" % (run.test_type, run.rate)

# Query the versions
for version in session.query (TestVersion).order_by (TestVersion.version_name):
    print "\nVersion %s has %d test suites:" % (version.version_name, len (version.suites))
    for suite in version.suites:
        print "  Suite directory %s, version %s has %d test runs:" % (suite.directory, suite.version_name, len (suite.runs))
        for run in suite.runs:
            print "    Test '%s', result %d" % (run.test_type, run.rate)

该程序的输出：

^{pr2}$

这是不正确的，因为有两个名为“v1.0”的TestVersion对象。为此，我添加了一个TestVersion对象的私有列表，以及一个查找匹配对象的函数：

versions = []
def find_or_create_version (version_name):

    # Find existing
    for version in versions:
        if version.version_name == version_name:
            return (version)

    # Create new
    version = TestVersion (version_name)
    versions.append (version)
    return (version)

然后我修改了代码，添加了使用它的记录：

# Add the suites in
version1 = find_or_create_version (suite1.version_name)
version1.suites.append (suite1)
session.add (suite1)

version2 = find_or_create_version (suite2.version_name)
version2.suites.append (suite2)
session.add (suite2)

version3 = find_or_create_version (suite3.version_name)
version3.suites.append (suite3)
session.add (suite3)

现在输出是我想要的：

Suite directory dir1, version v1.0 has 2 test runs:
  Test 'test1', result 100
  Test 'test2', result 200

Suite directory dir2, version v1.0 has 2 test runs:
  Test 'test1', result 101
  Test 'test2', result 201

Suite directory dir3, version v2.0 has 2 test runs:
  Test 'test1', result 102
  Test 'test2', result 202

Version v1.0 has 2 test suites:
  Suite directory dir1, version v1.0 has 2 test runs:
    Test 'test1', result 100
    Test 'test2', result 200
  Suite directory dir2, version v1.0 has 2 test runs:
    Test 'test1', result 101
    Test 'test2', result 201

Version v2.0 has 1 test suites:
  Suite directory dir3, version v2.0 has 2 test runs:
    Test 'test1', result 102
    Test 'test2', result 202

我觉得这是不对的；我手动跟踪唯一的版本名，并手动将套件添加到适当的TestVersion对象中是不对的。在

这个代码几乎是正确的吗？在

当我不是从头开始构建整个数据库时，会发生什么，如本例所示。如果数据库已经存在，是否必须查询数据库的TestVersion表来发现唯一的版本名？在

提前谢谢。我知道这是一个需要费力去完成的代码，我感谢你的帮助。在

Answer 1

我不明白你的问题是什么，很大程度上是因为你没有完善它。你的问题可能是关于一个模式，也可能是它对应的对象关系模型。因此，以下是ORM的核心部分：

class TestVersion(Base):
    __tablename__ = 'versions'
    id = Column(Integer, primary_key=True)
    name = Column(String)

class TestSuite(Base):
    __tablename__ = 'suites'
    directory = Column(String, primary_key=True)
    version = Column(Integer, ForeignKey ('versions.id'))

    parent = relationship(TestVersion, backref=backref('suites',
        order_by=directory))

class TestRun(Base):
    __tablename__ = 'runs'
    id = Column(Integer, primary_key=True)
    directory = Column(String, ForeignKey ('suites.directory'))

    parent = relationship(TestSuite, backref=backref('runs',
         order_by=id))

我对您的声明采取了很多自由度：抛出与您的问题无关的列、重新排序声明以使依赖链更加明显等等。也许相对于这种简化模型，您可以更好地描述您的问题。在

同样，像PEP 8这样的编码标准的存在是有原因的：如果你想让别人理解你的代码，就用4个空格缩进，避免名字和“（”之间的空格，限制行数为79个字符等等。是的，这看起来很迂腐，但你只是遇到了这样一种情况：你的读者阅读你的代码比你想的要困难。在