SQLAlchemy默认DateTime

问题:SQLAlchemy默认DateTime

这是我的声明性模型:

import datetime
from sqlalchemy import Column, Integer, DateTime
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Test(Base):
    __tablename__ = 'test'

    id = Column(Integer, primary_key=True)
    created_date = DateTime(default=datetime.datetime.utcnow)

但是,当我尝试导入此模块时,出现此错误:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "orm/models2.py", line 37, in <module>
    class Test(Base):
  File "orm/models2.py", line 41, in Test
    created_date = sqlalchemy.DateTime(default=datetime.datetime.utcnow)
TypeError: __init__() got an unexpected keyword argument 'default'

如果使用整数类型,则可以设置默认值。这是怎么回事?

This is my declarative model:

import datetime
from sqlalchemy import Column, Integer, DateTime
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Test(Base):
    __tablename__ = 'test'

    id = Column(Integer, primary_key=True)
    created_date = DateTime(default=datetime.datetime.utcnow)

However, when I try to import this module, I get this error:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "orm/models2.py", line 37, in <module>
    class Test(Base):
  File "orm/models2.py", line 41, in Test
    created_date = sqlalchemy.DateTime(default=datetime.datetime.utcnow)
TypeError: __init__() got an unexpected keyword argument 'default'

If I use an Integer type, I can set a default value. What’s going on?


回答 0

DateTime没有默认键作为输入。默认键应该是该Column功能的输入。试试这个:

import datetime
from sqlalchemy import Column, Integer, DateTime
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Test(Base):
    __tablename__ = 'test'

    id = Column(Integer, primary_key=True)
    created_date = Column(DateTime, default=datetime.datetime.utcnow)

DateTime doesn’t have a default key as an input. The default key should be an input to the Column function. Try this:

import datetime
from sqlalchemy import Column, Integer, DateTime
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Test(Base):
    __tablename__ = 'test'

    id = Column(Integer, primary_key=True)
    created_date = Column(DateTime, default=datetime.datetime.utcnow)

回答 1

计算数据库中的时间戳,而不是客户端中的时间戳

为了理智,您可能希望datetimes由数据库服务器而不是应用程序服务器来计算所有数据。计算应用程序中的时间戳可能会导致问题,因为网络等待时间是可变的,客户端会经历略微不同的时钟漂移,并且不同的编程语言有时会略有不同地计算时间。

SQLAlchemy允许您通过传递func.now()func.current_timestamp()(它们是彼此的别名)来执行此操作,该命令告诉DB计算时间戳本身。

使用SQLALchemy的 server_default

另外,对于已经告诉数据库计算值的默认值,通常最好使用server_default代替default。这告诉SQLAlchemy将默认值作为CREATE TABLE语句的一部分传递。

例如,如果您针对该表编写了一个临时脚本,则使用server_default意味着您无需担心手动向脚本添加时间戳调用-数据库将自动对其进行设置。

了解SQLAlchemy的onupdate/server_onupdate

SQLAlchemy还支持,onupdate以便每当更新该行时,它都会插入一个新的时间戳。再一次,最好告诉数据库来计算时间戳本身:

from sqlalchemy.sql import func

time_created = Column(DateTime(timezone=True), server_default=func.now())
time_updated = Column(DateTime(timezone=True), onupdate=func.now())

有一个server_onupdate参数,但与不同server_default,它实际上未在服务器端设置任何参数。它只是告诉SQLalchemy更新发生时(也许您在列上创建了触发器),数据库将更改列,因此SQLAlchemy将要求返回值,以便它可以更新相应的对象。

另一个潜在的陷阱:

您可能会惊讶地发现,如果在单个事务中进行大量更改,则它们都具有相同的时间戳。这是因为SQL标准指定CURRENT_TIMESTAMP根据事务的开始返回值。

PostgreSQL提供了非SQL标准statement_timestamp()clock_timestamp()并且在事务中更改。此处的文档:https : //www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

UTC时间戳

如果要使用UTC时间戳,请func.utcnow()SQLAlchemy文档中提供的实现存根。但是,您需要自己提供适当的特定于驱动程序的功能。

Calculate timestamps within your DB, not your client

For sanity, you probably want to have all datetimes calculated by your DB server, rather than the application server. Calculating the timestamp in the application can lead to problems because network latency is variable, clients experience slightly different clock drift, and different programming languages occasionally calculate time slightly differently.

