How do I code this relationship in SQLAlchemy?

I am new to SQLAlchemy (and SQL, for that matter). I can't figure out how to code the idea I have in my head.

I am creating a database of performance-test results.

A test run consists of a test type and a number (this is class TestRun below)

A test suite consists the version string of the software being tested, and one or more TestRun objects (this is class TestSuite below).

A test version consists of all test suites with the given version name.

Here is my code, as simple as I can make it:

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)

The output of this program:

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 1 test suites:
  Suite directory dir1, version v1.0 has 2 test runs:
    Test 'test1', result 100
    Test 'test2', result 200

Version v1.0 has 1 test suites:
  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

This is not correct, since there are two TestVersion objects with the name 'v1.0'. I hacked my way around this by adding a private list of TestVersion objects, and a function to find a matching one:

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)

Then I modified my code that adds the records to use it:

# 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)

Now the output is what I want:

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 'test开发者_JAVA百科2', 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

This feels wrong to me; it doesn't feel right that I am manually keeping track of the unique version names, and manually adding the suites to the appropriate TestVersion objects.

Is this code even close to being correct?

And what happens when I'm not building the entire database from scratch, as in this example. If the database already exists, do I have to query the database's TestVersion table to discover the unique version names?

Thanks in advance. I know this is a lot of code to wade through, and I appreciate the help.

I cannot understand what your question is, in large part because you haven't refined it. Your question is about a schema perhaps, and possibly its corresponding object relational model. So, here is the ORM stripped to its core:

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))

I took a lot of liberties with your declaration: throwing out columns unrelated to your issue, reordering the declarations to make the dependency chain more obvious, etc. Perhaps against this reduced model you can better describe your problem.

Also, coding standards like PEP 8 exist for a reason: if you want your code to be understandable to others, use 4 space indentions and eschew spaces between a Name and a '(', limit lines to 79 characters, etc. Yes, this seems pedantic, but you just ran into a situation where your reader had more difficulty reading your code than you'd like.