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

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.