[SQLObject] Join with results from both tables

[Goldberg, A. P <art...@ms...>]

Hi

I'm just learning sqlobject and find it pretty neat!

Suppose I have this:
from sqlobject import *

class Variant2(SQLObject):
    class sqlmeta:
        style = Style(longID=True)
    ref = StringCol()
    alt = StringCol()
    SubjectID = StringCol()

class Subject2(SQLObject):
    FamilyID = StringCol()
    IndivID = StringCol( alternateID=True, length=50 )  # do not know if this is long enough
    Sex = StringCol()

connection = connectionForURI(Minerva_URI)
connection.debug = True
sqlhub.processConnection = connection
Variant2.dropTable( True, )
Variant2.createTable()
Subject2.dropTable( True, )
Subject2.createTable()

Variant2(
    ref = 'A',
    alt = 'C',
    SubjectID = 'Sub1' )

Variant2(
    ref = 'A',
    alt = 'G',
    SubjectID = 'Sub2' )

Subject2( FamilyID = '', IndivID = 'Sub1', Sex = '1' )
Subject2( FamilyID = '', IndivID = 'Sub2', Sex = '2' )

for var in Variant2.select(
    """ subject2.indiv_id = Variant2.SubjectID""",
    clauseTables=['subject2']):
    print var

How would I generate a query that joins Variant2 and subject2? Like this:

SELECT Variant2.Variant2_id, Variant2.ref, Variant2.alt, Variant2.AAC, Variant2.SubjectID, subject2.Sex FROM subject2, Variant2 WHERE  subject2.indiv_id = Variant2.SubjectID;

Also, how could I define SubjectID as a foreign key that references indiv_id?

Thanks
Arthur




---

Arthur Goldberg
Associate Professor of Psychiatry
Seaver Autism Center and Icahn Institute for Genomics & Multiscale Biology
Icahn School of Medicine at Mount Sinai
Seaver Center, Room ABE-33
212-241-4229
Art...@ms...<mailto:Art...@ms...>
Follow us on Twitter @IcahnInstitute<https://twitter.com/IcahnInstitute>