如何在Django原生查询中正确使用PostgreSQL按年、月分组？

首次接触PostgreSQL项目，之前一直用sqlite3，两者语法存在差异。有一段在pgAdmin4中运行正常的查询语句：

SELECT auth_user.id, COUNT(coachregiszter_latogatoi_stat.id) AS count_id, DATE_TRUNC('year', datetime) AS year, DATE_TRUNC('month', datetime) AS month FROM auth_user LEFT JOIN coachregiszter_latogatoi_stat ON coachregiszter_latogatoi_stat.user_id = auth_user.id WHERE user_id=1 GROUP BY year, month, auth_user.id 

但把这段语句放到Django项目中用raw查询时，出现SyntaxError: invalid syntax错误。问题出在DATE_TRUNC('year', datetime) AS year, DATE_TRUNC('month', datetime) AS month部分的单引号上——Python字符串的外层单引号和SQL里的内层单引号冲突了，导致语法解析失败。

1. 用双引号包裹整个SQL语句 #

将外层的单引号替换为双引号，这样内部的单引号就不会触发语法错误：

ruser = request.user.id

coach_oldal_stat = Latogatoi_stat.objects.raw("SELECT auth_user.id, COUNT(coachregiszter_latogatoi_stat.id) AS count_id, DATE_TRUNC('year', datetime) AS year, DATE_TRUNC('month', datetime) AS month FROM auth_user LEFT JOIN coachregiszter_latogatoi_stat ON coachregiszter_latogatoi_stat.user_id = auth_user.id WHERE user_id=%s GROUP BY year, month, auth_user.id", [ruser])

2. 转义SQL中的单引号 #

在SQL内部的单引号前添加反斜杠\进行转义，让Python识别为字符串的一部分：

ruser = request.user.id

coach_oldal_stat = Latogatoi_stat.objects.raw('SELECT auth_user.id, COUNT(coachregiszter_latogatoi_stat.id) AS count_id, DATE_TRUNC(\'year\', datetime) AS year, DATE_TRUNC(\'month\', datetime) AS month FROM auth_user LEFT JOIN coachregiszter_latogatoi_stat ON coachregiszter_latogatoi_stat.user_id = auth_user.id WHERE user_id=%s GROUP BY year, month, auth_user.id', [ruser])

3. 使用Django ORM替代Raw查询（推荐） #

尽量避免直接手写SQL，用Django ORM实现相同逻辑，既安全又能自动适配不同数据库：

from django.db.models import Count, F
from django.db.models.functions import TruncYear, TruncMonth

ruser = request.user.id

coach_oldal_stat = Latogatoi_stat.objects.filter(user_id=ruser) \
    .annotate(
        year=TruncYear('datetime'),
        month=TruncMonth('datetime'),
        user_id=F('user__id')
    ) \
    .values('user_id', 'year', 'month') \
    .annotate(count_id=Count('id')) \
    .order_by('year', 'month')

这种写法无需处理引号冲突，还能兼容sqlite3（Django会自动将TruncYear/TruncMonth转换为对应数据库支持的函数）。

内容的提问来源于stack exchange，提问作者RlM