Script Error SQL

Traceback (most recent call last):
File “/home/frappe/frappe-bench/apps/frappe/frappe/app.py”, line 57, 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 935, in call
return fn(*args, **newargs)
File “/home/frappe/frappe-bench/apps/advocate/advocate/advocate/doctype/supervisors/supervisors.py”, line 18, in get_sup_first
sup_first = frappe.db.sql(sql_query, as_dict=True)
File “/home/frappe/frappe-bench/apps/frappe/frappe/database.py”, line 163, in sql
self._cursor.execute(query)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py”, line 250, in execute
self.errorhandler(self, exc, value)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py”, line 50, in defaulterrorhandler
raise errorvalue
ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '‘tabSupervisors’ WHERE \n advocate = ‘test7’\n ORDER BY ‘first_nam’ at line 1”)

Code

frappe.ui.form.on(‘Advocate’, {
refresh: function(frm) {
frappe.call({
method: ‘advocate.advocate.doctype.supervisors.supervisors.get_sup_first’,
args: {
‘advocate’: frm.doc.name
},
callback: function(r) {
if (r.message) {
frm.set_value(‘supervisor_first_name’, r.message.sup_first[0]);
}
}
});
}
});

check the order by field setting in your doctype or customize form, it should be first_name instead of first_nam?

@frappe.whitelist()
def get_sup_first(advocate):
sql_query= “”“SELECT ‘first_name’ FROM ‘tabSupervisors’ WHERE
advocate = ‘{0}’
ORDER BY ‘first_name’ DESC
LIMIT 1"”".format(advocate)
sup_first = frappe.db.sql(sql_query, as_dict=True)
if sup_first:
return { ‘sup_first’: sup_first[0].first_name }
else:
return { ‘sup_first’: ‘None’ }

i posted the code in the .py file

but from the above extract of the trace, the incorrect order by field name caused the error, your python seems ok, maybe you can do bench restart, then run the program again?

This code is working, check the double quotation

I rewrote the code thinking that it might be a quote still having an error also took out the order by and limit.

please share screenshot for py code because of double quotation in this site

Check your backticks and single quotes. In the screenshot and in the original query they don’t look right.

from tabSuperviosors

its ` not ’

best regards,

@Dan_Powell This is your solution

thank you that worked!