How to create generated column in Flask-SQLAlchemy?

Question

This SQL is OK

CREATE TABLE `calendar` (
  `dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `d` date as (dt),
  `t` time as (dt)
);

How to create this table in Flask-SQLAlchemy?

class Calendar(db.Model):
    __table_args__ = {'mysql_collate': 'utf8_general_ci', 'mysql_engine':'InnoDB'}

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    dt = db.Column(db.DateTime, nullable=False, server_default=db.func.current_timestamp())
    d = ???
    t = ???

db.create_all()

This is error

class Calendar(db.Model):
    __table_args__ = {'mysql_collate': 'utf8_general_ci', 'mysql_engine':'InnoDB'}

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    dt = db.Column(db.DateTime, nullable=False, server_default=db.func.current_timestamp())
    d = db.ColumnProperty(db.Date,dt)
    t = db.ColumnProperty(db.Time,dt)

It looks to me like you're trying to have separate `Date` and `Time` columns when you already have a `DateTime`; if that's the case, [see here](https://stackoverflow.com/a/25573176/565489). — Asmus, Apr 17 '19 at 21:20

Asmus · Answer 1 · 2022-04-26T07:06:49.620

Perhaps this is not exactly what you want, but still: I'd suggest to use a hybrid_property instead (see documentation here), like so:

from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Float, Dat
from datetime import datetime

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    firstname = Column(String(50))
    lastname = Column(String(50))
    birthdate = Column(DateTime, nullable=False, )

    @hybrid_property
    def birthyear(self):
        return self.birthdate.strftime("%Y/%m/%d")

    @hybrid_property
    def birthtime(self):
        return self.birthdate.strftime("%H:%M:%S")
    
us = User(firstname="Jon", 
    lastname="Snow",
    birthdate=datetime(1986, 12, 26, 23, 59, 59))

print(us.firstname, us.lastname, us.birthyear, us.birthtime)

I use generated column, because I only use SQLAlchemy to create database. I am actually using other ORM in my php website — CL So, Apr 19 '19 at 06:06
If you're only using SQLAlchemy to create the db, and already know how to run it in SQL, why don't you just run `engine.execute("ALTER TABLE … ")` in the end? [See here](https://stackoverflow.com/a/16448572/565489), for example. — Asmus, Apr 19 '19 at 07:45

score 1 · Answer 2 · edited May 22 '21 at 07:39

1

This works for me

geom is the referenced column
expire_on_flush avoids instertions or uptates in computed columns.

distance = column_property(db.Column(db.Float,Computed("Round((st_length(geom,false)::numeric /1000),2)",True),nullable=True),expire_on_flush= True)

Hope it works

edited May 22 '21 at 07:39

CoolCoder

786
7
20

answered May 22 '21 at 06:54

Javier Rivases

11
1

Here's the [documentation link](https://docs.sqlalchemy.org/en/14/core/defaults.html#sqlalchemy.schema.Computed) – json singh Dec 03 '22 at 03:56

How to create generated column in Flask-SQLAlchemy?

2 Answers2