How to update a DateTime column with the SQL server value `NOW()`?

[Jean-Philippe Morin]

(I am using PostgreSQL)

I want to use the TIMESTAMP values of the SQL server machine instead of the python `datetime.utcnow()` value of the WEB server machines. 

There could be latencies or time diffierences between machines, so I would like to use the database server date and time as a common ground.

Here is an example of what I would like to do:

class Sample(Base):
 start_date = Column('start_date', DateTime, server_default=func.now(), nullable=False)
 last_update_date = Column('last_update_date', DateTime, server_default=func.now(), onupdate=func.now(), nullable=False)
 end_date = Column('end_date', DateTime, nullable=True)

When the model is created, its `start_date` column is set to the default DateTime value of the SQL server by using `server_default=func.now()`.

Then, on every updates made on the model, its `last_update_date` is set to the current DateTime value of the SQL server by using `onupdate=func.now()`.

def start_acquisition():
  new_sample = Sample()
  # ... set sample columns ...
  DBSession.add(new_sample)
  DBSession.commit()
 
def stop_acquisition(sample_id):
  the_sample = DBSession.query(Sample).filter_by(...)
  # ... set sample columns ...
  the_sample.end_date = ???
  DBSession.commit()
 

 How can I tell SQLAlchemy that on THAT particular update in `stop_acquisition`, I want to set the `end_date` column to the `func.now()` of the SQL server with the rest of the other modified columns?

What would be the proper way of doing that? Is there something like an `HybridProperty.expression` that could be used for inserts and updates? 

[Mike Bayer]

just assign func.now() to it, it will render as that SQL expression in
the UPDATE.

http://docs.sqlalchemy.org/en/rel_1_0/orm/persistence_techniques.html#embedding-sql-insert-update-expressions-into-a-flush

>
> What would be the proper way of doing that? Is there something like an
> `HybridProperty.expression` that could be used for inserts and updates?
>

> --
> 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+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Jean-Philippe Morin]

This is great! Thanks!

> <mailto:sqlalchemy+unsub...@googlegroups.com>.

[Vikas Prasad]

Just in case someone stumbles upon it in future.

I am using Postgres and I got the following results:

# This worked:

`the_sample.end_date = func.now()`

`db.session.commit()`

# This too worked:

`setattr(the_sample, 'end_date', func.now())`

`db.session.commit()`

# This didn't work though:

`Sample.query.filter_by(...).update({'end_date': func.now()})`

`db.session.commit()`

When using `Flask-SQLAlchemy` and setting `SQLALCHEMY=True`, the first two cases logs the `UPDATE` query followed by logging `COMMIT`. The last case didn't even logs the `UDPATE` query but directly logs a `ROLLBACK` with no other info.

Not sure of underlying cause, just recording it so that someone from future saves some time.

[Mike Bayer]

On Tue, Aug 28, 2018 at 5:13 AM, Vikas Prasad
<vikaspras...@gmail.com> wrote:
> Just in case someone stumbles upon it in future.
> I am using Postgres and I got the following results:
>
> # This worked:
> `the_sample.end_date = func.now()`
> `db.session.commit()`
>
> # This too worked:
> `setattr(the_sample, 'end_date', func.now())`
> `db.session.commit()`
>
> # This didn't work though:
> `Sample.query.filter_by(...).update({'end_date': func.now()})`
> `db.session.commit()`
>
> When using `Flask-SQLAlchemy` and setting `SQLALCHEMY=True`, the first two
> cases logs the `UPDATE` query followed by logging `COMMIT`. The last case
> didn't even logs the `UDPATE` query but directly logs a `ROLLBACK` with no
> other info.
>
> Not sure of underlying cause, just recording it so that someone from future
> saves some time.

the last one should definitely emit an UPDATE or it should be raising
an error. that you only saw a ROLLBACK suggests it raised an error and
your flask setup is suppressing the logging of that error.

>>> > <mailto:sqlalchemy+...@googlegroups.com>.

>>> > To post to this group, send email to sqlal...@googlegroups.com
>>> > <mailto:sqlal...@googlegroups.com>.
>>> > Visit this group at https://groups.google.com/group/sqlalchemy.
>>> > For more options, visit https://groups.google.com/d/optout.
>

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