使用带有mysql unix时间戳的sqlalchemy定义表 [英] Defining a table with sqlalchemy with a mysql unix timestamp

问题描述

背景，有几种方法可以在MySQ中存储日期.

Background, there are several ways to store dates in MySQ.

1. 作为字符串，例如"09/09/2009".
2. 使用函数UNIX_TIMESTAMP()作为整数，这应该是传统的unix时间表示形式(您知道自历元前后加/减leap秒起的秒数).
3. 作为MySQL TIMESTAMP，特定于mysql的数据类型与unix时间戳不同.

4. 作为MySQL Date字段，是另一个特定于mysql的数据类型.

1. As a string e.g. "09/09/2009".
2. As integer using the function UNIX_TIMESTAMP() this is supposedly the traditional unix time representation (you know seconds since the epoch plus/minus leap seconds).
3. As a MySQL TIMESTAMP, a mysql specific data type not the same than unix timestamps.

4. As a MySQL Date field, another mysql specific data type.

不要混淆案例2和案例3(或案例4)，这一点非常重要. 我有一个带有整数日期字段的现有表(情况2)，如何在sqlalchemy中定义它，而不必访问mysql的"FROM_UNIXTIME"函数?

It's very important not to confuse case 2 with case 3 (or case 4). I have an existing table with an integer date field (case 2) how can I define it in sqlalchemy in a way I don't have to access mysql's "FROM_UNIXTIME" function?

作为记录，仅使用sqlalchemy.types.DateTime并希望它在检测到整数列时做正确的事情不起作用，它适用于时间戳记字段和日期字段.

For the record, just using sqlalchemy.types.DateTime and hoping it does the right thing when it detects an integer column doesn't work, it works for timestamp fields and date fields.

推荐答案

我认为您显示的类型装饰器存在一些问题.

I think there is a couple of issues with the type decorator you showed.

1. impl应该是sqlalchemy.types.Integer而不是DateTime.
2. 装饰器应允许可空列.

1. impl should be sqlalchemy.types.Integer instead of DateTime.
2. The decorator should allow nullable columns.

这就是我的想法:

import datetime, time
from sqlalchemy.types import TypeDecorator, DateTime, Integer

class IntegerDateTime(TypeDecorator):
    """a type that decorates DateTime, converts to unix time on
    the way in and to datetime.datetime objects on the way out."""
    impl = Integer # In schema, you want these datetimes to
                   # be stored as integers.
    def process_bind_param(self, value, _):
        """Assumes a datetime.datetime"""
        if value is None:
            return None # support nullability
        elif isinstance(value, datetime.datetime):
            return int(time.mktime(value.timetuple()))
        raise ValueError("Can operate only on datetime values. "
                         "Offending value type: {0}".format(type(value).__name__))
    def process_result_value(self, value, _):
        if value is not None: # support nullability
            return datetime.datetime.fromtimestamp(float(value))

这篇关于使用带有mysql unix时间戳的sqlalchemy定义表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！