Thread: [SQLObject] Join with results from both tables
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Goldberg, A. P <art...@ms...> - 2014-06-16 20:50:54
|
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> |
From: Oleg B. <ph...@ph...> - 2014-06-16 21:26:12
|
Hi! On Mon, Jun 16, 2014 at 08:50:44PM +0000, "Goldberg, Arthur P" <art...@ms...> wrote: > Hi > > I'm just learning sqlobject and find it pretty neat! Welcome! > 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; for var in Variant2.select(Subject2.q.IndivID==Variant2.q.SubjectID): print var When you use magic .q. attributes SQLObject derives clauseTables automatically; it lists all tables used in WHERE clause. > Also, how could I define SubjectID as a foreign key that references indiv_id? Alas, that much harder. Short answer is this (I show only relevant changes): class Variant2(SQLObject): class sqlmeta: idType = str Subject = ForeignKey('Subject2', refColumn='IndivID') 'idType = str' is required because you want SubjectID to be a string foreign key. But at the same time it changes the type of the 'id' column -- it also becomes TEXT and loses autoincrement property so you have to assign it manually. The entire programs is now: class Variant2(SQLObject): class sqlmeta: idType = str style = Style(longID=True) ref = StringCol() alt = StringCol() Subject = ForeignKey('Subject2', refColumn='IndivID') class Subject2(SQLObject): FamilyID = StringCol() IndivID = StringCol( alternateID=True, length=50 ) # do not know if this is long enough Sex = StringCol() Variant2.createTable() Subject2.createTable() Variant2( id = '1', ref = 'A', alt = 'C', SubjectID = 'Sub1' ) Variant2( id = '2', ref = 'A', alt = 'G', SubjectID = 'Sub2' ) Subject2( FamilyID = '', IndivID = 'Sub1', Sex = '1' ) Subject2( FamilyID = '', IndivID = 'Sub2', Sex = '2' ) for var in Variant2.select(Subject2.q.IndivID==Variant2.q.SubjectID): print var Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |