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

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

Thank you for the detailed explanation of what happens. Now I understand. 
Shame on me I hadn't noticed this in the docs...
Everything which deals with expiration and object state business is always 
a bit over my head...

You said it mostly the flush() business and is_modified() indeed looks like 
one of its private _function()s which behave bearing in mind what flush() 
wants and at what expense
BUT exposed as public. Maybe it worths rewriting it so it behaves without 
assuming some calling "context", even less productive, but with more 
expected behavior? It it would be pain maintaining diverging copies of 
logic... Anyway, thanks for the clarification, you're awesome as always

среда, 9 декабря 2015 г., 22:04:29 UTC+5 пользователь Michael Bayer написал:
>
>
>
> On 12/09/2015 02:07 AM, Юрий Пайков wrote: 
> > 
> > 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 
>
>
> great, thanks. The PASSIVE_NO_RESULT symbol in there is a normal thing, 
> it means that for this object, we didn't actually know what the "old" 
> value of the A.second attribute was - it was expired when you called 
> session.commit().  So the fact that we set it to this B() object we have 
> no choice but to record as "new history". 
>
> Now, if we really wanted it to know that no, this is the *same* object, 
> it would have to go out to the identity map and/or database and *load* 
> the old one.  You can have that, like this: 
>
> class A(Base): 
> __tablename__ = 'a' 
> first = Column(Integer, primary_key=True) 
> second_id = Column(Integer, ForeignKey('b.id')) 
> second = relationship('B', active_history=True) 
>
>
> this isn't the default setting, because when a many-to-one object is 
> assigned to a parent, we don't need to know what the old one was in 
> order to do an UPDATE, so if we don't have it, we don't bother most 
> likely hitting the database again and getting it.   You can see that the 
> test script emits an extra SELECT for it.   The majority of our users 
> were annoyed in the old days when every many-to-one assignment triggered 
> an unnecessary SELECT, so that's why it works this way. 
>
> Another level here is that, A.second points to a B which has an "id" 
> that is the same value we already know to have in A.second_id (we 
> emitted a SELECT for that also-expired value as well, but at least that 
> was just one SELECT), so in this (most common) case, we can actually see 
> that hey, no there really won't be an UPDATE, because if we look inside 
> this B, and its primary key,  and the primaryjoin condition inside our 
> relationship() (which could be anything) and run the rules for the 
> A.second relationship for synchronizing a many-to-one value into the 
> attributes of our A, there's no net change; that's essentially a really 
> involved and highly tweaked process that occurs within flush() which 
> unfortunately is not simplistic enough to just pull out and run in this 
> local, non-flush context.   I can see some potential tweaks where maybe 
> we look at the fact that this is a "simple" many-to-one (e.g. the 
> primaryjoin has no weird conditions in it) and then only consider the FK 
> columns, that could be doable.  I'd have it only on a flag. 
>
> The simple answer is that session.merge() is very likely to cause an 
> object to look "modified", and if very accurate detection of changes is 
> a use case, I wouldn't use merge(), or I'd make sure I'm not running it 
> on expired objects. 
>
> *or*, just look in the history yourself for the attributes you care about: 
>
>
> def a_is_modified(some_a): 
> attrs = inspect(some_a).attrs 
> return bool(sum( 
> attrs[key].history.has_changes() 
> for key in ['first', 'second_id'] 
> )) 
>
>
> new_a = A(first=3, second=b) 
> returned_a = session.merge(new_a) 
> print a_is_modified(a) 
>
>
>  
>
> The docs for session.is_modified() point this out as well: 
>
>
> http://docs.sqlalchemy.org/en/rel_1_0/orm/session_api.html#sqlalchemy.orm.session.Session.is_modified
>  
>
> > Scalar attributes may not have recorded the previously set value when 
> a new value was applied, if the attribute was not loaded, or was 
> expired, at the time the new value was received - in these cases, the 
> attribute is assumed to have a change, even if there is ultimately no 
> net change against its database value. SQLAlchemy in 

