I was confused as some of the SQLA functions I used here are from Core (e.g. in the whereclause) and was therefore focussing on Core.
But indeed aliased work much better (and make sense because of the mapped classes).... Thanks! On Thu, Aug 18, 2016 at 6:26 PM, Mike Bayer <mike...@zzzcomputing.com> wrote: > > > 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?highligh > t=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 sqlalchemy+unsubscr...@googlegroups.com >> <mailto:sqlalchemy+unsubscr...@googlegroups.com>. >> To post to this group, send email to sqlalchemy@googlegroups.com >> <mailto:sqlalchemy@googlegroups.com>. >> 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 a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit https://groups.google.com/d/to > pic/sqlalchemy/G9Fmd440PWg/unsubscribe. > To unsubscribe from this group and all its topics, 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. > -- 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.
