Hi!
On Mon, Jun 16, 2014 at 08:50:44PM +0000, "Goldberg, Arthur P"
<arthur.p.goldb...@mssm.edu> 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/ p...@phdru.name
Programmers don't die, they just GOSUB without RETURN.
------------------------------------------------------------------------------
HPCC Systems Open Source Big Data Platform from LexisNexis Risk Solutions
Find What Matters Most in Your Big Data with HPCC Systems
Open Source. Fast. Scalable. Simple. Ideal for Dirty Data.
Leverages Graph Analysis for Fast Processing & Easy Data Exploration
http://p.sf.net/sfu/hpccsystems
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