[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

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

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.

[sqlalchemy] How to make use of bindparam() in a custom Compiled expression?

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

I based my code on the Michaeil Bayer's answer to this Stack Overflow 
question  
. 
I extended it a little so it takes into account NULLs and ARRAY for 
Postgresql.

class values(FromClause):
named_with_column = True

def __init__(self, columns, *args, **kw):
self._column_args = columns
self.list = args
self.alias_name = self.name = kw.pop('alias_name', None)

def _populate_column_collection(self):
# self._columns.update((col.name, col) for col in self._column_args)
for c in self._column_args:
c._make_proxy(self, c.name)

@compiles(values)def compile_values(element, compiler, asfrom=False, **kw):
columns = element.columns
v = "VALUES %s" % ", ".join(
"(%s)" % ", ".join(
((compiler.visit_array(elem)+'::'+str(column.type)) if 
isinstance(column.type, ARRAY) else
 compiler.render_literal_value(elem, column.type))
if elem is not None else compiler.render_literal_value(elem, NULLTYPE)
for elem, column in zip(tup, columns))
for tup in element.list
)
if asfrom:
if element.alias_name:
v = "(%s) AS %s (%s)" % (v, element.alias_name, (", ".join(c.name 
for c in element.columns)))
else:
v = "(%s)" % v
return v

Everything worked fine until it turned out I couldn't insert values with 
"%"-sign to this VALUES clause - they get inlined in a resulting statement 
and this seems to cause binding problems

I guess if instead of render_literal_value() we used bindparam() we could 
avoid such an error. But Everything under @compiles should return plain 
text, am I right? 


How could I amend this to get a query which contains bind parfms along with 
it?

-- 
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] How to make use of bindparam() in a custom Compiled expression?

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

The problem is  - I can't wrap my head round using it... Could you direct 
me to same compiled clause which uses bindparams? 
Or perhaps maybe to some documentation on SQLA internals, because it is 
quite difficult to track the whole query compilation process for me
четверг, 19 ноября 2015 г., 21:14:59 UTC+5 пользователь Michael Bayer 
написал:
>
>
>
> you can try a bindparam(), sure.the original question, everything 
> under @compiles should return plain text, yes, those functions all need 
> to return text that is appended to the SQL statement. 
>

-- 
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] How to make use of bindparam() in a custom Compiled expression?

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

Oh, man I got it. Cannot imagine what kind of an insight hit me when I 
figured it out!
Here <https://gist.github.com/ojomio/d449abffe588a2abd32b> is a test case 
and final version of VALUES clause 

