[sqlalchemy] How to refer to fields in nested queries?

[SF Markus Elfring]

Hello,

I would like to compute a few fields in a query and then apply aggregate
functions on corresponding results. I imagine that I would need a subquery
(or a specific view) for this use case.

How should such a query structure be expressed by interfaces of
the software "SQLAlchemy"?
Which identifiers will be appropriate for the involved fields?
http://docs.sqlalchemy.org/en/rel_1_0/core/sqlelement.html#sqlalchemy.sql.expression.label

Regards,
Markus

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] session.is_modified() returns false positives sometimes

[Юрий Пайков]

I run a program which creates, fills and merges to DB a list of mapped 
objects. I need to know if a merge created a new instance in a database or 
if it updated previously existing one or if it did noop. When I know the 
instance is persistent, my goal is to figure out if it's going to be 
updated after COMMIT. 
I use 
session.is_modified(object)
for that.
If the first object in the list is truly not modified, this function 
correctly reports False. But for the next objects it returns True and 
blames one of the relationship attributes. 
As it turns out, for the first object state.committed_state includes the 
actual value of the relationship attribute - related object, while for the 
others it is 
symbol(PASSIVE_NO_DELETE)
and so the current value of the relationship is wrongly considered "added".

Does anyone know why it happens like this? How can I correctly check if the 
object's going to be updated on commit?

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] session.is_modified() returns false positives sometimes

[Mike Bayer]

On 12/08/2015 07:41 AM, Юрий Пайков wrote:
> I run a program which creates, fills and merges to DB a list of mapped
> objects. I need to know if a merge created a new instance in a database
> or if it updated previously existing one or if it did noop. When I know
> the instance is persistent, my goal is to figure out if it's going to be
> updated after COMMIT. 
> I use 
> |
> session.is_modified(object)
> |
> for that.
> If the first object in the list is truly not modified, this function
> correctly reports False. But for the next objects it returns True and
> blames one of the relationship attributes. 
> As it turns out, for the first object state.committed_state includes the
> actual value of the relationship attribute - related object, while for
> the others it is 
> |
> symbol(PASSIVE_NO_DELETE)
> |
> and so the current value of the relationship is wrongly considered "added".

We have a lot of "PASSIVE_*" symbols, but I can't find one, even looking
way back, that is called "PASSIVE_NO_DELETE".  Also these particular
symbols don't get populated into objects, they are arguments we send to
various attribute fetch/history methods.If I had to guess, you
*might* be seeing PASSIVE_NO_RESULT, but that wouldn't be present in
committed_state.

Overall this description of behavior is not clear.  If you could
reproduce your exact results in a console session or script and please
report on those exactly, that would help understand your case.
Following the guidelines at http://stackoverflow.com/help/mcve would be
most helpful.

The is_modified() method up until 0.8 included a default setting for its
own "passive" flag that would cause it to fetch relationships as a side
effect.  This was fixed in 0.8.   Also it does not include
collection-bound relationships as part of its answer unless the
include_collections flag is set to True.

> 
> Does anyone know why it happens like this? How can I correctly check if
> the object's going to be updated on commit?
> 
> -- 
> 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 http://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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: How to refer to fields in nested queries?

[Jonathan Vanasco]

If you don't use a subquery, you can use the `desc("column")` in 
`sqlalchemy`.  (there is an `asc()` as well).  Below I use `label('name')` 
to specify the result column name for computed field, then order by it.

results = dbSession.query(
   model.Foo.a,
   sqlalchemy.func.count(Model.Foo.b).label('count_b')
 )\
 .group_by(model.Foo.a)\
 .order_by(sqlalchemy,desc('count_b')\
 .all()

If you are using a subquery for additional processing or joins, you can use 
`label()` to specify the name for the computed column, then access that 
column as an attribute on the `.c` columns attribute of the subquery 
object.  

inner_query = dbSession.query(
   model.Foo.a,
   sqlalchemy.func.count(Model.Foo.b).label('count_b')
 )
 inner_query = inner_query.subquery('query_foo')
 results = 
dbSession.query(inner_query.c.count_b.label('count_results')).all()

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: How to refer to fields in nested queries?

[SF Markus Elfring]

> If you are using a subquery for additional processing or joins,
> you can use `label()` to specify the name for the computed column,
> then access that column as an attribute on the `.c` columns attribute of the 
> subquery object.

Thanks for your information.


> inner_query = dbSession.query(
>model.Foo.a,
>sqlalchemy.func.count(Model.Foo.b).label('count_b')
>  )
>  inner_query = inner_query.subquery('query_foo')
>  results = 
> dbSession.query(inner_query.c.count_b.label('count_results')).all()

Another part of the desired data analysis is working here now.

Will it become easier to recognise the relevant relationships
from the corresponding programming interface documentation?

Regards,
Markus

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Support for SQL Merge (or Upsert?)

[SF Markus Elfring]

Hello,

I would like to perform a specific data management task which will affect
a combination of checks for the operations "INSERT" and "UPDATE".
Such a functionality became part of the SQL standard as the operation "MERGE".
https://en.wikipedia.org/wiki/Merge_%28SQL%29

It seems that the support for it is still evolving in database implementations
and corresponding software libraries.

Now I am looking for a solution around a similar use case.
How should anything be added (or appended) to the value of a column in a query
as a single action?

Regards,
Markus

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: How to refer to fields in nested queries?

[Jonathan Vanasco]

On Tuesday, December 8, 2015 at 12:14:46 PM UTC-5, SF Markus Elfring wrote:
>
> Will it become easier to recognise the relevant relationships 
> from the corresponding programming interface documentation? 
>

You should read through the tutorial/narrative documentation on the ORM and 
"Core", as well as the FAQ.  That will familiarize you a lot with where to 
find the information.

The SqlAlchemy library is incredibly robust and large; even after years of 
usage people find new features.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] session.is_modified() returns false positives sometimes

[Юрий Пайков]

Ok, here is the test 
code https://gist.github.com/ojomio/941d03b728a88d93d010
Apart from reproducing the (seeming) problem, it prints out 
"PASSIVE_NO_RESULT"(yes, you were right about the name) which is *in* 
committed_state
вторник, 8 декабря 2015 г., 19:44:16 UTC+5 пользователь Michael Bayer 
написал:
>
>
> We have a lot of "PASSIVE_*" symbols, but I can't find one, even looking 
> way back, that is called "PASSIVE_NO_DELETE".  Also these particular 
> symbols don't get populated into objects, they are arguments we send to 
> various attribute fetch/history methods.If I had to guess, you 
> *might* be seeing PASSIVE_NO_RESULT, but that wouldn't be present in 
> committed_state. 
>
> Overall this description of behavior is not clear.  If you could 
> reproduce your exact results in a console session or script and please 
> report on those exactly, that would help understand your case. 
> Following the guidelines at http://stackoverflow.com/help/mcve would be 
> most helpful. 
>
> The is_modified() method up until 0.8 included a default setting for its 
> own "passive" flag that would cause it to fetch relationships as a side 
> effect.  This was fixed in 0.8.   Also it does not include 
> collection-bound relationships as part of its answer unless the 
> include_collections flag is set to True. 
>
> > 
> > Does anyone know why it happens like this? How can I correctly check if 
> > the object's going to be updated on commit? 
> > 
> > -- 
> > 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 post to this group, send email to sqlal...@googlegroups.com 
>  
> > . 
> > Visit this group at http://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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.