使用 SqlAlchemy 插入 MySQL 时间戳列值 [英] Insert MySQL timestamp column value with SqlAlchemy

问题描述

我有一个 sqlalchemy 类映射到 MySQL innoDB 中的数据库表.该表有几列，除了 TIMESTAMP 列之外，我都能够成功地填充它们:

I have a sqlalchemy class mapping to a database table in MySQL innoDB. The table has several columns and I am able to successfully populate them all except from a TIMESTAMP column:

映射:

class HarvestSources(Base):
    __table__ = Table('harvested', metadata, autoload=True)

MySQL 上的列是一个 TIMESTAMP，它的默认值是 CURRENT_TIMESTAMP，但是当我插入一行时，它被填充为 NULL.

The column on MySQL is a TIMESTAMP which has CURRENT_TIMESTAMP as default value, but when I insert a row it's being filled with NULL.

如果默认值不起作用，那么我需要手动设置时间戳，我该怎么做.

If default is not working then I need to manually set the timestamp, how could I do either of them.

用于向表中插入行的SqlAlchemy代码:

SqlAlchemy code to insert row to table:

source = HarvestSources()
source.url = url
source.raw_data = data
source.date = ?

DB.session.add(source)
DB.session.commit()

推荐答案

datetime 对象被转换为时间戳，所以你可以使用:

datetime objects are converted to timestamps, so you can just use:

from datetime import datetime
...
source.date = datetime.now()

或 datetime.utcnow() 如果您想使用 utc 保存它.默认值 (CURRENT_TIMESTAMP) 使用本地时区，因此 datetime.now() 更接近于该时区 - 但几乎总是首选以 UTC 存储时间相关数据，并且仅在向用户呈现数据时才进行时区转换.

or datetime.utcnow() if you want to save it using utc. The default (CURRENT_TIMESTAMP) uses the local timezone, so datetime.now() is closer to that - but it almost always should be preferrable to store time related data in UTC, and do timezone conversions only when presenting data to the user.

这篇关于使用 SqlAlchemy 插入 MySQL 时间戳列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！