SA：我可以有“一年”吗？日期列的column_property？

发布于 2024-10-29 09:20:11 字数 470 浏览 1 评论 0原文

我在 sqlalchemy-0.7 中有一个带有日期列的类。我可以有一个 column_property或者任何类似的东西可以让我知道年份并让我轻松过滤它？我该怎么写呢？

IE，我想要（声明性语法）：

class Foo(Base):
    id   = Column(Integer, primary_key=True)
    date = Column(Date(), nullable=False)
    year = column_property(something here)

# later on
q = session().query(Foo).filter_by(year=2011)

原文

I have a class with a Date column in sqlalchemy-0.7. Can I have a column_property or anything similar that gets me the year and lets me easily filter by it? How would I write it?

IE, I'd like to have (declarative syntax):

class Foo(Base):
    id   = Column(Integer, primary_key=True)
    date = Column(Date(), nullable=False)
    year = column_property(something here)

# later on
q = session().query(Foo).filter_by(year=2011)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

江心雾 2024-11-05 09:20:11

当然你可以定义这样的属性：

year = column_property(extract('year', date))

但是你真的需要它吗？您可以按年份进行过滤，而无需通过重写过滤条件来定义此类属性：

query(Foo).filter(extract('year', Foo.date)==2011)

更新

虽然此解决方案看起来很简单，但也有一个缺点：WHERE 子句中的此类条件永远不会在日期字段上使用索引。具有大量行和条件选择性高，这将对性能产生很大影响。因此，您可能想要重写导致范围索引扫描而不是全表扫描的条件（如西蒙的评论中所建议的）：

start = datetime.date(year, 1, 1)
end = datetime.date(year, 12, 31)
query(Foo).filter(Foo.date.between(start, end))

定义具有这种行为的属性也是可能的，您只需重新定义比较器：

class YearComparator(ColumnProperty.Comparator):
    def __eq__(self, year):
        if isinstance(year, int):
            column = self.prop.columns[0].get_children()[0].expr
            start = datetime.date(year, 1, 1)
            end = datetime.date(year, 12, 31)
            return column.between(start, end)
        else:
            # It can be a column or exression which we can't handle such way
            return ColumnProperty.Comparator.__eq__(self, year)
    # __lt__, __gt__ etc. are very similar to __eq__

def year_property(date_column, **kwargs):
    kwargs.setdefault('comparator_factory', YearComparator)
    return column_property(extract('year', date_column), **kwargs)

class Foo(Base):
    __tablename__ = 'Foo'
    id = Column(Integer, primary_key=True)
    date = Column(Date, index=True)
    year = year_property(date)

Sure you can define such property:

year = column_property(extract('year', date))

But do you realy need it? You can filter by year without such property defined by rewriting filter condition:

query(Foo).filter(extract('year', Foo.date)==2011)

Update

Although this solution looks simple it also have a drawback: such condition in WHERE clause will never use index on date field. Having a lot of rows and high selecivity of condition this will cause high impact on perfomance. So you may want to rewrite condition that will cause RANGE INDEX SCAN instead of FULL TABLE SCAN (as suggested in Simon's comment):

start = datetime.date(year, 1, 1)
end = datetime.date(year, 12, 31)
query(Foo).filter(Foo.date.between(start, end))

Defining a property with such behavior is possible too, you just have to redefine comparator:

class YearComparator(ColumnProperty.Comparator):
    def __eq__(self, year):
        if isinstance(year, int):
            column = self.prop.columns[0].get_children()[0].expr
            start = datetime.date(year, 1, 1)
            end = datetime.date(year, 12, 31)
            return column.between(start, end)
        else:
            # It can be a column or exression which we can't handle such way
            return ColumnProperty.Comparator.__eq__(self, year)
    # __lt__, __gt__ etc. are very similar to __eq__

def year_property(date_column, **kwargs):
    kwargs.setdefault('comparator_factory', YearComparator)
    return column_property(extract('year', date_column), **kwargs)

class Foo(Base):
    __tablename__ = 'Foo'
    id = Column(Integer, primary_key=True)
    date = Column(Date, index=True)
    year = year_property(date)

回复收藏 0 原文

~没有更多了~