使用 SQLAlchemy 处理 MySQL 上的 TIMESTAMP 类型的一点小技巧，很冷门的知识点哦。

最新推荐文章于 2024-05-16 09:25:24 发布
最新推荐文章于 2024-05-16 09:25:24 发布
阅读量4.2k 收藏 5
点赞数 3
文章标签： python sqlalchemy mysql
原文链接：https://blog.zengrong.net/post/salqlchemy-and-timestamp/
版权

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 ：

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

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

mysql> desc bonus;
+------------+------------+------+-----+-------------------+-----------------------------+
| Field      | Type       | Null | Key | Default           | Extra                       |
+------------+------------+------+-----+-------------------+-----------------------------+
| bid        | int(11)    | NO   | PRI | NULL              | auto_increment              |
| price      | int(11)    | NO   |     | 0                 |                             |
| share      | int(11)    | NO   |     | 0                 |                             |
| updatetime | timestamp  | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| createtime | timestamp  | NO   |     | CURRENT_TIMESTAMP |                             |
+------------+------------+------+-----+-------------------+-----------------------------+

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

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

# 条目的更新时间。每次更新条目的时候，本字段会自动更新时间戳
updatetime = db.Column(db.TIMESTAMP(True), nullable=False)
# 条目的创建时间。每次更新条目的时候，本字段不会自动更新时间戳
createtime = db.Column(db.TIMESTAMP(True), nullable=False, server_default=text('NOW()'))
# 或者
createtime = 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 列。

class Bonus(db.Model):
    __tablename__ = 'bonus'
    bid = db.Column(db.INT, primary_key=True, autoincrement=True)
    # 总充值
    price = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
    # 总分红
    share = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
    # 更新时间
    updatetime = db.Column(db.TIMESTAMP(True), nullable=False)
    # 创建时间
    createtime = db.Column(db.TIMESTAMP(True), nullable=False, server_default=text('NOW()'))

效果和 上面提到的 相同。

如果调换顺序如下：

class Bonus(db.Model):
    __tablename__ = 'bonus'
    bid = db.Column(db.INT, primary_key=True, autoincrement=True)
    # 总充值
    price = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
    # 总分红
    share = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
    # 创建时间
    createtime = db.Column(db.TIMESTAMP(True), nullable=False, server_default=text('NOW()'))
    # 更新时间
    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 列，可以这样做：

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

效果如下：

+------------+------------+------+-----+-------------------+-----------------------------+
| Field      | Type       | Null | Key | Default           | Extra                       |
+------------+------------+------+-----+-------------------+-----------------------------+
| bid        | int(11)    | NO   | PRI | NULL              | auto_increment              |
| agent      | bigint(20) | NO   | MUL | NULL              |                             |
| master     | bigint(20) | NO   | MUL | NULL              |                             |
| price      | int(11)    | NO   |     | 0                 |                             |
| share      | int(11)    | NO   |     | 0                 |                             |
| createtime | timestamp  | NO   |     | CURRENT_TIMESTAMP |                             |
| updatetime | timestamp  | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+------------+------+-----+-------------------+-----------------------------+

使用 default

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

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

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

+------------+------------+------+-----+-------------------+----------------+
| Field      | Type       | Null | Key | Default           | Extra          |
+------------+------------+------+-----+-------------------+----------------+
| bid        | int(11)    | NO   | PRI | NULL              | auto_increment |
| agent      | bigint(20) | NO   | MUL | NULL              |                |
| master     | bigint(20) | NO   | MUL | NULL              |                |
| price      | int(11)    | NO   |     | 0                 |                |
| share      | int(11)    | NO   |     | 0                 |                |
| createtime | timestamp  | NO   |     | CURRENT_TIMESTAMP |                |
| updatetime | timestamp  | YES  |     | NULL              |                |
+------------+------------+------+-----+-------------------+----------------+

MySQL 版本的限制

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

class Bonus(db.Model):
    __tablename__ = 'bonus'
    bid = db.Column(db.INT, primary_key=True, autoincrement=True)
    # 总充值
    price = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
    # 总分红
    share = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
    # 更新时间
    updatetime = db.Column(db.TIMESTAMP(True), nullable=False, server_default=text('0'))
    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’]

参考文章

