SQLAlchemy default DateTime

Question

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   lt stdin gt    line 1  in  lt module gt    File  orm models2 py   line 37  in  lt module gt      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

User · Answer

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

User · Answer

You can also use sqlalchemy builtin function for default DateTime  from sqlalchemy sql import func  DT   Column DateTime timezone True   default func now

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[python] SQLAlchemy default DateTime

Calculate timestamps within your DB, not your client

Use SQLALchemy's `server_default`

Understanding SQLAlchemy's `onupdate`/`server_onupdate`

One other potential gotcha:

UTC timestamp

Examples related to python

Examples related to date

Examples related to sqlalchemy