Sqlalchemy Timestamp 'on Update' Extra

I am using SqlAlchemy on python3.4.3 to manage a MySQL database. I was creating a table with: from datetime import datetime from sqlalchemy import Column, text, create_engine from

Solution 1:

Apparently the problem is not related with SqlAlchemy but with the underlying MySQL engine. The default behaviour is to set on update CURRENT_TIMESTAMP on the first TIMESTAMP column in a table.

This behaviour is described here. As far as I understand, a possible solution is to start MySQL with the --explicit_defaults_for_timestamp=FALSE flag. Another solution can be found here. I haven't tried either solution yet, I will update this answer as soon as I solve the problem.

EDIT: I tried the second method and it is not very handy but it works. In my case I created a set of the tables which do not have a created_at attribute and then I have altered all the remaining tables as described in the link above.

Something along the lines of:

_no_alter = set(['tables', 'which', 'do not', 'have', 'a created_at', 'column'])
Base.metadata.create_all(engine)
fortablein Base.metadata.tables.keys():
    iftablenotin _no_alter:
      engine.execute(text('ALTER TABLE {} MODIFY created_at TIMESTAMP NOT NULL DEFAULT 0'.format(table)))

EDIT2: another (easier) way to accomplish this is by setting in SqlAlchemy a server_default value for the column:

created_at =Column(TIMESTAMP, default=datetime.utcnow, nullable=False, server_default=text('0'))

Solution 2:

I faced same issue, you can accomplish this by:

created_time   = Column(TIMESTAMP, nullable=False, server_default=func.now())
updated_time   = Column(TIMESTAMP, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))