Pass Parameter in the SQL Statement

I want to pass patient parameters in the SQL statement , If I pass a value of a particular patient Id , I am getting the desired result, I am looking way to make it dynamic based on parameter . Thanks

Based on googled , there is simple way but I could not find the solution

def get_fav_doctors (**kwargs):
data = frappe._dict(kwargs)
patient= data.get(‘patient’)

favorite_doctor_list = frappe.db.sql(“select a.name,a.practitioner_name from tabHealthcare Practitioner as a left join tabMy Doctors as c on a.name=c.practitioner WHERE c.parent = patient”)
return favorite_doctor_list `

Parameters can be passed either as tuple (ordered parameters) or as dictionary (named parameters, preferred way to do it.)

named parameter example is present in docs:
https://frappeframework.com/docs/v13/user/en/api/database#frappedbsql

btw this isn’t frappe specific, it’s python DB-API spec: PEP 249 – Python Database API Specification v2.0 | peps.python.org

Thanks a lot @ankush , your link helped and we have working code

@frappe.whitelist(allow_guest=True)

def get_fav_doctors (**kwargs):

data = frappe._dict(kwargs)

patient= data.get('patient')

values = {'patient': patient}

try:

    favorite_doctor_list = frappe.db.sql("""select a.name,a.practitioner_name from `tabHealthcare Practitioner` as a left join `tabMy Doctors` as c on a.name=c.practitioner WHERE c.parent = %(patient)s""", values=values)

    return favorite_doctor_list

except Exception as e:

    frappe.log_error(frappe.get_traceback())

    gen_response(500,"Something Wrong. Please Try Again")

Make sure this is safe. allow_guest means anyone can query this endpoint without any permission checks at all.

1 Like

ya, its just testing, thx