如何在Django中使用自定义sql在两个日期之间选择数据？

Question

视图.py

st_date, end_date = week_magic(date.today())
cur = connection.cursor()
cur.execute("select myapp_deal.*,CONCAT(myapp_contacts1.first_name,myapp_contacts1.last_name) as full_name from myapp_deal LEFT JOIN myapp_contacts1 on myapp_contacts1.id = myapp_deal.contact_id where myapp_deal.closed_date BETWEEN" '%s' "and" '%s',[st_date] [end_date])
row2=dictfetchall(cur)
cur.close()
data_json = json.dumps(row2, datetime.datetime)

st_date= 2016-01-18和{}。在我执行代码时，发生了以下错误。在

"list indices must be integers, not datetime.date".

如何使用自定义sql在两个日期之间选择数据？在

Answer 1

A有PostgresQL和django 1.7的脏解决方案：

from django.db.models import query

DATE_COMPARE_TEMPLATE = '("{0}" AT TIME ZONE \'{1}\')::date {2} \'{3}\'::date'


def offset_to_string(timezone_offset_m):
    return '{sign}{hours}:{minutes}'.format(**{
        # note invesred signs: '-' for positive, '+' for negative
        # Postgres uses this notation to calculate offset
        'sign': '-' if timezone_offset_m > 0 else '+',
        'hours': '{:0>2}'.format(abs(int(timezone_offset_m)) // 60),
        'minutes': '{:0>2}'.format(abs(int(timezone_offset_m)) % 60),
    })


class PeriodQuerySet(query.QuerySet):

    def filter_date(self, field, compare, value, timezone_offset_m=0):
        timezone = offset_to_string(timezone_offset_m)
        query = DATE_COMPARE_TEMPLATE.format(field,
                                             timezone,
                                             compare,
                                             value.isoformat())
        qs = self.extra(where=[query])
        return qs

和用法（对于附加了queryset的模型）：

^{pr2}$

如果查询包含两个表，而这两个表都有“start\u time”列，则可能会出现名称冲突。在

Answer 2

试试这个

cur.execute("select myapp_deal.*,CONCAT(myapp_contacts1.first_name,myapp_contacts1.last_name) as full_name from myapp_deal LEFT JOIN myapp_contacts1 on myapp_contacts1.id = myapp_deal.contact_id where myapp_deal.closed_date BETWEEN" '%s' "and" '%s',(st_date,end_date))