Aakvatech Limited - Building Frappe Script Reports with ChatGPT

🚀 The Promise

You ask ChatGPT:

“Create a Frappe script report with dynamic monthly columns…”

It responds with clean, modular Python:

def execute(filters=None):
    ...
    return columns, data

Looks perfect.

Then you paste it into a Report Script field.

And everything breaks.

---

🧨 The Reality: safe_eval is not Python

Frappe’s non-standard Script Reports (stored in DB) run through:

safe_exec(...)

This is not normal Python execution. It’s a sandbox with heavy restrictions.

❌ What breaks immediately

• def execute(...) → functions may not run
• import ... → blocked
• frappe._dict → blocked (_ attributes forbidden)
• .format(...) → unsafe attribute
• strftime(...) → triggers __import__ → 💥
• x[key] += value → augmented assignment blocked

---

🧠 Lesson #1: You’re Writing “Restricted Python”

The correct mental model is:

“This is not Python — this is a constrained scripting DSL that looks like Python.”

So instead of:

def execute(filters):
    ...

You must write:

filters = filters or {}
columns = [...]
result = data

Top-level only.

---

⚠️ Lesson #2: Output Format is Inconsistent (and undocumented)

This is where most confusion happens.

You’ll see messages like:

“Send output as result = [result]”

or

“data = [columns], [result]”

These are misleading unless you understand which execution path you're in.

✅ What actually works (Frappe v15 safe_exec)

columns = [...]
result = data

❌ What does NOT work

result = [columns, data]     # breaks total row logic
data = columns, data         # ignored
return columns, data         # not executed

If you get this wrong, you’ll hit:

IndexError: list index out of range

inside add_total_row() — not helpful.

---

🧨 Lesson #3: Even harmless Python can break

Example:

current.strftime("%Y-%m")

This caused:

KeyError: '__import__'

Why?

Because internally, strftime may rely on Python’s import system — which is blocked.

✅ Fix: Build strings manually

if month < 10:
    key = str(year) + "-0" + str(month)
else:
    key = str(year) + "-" + str(month)

---

🧨 Lesson #4: .format() is forbidden

This fails:

query = "... where {conditions}".format(...)

Error:

SyntaxError: format is an unsafe attribute

✅ Fix: Use concatenation

where_clause = " and ".join(conditions)

query = """
    select ...
    where """ + where_clause + """
"""

---

🧠 Lesson #5: Defensive coding is mandatory

Frappe will happily crash with cryptic errors if your output is malformed.

Example failure:

IndexError: list index out of range

✅ Fix: Validate before returning

if not columns:
    frappe.throw("No columns generated")

if not data:
    frappe.throw("No data found for selected filters")

---

🤖 Lesson #6: ChatGPT needs constraints

If you don’t guide ChatGPT, it will generate standard Python, not safe_eval-compatible code.

Use a structured prompt like this:

Create a Frappe non-standard Script Report (safe_eval).

Constraints:

• top-level code only
• no functions
• no imports
• no frappe._dict
• no .format()
• no strftime()
• no += on dict/list
• output: columns = [...] result = [...]

Requirements:

• dynamic month columns
• filters: fromdate, todate, employee
• safe SQL construction

This dramatically improves output quality.

---

🔁 Lesson #7: Multiple execution modes exist

Frappe has at least 3 ways to run reports:

Mode	Output Style
Standard Python report	return columns, data
Script Report (safe_exec)	columns, result
Custom override (exec)	behaves like Python

If you mix them up, nothing works.

---

🛠️ Advanced: Bypassing safe_eval entirely

You can override Report execution like this:

exec(script_python, module_namespace, module_namespace)

Then use:

def execute(filters):
    return columns, data

This gives you full Python — but also full responsibility.

---

🧩 Final Takeaways

🔑 Key rules

• Write top-level code only
• Avoid restricted attributes and methods
• Define:

  columns = [...]
  result = [...]
  

• Never pack columns into result
• Avoid .format() and strftime()
• Always validate output

---

💬 Closing Thought

ChatGPT is incredibly powerful for generating Frappe reports — but only if you understand the execution model.

Without that, you end up in a loop of:

generate → paste → error → patch → repeat

With the right constraints, though, it becomes:

prompt → paste → done

---