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)