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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