Btw, why there is no _type_api mapping for a list() python type 
(https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/sql/sqltypes.py#L1960)
 
, at least as for version 1.0.9, which I use? We have to explicitly use 
`array` literal everywhere insted of just specifying python lists like 
[1,2,3]

пятница, 20 ноября 2015 г., 0:30:15 UTC+5 пользователь Michael Bayer 
написал:
>
> well what is the ARRAY data you're passing into it?  can you provide a 
> complete example? 
>
> On 11/19/2015 02:04 PM, Юрий Пайков wrote: 
> > The problem is  - I can't wrap my head round using it... Could you 
> > direct me to same compiled clause which uses bindparams? 
> > Or perhaps maybe to some documentation on SQLA internals, because it is 
> > quite difficult to track the whole query compilation process for me 
> > четверг, 19 ноября 2015 г., 21:14:59 UTC+5 пользователь Michael Bayer 
> > написал: 
> > 
> > 
> > 
> > you can try a bindparam(), sure.the original question, 
> everything 
> > under @compiles should return plain text, yes, those functions all 
> need 
> > to return text that is appended to the SQL statement. 
> > 
> > -- 
> > 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  
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com >. 
> > To post to this group, send email to sqlal...@googlegroups.com 
>  
> > <mailto: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.

Re: [sqlalchemy] How to make use of bindparam() in a custom Compiled expression?

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

But if I used bound parameters in a query - wouldn't it be a solution? 
I mean, if a value for a bindparam is not inlined in a query and rather 
carried along - there is no need to quote it for the driver, is there? Like 
done for any other literal comparison, for example.
The driver will insert it as is from some object/field  and won't parse the 
resulting query for params any further...

четверг, 19 ноября 2015 г., 19:43:39 UTC+5 пользователь Michael Bayer 
написал:
>
>
>
> On 11/19/2015 05:29 AM, Юрий Пайков wrote: 
> > I based my code on the Michaeil Bayer's answer to this Stack Overflow 
> > question 
> > <http://stackoverflow.com/questions/18858291/values-clause-in-sqlalchemy>. 
>
> > I extended it a little so it takes into account NULLs and ARRAY for 
> > Postgresql. 
> > 
> > |classvalues(FromClause):named_with_column 
> > =Truedef__init__(self,columns,*args,**kw):self._column_args =columns 
> > self.list =args self.alias_name =self.name 
> > =kw.pop('alias_name',None)def_populate_column_collection(self):# 
> > self._columns.update((col.name, col) for col in self._column_args)forc 
> > inself._column_args:c._make_proxy(self,c.name)@compiles(values)defcompile_values(element,compiler,asfrom=False,**kw):columns
> >  
>
> > =element.columns v ="VALUES %s"%", ".join("(%s)"%", 
> > 
> ".join(((compiler.visit_array(elem)+'::'+str(column.type))ifisinstance(column.type,ARRAY)elsecompiler.render_literal_value(elem,column.type))ifelem
>  
>
> > isnotNoneelsecompiler.render_literal_value(elem,NULLTYPE)forelem,column 
> > inzip(tup,columns))fortup inelement.list 
> > )ifasfrom:ifelement.alias_name:v ="(%s) AS %s 
> > (%s)"%(v,element.alias_name,(", ".join(c.name forc 
> > inelement.columns)))else:v ="(%s)"%v returnv| 
> > 
> > Everything worked fine until it turned out I couldn't insert values with 
> > "%"-sign to this VALUES clause - they get inlined in a resulting 
> > statement and this seems to cause binding problems 
> > 
> > I guess if instead of |render_literal_value()| we used |bindparam()| we 
> > could avoid such an error. But Everything under |@compiles| should 
> > return plain text, am I right? 
> > 
> > 
> > How could I amend this to get a query which contains bind parfms along 
> > with it? 
>
> if the issue is percent signs, this is a driver limitation and you need 
> to double them up as %%, so that they aren't confused as format/pyformat 
> indicators.   psycopg2 uses format/pyformat style for bound parameters. 
>
>
>
> > 
> > -- 
> > 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  
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com >. 
> > To post to this group, send email to sqlal...@googlegroups.com 
>  
> > <mailto: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.

[sqlalchemy] If path in joinedload() include self-referential relationship, should I alias() every table after that relation ?

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

I have an example here https://gist.github.com/ojomio/aa5eca3bea03d21e00e8. 
This code issue exactly one query and load everything at one time
What I am asking about is 
line https://gist.github.com/ojomio/aa5eca3bea03d21e00e8#file-gistfile1-py-L65

If I don not use alias *second_B* and simply write 

).join(
AWithChildren.bs
).options(
contains_eager(B.as_).
contains_eager(A.children, alias=AWithChildren).
contains_eager(AWithChildren.bs).
joinedload(B.cs)
)


Then  SQLAlchemy issue another query on *C* table, apparently not matching 
expression AWithChildren.bs and B
So my question is - if there are many other tables after *A.children -* 
should use alias() for every one and mention them like
.joinedload(
PreviousTable.relation,
alias=SomeTableAlias
).
?

-- 
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] If path in joinedload() include self-referential relationship, should I alias() every table after that relation ?

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

Ok, that is clear now. 

