In Script Report getting error - TypeError: not all arguments converted during string formatting

1

Hello,

I am trying to build a Script Report. But at the statement

data = frappe.db.sql(""" SELECT `tabPurchase Receipt`.name, `tabPurchase Receipt`.supplier_name, `tabPurchase Receipt`.posting_date, `tabPurchase Receipt Item`.item_name, `tabPurchase Receipt Item`.received_qty, `tabPurchase Receipt Item`.rejected_qty, `tabPurchase Receipt Item`.warehouse, `tabPurchase Receipt Item`.purchase_order, `tabPurchase Receipt Item`.batch_no, `tabPurchase Receipt Item`.coil_heat_no, `tabPurchase Receipt Item`.quality_inspection, `tabPurchase Receipt Item`.coil_qty, `tabPurchase Receipt Item`.coil_wt
FROM `tabPurchase Receipt`, `tabPurchase Receipt Item` WHERE `tabPurchase Receipt Item`.parent = `tabPurchase Receipt`.name ; """, conditions, as_dict=1)

I am getting error. Here is the dump of Browser Console.

Traceback (most recent call last):
  File "apps/frappe/frappe/app.py", line 69, in application
    response = frappe.api.handle()
  File "apps/frappe/frappe/api.py", line 55, in handle
    return frappe.handler.handle()
  File "apps/frappe/frappe/handler.py", line 38, in handle
    data = execute_cmd(cmd)
  File "apps/frappe/frappe/handler.py", line 76, in execute_cmd
    return frappe.call(method, **frappe.form_dict)
  File "apps/frappe/frappe/__init__.py", line 1457, in call
    return fn(*args, **newargs)
  File "apps/frappe/frappe/__init__.py", line 767, in wrapper_fn
    retval = fn(*args, **get_newargs(fn, kwargs))
  File "apps/frappe/frappe/desk/query_report.py", line 256, in run
    result = generate_report_result(report, filters, user, custom_columns, is_tree, parent_field)
  File "apps/frappe/frappe/__init__.py", line 767, in wrapper_fn
    retval = fn(*args, **get_newargs(fn, kwargs))
  File "apps/frappe/frappe/desk/query_report.py", line 90, in generate_report_result
    res = get_report_result(report, filters) or []
  File "apps/frappe/frappe/desk/query_report.py", line 71, in get_report_result
    res = report.execute_script_report(filters)
  File "apps/frappe/frappe/core/doctype/report/report.py", line 148, in execute_script_report
    res = self.execute_module(filters)
  File "apps/frappe/frappe/core/doctype/report/report.py", line 165, in execute_module
    return frappe.get_attr(method_name)(frappe._dict(filters))
  File "apps/mymfg/mymfg/my_mfg/report/my_pruchase_receipt_items/my_pruchase_receipt_items.py", line 11, in execute
    cs_data = get_cs_data(filters)
  File "apps/mymfg/mymfg/my_mfg/report/my_pruchase_receipt_items/my_pruchase_receipt_items.py", line 125, in get_cs_data
    data = frappe.db.sql(""" SELECT `tabPurchase Receipt`.name, `tabPurchase Receipt`.supplier_name, `tabPurchase Receipt`.posting_date, `tabPurchase Receipt Item`.item_name, `tabPurchase Receipt Item`.received_qty, `tabPurchase Receipt Item`.rejected_qty, `tabPurchase Receipt Item`.warehouse, `tabPurchase Receipt Item`.purchase_order, `tabPurchase Receipt Item`.batch_no, `tabPurchase Receipt Item`.coil_heat_no, `tabPurchase Receipt Item`.quality_inspection, `tabPurchase Receipt Item`.coil_qty, `tabPurchase Receipt Item`.coil_wt
  File "apps/frappe/frappe/database/database.py", line 174, in sql
    self._cursor.execute(query, values)
  File "env/lib/python3.10/site-packages/pymysql/cursors.py", line 146, in execute
    query = self.mogrify(query, args)
  File "env/lib/python3.10/site-packages/pymysql/cursors.py", line 125, in mogrify
    query = query % self._escape_args(args, conn)
TypeError: not all arguments converted during string formatting

What mistake am I making and how to solve this error?

TIA

Yogi Yang

2

Hello,

While experimenting to solve the error I changed the code to this

data = frappe.db.sql(""" SELECT `tabPurchase Receipt`.name, `tabPurchase Receipt`.supplier_name, `tabPurchase Receipt`.posting_date, `tabPurchase Receipt Item`.item_name, `tabPurchase Receipt Item`.received_qty, `tabPurchase Receipt Item`.rejected_qty, `tabPurchase Receipt Item`.warehouse, `tabPurchase Receipt Item`.purchase_order, `tabPurchase Receipt Item`.batch_no, `tabPurchase Receipt Item`.coil_heat_no, `tabPurchase Receipt Item`.quality_inspection, `tabPurchase Receipt Item`.coil_qty, `tabPurchase Receipt Item`.coil_wt
FROM `tabPurchase Receipt`, `tabPurchase Receipt Item` WHERE `tabPurchase Receipt Item`.parent = `tabPurchase Receipt`.name; """, as_dict=1)

After this change the error seems to be solved but nothing is coming in the report.
In the query I am getting data from two separate tables. Would this be a problem?

TIA

Yogi Yang