python接触的不多,最近在写一个查询接口的时候,需要用到日期格式化的函数DATE_FORMAT,我把sql写好之后,发现sqlalchemy好像不支持这些数据库函数?也可能是我不太了解吧,我的sql是这样的
select DATE_FORMAT(start_at, '%Y-%m-%d') date, count(id) count from alarm
WHERE 1=1
AND alarm.start_at >= '2021-08-05 00:59:59'
AND alarm.start_at <= '2021-08-05 20:00:00'
GROUP BY DATE_FORMAT(start_at, '%Y-%m-%d')
ORDER BY start_at desc
可以看到我需要在select的字段以及group by字段上都要用到DATE_FORMAT函数,所以最终在尝试之下选择了一个比较折中的写法,代码如下:
@sql_error_wrapper
class AlarmStatistics(Base):
def get_count_group_and_order(self, column, filters=None):
session = db.get_session()
with transaction(session):
query = session.query("DATE_FORMAT(" + column + ", '%Y-%m-%d')",
sqlalchemy.func.count(self.model.alarm_id))\
.group_by("DATE_FORMAT(" + column +
", '%Y-%m-%d')")
query = db_utils.make_conditions_from_filters(
self.model, query, filters)
query = query.order_by(self.model.start_at.desc())
resp = query.all()
result = dict(resp)
return result
@staticmethod
def get_model_resource_statistics():
return AlarmStatistics(db_models.Alarm,
db_models.Alarm.alarm_id)
class Alarm(Base, MEAOBase):
__tablename__ = 'alarm'
__table_args__ = (
sqlalchemy.UniqueConstraint("alarm_id",
name="uniq_alarm0alarm_id"),
COMMON_TABLES_ARGS
)
id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
alarm_id = sqlalchemy.Column(sqlalchemy.String(48), nullable=False)
alarm_name = sqlalchemy.Column(sqlalchemy.String(255), nullable=True)
调用的时候,传入需要group by的字段就可以了
当然这里只是拿DATE_FORMAT函数举例,那么对应的其他mysql函数应该可以这样去实现,举一反三我就不赘述了。