Re: [sqlalchemy] Dependency rule tried to blank-out primary key column when trying to update using association proxy

[Piotr Dobrogost]

On Wednesday, February 24, 2016 at 4:07:07 PM UTC+1, Mike Bayer wrote:
>
>
> There might be an old issue in bitbucket that the way associationproxy 
> insists on calling clear() without any hook to override, the AP has a 
> lot of old override hooks that are complicated and difficult to use, and 
> then don't even cover this case.  So the AP is just not great in this 
> area and I'd prefer subclassing is not the idiomatic solution to this. 
>

I found this one – "try to add a real "replace collection" to association 
proxy" (https://bitbucket.org/zzzeek/sqlalchemy/issues/2642/)

Regards,
Piotr

-- 
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] Dependency rule tried to blank-out primary key column when trying to update using association proxy

[Mike Bayer]

On 02/24/2016 09:56 AM, Piotr Dobrogost wrote:

On Wednesday, February 24, 2016 at 4:21:58 AM UTC+1, Mike Bayer wrote:


in that way you can control exactly what __set__() does and it will
never remove a TextValue object where the same identity is coming in on
assignment.


One more question; is this described somewhere in the docs? I don't see
it
at http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html



There might be an old issue in bitbucket that the way associationproxy 
insists on calling clear() without any hook to override, the AP has a 
lot of old override hooks that are complicated and difficult to use, and 
then don't even cover this case.  So the AP is just not great in this 
area and I'd prefer subclassing is not the idiomatic solution to this.







Regards,
Piotr

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

Re: [sqlalchemy] Dependency rule tried to blank-out primary key column when trying to update using association proxy

[Piotr Dobrogost]

On Wednesday, February 24, 2016 at 4:21:58 AM UTC+1, Mike Bayer wrote:


in that way you can control exactly what __set__() does and it will 
> never remove a TextValue object where the same identity is coming in on 
> assignment. 
>

One more question; is this described somewhere in the docs? I don't see it 
at http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html


Regards,
Piotr

-- 
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] Dependency rule tried to blank-out primary key column when trying to update using association proxy

[Piotr Dobrogost]

On Wednesday, February 24, 2016 at 4:21:58 AM UTC+1, Mike Bayer wrote:

in that way you can control exactly what __set__() does and it will 
> never remove a TextValue object where the same identity is coming in on 
> assignment. 
>

Mike, thank you very much for your help.
 
Regards,
Piotr

-- 
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] Dependency rule tried to blank-out primary key column when trying to update using association proxy

[Mike Bayer]

On 02/23/2016 06:11 PM, Piotr Dobrogost wrote:

On Tuesday, February 23, 2016 at 5:44:45 PM UTC+1, Mike Bayer wrote:



That the old TextValue objects need to be deleted I understand. What I
don't see
is what makes SA specifically blank-out primary key column
'text_value.text_id'?


the "blanking out" is what occurs if you do *not* delete the old 
TextValue.  It means to be de-associated with the parent Text object 
which necessarily means setting TextValue.text_id to None, which in this 
mapping happens to also be part of the primary key.   If the TextValue 
is instead to be deleted, then this step does not occur.




Asking differently; I would understand if I got some integrity errors
while SA
would be trying to insert new rows with the same keys without first deleting
old rows but I have no idea how SA "came up with the idea" to blank-out
primary key?

In this specific example, marking the TextValue as deleted ends up
gving
you an UPDATE of TextValue, because the ORM converts an
INSERT/DELETE of
the same primary key into a single UPDATE.  But if your dictionary


Is this an implementation detail or official semantics?


this is how the ORM deals with the case of a DELETE of a row, then an 
INSERT of a row with that same identity.  The unit of work currently has 
the limitation that within a single flush, all INSERT statements happen 
before all DELETE statements, so in this case it takes the presence of 
both commands and turns them into an UPDATE.  however, it's still 
considered to be a delete + insert - if you're using the versioning 
extensions, the operation will still show up as these two at the session 
level.



 It's very

important for me
to keep identity of existing TextValue objects and only update values in
their 'value' column.


