Ok. Here is my test file. I tried to set it up as much as I could, but I
don't normally set up my db and sessions this way, so you may have to hack
a bit here and there to finish some setup. My original setup has classes
from two different schema. I don't know if that makes any difference.
I've also added some lines to add objects into the tables that I think you
can use to recreate my problem. Let me know if you need anything more in
this file.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, deferred, sessionmaker
from sqlalchemy.schema import Column
from sqlalchemy.dialects.postgresql import *
from sqlalchemy.types import Float, Integer, String
from sqlalchemy.ext.hybrid import hybrid_property
Base = declarative_base()
Session = sessionmaker()
class Target(Base):
__tablename__ = 'target'
__table_args__ = {'autoload': True, 'schema': 'sampledb'}
id = Column(Integer, primary_key=True)
mangaid = Column(String)
class Wavelength(Base):
__tablename__ = 'wavelength'
__table_args__ = {'autoload': True, 'schema': 'datadb'}
pk = Column(Integer, primary_key=True)
wavelength = deferred(Column(ARRAY(Float, zero_indexes=True)))
def __repr__(self):
return '<Wavelength (pk={0})>'.format(self.pk)
class NSA(Base):
__tablename__ = 'nsa'
__table_args__ = {'autoload': True, 'schema': 'sampledb'}
z = Column(Float)
def __repr__(self):
return '<NSA (pk={0}, nsaid={1})>'.format(self.pk, self.nsaid)
class TargetToNSA(Base):
__tablename__ = 'target_to_nsa'
__table_args__ = {'autoload': True, 'schema': 'sampledb'}
target_pk = Column(Integer, ForeignKey('target.pk'))
nsa_pk = Column(Integer, ForeignKey('nsa.pk'))
def __repr__(self):
return '<TargetToNSA (pk={0})>'.format(self.pk)
# Relationships
NSA.targets = relationship(Target, backref='NSA_objects', secondary=TargetToNSA.__table__)
class Cube(Base):
__tablename__ = 'cube'
__table_args__ = {'autoload': True, 'schema': 'datadb'}
pk = Column(Integer, primary_key=True)
mangaid = Column(String)
target_pk = Column(Integer, ForeignKey('target.pk'))
target = relationship(Target, backref='cubes')
wavelength_pk = Column(Integer, ForeignKey('wavelength.pk'))
wavelength = relationship(Wavelength, backref='cubes')
@hybrid_property
def restwave(self):
if self.target:
redshift = self.target.NSA_objects[0].z
wave = np.array(self.wavelength.wavelength)
restwave = wave/(1+redshift)
return restwave
else:
return None
@restwave.expression
def restwave(cls):
session = Session.object_session(self)
nsaz = session.query(NSA.z.label('z')).\
join(TargetToNSA, Target, Cube).\
filter(Cube.mangaid == cls.mangaid).subquery('nsaz', with_labels=True)
unwave = session.query((func.unnest(Wavelength.wavelength)/(1+nsaz.c.z)).label('restw')).subquery('unwave', with_labels=True)
restwave = session.query(func.array_agg(unwave.c.restw)).as_scalar()
return restwave
session = Session()
# Add objects
session.begin()
wave = Wavelength(wavelength=[3950.00, 4000.00, 5100.00, 5200.00, 6212.00, 8348.00])
session.add(wave)
nsa = [NSA(z=0.09999543), NSA(z=1.234567), NSA(z=0.0167534563)]
session.add_all(nsa)
targets = [Target(mangaid='1-234567', NSA_objects=nsa[0]),
Target(mangaid='1-113520', NSA_objects=nsa[2]),
Target(mangaid='1-367842', NSA_objects=nsa[1])]
session.add_all(targets)
session.flush()
cubes = [Cube(wavelength=wave, mangaid='1-987345'),
Cube(wavelength=wave, mangaid='1-234567', target=targets[0]),
Cube(wavelength=wave, mangaid='1-367842', target=targets[2]),
Cube(wavelength=wave, mangaid='1-113520', target=targets[1])]
session.commit()
q = session.query(Cube.restwave).filter(Cube.mangaid == '1-113520')
print q
