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

Question

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.

Answer 1

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.