so this is what your example currently does, minus association proxy and 
the dict mapping:


# values = {'pl': u'org', 'en': u'org'}
session.add(
Text(_values=[
  TextValue(lang_id='pl', value='org'),
  TextValue(lang_id='en', value='org')])

text = session.query(Text).one()

# text.values = {'pl': u'modified', 'en': u'modified'}
text._values.clear()
text._values.append(TextValue(lang_id='pl', value='modified'))
text._values.append(TextValue(lang_id='en', value='modified'))


now if it instead was written like this, we wouldn't get redundant 
TextValue objects:


values = {'pl': u'org', 'en': u'org'}
session.add(Text(values=values))
text = session.query(Text).one()
text.values['pl'] = 'modified'
text.values['en'] = 'modified'


it would even keep the same TextValue if you did it like this:

text.values.update({'pl': u'modified', 'en': u'modified'})

so currently, the assignment you're doing treats this as "remove the 
old, add the new".  This is called a "bulk assignment" inside the 
collection mechanics.   The associationproxy has a few hooks to modify 
this but none of them currently provide a direct link to not first do 
the "clear" of the current value, if the incoming value is not the same 
object.  However, we can just override __set__ and make assignment do 
anything we want:


from sqlalchemy.ext.associationproxy import AssociationProxy


class DictUpdateProxy(AssociationProxy):
def __set__(self, obj, values):
proxy = self.__get__(obj, None)

keys_to_remove = set(proxy).difference(values)
for key in keys_to_remove:
del proxy[key]
proxy.update(values)


class Text(Base):
__tablename__ = 'text'
id = Column(Integer, primary_key=True)
_values = relationship('TextValue', 
collection_class=attribute_mapped_collection('lang_id'))


values = DictUpdateProxy(
'_values', 'value',
creator=lambda k, v: TextValue(lang_id=k, value=v))


in that way you can control exactly what __set__() does and it will 
never remove a TextValue object where the same identity is coming in on 
assignment.











(The reason it's important is that I'm using sqlalchemy-continuum
extension for automatic
versioning). Is there some preferred way to make sure identity is intact
and modifications
are done through UPDATE and not DELETE/INSERT?
Also, you say that in this specific example there is no DELETE needed
yet there's an error.
Does it mean SA tries to make sure DELETEs would be possible if they
were needed even if
in this specific example they are not needed?


Regards,
Piotr

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

Re: [sqlalchemy] Dependency rule tried to blank-out primary key column when trying to update using association proxy

[Mike Bayer]

On 02/23/2016 10:47 AM, Piotr Dobrogost wrote:

Hi!

I'm getting AssertionError: Dependency rule tried to blank-out primary
key column 'text_value.text_id' on instance '' error while trying to update row using association
proxy ('values') like this:

|
session.add(Text(values={'pl':u'org','en':u'org'}))
text =session.query(Text).one()
text.values ={'pl':u'modified','en':u'modified'}
session.commit()

|

Working example is at
https://gist.github.com/piotr-dobrogost/74073cf11006fb68e555
What am I doing wrong?



well the first thing is you definitely don't need 
UniqueConstraint('lang_id', 'text_id') because these columns are already 
in the primary key.


the next issue in this test at least is that you're referring to the 
primary key of a row in Lang but there is no Lang row being created 
here.  SQLite will let you put the invalid primary key into lang_id but 
only because foreign keys aren't enforced by default.


then the reason for the error is that TextValue objects are potentially 
being replaced but the mapping is not emitting a delete for the old 
TextValue, so you'd need to put cascade="all, delete-orphan" on the 
Text._values relationship so that when you assign a new dictionary 
value, the old TextValue objects are deleted.


In this specific example, marking the TextValue as deleted ends up gving 
you an UPDATE of TextValue, because the ORM converts an INSERT/DELETE of 
the same primary key into a single UPDATE.  But if your dictionary 
removed some of those keys you'd need a DELETE so the cascade rule 
should be applied here.








Best regards,
Piotr Dobrogost

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