Eagerloading of tables occurring more than once in a query is a bit 
confusing for me as it is not well-documented, 
for example that *contains_eager()* needs *alias=parameter* in order to 
work properly for a second occurrence of a table. If I might I would advise 
you to shed some light on the usage in a documentation.

OK, moving on
When using
 .join(relation, aliased=True)
it is stated that next *.fliter() *call will refer to the second occurrence 
(unless  *.reset_joinpoint() *is called). 

Here https://gist.github.com/ojomio/08e5d91d8eed6cc76a2c I have an 
example of similar behavior. Consider my slightly modified example.
it seems *contains_eager()*, when given not the full path from the first 
occurrence(B-A-A-B-C) but rather only the portion of path from the last 
mention of table(B-C), populates the collection with the data from the 
*latest* mentioned instance of that table

Is it the desired outcome or should it be considered a bug? Could this 
pattern change in future?
And, as always, thank you for you patience and attention brought to my 
problem

среда, 15 июля 2015 г., 22:32:42 UTC+5 пользователь Michael Bayer написал:

  

 On 7/15/15 2:42 AM, Юрий Пайков wrote:
  
 I have an example here https://gist.github.com/ojomio/aa5eca3bea03d21e00e8. 
 This code issue exactly one query and load everything at one time 
 What I am asking about is line 
 https://gist.github.com/ojomio/aa5eca3bea03d21e00e8#file-gistfile1-py-L65

  If I don not use alias *second_B* and simply write 
   
 ).join(
 AWithChildren.bs
 ).options(
 contains_eager(B.as_).
 contains_eager(A.children, alias=AWithChildren).
 contains_eager(AWithChildren.bs).
 joinedload(B.cs)
 )

  
 Then  SQLAlchemy issue another query on *C* table, apparently not 
 matching expression AWithChildren.bs and B
 So my question is - if there are many other tables after *A.children -* 
 should use alias() for every one and mention them like
 .joinedload( PreviousTable.relation, alias=SomeTableAlias ).
 ?
  

 the use here of .joinedload() on the end of a series of contains_eager() 
 calls is already very unusual, and I'm somewhat surprised it works 
 correctly in the first case as this is not a use case that's really 
 tested.   As for the case of second_b not being present, this is not 
 surprising as AWithChildren.bs refers to B, which is already present in 
 the query as the primary entity, for a separate collection of B to be 
 present it needs to be aliased.   the join(AWithChildren.bs) is the same as 
 join(B, AWithChildren.bs), and you'd never want to say what is essentially 
 session.query(B).join(Q).join(B) - you need an alias for each subsequent 
 occurrence of B.




  -- 
 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 javascript:.
 To post to this group, send email to sqlal...@googlegroups.com 
 javascript:.
 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.

Re: [sqlalchemy] Preserve mapped entities after wrapping the query

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

Michael, thank you for you reply, I expected you to mention from_self :) I 
know about it, it is a handy trick indeed
But I deliberately don't use it, because this way I don't know how to 
mention a column which I want to filter on
This is due to the fact, that it is calculated i.e. there is no table to 
refer to!  I might resert to using literals(filter('avg_110')), but 'd 
prefer to stay in the more ORM-style


суббота, 25 апреля 2015 г., 2:37:11 UTC+5 пользователь Michael Bayer 
написал:

  

 On 4/24/15 5:25 PM, Пайков Юрий wrote:
  
  
 q = session.query(Recipe, func.avg(Recipe.field1).over(...)).join(...)
  
 I have a query which selects some mapped entity as well as other columns.
  
 I then refer to the name of that entity when working with the result of 
 the query:
 for entry in q.all():
   recipe=entry.Recipe
   
  
 Now, I want to add filtering by some calculated criteria to my query, and 
 so I wrap it in an additional query:
 q = q.subquery();
 q = session.query(q).filter(q.c.avg_1  10 )
  
 However, this way I can no longer access entry.Recipe! Is there a way to 
 make sqlalchemy adapt names? I tried aliased and select_entity_from, but no 
 luck :(


 this is getting into less reliable stuff, but instead of subquery() - 
 session.query(q), use the from_self() method.  It's designed to work this 
 way, and your Recipe entity will be adapted into the subquery.

 I've observed that the vast majority of my users don't seem to get into 
 queries like these, so from_self() is not as popular (or widely tested) as 
 it should be, but it is at the base of a lot of widely used functions like 
 count() and subquery eager loading, so give it a try:


 http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html?highlight=from_self#sqlalchemy.orm.query.Query.from_self

 apparently it needs some documentation too :)


  -- 
 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 javascript:.
 To post to this group, send email to sqlal...@googlegroups.com 
 javascript:.
 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.

