How Can I Automatically Populate SQLAlchemy Database Fields? (Flask-SQLAlchemy)

Question

I've got a simple User model, defined like so:

# models.py
from datetime import datetime
from myapp import db

class User(db.Model):
  id = db.Column(db.Integer(), primary_key=True)
  email = db.Column(db.String(100), unique=True)
  password = db.Column(db.String(100))
  date_updated = db.Column(db.DateTime())

  def __init__(self, email, password, date_updated=None):
    self.email = email
    self.password = password
    self.date_updated = datetime.utcnow()

When I create a new User object, my date_updated field gets set to the current time. What I'd like to do is make it so that whenever I save changes to my User object my date_updated field is set to the current time automatically.

I've scoured the documentation, but for the life of me I can't seem to find any references to this. I'm very new to SQLAlchemy, so I really have no prior experience to draw from.

Would love some feedback, thank you.

Solution 1

Just add server_default or default argument to the column fields:

created_on = db.Column(db.DateTime, server_default=db.func.now())
updated_on = db.Column(db.DateTime, server_default=db.func.now(), server_onupdate=db.func.now())

I prefer the {created,updated}_on column names. ;)

SQLAlchemy docs about column insert/update defaults.

[Edit]: Updated code to use server_default arguments in the code.

[Edit 2]: Replaced onupdate with server_onupdate arguments.

Solution 2

date_created  = db.Column(db.DateTime,  default=db.func.current_timestamp())
date_modified = db.Column(db.DateTime,  default=db.func.current_timestamp(),
                                       onupdate=db.func.current_timestamp())

Comments

[Samoth]

Hi, would you please advise how to modify the timezone since the db.func.now() return the timestamp out of my timezone. Thanks in advance.

[Samoth]

I found simply updated_on = db.Column(db.DateTime, onupdate=datetime.datetime.now) resolve the timezone issue according to this .

[Gringo Suave]

Why use server_default? Docs are wordy but useless. Guessing from the name that it occurs at the database?

[Gringo Suave]

Some justification for this variant, found here: groups.google.com/forum/#!topic/sqlalchemy/7A6LCOKnrVY

[bluesmonk]

server_default is the server side value issued in the SQL during a create_table instruction, in contrast to the default value which refers to the python side of the model. as in alembic.zzzcomputing.com/en/latest/ops.html * In particular, default values to be created on the database side are specified using the server_default parameter, and not default which only specifies Python-side defaults*

[Mithril]

Don't work on mysql 5.5 , because it doesn't support DATETIME DEFAULT now() . Any other way ?

[plaes]

@Mithril Upgrade? Or you could try CURRENT_TIMESTAMP as an alternative for now(), though it might be also unsupported on first 5.5.x versions.