SQLAlchemy allows you to do this by passing func.now() or func.current_timestamp() (they are aliases of each other) which tells the DB to calculate the timestamp itself.

Use SQLALchemy’s server_default

Additionally, for a default where you’re already telling the DB to calculate the value, it’s generally better to use server_default instead of default. This tells SQLAlchemy to pass the default value as part of the CREATE TABLE statement.

For example, if you write an ad hoc script against this table, using server_default means you won’t need to worry about manually adding a timestamp call to your script–the database will set it automatically.

Understanding SQLAlchemy’s onupdate/server_onupdate

SQLAlchemy also supports onupdate so that anytime the row is updated it inserts a new timestamp. Again, best to tell the DB to calculate the timestamp itself:

from sqlalchemy.sql import func

time_created = Column(DateTime(timezone=True), server_default=func.now())
time_updated = Column(DateTime(timezone=True), onupdate=func.now())

There is a server_onupdate parameter, but unlike server_default, it doesn’t actually set anything serverside. It just tells SQLalchemy that your database will change the column when an update happens (perhaps you created a trigger on the column ), so SQLAlchemy will ask for the return value so it can update the corresponding object.

One other potential gotcha:

You might be surprised to notice that if you make a bunch of changes within a single transaction, they all have the same timestamp. That’s because the SQL standard specifies that CURRENT_TIMESTAMP returns values based on the start of the transaction.

PostgreSQL provides the non-SQL-standard statement_timestamp() and clock_timestamp() which do change within a transaction. Docs here: https://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

UTC timestamp

If you want to use UTC timestamps, a stub of implementation for func.utcnow() is provided in SQLAlchemy documentation. You need to provide appropriate driver-specific functions on your own though.


回答 2

您还可以默认使用sqlalchemy内置函数 DateTime

from sqlalchemy.sql import func

DT = Column(DateTime(timezone=True), default=func.now())

You can also use sqlalchemy builtin function for default DateTime

from sqlalchemy.sql import func

DT = Column(DateTime(timezone=True), default=func.now())

回答 3

您可能想要使用,onupdate=datetime.now以便UPDATE也可以更改该last_updated字段。

SQLAlchemy对于python执行的函数有两个默认值。

  • default 设置一次INSERT的值
  • onupdate还将值设置为UPDATE 上的可调用结果。

You likely want to use onupdate=datetime.now so that UPDATEs also change the last_updated field.

SQLAlchemy has two defaults for python executed functions.

  • default sets the value on INSERT, only once
  • onupdate sets the value to the callable result on UPDATE as well.

回答 4

default关键字参数应被给予Column对象。

例:

Column(u'timestamp', TIMESTAMP(timezone=True), primary_key=False, nullable=False, default=time_now),

默认值可以是可调用的,在这里我定义如下。

from pytz import timezone
from datetime import datetime

UTC = timezone('UTC')

def time_now():
    return datetime.now(UTC)

The default keyword parameter should be given to the Column object.

Example:

Column(u'timestamp', TIMESTAMP(timezone=True), primary_key=False, nullable=False, default=time_now),

The default value can be a callable, which here I defined like the following.

from pytz import timezone
from datetime import datetime

UTC = timezone('UTC')

def time_now():
    return datetime.now(UTC)

回答 5

根据PostgreSQL文档,https://www.postgresql.org/docs/9.6/static/functions-datetime.html

now, CURRENT_TIMESTAMP, LOCALTIMESTAMP return the time of transaction.

这被认为是一个功能:目的是允许单个事务具有“当前”时间的一致概念,以便同一事务内的多个修改具有相同的时间戳。

如果您不希望事务时间戳记,则可能要使用statement_timestampclock_timestamp

statement_timestamp()

返回当前语句的开始时间(更具体地说,是从客户端收到最新命令消息的时间)。statement_timestamp

clock_timestamp()

返回实际的当前时间,因此,即使在单个SQL命令中,其值也会更改。

As per PostgreSQL documentation, https://www.postgresql.org/docs/9.6/static/functions-datetime.html

now, CURRENT_TIMESTAMP, LOCALTIMESTAMP return the time of transaction.

This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications within the same transaction bear the same time stamp.

You might want to use statement_timestamp or clock_timestamp if you don’t want transaction timestamp.

statement_timestamp()

returns the start time of the current statement (more specifically, the time of receipt of the latest command message from the client). statement_timestamp

clock_timestamp()

returns the actual current time, and therefore its value changes even within a single SQL command.