tboqi1
关注
  • 3
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
SQLAlchemy Datetime 和 TIMESTAMP
C2575的博客
02-04 8372
SQLAlchemy 是 python 中常用都ORM的组建，在Flask 中广泛使用。 default 和server_default : default = 默认值只在使用SQLAlchemy创建数据时候才生效, 如果想要在任何地方都生效需要使用 server_default Datetime 的server_default 使用func.utime() TIMESTAMP的server_default 使用text("CURRENT_TIMESTAMP") 在这里我们创建2个表...
python使用SQLAlchemy操作MySQL
12-23
SQLAlchemy是Python编程语言下的一款开源软件，提供了SQL工具包及对象关系映射（ORM）工具，使用MIT许可证发行。SQLAlchemy首次发行于2006年2月，并迅速地在Python社区中最广泛使用的ORM工具之一，不亚于Django的ORM...
SQLAlchemy常用数据类型
a272329874a的博客
03-26 698
是专门为了解决浮点类型精度丢失的问 题的。在存储钱相关的字段的时候建议大家都使用这个数据类型。：存储时间，可以存储年月日时分秒毫秒等。映射到 数据库中也是datetime。指定某个字段只能是枚举中指定的几个值， 不能为其他值。：存储时间，可以存储时分秒。：存储时间，只能存储年月日。：双精度浮点类型，映射到数据库中是。：可变字符类型，映射到数据库中是。：布尔类型，映射到数据库中的是。：长文本类型，映射到数据库中是。：浮点类型，映射到数据库中是。：整形，映射到数据库中是。
sqlalchemy+sqlite 日期存储时只存储月日年
最新发布
qq_74452259的博客
05-16 126
无
SQLAlchemy and MySQL TIMESTAMP使用 SQLAlchemy 处理 MySQL 上的 TIMESTAMP 类型的一点小技巧
草根上的须子
06-16 606
SQLAlchemy 是一个功能强大的 ORM 。本篇介绍使用 SQLAlchemy 处理 MySQL 上的 TIMESTAMP 类型的一点小技巧，很冷门的知识点哦。 列的默认值 注意，在使用 db.create_all() 进行初始化创建表的时候，如果为 Column 指定了 default 的值，并不会影响创建的表中的对应列的默认值。这些 default 的值仅仅是在使用 SQLAlchemy 系统插入值的时候会提供默认值。如果你希望影响 MySQL 中 Column 的默认值，必须使用 serve
sqlalchemy 设置 TIMESTAMP 类型的字段自动更新时间
远哥的小迷弟
08-29 4988
from sqlalchemy import Column, TIMESTAMP, text ts = Column(TIMESTAMP, server_default=text('CURRENT_TIMESTAMP'), server_onupdate=text('CURRENT_TIMESTAMP'))
sqlalchemy查询时过滤类型为TIMESTAMP字段的数据
请叫我皮皮虾的博客
05-10 831
1、问题记录：python中，如何得到过滤字段为TIMESTAMP类型的数据库表中的数据？（过滤create_time为当天的数据） python中简化的表的结构如下： class Test(Base): __tablename__ = 'test' create_time = Column(TIMESTAMP, nullable=False, server_default=text("CURRENT_TIMESTAMP")) id = Column(Inter) create_time的数据类
SQLAlchemy学习-7.Column 设置日期时间类型
qq_27371025的博客
08-20 1558
在表里面一般会设置创建时间和更新时间，创建时间是数据写入时候的时间，更新时间是表的数据有更新，自动获取当前时间。 onupdate 是在更新时执行该方法，一般用在更新时间字段上。
基于sqlalchemy对mysql实现增删改查操作
09-08
本篇将详细讲解如何使用SQLAlchemy与MySQL进行增删改查操作。 首先，我们需要安装SQLAlchemy库，可以通过pip命令进行安装： ```bash pip install sqlalchemy ``` 接下来，我们要建立与MySQL数据库的连接。在Python...
基于Flask的RESTful API实战代码：集成Flask-SQLAlchemy与MySQL
03-25
项目概述：本项目是一个基于Python语言的实战项目，使用了Flask框架构建RESTful ...简单描述：本项目是使用Flask RESTful扩展和Flask-SQLAlchemy进行数据库管理的实践案例，旨在通过MySQL提供可靠的数据存储解决方案。
sqlalchemy定义mysql时间戳字段
weixin_30377461的博客
09-06 485
update_time = Column(TIMESTAMP, nullable=False, comment='更新时间戳', server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) server_onupdate=func.now()不...
Python sqlalchemy时间戳及密码管理实现代码详解
09-16
主要介绍了Python sqlalchemy时间戳及密码管理实现代码详解,文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
解决SQLAlchemy（Python）调用MySQL数据库时插入时间戳数据的问题
xiecheng1995的博客
06-05 7512
在建表的类中定义时间戳类型的column时，应写成: d_time = db.Column(db.DateTime, nullable=False) 向MySQL数据库中插入数据时，插入数据的类型和格式（以当前系统时间为例）应写成如下形式： d_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) 此处需要import time 这...
拥抱SQLAlchemy 之二 拉拉手，我请你去看电影~
weixin_30566063的博客
08-27 263
所有按照我Pao妞的程序，上次是一见钟情，那么这次一定是要约她看电影的，坐在情侣包，你挨着我，我爱着你，可以好好的了解了解，拉近感情了，哈哈，这又让我想起了初恋，都说初恋无限美，正所谓“举头望明月，低头思故乡”，好诗呀~~ 今天看的电影名字叫《SQL Expression Language》，绝对引进大片，首映哦~ 这次，我主要是要了解一下SQLAlchemy构建SQL的一些细节，如果你没...
sqlalchemy
mark 一下
10-18 184
# -*- coding:utf-8 -*- # Date: 2018/10/16 10:30 from datetime import datetime from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import ...
SQLAlchemy建表 更新时间随时间戳自动更新
大潘的博客
10-15 977
update_time = db.Column(db.TIMESTAMP, nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')) # 记录的更新时间 如果想要根据当前时间戳更新生效 需要有这个属性 server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP') ...
flask-sqlalchemy中Datetime的创建时间、修改时间,default，server_default，onupdate
aobian2884的博客
04-18 1373
记录第一次创建时间，default falsk中如下两个字段 create_time1 = db.Column(db.DateTime, default=datetime.now) create_time2 = db.Column(db.DateTime, default=datetime.now()) 两者的区别: 第一个插入的是期望的, 数据的插入时间，每条数据...
[DB]-MySQL中datatime和timestamp的区别
xyzhang的博客
07-27 170
原文链接：https://blog.csdn.net/u014696474/article/details/70568733 mysql中有三种日期类型： date(年-月-日) create table test(hiredate date); datetime（日期时间类型） create table test(hiredate datetime); timestamp（邮戳类型，保存年-月-日 时-分-秒） create table test(hiredate timestamp); datetime
column_timestamp plugin
Woody
10-16 240
  有些时候，我们可能需要记录某些列的更新时间，类似于rails的timestamps。手工一一处理稍显繁琐，于是将其抽取出来，写成了一个简单的plugin--column_timestamp，自动记录一些简单列的更新时间。   使用方法   1.将column_timestamp放到plugins目录。   2.在表中针对要记录更新时间的列建立一个新列：[column_name]_updat...
