SQLAlchemy and MySQL TIMESTAMP

2017-02-27 [2628] technology mysql python sqlalchemy

SQLAlchemy 是一个功能强大的 ORM 。本篇介绍使用 SQLAlchemy 处理 MySQL 上的 TIMESTAMP 类型的一点小技巧，很冷门的知识点哦。

列的默认值

注意，在使用 db.create_all() 进行初始化创建表的时候，如果为 Column 指定了 default 的值，并不会影响创建的表中的对应列的默认值。这些 default 的值仅仅是在使用 SQLAlchemy 系统插入值的时候会提供默认值。如果你希望影响 MySQL 中 Column 的默认值，必须使用 server_default 来指定。

例如要设置一个 Colmun 默认值为0 ，则需要设定 server_default=text('0') 。

MySQL 的默认行为

使用下面的代码创建一个默认值不为空的 TIMESTAMP Column ：

1updatetime = db.Column(db.TIMESTAMP(True), nullable=False)

如果对一个 TIMESTAMP Column 使用 nullable=False ，MySQL 会自动加入on update CURRENT_TIMESTAMP 。这是 MySQL 的默认行为：sysvar_explicit_defaults_for_timestamp 。 请关注下面的 updatetime Field ：

 1mysql> desc bonus;
 2+------------+------------+------+-----+-------------------+-----------------------------+
 3| Field      | Type       | Null | Key | Default           | Extra                       |
 4+------------+------------+------+-----+-------------------+-----------------------------+
 5| bid        | int(11)    | NO   | PRI | NULL              | auto_increment              |
 6| price      | int(11)    | NO   |     | 0                 |                             |
 7| share      | int(11)    | NO   |     | 0                 |                             |
 8| updatetime | timestamp  | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
 9| createtime | timestamp  | NO   |     | CURRENT_TIMESTAMP |                             |
10+------------+------------+------+-----+-------------------+-----------------------------+

然而，Extra 中包含 on update CURRENT_TIMESTAMP 的 Column， 在每次更新该 Recored 的时候，updatetime 都会自动更新。

所以，如果需要给时间戳类型加入默认值，但不在每次更新的时候自动更新时间戳，可以这样做：

1# 条目的更新时间。每次更新条目的时候，本字段会自动更新时间戳
2updatetime = db.Column(db.TIMESTAMP(True), nullable=False)
3# 条目的创建时间。每次更新条目的时候，本字段不会自动更新时间戳
4createtime = db.Column(db.TIMESTAMP(True), nullable=False, server_default=text('NOW()'))
5# 或者
6createtime = db.Column(db.TIMESTAMP(True), nullable=False, server_default=text('CURRENT_TIMESTAMP'))

调整默认行为的顺序

SqlAlchemy TIMESTAMP 'on update' extra 中提到 on update CURRENT_TIMESTAMP 必须是第一个 TIMESTAMP 列。对这点我并不认同，经过测试，我的结论如下：

如果你希望通过设定非空让 MySQL 自动生成 on update CURRENT_TIMESTAMP ，则 必须 将该列作为第一个 TIMESTAMP 列。

 1class Bonus(db.Model):
 2    __tablename__ = 'bonus'
 3    bid = db.Column(db.INT, primary_key=True, autoincrement=True)
 4    # 总充值
 5    price = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
 6    # 总分红
 7    share = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
 8    # 更新时间
 9    updatetime = db.Column(db.TIMESTAMP(True), nullable=False)
10    # 创建时间
11    createtime = db.Column(db.TIMESTAMP(True), nullable=False, server_default=text('NOW()'))

效果和 上面提到的 相同。

如果调换顺序如下：

 1class Bonus(db.Model):
 2    __tablename__ = 'bonus'
 3    bid = db.Column(db.INT, primary_key=True, autoincrement=True)
 4    # 总充值
 5    price = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
 6    # 总分红
 7    share = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
 8    # 创建时间
 9    createtime = db.Column(db.TIMESTAMP(True), nullable=False, server_default=text('NOW()'))
10    # 更新时间
11    updatetime = db.Column(db.TIMESTAMP(True), nullable=False)

会报错：

sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1067, "Invalid default value for 'updatetime'") [SQL: '\nCREATE TABLE bonus (\n\tbid INTEGER NOT NULL AUTO_INCREMENT, \n\tagent BIGINT NOT NULL, \n\tmaster BIGINT NOT NULL, \n\tprice INTEGER NOT NULL DEFAULT 0, \n\tshare INTEGER NOT NULL DEFAULT 0, \n\tcreatetime TIMESTAMP NOT NULL DEFAULT NOW(), \n\tupdatetime TIMESTAMP NOT NULL, \n\tPRIMARY KEY (bid), \n\tFOREIGN KEY(agent) REFERENCES account (gameuid), \n\tFOREIGN KEY(master) REFERENCES account (gameuid)\n)\n\n']

