weird behavior of connection.cursor.execute()

hi all

the 2 methods below have no errors but are different in a
way that the
first one doesn't give me the resultset in the order that I
want while
the second method give me the resultset in the right order
that I
want.

first method

def sales(self, year=datetime.today().year,
order_by='Customer'):
        cursor = connection.cursor()
        cursor.execute("""
            select c.id, c.name as Customer,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 1 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as January,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 2 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as  February,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 3 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as March,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 4 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as April,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 5 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as May,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 6 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as June,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 7 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as July,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 8 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as August,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 9 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as September,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 10 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as October,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 11 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as November,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 12 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as December
            from ap_customer c
            order by %s desc
            """, [year]*12 + [order_by])
        return cursor.fetchall()

second method

def sales(self, year=datetime.today().year,
order_by='Customer'):
        sql = """
            select c.id, c.name as Customer,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 1 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as January,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 2 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as  February,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 3 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as March,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 4 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as April,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 5 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as May,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 6 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as June,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 7 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as July,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 8 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as August,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 9 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as September,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 10 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as October,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 11 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as November,
                (select sum(i.amount) from ap_invoice i
where
Month(i.date) = 12 and Year(i.date) = %s and i.customer_id =
c.id and
i.status != 'CAN') as December
            from ap_customer c
            order by %s desc
            """ % (year, year, year, year,
year, year, year, year,
year ,year, year, year, order_by)
        cursor = connection.cursor()
        cursor.execute(sql)
        return cursor.fetchall()

both method return the same connection.queries, and the
weirdest part
is that while the first method doesn't work in my personal
pc, but
it's work on my client workstation. both has the same
python, mysql,
and pymysql version ...

thanks
james


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

anyone who has opinion on this?

thanks
james

On Sep 29, 10:01 am, james_027 <cai.hai...gmail.com> wrote:
> hi all
>
> the 2 methods below have no errors but are different in
a way that the
> first one doesn't give me the resultset in the order
that I want while
> the second method give me the resultset in the right
order that I
> want.
>
> first method
>
> def sales(self, year=datetime.today().year,
order_by='Customer'):
>         cursor = connection.cursor()
>         cursor.execute("""
>             select c.id, c.name as Customer,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 1 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as January,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 2 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as  February,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 3 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as March,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 4 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as April,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 5 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as May,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 6 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as June,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 7 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as July,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 8 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as August,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 9 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as September,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 10 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as October,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 11 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as November,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 12 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as December
>             from ap_customer c
>             order by %s desc
>             """, [year]*12 +
[order_by])
>         return cursor.fetchall()
>
> second method
>
> def sales(self, year=datetime.today().year,
order_by='Customer'):
>         sql = """
>             select c.id, c.name as Customer,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 1 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as January,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 2 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as  February,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 3 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as March,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 4 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as April,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 5 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as May,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 6 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as June,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 7 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as July,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 8 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as August,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 9 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as September,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 10 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as October,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 11 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as November,
>                 (select sum(i.amount) from ap_invoice i
where
> Month(i.date) = 12 and Year(i.date) = %s and
i.customer_id = c.id and
> i.status != 'CAN') as December
>             from ap_customer c
>             order by %s desc
>             """ % (year, year, year,
year, year, year, year, year,
> year ,year, year, year, order_by)
>         cursor = connection.cursor()
>         cursor.execute(sql)
>         return cursor.fetchall()
>
> both method return the same connection.queries, and the
weirdest part
> is that while the first method doesn't work in my
personal pc, but
> it's work on my client workstation. both has the same
python, mysql,
> and pymysql version ...
>
> thanks
> james


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---