How to set DEFAULT ON UPDATE CURRENT_TIMESTAMP in mysql with sqlalchemy?

I am creating table with sqlalchemy.

user = Table('users', Metadata,
开发者_如何学运维Column('datecreated', TIMESTAMP,                                  
            server_default=text('CURRENT_TIMESTAMP')),            
Column('datemodified', TIMESTAMP,                                 
               server_onupdate=text('CURRENT_TIMESTAMP')),
)

But this will not set DEFAULT ON UPDATE CURRENT_TIMESTAMP.

I checked out How do you get SQLAlchemy to override MySQL "on update CURRENT_TIMESTAMP" but that will for literal I need to wire that in create table definition.

---

You can hijack the server_default to set also the ON UPDATE:

Column('datemodified', TIMESTAMP,
       server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))

This generates the following column entry:

datemodified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

However, Mchl's answer still applies: There can be only one automated TIMESTAMP column in a table (http://dev.mysql.com/doc/refman/5.5/en/timestamp.html)

Also note that the order of the columns is of importance! If you have a TIMESTAMP column without DEFAULT and ON UPDATE modifiers, and it is the first TIMESTAMP column in your table, it automatically will be set to DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. (http://dev.mysql.com/doc/refman/5.5/en/timestamp.html)

So this is fine:

Column('datemodified', TIMESTAMP,
       server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))
Column('datecreated', TIMESTAMP)

while this is not:

Column('datecreated', TIMESTAMP)
Column('datemodified', TIMESTAMP,
       server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))

In order to set the `datecreated' to the current time during first entry of a row, just set its value explicitly to NULL. Again, from http://dev.mysql.com/doc/refman/5.5/en/timestamp.html:

By default, TIMESTAMP columns are NOT NULL, cannot contain NULL values, and assigning NULL assigns the current timestamp.

---

If you're on MySQL 5.6 or later please scroll down for the relevant answer. Users of older versions, please read this one.

---

Until MySQL 5.6. in one table, you can only have one 'automated' TIMESTAMP column.

From: http://dev.mysql.com/doc/refman/5.5/en/timestamp.html

For one TIMESTAMP column in a table, you can assign the current timestamp as the default value and the auto-update value. It is possible to have the current timestamp be the default value for initializing the column, for the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

---

This worked on Mysql 8

sa.Column('created_at', sa.TIMESTAMP, server_default=func.now()),
sa.Column('updated_at', sa.TIMESTAMP, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))

For some reason the server_onupdate options was not working.

---

from sqlalchemy.sql import func

Column('datemodified',
       TIMESTAMP,                                 
       server_onupdate=text(
           ' ON UPDATE '.join([str(func.current_timestamp())] * 2)))

This way you use SQLAlchemy's inner func.current_timestamp() function.

---

If all you want is DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP in MySQL, then just setting the TIMESTAMP column in sqlalchemy to be non-nullable works

Column(TIMESTAMP, nullable=False)