Re: [sqlalchemy] correlated subquery as column_property can't be queried independently?
if you use 1.4 /2.0 querying style you can call upon the scalars() method of
the result
result = session.execute(select(MyClass.attr))
elements = result.scalars().all()
or
result = session.scalars(select(...))
elements = result.all()
otherwise you can just iterate like this:
elements = [e for e, in sess.query(MyClass.attr)]
On Wed, Nov 10, 2021, at 3:22 PM, niuji...@gmail.com wrote:
> This is very helpful.
>
> How to return a list of scalar values in this case? Now the query returns a
> list of tuples, and each tuple only has one value, which is what actually
> needed. Is there a parameter to return a series of scalar value like this?
> On Wednesday, November 10, 2021 at 12:05:27 PM UTC-8 Mike Bayer wrote:
>> __
>> it has to do with how SQLAlchemy determines the FROM list in a select()
>> statement.
>>
>> if you say select(table.c.id), it knows that "table" is the thing to select
>> "from".
>>
>> however, if you say select(select(...).correlate(...).scalar_subquery()),
>> that's assuming it's a SELECT from a scalar subquery, which normally does
>> not imply a FROM clause.
>>
>> In this case, that you are saying correlate(C), we could argue that *does*
>> imply a FROM clause of some kind, but SQLAlchemy doesn't make that leap
>> right now. You might have said correlate(C, D) or correlate_except(Q) and
>> then it's not as clear what the scalar subquery would be selected FROM.
>>
>> going further, there *could* be some new kind of API where a scalar subquery
>> could suggest a FROM clause that should be used when this subquery is
>> selected FROM. that would allow your column_property() to be built up such
>> that it would be "self sufficient" when queried by itself. but again we
>> don't have that right now.
>>
>> with all those options exhausted, for SQLAlchemy to be given
>> select(select().scalar_subquery()), and then to produce "SELECT * FROM
>> (SELECT ) FROM ", that FROM part has to be added on
>> explicitly, i.e. select(select().scalar_subuqery()).select_from(> table>).
>>
>>
>>
>> On Wed, Nov 10, 2021, at 2:35 PM, niuji...@gmail.com wrote:
>>> This works! Could you explain a little about this differences by using
>>> select_from here? I think this is very important and useful, really want to
>>> learn it right.
>>>
>>> On Wednesday, November 10, 2021 at 5:55:44 AM UTC-8 Mike Bayer wrote:
__
try calling:
query(C.symbol_from_a).select_from(C)
On Wed, Nov 10, 2021, at 4:50 AM, niuji...@gmail.com wrote:
> class A(Base):
> primary_id = Column(Integer, prirmary_key=True)
> some_A_marker = Column(String)
>
> class B(Base):
> primary_id = Column(Integer, primary_key=True)
> referencing_A_id = Column(Integer, ForeignKey(A.primary_id))
>
> class C(Base):
> primary_id = Column(Integer, primary_key=True)
> basedOn_B_id = Column(Integer, ForeignKey(B.primary_id))
> symbol_from_A = column_property(
>
> sasql.select(A.some_A_marker).select_from(A).join(B).where(B.primary_id
> == C.basedOn_B_id).correlate(C).scalar_subquery().label("symbol_from_A")
>)
>
>
> When I query this highlighted column_property along with the whole
> object, it works fine:
>
> >>> sess.query(C, C.symbol_from_A).all()
>
> However, when I query this column_property alone, it doesn't work:
>
> >>>sess.query(C.symbol_from_A).distinct().all()
>
> I noticed that somehow the correlated subquery wasn't executed property
> when query that column_property independently.
>
> How to solve this?
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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+...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/f7a0c360-ada2-456b-b02a-498ca50334f3n%40googlegroups.com
>
> .
>>>
>>>
>>> --
>>> SQLAlchemy -
>>> The Python SQL Toolkit and Object Relational Mapper
>>>
>>> http://www.sqlalchemy.org/
>>>
>>> To post example code, please provide an MCVE: Minimal, Complete, and
>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>>> description.
>>> ---
>>> You received this message because you are subscribed to the Google Groups
>>> "sqlalchemy" group.
>>> To unsubscribe
Re: [sqlalchemy] correlated subquery as column_property can't be queried independently?
This is very helpful.
How to return a list of scalar values in this case? Now the query returns a
list of tuples, and each tuple only has one value, which is what actually
needed. Is there a parameter to return a series of scalar value like this?
On Wednesday, November 10, 2021 at 12:05:27 PM UTC-8 Mike Bayer wrote:
> it has to do with how SQLAlchemy determines the FROM list in a select()
> statement.
>
> if you say select(table.c.id), it knows that "table" is the thing to
> select "from".
>
> however, if you say select(select(...).correlate(...).scalar_subquery()),
> that's assuming it's a SELECT from a scalar subquery, which normally does
> not imply a FROM clause.
>
> In this case, that you are saying correlate(C), we could argue that *does*
> imply a FROM clause of some kind, but SQLAlchemy doesn't make that leap
> right now. You might have said correlate(C, D) or correlate_except(Q) and
> then it's not as clear what the scalar subquery would be selected FROM.
>
> going further, there *could* be some new kind of API where a scalar
> subquery could suggest a FROM clause that should be used when this subquery
> is selected FROM. that would allow your column_property() to be built up
> such that it would be "self sufficient" when queried by itself. but again
> we don't have that right now.
>
> with all those options exhausted, for SQLAlchemy to be given
> select(select().scalar_subquery()), and then to produce "SELECT * FROM
> (SELECT ) FROM ", that FROM part has to be added on
> explicitly, i.e. select(select().scalar_subuqery()).select_from( table>).
>
>
>
> On Wed, Nov 10, 2021, at 2:35 PM, niuji...@gmail.com wrote:
>
> This works! Could you explain a little about this differences by using
> select_from here? I think this is very important and useful, really want to
> learn it right.
>
> On Wednesday, November 10, 2021 at 5:55:44 AM UTC-8 Mike Bayer wrote:
>
>
> try calling:
>
> query(C.symbol_from_a).select_from(C)
>
>
>
> On Wed, Nov 10, 2021, at 4:50 AM, niuji...@gmail.com wrote:
>
> class A(Base):
> primary_id = Column(Integer, prirmary_key=True)
> some_A_marker = Column(String)
>
> class B(Base):
> primary_id = Column(Integer, primary_key=True)
> referencing_A_id = Column(Integer, ForeignKey(A.primary_id))
>
> class C(Base):
> primary_id = Column(Integer, primary_key=True)
> basedOn_B_id = Column(Integer, ForeignKey(B.primary_id))
> symbol_from_A = column_property(
>
> sasql.select(A.some_A_marker).select_from(A).join(B).where(B.primary_id ==
> C.basedOn_B_id).correlate(C).scalar_subquery().label("symbol_from_A")
>)
>
>
> When I query this highlighted column_property along with the whole object,
> it works fine:
>
> >>> sess.query(C, C.symbol_from_A).all()
>
> However, when I query this column_property alone, it doesn't work:
>
> >>>sess.query(C.symbol_from_A).distinct().all()
>
> I noticed that somehow the correlated subquery wasn't executed property
> when query that column_property independently.
>
> How to solve this?
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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+...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/f7a0c360-ada2-456b-b02a-498ca50334f3n%40googlegroups.com
>
>
> .
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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+...@googlegroups.com.
>
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/8549e3f3-11fb-47e1-b86a-270fecce8caan%40googlegroups.com
>
>
> .
>
>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google
Re: [sqlalchemy] correlated subquery as column_property can't be queried independently?
it has to do with how SQLAlchemy determines the FROM list in a select()
statement.
if you say select(table.c.id), it knows that "table" is the thing to select
"from".
however, if you say select(select(...).correlate(...).scalar_subquery()),
that's assuming it's a SELECT from a scalar subquery, which normally does not
imply a FROM clause.
In this case, that you are saying correlate(C), we could argue that *does*
imply a FROM clause of some kind, but SQLAlchemy doesn't make that leap right
now. You might have said correlate(C, D) or correlate_except(Q) and then it's
not as clear what the scalar subquery would be selected FROM.
going further, there *could* be some new kind of API where a scalar subquery
could suggest a FROM clause that should be used when this subquery is selected
FROM. that would allow your column_property() to be built up such that it
would be "self sufficient" when queried by itself. but again we don't have
that right now.
with all those options exhausted, for SQLAlchemy to be given
select(select().scalar_subquery()), and then to produce "SELECT * FROM (SELECT
) FROM ", that FROM part has to be added on explicitly, i.e.
select(select().scalar_subuqery()).select_from().
On Wed, Nov 10, 2021, at 2:35 PM, niuji...@gmail.com wrote:
> This works! Could you explain a little about this differences by using
> select_from here? I think this is very important and useful, really want to
> learn it right.
>
> On Wednesday, November 10, 2021 at 5:55:44 AM UTC-8 Mike Bayer wrote:
>> __
>> try calling:
>>
>> query(C.symbol_from_a).select_from(C)
>>
>>
>>
>> On Wed, Nov 10, 2021, at 4:50 AM, niuji...@gmail.com wrote:
>>> class A(Base):
>>> primary_id = Column(Integer, prirmary_key=True)
>>> some_A_marker = Column(String)
>>>
>>> class B(Base):
>>> primary_id = Column(Integer, primary_key=True)
>>> referencing_A_id = Column(Integer, ForeignKey(A.primary_id))
>>>
>>> class C(Base):
>>> primary_id = Column(Integer, primary_key=True)
>>> basedOn_B_id = Column(Integer, ForeignKey(B.primary_id))
>>> symbol_from_A = column_property(
>>>
>>> sasql.select(A.some_A_marker).select_from(A).join(B).where(B.primary_id ==
>>> C.basedOn_B_id).correlate(C).scalar_subquery().label("symbol_from_A")
>>>)
>>>
>>>
>>> When I query this highlighted column_property along with the whole object,
>>> it works fine:
>>>
>>> >>> sess.query(C, C.symbol_from_A).all()
>>>
>>> However, when I query this column_property alone, it doesn't work:
>>>
>>> >>>sess.query(C.symbol_from_A).distinct().all()
>>>
>>> I noticed that somehow the correlated subquery wasn't executed property
>>> when query that column_property independently.
>>>
>>> How to solve this?
>>>
>>>
>>>
>>> --
>>> SQLAlchemy -
>>> The Python SQL Toolkit and Object Relational Mapper
>>>
>>> http://www.sqlalchemy.org/
>>>
>>> To post example code, please provide an MCVE: Minimal, Complete, and
>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>>> description.
>>> ---
>>> 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+...@googlegroups.com.
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/f7a0c360-ada2-456b-b02a-498ca50334f3n%40googlegroups.com
>>>
>>> .
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/8549e3f3-11fb-47e1-b86a-270fecce8caan%40googlegroups.com
>
> .
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 view this discussion on the web visit
Re: [sqlalchemy] correlated subquery as column_property can't be queried independently?
This works! Could you explain a little about this differences by using
select_from here? I think this is very important and useful, really want to
learn it right.
On Wednesday, November 10, 2021 at 5:55:44 AM UTC-8 Mike Bayer wrote:
> try calling:
>
> query(C.symbol_from_a).select_from(C)
>
>
>
> On Wed, Nov 10, 2021, at 4:50 AM, niuji...@gmail.com wrote:
>
> class A(Base):
> primary_id = Column(Integer, prirmary_key=True)
> some_A_marker = Column(String)
>
> class B(Base):
> primary_id = Column(Integer, primary_key=True)
> referencing_A_id = Column(Integer, ForeignKey(A.primary_id))
>
> class C(Base):
> primary_id = Column(Integer, primary_key=True)
> basedOn_B_id = Column(Integer, ForeignKey(B.primary_id))
> symbol_from_A = column_property(
>
> sasql.select(A.some_A_marker).select_from(A).join(B).where(B.primary_id ==
> C.basedOn_B_id).correlate(C).scalar_subquery().label("symbol_from_A")
>)
>
>
> When I query this highlighted column_property along with the whole object,
> it works fine:
>
> >>> sess.query(C, C.symbol_from_A).all()
>
> However, when I query this column_property alone, it doesn't work:
>
> >>>sess.query(C.symbol_from_A).distinct().all()
>
> I noticed that somehow the correlated subquery wasn't executed property
> when query that column_property independently.
>
> How to solve this?
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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+...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/f7a0c360-ada2-456b-b02a-498ca50334f3n%40googlegroups.com
>
>
> .
>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/8549e3f3-11fb-47e1-b86a-270fecce8caan%40googlegroups.com.
Re: [sqlalchemy] correlated subquery as column_property can't be queried independently?
try calling:
query(C.symbol_from_a).select_from(C)
On Wed, Nov 10, 2021, at 4:50 AM, niuji...@gmail.com wrote:
> class A(Base):
> primary_id = Column(Integer, prirmary_key=True)
> some_A_marker = Column(String)
>
> class B(Base):
> primary_id = Column(Integer, primary_key=True)
> referencing_A_id = Column(Integer, ForeignKey(A.primary_id))
>
> class C(Base):
> primary_id = Column(Integer, primary_key=True)
> basedOn_B_id = Column(Integer, ForeignKey(B.primary_id))
> symbol_from_A = column_property(
>
> sasql.select(A.some_A_marker).select_from(A).join(B).where(B.primary_id ==
> C.basedOn_B_id).correlate(C).scalar_subquery().label("symbol_from_A")
>)
>
>
> When I query this highlighted column_property along with the whole object, it
> works fine:
>
> >>> sess.query(C, C.symbol_from_A).all()
>
> However, when I query this column_property alone, it doesn't work:
>
> >>>sess.query(C.symbol_from_A).distinct().all()
>
> I noticed that somehow the correlated subquery wasn't executed property when
> query that column_property independently.
>
> How to solve this?
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/f7a0c360-ada2-456b-b02a-498ca50334f3n%40googlegroups.com
>
> .
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/d23fa6bc-c5db-44d6-9416-a4f169df20da%40www.fastmail.com.
[sqlalchemy] correlated subquery as column_property can't be queried independently?
class A(Base):
primary_id = Column(Integer, prirmary_key=True)
some_A_marker = Column(String)
class B(Base):
primary_id = Column(Integer, primary_key=True)
referencing_A_id = Column(Integer, ForeignKey(A.primary_id))
class C(Base):
primary_id = Column(Integer, primary_key=True)
basedOn_B_id = Column(Integer, ForeignKey(B.primary_id))
symbol_from_A = column_property(
sasql.select(A.some_A_marker).select_from(A).join(B).where(B.primary_id ==
C.basedOn_B_id).correlate(C).scalar_subquery().label("symbol_from_A")
)
When I query this highlighted column_property along with the whole object,
it works fine:
>>> sess.query(C, C.symbol_from_A).all()
However, when I query this column_property alone, it doesn't work:
>>>sess.query(C.symbol_from_A).distinct().all()
I noticed that somehow the correlated subquery wasn't executed property
when query that column_property independently.
How to solve this?
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/f7a0c360-ada2-456b-b02a-498ca50334f3n%40googlegroups.com.
