SqlAlchemy TIMESTAMP 'on update' 额外 [英] SqlAlchemy TIMESTAMP 'on update' extra
问题描述
我在 python3.4.3 上使用 SqlAlchemy 来管理 MySQL 数据库.我正在创建一个表:
from datetime import datetime从 sqlalchemy 导入列、文本、create_engine从 sqlalchemy.types 导入 TIMESTAMP从 sqlalchemy.dialects.mysql 导入 BIGINT从 sqlalchemy.ext.declarative 导入 declarative_baseBase = declarative_base()类 MyClass(Base):__tablename__ = 'my_class'id = Column(BIGINT(unsigned=True),primary_key=True)created_at = Column(TIMESTAMP, default=datetime.utcnow, nullable=False)更新时间 = 列(时间戳，默认值=datetime.utcnow，onupdate=datetime.utcnow，nullable=False)param1 = Column(BIGINT(unsigned=True), server_default=text('0'), nullable=False)当我创建这个表时:
engine = create_engine('{dialect}://{user}:{password}@{host}/{name}'.format(**utils.config['db']))Base.metadata.create_all(引擎)我明白了:
mysql>描述 my_class;+----------------+----------------------+------+------+---------------------+-----------------------------+|领域 |类型 |空 |钥匙 |默认 |额外 |+----------------+----------------------+------+------+---------------------+-----------------------------+|身份证 |bigint(20) 无符号 |否 |PRI |空 |自动增量||created_at |时间戳 |否 ||CURRENT_TIMESTAMP |更新 CURRENT_TIMESTAMP |||更新时间 |时间戳 |否 ||0000-00-00 00:00:00 |||参数 1 |bigint(20) 无符号 |否 ||0 ||现在的问题是我不希望在我的 created_at 属性上有任何 on_update 服务器默认值，事实上，它的目的是只在创建记录时写入，而不是在每次更新时写入，如类声明中所述.
从我所做的几个测试中，我注意到如果我在 created_at 之前插入另一个 TIMESTAMP 类型的属性，那么这个属性会在更新时获得 CURRENT_TIMESTAMP 额外，而 created_at 没有，根据需要.这表明 SqlAlchemy 在映射声明中找到的第一个 TIMESTAMP 属性获得了 on update CURRENT_TIMESTAMP 额外，尽管我没有看到这种行为的任何原因.>
我也试过:
created_at = Column(TIMESTAMP, default=datetime.utcnow, server_onupdate=None, nullable=False)和
created_at = Column(TIMESTAMP, default=datetime.utcnow, server_onupdate=text(''), nullable=False)但问题依然存在.有什么建议吗?
显然问题与 SqlAlchemy 无关，而是与底层 MySQL 引擎有关.默认行为是在表中的第一个 TIMESTAMP 列上设置 on update CURRENT_TIMESTAMP.
此行为在此处进行了描述.据我了解，一个可能的解决方案是使用 --explicit_defaults_for_timestamp=FALSE 标志启动 MySQL.可以在此处找到另一种解决方案.我还没有尝试过任何一种解决方案，我会在解决问题后立即更新此答案.
我尝试了第二种方法，它不是很方便，但它有效.在我的例子中，我创建了一组没有 created_at 属性的表，然后我按照上面链接中的描述更改了所有剩余的表.
大致如下:
_no_alter = set(['tables', 'which', 'do not', 'have', 'a created_at', 'column'])Base.metadata.create_all(引擎)对于 Base.metadata.tables.keys() 中的表:如果表不在 _no_alter 中:engine.execute(text('ALTER TABLE {} MODIFY created_at TIMESTAMP NOT NULL DEFAULT 0'.format(table)))另一种(更简单的)方法是在 SqlAlchemy 中为列设置一个 server_default 值:
created_at = Column(TIMESTAMP, default=datetime.utcnow, nullable=False, server_default=text('0'))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'))
这篇关于SqlAlchemy TIMESTAMP 'on update' 额外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
