How do I code this relationship in SQLAlchemy?
- by Martin Del Vecchio
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.
