[sqlalchemy] Re: objects created using sqlalchemy
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'
[sqlalchemy] Re: objects created using sqlalchemy
On Dec 5, 2008, at 3:01 PM, 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.
there's no need to session.save() any objects created within
associationproxy - when the parent object is saved, or if the parent
is already saved, any attachments are also saved using the on-by-
default save-update cascade.
Thanks in advance. Please CC me on any reply.
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')
# *patients*
# patient_id (PK)
# (Can use actual patient id as unique/alternate identifier
# Create index).
# sex - list of choices allowed
# age - (0, 140)
# time of death
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)
)
# *snps*
# snp_id (PK)
# name (name of snp)
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),
)
# *doublets*
# doublet_id (PK)
# seq (two letters AA, AG)
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
# mapper(Broker, brokers_table, properties={
# 'by_stock': relation(Holding,
# collection_class=attribute_mapped_collection('stock'))
# })
# 'cells' corresponds to a 1 to many relation.
mapper(Patient, patient_table, properties={'cells':relation(Cell,
backref='patient'),
'by_rsid': relation(Cell,
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,
