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

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.