Conditional onupdate

[Tony Cao]

Hi all,

Is there way to use Column.onupdate conditionally? For example, say I have:

class A(Base):

    foo = Column(String)

    bar = Column(String)

    foo_updated = Column(DateTime, onupdate=update_fn) # Should only update when foo is updated

def update_fn(context):

    if ...: # How can I check if only foo was updated?

        return datetime.now()

    else:

        return ... # How can I say to not update?

Is there a way to define update_fn to only update foo_updated when foo changes? I can look at context.get_current_parameters() to see what columns are being used in the compiled statement, but it doesn't explicitly say which columns are the ones actually being updated; for example, if I have

A.query.filter(A.bar == 'test').update({A.foo: 'new'}, synchronize_session=False)

then context.get_current_parameters will return a dict with keys for both 'bar' and 'foo', although it looks like it suffixes the filter param with a '_1' - is that something I can rely on to know if a column is used as a filter instead of an update? And beyond that, is there a way I can specify to not update the column?

Alternatively, is there another approach recommended to doing this?

Thanks!

[Mike Bayer]

the most common way is to use the ORM level before_update event:

https://docs.sqlalchemy.org/en/13/orm/events.html?highlight=before_update#sqlalchemy.orm.events.MapperEvents.before_update

there's also before_execute() at the Core level, but before the UPDATE
statement is even written, e.g. at the ORM level, is the best way.
Once you are in the onupdate Python function, you have to return a
value, as this occurs well after the UPDATE statement has been
compiled.




>
> Thanks!
>
> --
> 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 post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/b66e89c9-6618-4600-9381-182fa101f5b6%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Tony Cao]

Hi,

Thanks for the response! I also tried looking into before_update, but is there a recommended way to figure out what columns are being updated from that event? I had found this post (https://stackoverflow.com/questions/15642286/how-can-i-get-a-sqlalchemy-orm-objects-previous-state-after-a-db-update) that suggests looking at the history of the attribute state - is there a better way?

> To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

[Mike Bayer]

you can inspect() the object and look at inspect(obj).attrs['some_attr'].history

https://docs.sqlalchemy.org/en/13/orm/internals.html#sqlalchemy.orm.state.AttributeState

> 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 https://groups.google.com/group/sqlalchemy.

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f13c4205-1df5-49c5-8c91-3c97cd8d9b05%40googlegroups.com.

[Tony Cao]

Ah but it also looks like the before_update event isn't triggered when doing bulk query updates, which we'd like to also update on. Is there a way to track those?

[Mike Bayer]

Bulk query updates, you mean, query.update() ? Or
session.bulk_update_mappings() ? In both cases you are
programatically providing the VALUES clause, so you know from your own
data what the UPDATE statement will be.

> 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 https://groups.google.com/group/sqlalchemy.

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/38b1162d-1204-41b7-9649-3938775eeef8%40googlegroups.com.

[Tony Cao]

I mean query.update(). 

Ah our goal was to make it so the update in question happened automatically without the developer having to explicitly specify it - in that case both obj.attr = x and obj_class.query.update({obj.attr: x}) should both trigger an update to obj.attr_modified. That's why I initially asked about onupdate, as it is triggered for both cases.

[Mike Bayer]

On Wed, May 15, 2019 at 6:10 PM Tony Cao <to...@benchling.com> wrote:
>
> I mean query.update().
>
> Ah our goal was to make it so the update in question happened automatically without the developer having to explicitly specify it - in that case both obj.attr = x and obj_class.query.update({obj.attr: x}) should both trigger an update to obj.attr_modified. That's why I initially asked about onupdate, as it is triggered for both cases.

for query.update() you can use the before_compile_update() and modify
the context.values dictionary in place to affect the UPDATE statement
generated by query.update(). See
https://docs.sqlalchemy.org/en/13/orm/events.html?highlight=before_compile_update#sqlalchemy.orm.events.QueryEvents.before_compile_update.
Additional detail regarding .values will be up within 30 minutes.

> 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 https://groups.google.com/group/sqlalchemy.

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/6dfe0cd9-c55c-486a-94b0-85eb222bdb55%40googlegroups.com.

[Tony Cao]

Ohh I see thanks for the help!

And just to confirm on the onupdate front, is it not possible to return the current value of the object being updated? It's ok to emit an extraneous update if the value doesn't change.

[Mike Bayer]

On Thu, May 16, 2019 at 4:26 PM Tony Cao <to...@benchling.com> wrote:
>
> Ohh I see thanks for the help!
>
> And just to confirm on the onupdate front, is it not possible to return the current value of the object being updated? It's ok to emit an extraneous update if the value doesn't change.

inside the onupdate handler you can see the current statement and the
parameters in the context, as far as the "a_1" thing, the parameters
for the columns being SET should be the same keys as the column
itself, it's the WHERE clause where you'd see the underscore thing
happening. show me what you get if you want to work with that.

> 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 https://groups.google.com/group/sqlalchemy.

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/1151a938-bcce-4d16-ba18-d2e489af7af3%40googlegroups.com.

[Tony Cao]

Sorry, I will clarify what I was asking. If I had my class example from above:

class A(Base):

    foo = Column(String)

    bar = Column(String)

    foo_updated = Column(DateTime, onupdate=update_fn) # Should only update when foo is updated

Based on what information is in the context, sometimes I may not want to update the value. However, my understanding is that onupdate_fn *must* return a value always. 

Given that we need to return a value from onupdate_fn, one idea I had was to return a value to emit:

 UPDATE A SET bar="new value", foo_updated=A.foo_updated WHERE id = 1

If we can somehow specify "set foo_updated to whatever value it already is" in SQL land, then we should be set! However, I tried some variations of this:

- `return 'foo_updated'`

- `return '"foo_updated"'

- `return A.foo_updated`

- `return A.__table__.c.foo_updated`

and none of these ended up working. Do you know if there's a way to specify an onupdate function to return a SQL column?

[Mike Bayer]

On Wed, May 22, 2019, at 2:09 PM, Tony Cao wrote:

Sorry, I will clarify what I was asking. If I had my class example from above:

class A(Base):

    foo = Column(String)

    bar = Column(String)

    foo_updated = Column(DateTime, onupdate=update_fn) # Should only update when foo is updated

Based on what information is in the context, sometimes I may not want to update the value. However, my understanding is that onupdate_fn *must* return a value always. 

Given that we need to return a value from onupdate_fn, one idea I had was to return a value to emit:

 UPDATE A SET bar="new value", foo_updated=A.foo_updated WHERE id = 1

If we can somehow specify "set foo_updated to whatever value it already is" in SQL land, then we should be set! However, I tried some variations of this:

- `return 'foo_updated'`

- `return '"foo_updated"'

- `return A.foo_updated`

- `return A.__table__.c.foo_updated`

and none of these ended up working. Do you know if there's a way to specify an onupdate function to return a SQL column?

again, within the "onupdate" handler, the SQL has already been written.  You can't change the bound parameter SQL syntax to be a column syntax at that point, you can only return a Python value.  You would need to rewrite the string using the before_cursor_update event, or modify the kind of statement to be generated using the before_execute or ORM level before_update / etc. events I illustrated earlier.

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 https://groups.google.com/group/sqlalchemy.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/bc2bce76-29bc-491d-a25b-f304a4274f1f%40googlegroups.com.