Error in script report if Add Total Row Enabled

ERPNext Reports
1

I am getting the following error in script report,

        Traceback (most recent call last):
          File "/home/frappe/frappe-bench/apps/frappe/frappe/app.py", line 62, in application
        response = frappe.handler.handle()
          File "/home/frappe/frappe-bench/apps/frappe/frappe/handler.py", line 22, in handle
        data = execute_cmd(cmd)
          File "/home/frappe/frappe-bench/apps/frappe/frappe/handler.py", line 53, in execute_cmd
        return frappe.call(method, **frappe.form_dict)
          File "/home/frappe/frappe-bench/apps/frappe/frappe/__init__.py", line 939, in call
        return fn(*args, **newargs)
          File "/home/frappe/frappe-bench/apps/frappe/frappe/desk/query_report.py", line 110, in run
        result = add_total_row(result, columns)
          File "/home/frappe/frappe-bench/apps/frappe/frappe/desk/query_report.py", line 231, in add_total_row
        result.append(total_row)
        AttributeError: 'tuple' object has no attribute 'append'

This is the code in the py file, it is working fine if I turn off add total row. If I turn it on, I am getting this error, only on the production server, it is working fine on my virtualbox development environment.

Production Server:
ERPNext: v10.1.45 (master)
Frappe Framework: v10.1.44 (master)

Development:
ERPNext: v11.x.x-develop (d305a7a) (develop)
Frappe Framework: v11.x.x-develop (544707d) (develop)

    def execute(filters=None):
    	if not filters.machine_number:
    		filters.machine_number = ""
    	if not filters.site:
    		filters.site = ""
    	sqlq = """select 
    			a.site,
    			a.creation,
    			a.machine_number,
    			a.previous_reading,
    			a.meter_reading,	
    			b.coin_count
    		from `tabCollection Entry` a right join `tabCollection Counting` b 
    		ON a.name = b.collection_entry
    		where a.machine_number like '%{}%'
    		and coalesce(a.site, '<NULL>') like '%{}%'
    		and a.creation BETWEEN '{}' AND '{}'
    		""".format(filters.machine_number,filters.site,filters.from_date,filters.to_date)
    	

    	columns = [
    		"Site:Link/Warehouse:100",
    		"Collected On :Date:150",
    		"Machine No.:Link/Asset:100",
    		"Previous Reading::100",
    		"Meter Reading::100",
    		"Counted Coins:Int:100"
    	]

    	data = frappe.db.sql(sqlq)
    	
    	return columns, data
2

Any help would be highly appreciated.

3

Seems query not returning any data. Thus data returning None as output

return columns, data

initialize data to empty list

data = []
4

if you wish to directly post sql query data to the report, try using as_list=1 as parameter like this:

this is your version

frappe.db.sql(sqlq)

try this

frappe.db.sql(sql, as_list=1)

5

It worked :slight_smile:
Just curious, what does the as_list argument do?
It was working fine on the development server but not on production.

6

From what I know, as_list prompts the function to get the sql results as a list of lists whereas as_dict gets the results as a list of dictionaries where each nested list or dictionary is one row from the SQL output.

Here’s an example where we will compare MySQL results with those given by frappe.db.sql when no parameters were specified or not specified :

MySQL

Here I am querying Sales Invoice (tabSales Invoice in the database), limiting it to 2 results for easy understanding:

This is one sample query in question that we will test:

MariaDB [1bd3e0294da19198]> SELECT 
    ->                   name, crf_no, posting_date, 
    ->                   net_total, total_taxes_and_charges, grand_total, 
    ->                   status 
    ->               FROM `tabSales Invoice` limit 2
    -> ;
+--------------+--------+--------------+-------------+-------------------------+-------------+---------+
| name         | crf_no | posting_date | net_total   | total_taxes_and_charges | grand_total | status  |
+--------------+--------+--------------+-------------+-------------------------+-------------+---------+
| INV-No-00003 | 215425 | 2018-02-22   |  932.203000 |              167.797000 | 1100.000000 | Paid    |
| INV-No-00004 | 215425 | 2018-02-22   | 3940.000000 |              709.200000 | 4649.200000 | Overdue |
+--------------+--------+--------------+-------------+-------------------------+-------------+---------+
2 rows in set (0.00 sec)

Now we will have a look at this same query but via frappe.db.sql. You can try the same in bench console and I encourage you to try this out.

Case I : No parameters specified

In [6]: print(frappe.db.sql("
              SELECT 
                  name, crf_no, posting_date, 
                  net_total, total_taxes_and_charges, grand_total, 
                  status 
              FROM `tabSales Invoice` limit 2"))

Out [6]:
((u'INV-No-00003', u'215425', datetime.date(2018, 2, 22), 932.203, 167.797, 1100.0, u'Paid'), 
(u'INV-No-00004', u'215425', datetime.date(2018, 2, 22), 3940.0, 709.2, 4649.2, u'Overdue'))

If you compare the result of function with that of the query, you see the function returns one row as a tuple of comma seperated value which is nested inside another tuple.

Case II : as_list = 1

In [7]: print(frappe.db.sql("
              SELECT 
                  name, crf_no, posting_date, 
                  net_total, total_taxes_and_charges, grand_total, 
                  status 
              FROM `tabSales Invoice` limit 2", as_list=1))
Out [7]:
[[u'INV-No-00003', u'215425', datetime.date(2018, 2, 22), 932.203, 167.797, 1100.0, u'Paid'], 
[u'INV-No-00004', u'215425', datetime.date(2018, 2, 22), 3940.0, 709.2, 4649.2, u'Overdue']]

Case III : as_dict = 1


In [8]: print(frappe.db.sql("
              SELECT 
                  name, crf_no, posting_date, 
                  net_total, total_taxes_and_charges, grand_total, 
                  status 
              FROM `tabSales Invoice` limit 2"as_dict=1))

Out [8]
[
{'status': u'Paid', 'grand_total': 1100.0, 'name': u'INV-No-00003', 'crf_no': u'215425', 'net_total': 932.203, 'total_taxes_and_charges': 167.797, 'posting_date': datetime.date(2018, 2, 22)}, 
{'status': u'Overdue', 'grand_total': 4649.2, 'name': u'INV-No-00004', 'crf_no': u'215425', 'net_total': 3940.0, 'total_taxes_and_charges': 709.2, 'posting_date': datetime.date(2018, 2, 22)}]

Here you get a dictionary of one row, with each field as you selected in query against the values that were fetched.

However, here you will have to append each result according to how you wish to see the final report.

Conclusion

So, all three have their own advantages, depending on how you work. Suppose you are focused on doing most of the work in the query itself, then as_list=1 will suit you well.

But obviously not everybody is a database wizard, so Python can come to rescue. You use as_dict=1 get each result as dictionary, do computations on individual results and then finally append them to the result list that will finally be returned.

I highly recommend reading code of existing ERPNext reports, as many such practices will be encountered there and you can adopt those techniques for your own script reports.

4 Likes
7

Why though ? Did you face any error because this will work irrespective of dev or production server.

8

No errors now, but what I don’t get is why it was working on development and not on production.
Was it because of the different versions of frappe?

9

Quite possible, have you checked if the versions were different ?

10

Yes, the versions are different

11

Is the code version of the script report on both machines the same ?

no errors in trace or browser console ? there must be some.

there could be another problem. I run erpnext v8 and this code runs totally fine. I think there is some other problem that is hampering the report. Debug that report is what I can say. If there a change in how frappe.db.sql work in the develop branch then I cannot say…