I am wondering whether I use the incorrect functions in sqlalchemy or if
I'm possibly just going the complete wrong way about this.
I have two Tables, "Tests" and "Signals", which are related through a
many-to-many relationship (technically it could be one-to-many, but that
decision has been made before me).
I would like to make a select statement in sqlalchemy that takes fields
from two records in the Signal table and present them as separate columns
in the output. I was thinking to achieve this with aliases as in the code
below. But this fails already when trying to set an alias:
"Signals_1 = alias(Signals,'Signals_1')"
This delivers the follow (truncated) stracktrace:
"File
"D:\Workspace\SMARTCamelot\src\venv_SMART_CAMELOT\lib\site-packages\sqlalchemy\sql\expression.py",
line 832, in alias
return Alias(selectable, name=name)
File
"D:\Workspace\SMARTCamelot\src\venv_SMART_CAMELOT\lib\site-packages\sqlalchemy\sql\expression.py",
line 4054, in __init__
self.supports_execution = baseselectable.supports_execution
AttributeError: type object 'Signals' has no attribute 'supports_execution'"
Why can I not set an alias?
Also, I realise that this design is probably not the best. In my case the
Signals table is very large. By first doing two outerjoins, I am
effectively working with a very large number of records. The whereclause
could also be applied to the two aliased Signals tables earlier. How could
I do that?
Note that I use this select statement as part of a view in Camelot:
http://python-camelot.s3.amazonaws.com/gpl/default/pyqt/doc/doc/views.html#definition-of-the-view
This part however only uses sqlalchemy code.
More complete code is as follows:
...
from model import Tests, Signals
from sqlalchemy.orm import outerjoin
from sqlalchemy.sql import select, and_
from sqlalchemy.sql.expression import alias
Signals_1 = alias(Signals,'Signals_1')
Signals_2 = alias(Signals,'Signals_2')
from_obj = outerjoin(Signals_1,Tests,Signals_1.Tests)
from_obj = from_obj.outerjoin(Signals_2,Tests.Signals)
s = select([
Signals_1.id,
Tests.id.label('Tests.id'),
Tests.TestNumber.label('TestNo'),
Signals_1.id.label('Signals_1.id'),
Signals_1.value.label('Signals_1_value),
Signals_2.id.label('Signals_2.id'),
Signals_2.value.label('Signals_2_value)
],
from_obj = from_obj,
whereclause = and_(
Signals_1.SignalName.like('sig1'),
Signals_2.SignalName.like('sig2')
)
)
...
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
