Use to_char in Flask SqlAlchemy query

user_470783 0
Viewed 1871

In my Flask model I have this column:

datetime = db.Column(db.DateTime, nullable=False, default=datetime.now)

When I populate the rows from a PostgreSql database I get this:

2020-07-03 18:12:49.362271
2020-07-04 09:21:34.644202

Now, I want to format those results using the to_char function from Postgresql. The query shoudl be this:

select to_char(datetime, 'YYYY-MM-DD HH24:MI:SS') as datetime from order_detail;

And the result is this:

---------------------
 2020-07-03 18:12:49
 2020-07-04 09:21:34
(2 rows)

How can I tell Flask-SqlAlchemy to execute that function when it queries the order_detail table?.

Important: I don't want to do the formatting in my Flask app, I want to let Postgresql format the results.

flask-sqlalchemy flask postgresql
1 Answers

Use column_property() and func(). Assuming your model is named Foo:

from sqlalchemy import func
from sqlalchemy.orm import column_property

class Foo(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    datetime = db.Column(db.DateTime, nullable=False, default=datetime.now)
    formatted_datetime = column_property(func.to_char(datetime, 'YYYY-MM-DD HH24:MI:SS'))

# print(Foo.query.all()[0].formatted_datetime)
# ->'2020-07-04 06:59:09'

If it's important that the model's attribute name be datetime, you'll need to give the underlying column a different attribute name and reference the column using it's name kwarg.

class Foo2(db.Model):
    __tablename__ = 'foo'
    id = db.Column(db.Integer, primary_key=True)
    orig_datetime = db.Column(db.DateTime, name='datetime', nullable=False, default=datetime.now)
    datetime = column_property(func.to_char(orig_datetime, 'YYYY-MM-DD HH24:MI:SS'))

# print(Foo2.query.all()[0].datetime)
# ->'2020-07-04 06:59:09'
AdamKG_163610
Related