pymysql ifnull

[aland]

I am using IFNULL in a select statement with a group by & rollup . In the mysql work bench it works perfectly but when I use it in python-pymysql in stead of replacing the null with my literal it repeats the last value from the last row.

The code is :-

sql = ('SELECT IFNULL(categories.desc,"Total") as Category, YEAR(trans_dat), sum(transactions.amount) '
           'FROM transactions '
           'inner join vendor on transactions.acct_no = vendor.acct_no '
           'inner join categories on vendor.cat_1 = categories.cat_no '
           'group by cat_1 with rollup ')

With mysql I get :-

Car
Comms
Entertainment
Food
Health
House Hold
Misc
Payments
Personal
School
Transport
Travel
Total

With python - pymysql I get :-

[['Food', '-31163.28', 0, 0, 0], ['Car', '-29888.81', 0, 0, 0], ['Entertainment', '-10250.68', 0, 0, 0], ['Health', '-22569.32', 0, 0, 0], ['Transport', '-4065.60', 0, 0, 0], ['Comms', '-4367.56', 0, 0, 0], ['School', '-3073.55', 0, 0, 0], ['House Hold', '-29560.66', 0, 0, 0], ['Travel', '-976.04', 0, 0, 0], ['Personal', '-152201.22', 0, 0, 0], ['Payments', '251313.75', 0, 0, 0], ['Misc', '-2319.53', 0, 0, 0], ['Misc', '-39122.50', 0, 0, 0]]

[buran]

can you show your full code. Cursor would return tuples, not lists. That is why I guess the output is what you print, i.e. it may be problem with your output, not what is returned from the database.
Also, you may want to try using the offical mysql connector - https://dev.mysql.com/doc/connector-pyth...eface.html

[aland]

This is the section of code (Note: this is only my second attempt at writing a program):-

su_stat = []
indx = 0

sql = ('SELECT IFNULL(categories.desc,"Total") as Category, YEAR(trans_dat), sum(transactions.amount) '

       'FROM transactions '
       'inner join vendor on transactions.acct_no = vendor.acct_no '
       'inner join categories on vendor.cat_1 = categories.cat_no '
       'group by cat_1 with rollup ')

cursor.execute(sql)
su_row = cursor.fetchall()
print(su_row)
for row in su_row:
    print(row)
    su_stat.append(row)


for row in su_stat:
    print(row)
    my_c1 = row[0]
    my_amt = str(row[2])

    sudat[indx][0] = my_c1
    sudat[indx][1] = my_amt

    indx = indx + 1
    print(sudat)
    print(indx)

connection.close()

The individual rows are:-

Output:
('Food', 2018, Decimal('-31163.28'))
('Car', 2018, Decimal('-29888.81'))
('Entertainment', 2018, Decimal('-10250.68'))
('Health', 2018, Decimal('-22569.32'))
('Transport', 2018, Decimal('-4065.60'))
('Comms', 2018, Decimal('-4367.56'))
('School', 2017, Decimal('-3073.55'))
('House Hold', 2016, Decimal('-29560.66'))
('Travel', 2018, Decimal('-976.04'))
('Personal', 2018, Decimal('-152201.22'))
('Payments', 2016, Decimal('251313.75'))
('Misc', 2018, Decimal('-2319.53'))
('Misc', 2018, Decimal('-39122.50'))

[aland]

Found the answer - I should have group by categories.desc - this fixed the problem

[buran]

I should have group by categories.desc - this fixed the problem

That is strange as you said original one works with mysql ad workbench...