SqlAlchemy TIMESTAMP 'on update' extra

Question

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 sqlalchemy.types import TIMESTAMP
from sqlalchemy.dialects.mysql import BIGINT
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class MyClass(Base):

  __tablename__ = 'my_class'

  id = Column(BIGINT(unsigned=True), primary_key=True)
  created_at = Column(TIMESTAMP, default=datetime.utcnow, nullable=False)
  updated_at = Column(TIMESTAMP, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)
  param1 = Column(BIGINT(unsigned=True), server_default=text('0'), nullable=False)

when I create this table with:

engine = create_engine('{dialect}://{user}:{password}@{host}/{name}'.format(**utils.config['db']))
Base.metadata.create_all(engine)

I get:

mysql> describe my_class;
+----------------+---------------------+------+-----+---------------------+-----------------------------+
| Field          | Type                | Null | Key | Default             | Extra                       |
+----------------+---------------------+------+-----+---------------------+-----------------------------+
| id             | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment              |
| created_at     | timestamp           | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |                           |
| updated_at     | timestamp           | NO   |     | 0000-00-00 00:00:00 |                             |
| param1         | bigint(20) unsigned | NO   |     | 0                   |                             |

Now the problem is that I do not want any on_update server default on my created_at attribute, its purpose is, in fact, being written only at the creation of the record, not on every update, as stated in the declaration of the class.

From a couple of tests I have made, I noticed that if I insert another attribute of type TIMESTAMP before created_at, then this attribute gets the on update CURRENT_TIMESTAMP extra, while created_at does not, as desired. This suggests that the first TIMESTAMP attribute SqlAlchemy finds in the declaration of a mapping gets the on update CURRENT_TIMESTAMP extra, though I don't see any reason for such behaviour.

I have also tried:

created_at = Column(TIMESTAMP, default=datetime.utcnow, server_onupdate=None, nullable=False)

and

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

but the problem persists. Any suggestion?

Ranchod

Answer 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)
for table in Base.metadata.tables.keys():
    if table not in _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'))

Ranchod

Answer 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'))

Thoroughgoing