Re: [sqlalchemy] Preserve mapped entities after wrapping the query

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

Ok, I seemed to figure out how to deal with it - 
row_number_column = func.row_number().over(
partition_by=Recipe.id
).label('row_number')
 query = query.add_column(
row_number_column
)
query = query.from_self().filter(row_number_column == 1)

Using an explicit column construct
суббота, 25 апреля 2015 г., 11:39:08 UTC+5 пользователь Юрий Пайков написал:

 Michael, thank you for you reply, I expected you to mention from_self :) I 
 know about it, it is a handy trick indeed
 But I deliberately don't use it, because this way I don't know how to 
 mention a column which I want to filter on
 This is due to the fact, that it is calculated i.e. there is no table to 
 refer to!  I might resert to using literals(filter('avg_110')), but 'd 
 prefer to stay in the more ORM-style


 суббота, 25 апреля 2015 г., 2:37:11 UTC+5 пользователь Michael Bayer 
 написал:

  

 On 4/24/15 5:25 PM, Пайков Юрий wrote:
  
  
 q = session.query(Recipe, func.avg(Recipe.field1).over(...)).join(...)
  
 I have a query which selects some mapped entity as well as other columns.
  
 I then refer to the name of that entity when working with the result of 
 the query:
 for entry in q.all():
   recipe=entry.Recipe
   
  
 Now, I want to add filtering by some calculated criteria to my query, and 
 so I wrap it in an additional query:
 q = q.subquery();
 q = session.query(q).filter(q.c.avg_1  10 )
  
 However, this way I can no longer access entry.Recipe! Is there a way to 
 make sqlalchemy adapt names? I tried aliased and select_entity_from, but no 
 luck :(


 this is getting into less reliable stuff, but instead of subquery() - 
 session.query(q), use the from_self() method.  It's designed to work this 
 way, and your Recipe entity will be adapted into the subquery.

 I've observed that the vast majority of my users don't seem to get into 
 queries like these, so from_self() is not as popular (or widely tested) as 
 it should be, but it is at the base of a lot of widely used functions like 
 count() and subquery eager loading, so give it a try:


 http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html?highlight=from_self#sqlalchemy.orm.query.Query.from_self

 apparently it needs some documentation too :)


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

[sqlalchemy] Preserving entity in a query after wrapping that query in additional select

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

q = session.query(Recipe, func.avg(Recipe.field1).over(...)).join(...)

I have a query which selects some mapped entity as well as other columns.

I then refer to the name of that entity when working with the result of the 
query:
for entry in q.all():
   recipe=entry.Recipe
   

Now, I want to add filtering by some calculated criteria to my query, and 
so I wrap it in an additional query:
q = q.subquery();
q = session.query(q).filter(q.c.avg_1  10 )

However, this way I can no longer access entry.Recipe! Is there a way to 
make sqlalchemy adapt names? I tried aliased and select_entity_from, but no 
luck :(

-- 
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] Is there a way to preserve entity after wrapping the Query in additional select?

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

