Error creating script report

Abderrahim · February 15, 2017, 11:15pm

can any one help me with this error:

Traceback (most recent call last):
File “/home/frappe/frappe-bench/apps/frappe/frappe/app.py”, line 55, in application
response = frappe.handler.handle()
File “/home/frappe/frappe-bench/apps/frappe/frappe/handler.py”, line 19, in handle
execute_cmd(cmd)
File “/home/frappe/frappe-bench/apps/frappe/frappe/handler.py”, line 40, in execute_cmd
ret = frappe.call(method, **frappe.form_dict)
File “/home/frappe/frappe-bench/apps/frappe/frappe/init.py”, line 897, in call
return fn(*args, **newargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/desk/query_report.py”, line 93, in run
res = frappe.get_attr(method_name)(frappe._dict(filters))
File “/home/frappe/frappe-bench/apps/sdxenergy_management/sdxenergy_management/sdxenergy_management/report/whp_graph/whp_graph.py”, line 16, in execute
group by well order by creation_date desc’‘’, (filters.from_date, filters.to_date))
File “/home/frappe/frappe-bench/apps/frappe/frappe/database.py”, line 137, in sql
self._cursor.execute(query, values)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py”, line 187, in execute
query = query % tuple([db.literal(item) for item in args])
TypeError: not all arguments converted during string formatting

netchampfaris · February 16, 2017, 6:57am

Can you post your sql query?

SOLOSOFT · February 16, 2017, 7:29am

data = frappe.db.sql(‘’’ select date(date) as creation_date , well, whp from tabProduction where date(date) between %s and %s group by well order by creation_date desc ‘’', (filters.from_date, filters.to_date))

thank you @netchampfaris

SOLOSOFT · February 16, 2017, 10:14am

any help

Thiago_Henry · February 16, 2017, 10:38am

I don’t understand “date(date)” is DATE(field_date)? and if “date(date)” is DATE(field_date) won’t work because you convert field to date and “between %s and %s” is comparing two strings. I’m not sure it’s just a tip.

SOLOSOFT · February 16, 2017, 10:42am

actually it is a date field which have the fieldname date in the database.

Thiago_Henry · February 16, 2017, 10:43am

Try not use DATE() and put this after arguments as_dict=1 and debug=1. With debug=1 you see in your console your query executed, I guess that this will help you a lot.

SOLOSOFT · February 16, 2017, 10:50am

i get the same error
data = frappe.db.sql(‘’’ select date(date) as creation_date , well, whp from tabProduction where date between %s and %s group by well order by creation_date desc ‘’', (filters.from_date, filters.to_date),as_dict=1,debug=1)

Thiago_Henry · February 16, 2017, 10:52am

Show me the result that’s in your console. The query executed.

SOLOSOFT · February 16, 2017, 10:53am

{“exc”:“["Traceback (most recent call last):\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/app.py\", line 55, in application\n response = frappe.handler.handle()\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/handler.py\", line 19, in handle\n execute_cmd(cmd)\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/handler.py\", line 40, in execute_cmd\n ret = frappe.call(method, **frappe.form_dict)\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/init.py\", line 897, in call\n return fn(*args, **newargs)\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/desk/query_report.py\", line 93, in run\n res = frappe.get_attr(method_name)(frappe._dict(filters))\n File \"/home/frappe/frappe-bench/apps/sdxenergy_management/sdxenergy_management/sdxenergy_management/report/whp_graph/whp_graph.py\", line 16, in execute\n where date between %s and %s\n File \"/home/frappe/frappe-bench/apps/frappe/frappe/database.py\", line 137, in sql\n self._cursor.execute(query, values)\n File \"/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py\", line 187, in execute\n query = query % tuple([db.literal(item) for item in args])\nTypeError: not all arguments converted during string formatting\n"]”}

Thiago_Henry · February 16, 2017, 10:54am

Sorry, my bad the console of your browser.

SOLOSOFT · February 16, 2017, 10:57am

it’s actually what console gives me

SOLOSOFT · February 16, 2017, 11:00am

i deplicated the same code in the link:

github.com

frappe/erpnext/blob/master/erpnext/crm/report/minutes_to_first_response_for_opportunity/minutes_to_first_response_for_opportunity.js

// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
// For license information, please see license.txt

frappe.query_reports["Minutes to First Response for Opportunity"] = {
	"filters": [
		{
			"fieldname": "from_date",
			"label": __("From Date"),
			"fieldtype": "Date",
			'reqd': 1,
			"default": frappe.datetime.add_days(frappe.datetime.nowdate(), -30)
		},
		{
			"fieldname": "to_date",
			"label": __("To Date"),
			"fieldtype": "Date",
			'reqd': 1,
			"default": frappe.datetime.nowdate()
		},
	],

This file has been truncated. show original

Thiago_Henry · February 16, 2017, 11:04am

Ok but in the Console of your browser you can see your query executed. Change tab to Console you’re in Réseau and XHR in console you’ll see the result.

SOLOSOFT · February 16, 2017, 11:08am

shows me the same text i think the problem is with string conversion if you have any idea how to change %s to something else coz in the filters.from_date and filters.to_date in a date .

i replicate the same report as CRM mins to first respond opportunity.

Thiago_Henry · February 16, 2017, 11:21am

Well I’m sorry but in your console should show your query executed.
Anyway print this fields “filters.from_date”, “filters.to_date” before you execute your query and your type. This way you can see what you pass to %s.

SOLOSOFT · February 16, 2017, 11:32am

i used frappe.errprint(data) or frappe.msgprint(data) gives me the same error i am really stucked with this issue

Thiago_Henry · February 16, 2017, 11:54am

I don’t know how is your code then try this way
frappe.msgprint(filters.from_date) and frappe.msgprint(filters.to_date) else this way
frappe.msgprint(frappe.as_json(ilters.from_date)) and frappe.msgprint(frappe.as_json(filters.to_date))
before you execute your query.

nabinhait · February 16, 2017, 1:02pm

I think from_date and to_date value contains “%”, which is creating the problem.