如果你一定要把 updatetime 作为第二个 timestamp 列，可以这样做：

 1class Bonus(db.Model):
 2    __tablename__ = 'bonus'
 3    bid = db.Column(db.INT, primary_key=True, autoincrement=True)
 4    # 总充值
 5    price = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
 6    # 总分红
 7    share = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
 8    # 创建时间
 9    createtime = db.Column(db.TIMESTAMP(True), nullable=False, server_default=text('NOW()'))
10    # 更新时间
11    updatetime = db.Column(db.TIMESTAMP(True), nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))

效果如下：

 1+------------+------------+------+-----+-------------------+-----------------------------+
 2| Field      | Type       | Null | Key | Default           | Extra                       |
 3+------------+------------+------+-----+-------------------+-----------------------------+
 4| bid        | int(11)    | NO   | PRI | NULL              | auto_increment              |
 5| agent      | bigint(20) | NO   | MUL | NULL              |                             |
 6| master     | bigint(20) | NO   | MUL | NULL              |                             |
 7| price      | int(11)    | NO   |     | 0                 |                             |
 8| share      | int(11)    | NO   |     | 0                 |                             |
 9| createtime | timestamp  | NO   |     | CURRENT_TIMESTAMP |                             |
10| updatetime | timestamp  | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
11+------------+------------+------+-----+-------------------+-----------------------------+

使用 default

我们也可以把默认值设置为空，然后通过 SQLAlchemy Column 提供的 default 在 python 层面自动加入默认值：

 1class Bonus(db.Model):
 2    __tablename__ = 'bonus'
 3    bid = db.Column(db.INT, primary_key=True, autoincrement=True)
 4    # 总充值
 5    price = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
 6    # 总分红
 7    share = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
 8    # 创建时间
 9    createtime = db.Column(db.TIMESTAMP(True), nullable=False, server_default=text('NOW()'))
10    # 更新时间
11    updatetime = db.Column(db.TIMESTAMP(True), nullable=True, default=func.utcnow())

效果如下。在这种情况下，MySQL 中没有设定 updatetime 的默认值，但是在给 Column 赋值的时候，python 会使用 utcnow 自动为其加入默认值。这是在 SQLAlchemy 层面实现的，并不是在 MySQL 中实现的。

 1+------------+------------+------+-----+-------------------+----------------+
 2| Field      | Type       | Null | Key | Default           | Extra          |
 3+------------+------------+------+-----+-------------------+----------------+
 4| bid        | int(11)    | NO   | PRI | NULL              | auto_increment |
 5| agent      | bigint(20) | NO   | MUL | NULL              |                |
 6| master     | bigint(20) | NO   | MUL | NULL              |                |
 7| price      | int(11)    | NO   |     | 0                 |                |
 8| share      | int(11)    | NO   |     | 0                 |                |
 9| createtime | timestamp  | NO   |     | CURRENT_TIMESTAMP |                |
10| updatetime | timestamp  | YES  |     | NULL              |                |
11+------------+------------+------+-----+-------------------+----------------+

MySQL 版本的限制

另外，很多文章提到了 使用 server_default=text('0') 作为默认值。在 MySQL5.7上，这个默认值是不可用的：

 1class Bonus(db.Model):
 2    __tablename__ = 'bonus'
 3    bid = db.Column(db.INT, primary_key=True, autoincrement=True)
 4    # 总充值
 5    price = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
 6    # 总分红
 7    share = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
 8    # 更新时间
 9    updatetime = db.Column(db.TIMESTAMP(True), nullable=False, server_default=text('0'))
10    createtime = db.Column(db.TIMESTAMP(True), nullable=False, server_default=text('NOW()'))

sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1067, "Invalid default value for 'updatetime'") [SQL: '\nCREATE TABLE bonus (\n\tbid INTEGER NOT NULL AUTO_INCREMENT, \n\tagent BIGINT NOT NULL, \n\tmaster BIGINT NOT NULL, \n\tprice INTEGER NOT NULL DEFAULT 0, \n\tshare INTEGER NOT NULL DEFAULT 0, \n\tupdatetime TIMESTAMP NOT NULL DEFAULT 0, \n\tcreatetime TIMESTAMP NOT NULL DEFAULT NOW(), \n\tPRIMARY KEY (bid), \n\tFOREIGN KEY(agent) REFERENCES account (gameuid), \n\tFOREIGN KEY(master) REFERENCES account (gameuid)\n)\n\n']

参考文章