Yeah, sorry about that. Like I said, I don't normally build my classes
this way, defining all the columns in the Base Class. I define all my
columns, primary and foreign keys, etc manually first, so my SQLA classes
are minimally defined. And I cobbled together pieces from my real code and
your example to build that test code I sent you. So I forgot some things.
I did say it may not be completely right, and you might have to hack it
some. I apologize. Here is an attempt at cleaning it up.
When I try to implement your @expression into my real code, it doesn't
work. I am getting an error about multiple results returned within the
subquery.
@restwave.expression
def restwave(cls):
s = db.Session()
restw = (func.unnest(Wavelength.wavelength) / (1 + sampledb.NSA.z)).
label("restw")
unwave = s.query(restw).select_from(Wavelength).correlate(sampledb.
NSA).subquery("unwave")
agg_unwave = s.query(func.array_agg(unwave.c.restw)).label(
"restwarr")
joined = s.query(agg_unwave).select_from(
Cube
).join(sampledb.MangaTarget, sampledb.MangaTargetToNSA,
sampledb.NSA, Wavelength)
return joined.as_scalar()
session.query(datadb.Cube.restwave).filter(datadb.Cube.mangaid=='1-113520').
all()
ProgrammingError: (psycopg2.ProgrammingError) more than one row returned by
a subquery used as an expression
[SQL: 'SELECT (SELECT (SELECT array_agg(unwave.restw) AS array_agg_1
\nFROM (SELECT unnest(mangadatadb.wavelength.wavelength) / (%(z_1)s +
mangasampledb.nsa.z) AS restw \nFROM mangadatadb.wavelength) AS unwave) AS
restwarr \nFROM mangadatadb.cube JOIN mangasampledb.manga_target ON
mangasampledb.manga_target.pk = mangadatadb.cube.manga_target_pk JOIN
mangasampledb.manga_target_to_nsa ON mangasampledb.manga_target.pk =
mangasampledb.manga_target_to_nsa.manga_target_pk JOIN mangasampledb.nsa ON
mangasampledb.nsa.pk = mangasampledb.manga_target_to_nsa.nsa_pk JOIN
mangadatadb.wavelength ON mangadatadb.wavelength.pk =
mangadatadb.cube.wavelength_pk) AS anon_1 \nFROM mangadatadb.cube \nWHERE
mangadatadb.cube.mangaid = %(mangaid_1)s'] [parameters: {'z_1': 1,
'mangaid_1': '1-113520'}]
I don't know if this is because I have multiple versions of the same
object or not. I tried adding in a selection on version, but the joins did
not work out properly. This NSA table does not need to be joined with the
versions. What I expect the above to return is exactly what the raw SQL
returns. A list of the restwave array for each version of the object with
id = '1-113520'. I thought SQLA was designed precisely to let users do
what I'm trying, i.e. construct complex columns involving multiple selects,
without having to physically add a new column into the db, or write a new
SQL function in the db to call?
session.query(datadb.Cube.restwave).join(datadb.Cube,datadb.PipelineInfo,
datadb.PipelineVersion).filter(datadb.PipelineVersion=='v1_5_1',datadb.Cube.
mangaid=='1-113520').all()
InvalidRequestError: Could not find a FROM clause to join from. Tried
joining to <class
'sdss.internal.database.utah.mangadb.DataModelClasses.Cube'>, but got:
Can't find any foreign key relationships between 'nsa' and 'cube'.
On Wednesday, August 3, 2016 at 2:22:43 PM UTC-4, Mike Bayer wrote:
>
> There is still much ambiguity here and inaccuracy (JOINs on the outside
> or JOINs on the inside, the mappings have mistakes like foreign key to
> "pk" but no "pk", mappings without primary keys, "autoload" makes no
> sense as I don't have your tables, etc.), so I can only guess but
> perhaps give you enough clues. It is highly unusual to have a string
> of four JOINs inside of a column-based subquery, but when you say
> s.query(Cube.restwave), that's what that means here.
>
>
--
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 import ForeignKey, func
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
from sqlalchemy import create_engine
# import sqlite3
# conn = sqlite3.connect('/Users/Brian/Work/python/manga/test_sqla.db')
# c = conn.cursor()
# c.execute('''create table cube (pk integer primary key, mangaid text, wavelength_pk integer, target_pk integer);''')
# c.execute('''create table nsa (pk integer primary key, z real);''')
# c.execute('''create table target (pk integer primary key, mangaid text);''')
# c.execute('''create table target_to_nsa (pk integer primary key, target_pk integer, nsa_pk integer);''')
# c.execute('''create table wavelength (pk integer primary key, wavelength real[]);''')
# conn.commit()
# conn.close()
engine = create_engine('sqlite:///test_sqla.db', echo=True)
Base = declarative_base()
Session = sessionmaker(bind=engine)
class Target(Base):
__tablename__ = 'target'
pk = Column(Integer, primary_key=True)
mangaid = Column(String)
class Wavelength(Base):
__tablename__ = 'wavelength'
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'
pk = Column(Integer, primary_key=True)
z = Column(Float)
def __repr__(self):
return '<NSA (pk={0}, nsaid={1})>'.format(self.pk, self.nsaid)
class TargetToNSA(Base):
__tablename__ = 'target_to_nsa'
pk = Column(Integer, primary_key=True)
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'
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()
s = Session()
restw = (func.unnest(Wavelength.wavelength) / (1 + NSA.z)).label("restw")
unwave = s.query(restw).select_from(Wavelength).correlate(NSA).subquery("unwave")
agg_unwave = s.query(func.array_agg(unwave.c.restw)).label("restwarr")
joined = s.query(agg_unwave).select_from(
Cube
).join(Target, TargetToNSA, NSA, Wavelength)
return joined.as_scalar()
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
