hybrid-tests.py

import datetime
from sqlalchemy.ext.hybrid import hybrid_property

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://localhost'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)


class GalleryShowOnSite(db.Model):
    __tablename__ = 'shows_on_site'

    id = db.Column(db.Integer, nullable=False, primary_key=True)
    start_year = db.Column(db.SmallInteger, nullable=True)
    start_month = db.Column(db.SmallInteger, nullable=True)
    start_day = db.Column(db.SmallInteger, nullable=True)
    end_year = db.Column(db.SmallInteger, nullable=True)
    end_month = db.Column(db.SmallInteger, nullable=True)
    end_day = db.Column(db.SmallInteger, nullable=True)

    @hybrid_property
    def start_date(self):
        if self.start_year and self.start_month:
            return datetime.date(year=self.start_year, month=self.start_month, day=(self.start_day or 1))
        elif self.start_year:
            return datetime.date(year=self.start_year, month=1, day=(self.start_day or 1))
        return None

    @start_date.expression
    def start_date(cls):
        from sqlalchemy import func, and_
        from sqlalchemy.sql import case
        return case([
            (and_(cls.start_year != None, cls.start_month != None, cls.start_day != None), func.make_date(cls.start_year, cls.start_month, cls.start_day)),
            (and_(cls.start_year != None, cls.start_month != None), func.make_date(cls.start_year, cls.start_month, 1)),
            (cls.start_year != None, func.make_date(cls.start_year, 1, 1)),
        ], else_ = None)

db.drop_all()
db.create_all()
galshow1 = GalleryShowOnSite()
galshow1.start_year = 2015
galshow1.start_month = 2
galshow1.start_day = 20
galshow1.end_year = 2018
galshow1.end_month = 2
galshow1.end_day = 20
db.session.add(galshow1)
db.session.commit()

my_galshow1 = GalleryShowOnSite.query.first()
print(my_galshow1.start_date)
my_galshow2 = GalleryShowOnSite.query.filter(GalleryShowOnSite.start_date == '2015-02-20').first()
print(my_galshow2.start_date)