SqlAlchemy TIMESTAMP 'on update' extra
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)
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'))
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'))
Simone Bronzini
PhD student in Computer Science, main languages used: Python, C, Go, Bash, Gnuplot, PHP, Perl. When not working I enjoy cinema and music.
Updated on June 21, 2022Comments
-
Simone Bronzini almost 2 years
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
beforecreated_at
, then this attribute gets theon update CURRENT_TIMESTAMP
extra, whilecreated_at
does not, as desired. This suggests that the firstTIMESTAMP
attribute SqlAlchemy finds in the declaration of a mapping gets theon 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?