On Fri, 5 Dec 2008, Faheem Mitha wrote:
>
> Hi,
>
> I'm using sqla with the following schema (see below). I'm creating a cell
> object implicitly, using the function make_cell and the association proxy
> pattern.
>
> def make_cell(patient_obj, snp_obj, snpval):
> patient_obj.snps[snp_obj] = snpval
> return patient_obj
>
> My question is, is there some way to get my hands on the Cell object that was
> just created? If possible, I'd like make_cell to return the cell object. My
> immediate reason is that this would make it easy to save the object using
> session.save() (there might be some indirect way to do this, of course), but
> it would be nice anyway.
A followup to my original post. I must be doing something wrong, because
the Cell object is not being saved. and the proxy in the "other direction"
is not being updated either. I'm reluctant to ask for debugging help, but
I'm having difficulty tracking down the problem.
The files included in order below are
Schema file: dbschema.py
Utility functions: dbutils.py
Session file: dbsession.py
The last file runs the actual code to populate the dbs, and is one big
function, make_tables.
The most relevant lines here are:
print "p1.snps is %s"%p1.snps
print "s.patients is %s"%s.patients
print "cell table is %s"%list(cell_table.select().execute())
[...]
get_obj(session, Cell)
The output I'm getting is
p1.snps is {<SNP rs10458597>: <Snpval 0>}
s.patients is {}
cell table is []
[...]
*** list of Cell objects in class. ***
*** end list of Cell objects. ***
I wouldn't expect the last three, namely s.patients, cell table and
list of Cell objects to all be empty. Can someone tell me what I'm
doing wrong? For an experienced person, it may be obvious.
Note: My use of cascade in the Mappers may be redundant. I just put it
in there for good measure, and I'm not sure what it does.
Regards, Faheem.
****************************************************************************
dbschema.py
****************************************************************************
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('sqlite:///btsnp.sqlite')
patient_table = Table(
'patient', metadata,
Column('id', String(20), primary_key=True, index=True),
Column('celfilename', String(30), nullable=False, index=True, unique=True),
Column('sex', String(1)),
)
cell_table = Table(
'cell', metadata,
Column('patient_id', None, ForeignKey('patient.id', onupdate='CASCADE',
ondelete='CASCADE'), index=True, nullable=False, primary_key=True),
Column('snp_id', None, ForeignKey('snp.rsid', 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('rsid', String(20), nullable=False, primary_key=True),
Column('chromosome', Integer, nullable=False),
Column('location', Integer, nullable=False),
Column('probe_set_id', String(20), nullable=False, unique=True),
Column('allele', String(3), nullable=False),
)
snpval_table = Table(
'snpval', metadata,
Column('val', Integer, primary_key=True),
)
metadata.create_all()
def create_cell(snp, snpval):
return Cell(snp=snp, snpval=snpval)
class Patient(object):
def __init__(self, id, celfilename, sex):
self.id = id
self.celfilename = celfilename
self.sex = sex
def __repr__(self):
return '<Patient %s>'%self.id
snps = association_proxy('by_rsid', 'snpval', creator=create_cell)
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, rsid, chromosome, location, probe_set_id, allele):
self.rsid = rsid
self.chromosome = chromosome
self.location = location
self.probe_set_id = probe_set_id
self.allele = allele
def __repr__(self):
return '<SNP %s>'%self.rsid
patients = association_proxy('by_patient', 'snpval', creator=create_cell)
class Snpval(object):
def __init__(self, val):
self.val = val
def __repr__(self):
return '<Snpval %s>'%self.val
# 'cells' corresponds to a 1 to many relation.
mapper(Patient, patient_table, properties={'cells':relation(Cell,
backref='patient'),
'by_rsid': relation(Cell, cascade =
"all, delete-orphan", collection_class=attribute_mapped_collection('snp'))}
)
# 'patient_snpval' corresponds to a many to 1 relation.
# 'patient_snpval' corresponds to a 1 to 1 relation.
mapper(Cell, cell_table, properties={'snp':relation(Snp, backref='cells'),
'snpval':cell_table.c.snpval_id,
'snpval_obj':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'))})
mapper(Snpval, snpval_table)
****************************************************************************
dbutils.py
****************************************************************************
def add_obj(session, obj):
""" Check if object primary key exists in db. If so,exit, else
add.
"""
from sqlalchemy import and_
from sqlalchemy.orm import object_mapper
mapper = object_mapper(obj)
pid = mapper.primary_key_from_instance(obj)
criterion = and_(*(col == val for col, val in zip(mapper.primary_key,
mapper.primary_key_from_instance(obj))))
if session.query(obj.__class__).filter(criterion).count() > 0:
print "%s object with id %s is already in db."%(type(obj).__name__,
pid)
exit
else:
session.save(obj)
session.commit()
def make_cell(patient_obj, snp_obj, snpval):
print "making cell..."
patient_obj.snps[snp_obj] = snpval
return patient_obj
def get_obj(session, cls):
query = session.query(cls)
print "*** list of %s objects in class. ***"%cls.__name__
for obj in query:
print "%s is %s"%(cls.__name__, obj)
print "*** end list of %s objects. ***"%cls.__name__
************************************************************************
dbsession.py
************************************************************************
def make_tables():
from dbschema import cell_table, patient_table, Patient, Cell, Snp,
Snpval, metadata
from sqlalchemy import *
from sqlalchemy.orm import *
from dbutils import add_obj, get_obj, make_cell
import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
logging.getLogger('sqlalchemy.orm.unitofwork').setLevel(logging.DEBUG)
metadata.bind.echo = True
Session = sessionmaker()
session = Session()
patientname1 = "John"
patientname2 = "Ted"
cfname1 = "DUKE00001_plateA_A10.CEL"
cfname2 = "DUKE00001_plateA_A11.CEL"
sex = "M"
rsid = "rs10458597"
chromosome = "1"
location = "554484"
snpval = "AA"
psid1 = "SNP_A-1780419"
allele = "A/G"
p1 = Patient(patientname1, cfname1, sex)
p2 = Patient(patientname2, cfname2, sex)
s = Snp(rsid, chromosome, location, psid1, allele)
svlst = []
for i in [0, 1, 2]:
svlst.append(Snpval(i))
make_cell(p1, s, svlst[0])
make_cell(p2, s, svlst[1])
print "p1.snps is %s"%p1.snps
print "s.patients is %s"%s.patients
print "cell table is %s"%list(cell_table.select().execute())
for i in [0, 1, 2]:
add_obj(session, svlst[i])
add_obj(session, p1)
add_obj(session, p2)
add_obj(session, s)
## Delete all patient objects.
#session.query(Patient).delete()
session.flush()
get_obj(session, Patient)
get_obj(session, Snp)
get_obj(session, Snpval)
get_obj(session, Cell)
make_tables()
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