sqlalchemy 使用mysql全文检索
05-26
SQLAlchemy 是一种 Python 的 ORM（对象关系映射）库，它提供了一种抽象层，可以用 Python 对象的方式来操作数据库。而 MySQL 全文检索（Full-Text Search）是一种用于处理自然语言的搜索技术，它可以在 MySQL 中进行配置和使用。 在 SQLAlchemy 中，我们可以使用 MySQL 的全文检索来进行高效的文本搜索。具体步骤如下： 1. 首先，我们需要在 MySQL 中创建一个支持全文检索的表。例如： ```sql CREATE TABLE products ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), description TEXT, FULLTEXT(name, description) ) ENGINE=InnoDB; ``` 这个表包括了 id、name 和 description 三个字段，其中 name 和 description 两个字段被指定为全文检索字段。 2. 然后，在 SQLAlchemy 中定义一个对应的模型类。例如： ```python from sqlalchemy import Column, Integer, String, Text from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Product(Base): __tablename__ = 'products' id = Column(Integer, primary_key=True) name = Column(String(255)) description = Column(Text) ``` 这个模型类对应了 MySQL 中的 products 表，并且使用 SQLAlchemy 提供的 Column 类型来定义了 id、name 和 description 三个字段。 3. 接下来，我们可以使用 SQLAlchemy 提供的 fulltext 模块来执行全文检索。例如： ```python from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base from sqlalchemy_searchable import make_searchable Base = declarative_base() engine = create_engine('mysql+pymysql://user:password@host:port/dbname?charset=utf8mb4') Session = sessionmaker(bind=engine) make_searchable() class Product(Base): __tablename__ = 'products' id = Column(Integer, primary_key=True) name = Column(String(255)) description = Column(Text) @classmethod def search(cls, query): session = Session() return session.query(cls).search(query).all() ``` 这里我们使用了 SQLAlchemy-Searchable 扩展，它提供了一个 make_searchable() 函数来启用全文检索功能。然后我们在模型类中定义了一个 search() 方法，它接受一个查询字符串并返回符合条件的结果。 4. 最后，我们可以使用 search() 方法来进行全文检索。例如： ```python products = Product.search('foo bar') for product in products: print(product.name, product.description) ``` 这个例子中，我们执行了一个名为 'foo bar' 的全文检索，并遍历了检索结果中的每个产品，然后打印出了产品的名称和描述。 总的来说，使用 SQLAlchemy 和 MySQL 全文检索可以帮助我们在 Python 中高效地进行文本搜索，从而提高了应用程序的性能和用户体验。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值