Python SQLAlchemy: Reflecting the database breaks default/onupdate methods?

Question

I have two separate SQLAlchemy interfaces to a Postgres database. The first interface, in the context of a Flask App, contains this model:

app = create_app() # sets the SQLAlchemy Database URI, etc.
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow)
    updated_at = db.Column(db.DateTime, onupdate=datetime.datetime.utcnow)
    name = db.Column(db.String, nullable=False)

The second interface is not through Flask -- rather, it's a script that listens for a particular event, in which case it is meant to perform some computations and update a row in the database. To accomplish this, I have SQLAlchemy reflect the existent database:

from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.orm import mapper, sessionmaker
from sqlalchemy.ext.automap import automap_base
from os import environ

dbPath = "postgresql://" + ...
engine = create_engine(dbPath)

Base = automap_base()
Base.prepare(engine, reflect=True)

metadata = MetaData(engine)

class User(object):
    pass

users = Table('user', metadata, autoload=True, autoload_with=engine)
mapper(User, users)

Session = sessionmaker(bind=engine)
session = Session()

The issue I'm now running into is this: when I'm using the first interface to create a new entry or update one, things work as expected, and the created_at and updated_at fields are updated appropriately.

However, when I'm using the second interface -- importing the code and using session.query(User) to get an entry and to update it, the updated_at field doesn't change. Moreover, when I'm using this interface to create a new User, while it creates the new row as expected, it populates neither the created_at nor updated_at fields.

My questions:

Why is this happening? Why does the reflection seemingly break the default/onupdate methods?
How can I fix this?

In spite of the script have no connection with flask app, You can also reuse the definition of the user model in flask. — stamaimer
– stamaimer, Commented Apr 12, 2017 at 2:42
@stamaimer is that good form though? This approach seems to engender technical debt. — Newb
– Newb, Commented Apr 12, 2017 at 2:44
What event do you listen? Does event meet your requirements？ — stamaimer
– stamaimer, Commented Apr 12, 2017 at 2:48
@stamaimer no, I am listening to an event that is unrelated to sqlalchemy. The code I have provided is a minimal working example. — Newb
– Newb, Commented Apr 12, 2017 at 2:50

Ilja Everilä · Accepted Answer · 2017-04-12 06:25:27Z

3

default and onupdate are handled entirely client side in Python and so cannot be reflected from the DB. See "Limitations of Reflection". In case of default you could use server_default:

class User(db.Model):
    ...
    created_at = db.Column(db.DateTime,
                           server_default=text("now() at time zone 'UTC'"))

and for onupdate you'd have to write a DB trigger and use server_onupdate=FetchedValue().

On the other hand you could avoid all that and just separate your models from your application code to a module, used by both your Flask application and your script. This would of course be a bit more involved as you'd have to use vanilla SQLAlchemy declarative instead of the customized db.Model base of Flask-SQLAlchemy. Or, you could use custom commands with Flask to implement your scripts, which would allow using the Flask-SQLAlchemy extensions.

edited Apr 12, 2017 at 6:25

answered Apr 12, 2017 at 4:32

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

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

Collectives™ on Stack Overflow

Python SQLAlchemy: Reflecting the database breaks default/onupdate methods?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related