I have a query which selects an entity A and some calculated fields 
q = session.query(Recipe,func.avg(Recipe.somefield).join(.)

I then use what I select in a way which assumes I can subscript result with 
Recipe string:
for entry in q.all():
   recipe=entry.Recipe # Access KeyedTuple by Recipe attribute
   ...

Now I need to wrap my query in an additional select, say to filter by 
calculated field AVG:
q=q.subquery(); q= session.query(q).filter(q.c.avg_1  1)

And now I cannot access entry.Recipe anymore!
Is there a way to *make* SQLAlchemy adapt a query to an enclosing one, like 
aliased(adapt_on_names=True) or select_from_entity()?
I tried using those but was given an error

-- 
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] SQLAlchemy's merge doesn't fill foreign keys with ids of new objects in relationship

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

My question is when I have in a session a newly created object(doesn't have 
primary key yet, but will obtain it upon flush) and I merge to that session 
another object referring to the first one by relationship (*b* in the 
example) SQLAlchemy doesn't populate latter object with the primary key 
from the former. Instead it just generate next value from the sequence. Why 
is it the case ?

from sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmaker



engine = 
create_engine(postgresql+psycopg2://psql_admin:psql_admin@localhost/fm)


from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy import Integer, ForeignKey, VARCHAR, TEXT, Boolean, 
DateTimefrom sqlalchemy.orm import relationshipfrom sqlalchemy.sql.schema 
import Column
class B(Base):
__tablename__='B'
id_=Column(Integer, primary_key=True)
data = Column(VARCHAR(30))
class Rel(Base):
__tablename__='Rel'
id_a=Column(Integer, primary_key=True)
id_b=Column(Integer, ForeignKey('B.id_'), primary_key=True)
b = relationship(B)
rel_data=Column(VARCHAR(30))

Session = sessionmaker(bind=engine)   
session = Session()Base.metadata.create_all(engine, checkfirst=True)


first_b=B(id_=1, data='ololo')
session.add(first_b)
session.commit()

session.add(Rel(id_a=800,id_b=1, rel_data='first relation data'))

second_b=B(data='f')
session.add(second_b)
x=session.merge(Rel(id_a=800, rel_data=second, b=second_b))
session.commit()

Here I have an error

IntegrityError: (raised as a result of Query-invoked autoflush; consider 
using a session.no_autoflush block if this flush is occuring prematurely) 
(IntegrityError) duplicate key value violates unique constraint B_pkey 
DETAIL: Key (id_)=(1) already exists. 'INSERT INTO B (data) VALUES 
(%(data)s) RETURNING B.id_' {'data': 'f'}

-- 
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] SQLAlchemy's merge doesn't fill foreign keys with ids of new objects in relationship

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

Oh, it seems that merge() actually does populate the b_id if that B is 
non-conflicting... Seems I have another problem . 
Thank you anyway, Michael

вторник, 14 апреля 2015 г., 20:08:04 UTC+5 пользователь Michael Bayer 
написал:

  

 that's not what I see happening here.   I see very simply that the B.id_ 
 column is a SERIAL so is linked to a sequence, however you are inserting a 
 row with a hardcoded 1 for a primary key; so the second B object, which 
 relies on the sequence, fails due to an identity conflict.

 So let's repair the test case first, and that first B.id we'll set to 10 
 so that it doesn't conflict.

 Now we get the error you probably intended to send:

 SELECT Rel.id_a AS Rel_id_a, Rel.id_b AS Rel_id_b, Rel.rel_data 
 AS Rel_rel_data 
 FROM Rel 
 WHERE Rel.id_a = %(param_1)s AND Rel.id_b = %(param_2)s
 2015-04-14 11:05:11,850 INFO sqlalchemy.engine.base.Engine {'param_1': 
 800, 'param_2': symbol('NEVER_SET')}

 where this is, the merge() is proceeding to attempt to locate the object 
 by primary key but the PK is not filled in.  This is the expected 
 behavior.   The primary key of an object is never auto-populated until it 
 is flushed.   So here, if you are passing in a transient object, you need 
 to set the PK yourself:

 second_b = B(data='f')
 session.add(second_b)
 session.flush()
 x = session.merge(Rel(id_a=800, rel_data=second, id_b=second_b.id_))



 

