+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: pymysql ifnull (/thread-12643.html)
pymysql ifnull - aland - Sep-05-2018
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]]
RE: pymysql ifnull - buran - Sep-05-2018
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-python/en/preface.html
RE: pymysql ifnull - aland - Sep-05-2018
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'))RE: pymysql ifnull - aland - Sep-06-2018
Found the answer - I should have group by categories.desc - this fixed the problem
RE: pymysql ifnull - buran - Sep-06-2018
(Sep-06-2018, 01:01 AM)aland Wrote: I should have group by categories.desc - this fixed the problemThat is strange as you said original one works with mysql ad workbench...