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.

Such fields are typically known as audit or record timestamp fields. Searching with those terms may reveal more. — Craig Ringer
– Craig Ringer, Commented Aug 28, 2012 at 6:55

plaes · Accepted Answer · 2017-06-01 07:01:31Z

84

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.

edited Jun 1, 2017 at 7:01

answered Aug 28, 2012 at 8:46

plaes

32.9k11 gold badges95 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Samoth Over a year ago

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 Over a year ago

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

Gringo Suave Over a year ago

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

bluesmonk Over a year ago

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 Over a year ago

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

|

Savad KP · Accepted Answer · 2015-11-27 05:50:37Z

7

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())

answered Nov 27, 2015 at 5:50

Savad KP

1,6744 gold badges28 silver badges40 bronze badges

2 Comments

Gringo Suave Over a year ago

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

lmiguelvargasf Over a year ago

I am using PostgreSQL 14+ and this worked!

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related