-- 
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] Is it possible to populate relationship's collection with only query-filtered results using contains_eager()?

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

Well, I added echo=True and saw the queries - no additional were made. 
Then I added 
populate_existing()

 and out of the blue I started to get the desired result, but frankly I 
don't quite understand why. What side-effects this could bring?


суббота, 31 января 2015 г., 21:22:08 UTC+5 пользователь Michael Bayer 
написал:


 Anyway, yes, contains_eager says, “populate these collections”.   the 
 usage looks correct, so the echo=True, or even echo=‘debug’ which will show 
 you the result rows coming in, will show you where its going wrong. 

 or if those collections were already populated previously within that 
 Session, that would affect it also.   you can say query.populate_existing() 
 to force it to re-load everything. 


  and out of the blue I started to get the desired result, but frankly I 
don't quite understand why


суббота, 31 января 2015 г., 21:22:08 UTC+5 пользователь Michael Bayer 
написал:


 Anyway, yes, contains_eager says, “populate these collections”.   the 
 usage looks correct, so the echo=True, or even echo=‘debug’ which will show 
 you the result rows coming in, will show you where its going wrong. 

 or if those collections were already populated previously within that 
 Session, that would affect it also.   you can say query.populate_existing() 
 to force it to re-load everything. 




-- 
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] Is it possible to populate relationship's collection with only query-filtered results using contains_eager()?

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

Thanks Michael for your attention!

понедельник, 2 февраля 2015 г., 2:56:51 UTC+5 пользователь Michael Bayer 
написал:



 Юрий Пайков dia...@cry5tal.in javascript: wrote: 

  Well, I added echo=True and saw the queries - no additional were made. 
  Then I added 
  populate_existing() 
  
   and out of the blue I started to get the desired result, but frankly I 
 don't quite understand why. What side-effects this could bring? 

 it means the parent records in question were already loaded, and already 
 present in the identity map for your session - the collections were there 
 already, and the contains_eager had no effect on their contents. 



  
  
  суббота, 31 января 2015 г., 21:22:08 UTC+5 пользователь Michael Bayer 
 написал: 
  
  Anyway, yes, contains_eager says, “populate these collections”.   the 
 usage looks correct, so the echo=True, or even echo=‘debug’ which will show 
 you the result rows coming in, will show you where its going wrong. 
  
  or if those collections were already populated previously within that 
 Session, that would affect it also.   you can say query.populate_existing() 
 to force it to re-load everything. 
  
  
   and out of the blue I started to get the desired result, but frankly I 
 don't quite understand why 
  
  
  суббота, 31 января 2015 г., 21:22:08 UTC+5 пользователь Michael Bayer 
 написал: 
  
  Anyway, yes, contains_eager says, “populate these collections”.   the 
 usage looks correct, so the echo=True, or even echo=‘debug’ which will show 
 you the result rows coming in, will show you where its going wrong. 
  
  or if those collections were already populated previously within that 
 Session, that would affect it also.   you can say query.populate_existing() 
 to force it to re-load everything. 
  
  
  
  -- 
  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 javascript:. 
  To post to this group, send email to sqlal...@googlegroups.com 
 javascript:. 
  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] Is it possible to populate relationship's collection with only query-filtered results using contains_eager()?

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

When I do a query like this

result = session.query(A).\
join(A.bs).\
join(B.cs).\
filter_by(C.somedata.in_([4455, 4466])).\
options(contains_eager(A.bs).contains_eager(B.cs)).one()

and list related C objects

for b in result.bs:
   b.cs

I get all related to B C objects instead of only those with 4455, 4466 
values - even though I filtered the output of the statement.

So eager loading when used with contains_eager() - as I understand - 
shouldn't load other related objects apart from those which are selected by 
statement. Still I get Cs with values other that 4455, 4466(i.e. all the 
collection) :( Could any suggest from where come other Cs? Thanks in advance

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