Frappe Server ScriptingPython

How to Build a Dynamic SQL WHERE Clause in a Frappe Server Script

Using Where Clause in SQL

Learn how to create flexible server-side APIs in Frappe by dynamically constructing SQL WHERE clauses based on user input. This snippet uses frappe.whitelist and frappe.db.sql.

In Frappe and ERPNext development, it's often necessary to fetch data based on multiple, optional filters provided by the user. Hardcoding every possible filter combination is inefficient. A more robust approach is to dynamically build the SQL `WHERE` clause in a server script.

This code demonstrates a common pattern for creating a whitelisted Python method that constructs a `WHERE` clause by appending conditions only if the corresponding filter values are provided. This allows for creating flexible and powerful server-side APIs for your custom applications.

<strong>Important Security Note:</strong> This method of string formatting to build SQL queries is highly vulnerable to SQL Injection. The example is provided for educational purposes to understand the logic, but for production, you MUST use parameterized queries as explained in the Troubleshooting section.

python
1@frappe.whitelist()
2def get_purchase(item, broker_name, supplier_name):
3	
4	where_clause = ''
5	where_clause += item and " and items = '%s'" % 		item.replace("'", "\'") or ""
6	where_clause += broker_name and " and broker_name = '%s'" % 		broker_name.replace("'", "\'") or ""
7	where_clause += supplier_name and " and supplier_name = '%s'" % 		supplier_name.replace("'", "\'") or ""
8			
9	return frappe.db.sql("""select name, items, date, lorry_no, bags, rate, gross_wt, net_wt, moisture, amount, tax_amount, after_tax_amount, after_commission_amount from `tabShivshakti Purchases` where status="Pending"
10		%s""" % where_clause, as_dict=1)

Understanding This Code

What It Does

This server script defines a whitelisted function that dynamically constructs a SQL WHERE clause to filter records from a custom DocType based on optional parameters.

When To Use

Use this pattern when creating server-side APIs for reports or client scripts that require filtering on a variable number of optional fields.

Prerequisites

  • •A custom Frappe app
  • •A DocType (e.g., `Shivshakti Purchases`) with the required fields.

Key Concepts

Important ideas to understand in this code

@frappe.whitelist()

A decorator that exposes a Python function as a public API endpoint. This allows it to be called from the client-side (e.g., using `frappe.call`) without the user needing special permissions, although standard DocType permission checks still apply.

Learn more

frappe.db.sql()

The primary method for executing raw SQL queries in Frappe. It's powerful but must be used with caution to avoid SQL injection vulnerabilities. Always use the `values` parameter to pass user input safely.

Learn more

Dynamic Query Building

The practice of constructing a query string programmatically based on variable inputs. While flexible, it requires careful handling of user input to prevent security risks like SQL injection. The safest method is to build a list of conditions and use parameterized queries.

Learn more

Step-by-Step Tutorial

Follow along to understand how this code works

1

Set Up the Server Script

In your custom Frappe app, create a new Python file or open an existing one for your API logic (e.g., `my_app/api.py`). Use the `@frappe.whitelist()` decorator to expose your function.

python
import frappe

@frappe.whitelist()
def get_purchase(item, broker_name, supplier_name):
    # Function logic will go here
Next Step
2

Initialize a WHERE Clause String

Create an empty string. This string will be appended with SQL conditions based on the function's arguments.

python
where_clause = ''
Next Step
3

Conditionally Append Filters

For each potential filter, check if an argument was provided. If it exists, append the corresponding SQL condition to the `where_clause` string. Note the leading 'and' which is necessary for chaining conditions.

python
where_clause += item and " and items = '%s'" % item.replace("'", "\'") or ""
where_clause += broker_name and " and broker_name = '%s'" % broker_name.replace("'", "\'") or ""
where_clause += supplier_name and " and supplier_name = '%s'" % supplier_name.replace("'", "\'") or ""
Next Step
4

Execute the Full Query

Construct the final SQL query by combining the base select statement with your dynamic `where_clause`. Use `frappe.db.sql` to execute it and return the data `as_dict=1`.

python
return frappe.db.sql("""SELECT name, items, date 
FROM `tabShivshakti Purchases` 
WHERE status="Pending" %s""" % where_clause, as_dict=1)
Next Step
5

Call from a Client Script (Example)

You can now call this server script from the client-side using `frappe.call`. Pass your filter values in the `args` dictionary.

javascript
frappe.call({
    method: 'my_app.api.get_purchase',
    args: {
        item: 'Raw Cotton',
        broker_name: '', // Leave empty to ignore filter
        supplier_name: 'Example Supplier'
    },
    callback: function(response) {
        if (response.message) {
            console.log(response.message);
        }
    }
});

Common Issues & Solutions

Troubleshoot problems you might encounter