Hi,
I've got some genome data, and I'm trying to move it into a db. The data looks like Patient FOOSNP BARSNP ... Tom AA AT John AT AA ... These columns correspond to SNPS (http://en.wikipedia.org/wiki/Single_nucleotide_polymorphism). Note there are a lot of columns, The question is what the best approach is to emulate such a setup. I could not find much information about this scenario. The approach I went for was to have the row info and column info in separate tables, and then have a linker table containing foreign keys pointing to a row, a column, and a SNP value. Then this will look something like PATIENT SNP SNPVAL John Foosnp AA Tom Barsnp AT This essentially maps the cartesian product of patient and snp to snpval. function: PATIENT x SNP -> SNPVAL Is this a reasonable way to approach this? If so, is there some way to tell the mapper what kind of relationship this table is trying to define? Thanks in advance, Faheem. ************************************************************************* from sqlalchemy import * from sqlalchemy.orm import * from datetime import datetime metadata = MetaData('sqlite:///data.sqlite') patient_table = Table( 'patient', metadata, Column('id', String(100), primary_key=True, index=True), Column('sex', String(1)), ) snp_table = Table( 'snp', metadata, Column('name', String(20), nullable=False, primary_key=True), ) snpval_table = Table( 'snpval', metadata, Column('id', Integer, primary_key=True), Column('val', String(2), nullable=False), ) cell_table = Table( 'patient_snpval', metadata, Column('patient', None, ForeignKey('patient.id', onupdate='CASCADE', ondelete='CASCADE'), index=True, nullable=False), Column('snp', None, ForeignKey('snp.name', onupdate='CASCADE', ondelete='CASCADE'), index=True, nullable=False), Column('snpval', None, ForeignKey('snpval.id', onupdate='CASCADE', ondelete='CASCADE'), index=True, nullable=False) ) metadata.create_all() class Patient(object): pass class Snp(object): pass class Snpval(object): pass class Cell(object): pass mapper(Patient, patient_table) mapper(Snp, snp_table) mapper(Snpval, snpval_table) mapper(Cell, cell_table) Session = sessionmaker() session = Session() session.commit() --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
