On 08/18/2016 11:41 AM, Rogier Eggers wrote:
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?
Signals appears to be a mapped class, not a Table, so normally you would
use aliased():
http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=aliased#sqlalchemy.orm.aliased
alias() is a Core construct that expects something like a Table object
or Select object, and you'd get back an Alias object that has a .c.
attribute and doesn't act like a class at all, it acts like a core
selectable.
However, in version 1.1 of SQLAlchemy (due for release any day), the
alias() function *will* work with your class, but again you get back a
Core selectable, and not an object that acts like your class. Your
example seems to want to use Core ultimately which is fine, you'd just
need to keep straight between which objects are ORM entities (no .c.
namespace, all your methods and attributes remain available) and which
are Core selectables (.c. namespace, only columns).
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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
