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