Hi,
I'm writing code (see below) to drop and add back foreign key constraints to a db table. Incidentally, this code is not working (the function just hangs) so I may have made some kind of syntax error. Anyway, I was wondering if there was some way to accomplish this in a more "high-level" way using the sqla ORM. The tables in question were created using the ORM, so the ORM knows about them, and, at least in theory should be able to manipulate them. Schema follows below. However, currently, I'm not sure how do this. Suggestions appreciated. Please CC me on any reply. Regards, Faheem. ************************************************************************* conn = db.connect() conn.execute("ALTER TABLE cell DROP CONSTRAINT cell_patient_chipid_fkey; ALTER TABLE cell DROP CONSTRAINT cell_snp_id_fkey; ALTER TABLE cell DROP CONSTRAINT cell_snpval_id_fkey;") #conn.execute("COPY cell FROM '" + csvfilename + "' USING DELIMITERS ','") conn.execute("ALTER TABLE ONLY cell ADD CONSTRAINT cell_patient_chipid_fkey FOREIGN KEY (patient_chipid) REFERENCES patient(chipid) ON UPDATE CASCADE ON DELETE CASCADE;") conn.execute("ALTER TABLE ONLY cell ADD CONSTRAINT cell_snp_id_fkey FOREIGN KEY (snp_id) REFERENCES snp(fid) ON UPDATE CASCADE ON DELETE CASCADE;") conn.execute("ALTER TABLE ONLY cell ADD CONSTRAINT cell_snpval_id_fkey FOREIGN KEY (snpval_id) REFERENCES snpval(val) ON UPDATE CASCADE ON DELETE CASCADE;") conn.close() ************************************************************************** from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.associationproxy import association_proxy from sqlalchemy.orm.collections import attribute_mapped_collection from datetime import datetime metadata = MetaData() patient_table = Table( 'patient', metadata, Column('chipid', String(30), primary_key=True, index=True), Column('studyid', String(20), nullable=False, index=True), Column('sex_id', None, ForeignKey('sex.val', onupdate='CASCADE', ondelete='CASCADE'), index=True, nullable=False), Column('race_id', None, ForeignKey('race.val', onupdate='CASCADE', ondelete='CASCADE'), index=True, nullable=False), Column('phenotype', Boolean), ) # Allow M (male), F (female), U (unknown). sex_table = Table( 'sex', metadata, Column('val', String(1), primary_key=True), ) race_table = Table( 'race', metadata, Column('val', String(25), primary_key=True), ) cell_table = Table( 'cell', metadata, Column('patient_chipid', None, ForeignKey('patient.chipid', onupdate='CASCADE', ondelete='CASCADE'), index=True, nullable=False, primary_key=True), Column('snp_id', None, ForeignKey('snp.fid', onupdate='CASCADE', ondelete='CASCADE'), index=True, nullable=False, primary_key=True), Column('snpval_id', None, ForeignKey('snpval.val', onupdate='CASCADE', ondelete='CASCADE'), index=True, nullable=False) ) snp_table = Table( 'snp', metadata, Column('fid', String(20), nullable=False, primary_key=True), Column('rsid', String(20), nullable=False), Column('chromosome', String(2), nullable=False), Column('location', Integer, nullable=False), Column('alleleA_id', None, ForeignKey('allele.val', onupdate='CASCADE', ondelete='CASCADE'), index=True, nullable=False), Column('alleleB_id', None, ForeignKey('allele.val', onupdate='CASCADE', ondelete='CASCADE'), index=True, nullable=False), ) allele_table = Table( 'allele', metadata, Column('val', String(1), primary_key=True), ) snpval_table = Table( 'snpval', metadata, Column('val', Integer, primary_key=True), ) def create_cell(snp, snpval): return Cell(snp=snp, snpval=snpval) class Patient(object): def __init__(self, chipid, studyid, sex, race, phenotype): self.chipid = chipid self.studyid = studyid self.sex = sex self.race = race self.phenotype = phenotype def __repr__(self): return '<Patient %s>'%self.chipid snps = association_proxy('by_fid', 'snpval', creator=create_cell) class Sex(object): def __init__(self, val): self.val = val def __repr__(self): return '<Sex %s>'%self.val class Race(object): def __init__(self, val): self.val = val def __repr__(self): return '<Race %s>'%self.val class Cell(object): def __init__(self, patient=None, snp=None, snpval=None): self.patient = patient self.snp = snp self.snpval = snpval def __repr__(self): return '<Cell %s>'%self.snpval class Snp(object): def __init__(self, fid, rsid, chromosome, location, alleleA, alleleB): self.fid = fid self.rsid = rsid self.chromosome = chromosome self.location = location self.alleleA = alleleA self.alleleB = alleleB def __repr__(self): return '<SNP %s>'%self.fid patients = association_proxy('by_patient', 'snpval', creator=create_cell) class Allele(object): def __init__(self, val): self.val = val def __repr__(self): return '<Allele %s>'%self.val class Snpval(object): def __init__(self, val): self.val = val def __repr__(self): return '<Snpval %s>'%self.val mapper(Patient, patient_table, properties= {'sex':relation(Sex, backref='patients'), 'race':relation(Race, backref='patients'), 'by_fid': relation(Cell, cascade = "all, delete-orphan", collection_class=attribute_mapped_collection('snp')) }) mapper(Sex, sex_table) mapper(Race, race_table) mapper(Cell, cell_table, properties={'patient':relation(Patient, backref='cells'), 'snp':relation(Snp, backref='cells'), 'snpval':relation(Snpval, uselist=False, backref='cell'), }) mapper(Snp, snp_table, properties={'by_patient':relation(Cell, cascade = "all, delete-orphan", collection_class=attribute_mapped_collection('patient')), 'alleleA':relation(Allele, backref='snps_alleleA', primaryjoin=snp_table.c.alleleA_id==allele_table.c.val), 'alleleB':relation(Allele, backref='snps_alleleB', primaryjoin=snp_table.c.alleleB_id==allele_table.c.val), }) mapper(Allele, allele_table) mapper(Snpval, snpval_table) --~--~---------~--~----~------------~-------~--~----~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---