sql_custom.py

from django.db import models, connection


class Assinantes(models.Model):
    id = models.AutoField(db_column='ID', primary_key=True)  # Field name made lowercase.
    msisdn = models.BigIntegerField(unique=True, blank=True, null=True)
    # faixaid = models.ForeignKey('Faixa', models.DO_NOTHING, db_column='faixaID', blank=True, null=True)  # Field name made lowercase.
    faixaid = models.IntegerField(db_column='faixaID', blank=True, null=True)  # Field name made lowercase.
    docnum = models.CharField(db_column='docNum', max_length=28, blank=True, null=True)  # Field name made lowercase.
    offeramount = models.DecimalField(db_column='offerAmount', max_digits=7, decimal_places=2, blank=True, null=True)  # Field name made lowercase.
    originalamount = models.DecimalField(db_column='originalAmount', max_digits=7, decimal_places=2, blank=True, null=True)  # Field name made lowercase.
    offerduedate = models.DateTimeField(db_column='offerDueDate')  # Field name made lowercase.
    status = models.IntegerField(blank=True, null=True)
    tipoassinante = models.CharField(max_length=20, blank=True, null=True)
    datainsert = models.DateTimeField()
    dataupdate = models.DateTimeField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'assinantes'

    def custom_sql():
        from django.db import connection
        results = []
        cursor = connection.cursor()
        sql = "SELECT CASE WHEN `offerDueDate` >= CURDATE() THEN FLOOR(`offerAmount` + 0.01 ) ELSE FLOOR(`originalAmount` + 0.01 )END AS pontos, count(*) AS `qtd_clientes` FROM `assinantes` WHERE `status` IN(1, 2) Group BY pontos;"
        cursor.execute(sql)
        columns = ['pontos', 'qtd_clientes']
        for row in cursor.fetchall():
          results.append(dict(zip(columns, row)))
        return results


>>> Assinantes.custom_sql()
[{'pontos': None, 'qtd_clientes': 16}, {'pontos': 1, 'qtd_clientes': 1}, {'pontos': 10, 'qtd_clientes': 1}, {'pontos': 70, 'qtd_clientes': 2}, {'pontos': 87, 'qtd_clientes': 1}, {'pontos': 95, 'qtd_clientes': 1}, {'pontos': 135, 'qtd_clientes': 6}, {'pontos': 150, 'qtd_clientes': 22}, {'pontos': 300, 'qtd_clientes': 1}, {'pontos': 350, 'qtd_clientes': 3}]