Re: [sqlalchemy] Aliased table in subsequent join and select statement does not work?

[Mike Bayer]

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 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.

[sqlalchemy] Aliased table in subsequent join and select statement does not work?

[Rogier Eggers]

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 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.

Re: [sqlalchemy] suppress echo of INSERT/UPDATE large binary data

[Kent Bower]

I attempted to search for such an enhancement but obviously failed to find
it.   Patching the changeset in was fairly straightforward.

Thanks very much Jonathan and Mike!!



On Wed, Aug 17, 2016 at 5:41 PM, Mike Bayer 
wrote:

>
>
> On 08/17/2016 01:25 PM, Kent wrote:
>
>> Generally, echo=True for log level of INFO is very helpful for log files.
>>
>> But on INSERT/UPDATE of a Binary column (at least with Oracle, BLOB) it
>> sure would be nice to skip the logging of the sometimes massive binary
>> data.
>>
>> Is this possible?
>>
>
> http://docs.sqlalchemy.org/en/latest/changelog/migration_11.
> html#large-parameter-and-row-values-are-now-truncated-in-log
> ging-and-exception-displays
>
> until you can upgrade write a logging output filter...
>
>
>
>
>> Thanks,
>> Kent
>>
>> --
>> 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.
>>
>
> --
> 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/gA3Q4DQWLKw/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.

[sqlalchemy] Re: Parameter injection issue upon db engine creation (tested with db2)

[Hemlata bhatt]

Hi Miklós,

Thanks for reporting the issue.The issue with be addressed in the next 
ibm_db_sa release.

Hemlata 

-- 
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.