Skip to content
LENS Docs

PRISM - Report

frappe.utils.getdate()

  • Converts a string like "2024-12-28" to a Python date object
  • Useful for standardizing date inputs before performing comparisons or calculations

Command Syntax

 frappe.utils.getdate(i_date_string)

Parameters & Options

ParameterTypeDescription
date_stringStringA valid date string (e.g. “2024-12-28”)

Common Patterns or Use Cases

  • Converting string input into a date object for safe comparison
l_date=frappe.utils.getdate("2025-05-12")

Sample Output :

(2025,05,12)

isocalendar()

  • Returns a tuple with the ISO year, ISO week number, and ISO weekday for a given date.
  • Useful for building week-based reports and ensuring proper calendar logic across years.

Prerequisite: A valid Python date object (e.g., from frappe.utils.getdate())

Command Syntax

l_date.isocalendar()

Return Value A tuple in the format:

(ISO year, ISO week number, ISO weekday)
IndexValueExampleDescription
[0]ISO year2024Usually same as calendar year (sometimes different in early Jan or late Dec)
[1]ISO week number19Week number in the year (1–53)
[2]ISO weekday7Day of the week (1 = Monday, …, 7 = Sunday)

Common Patterns or Use Cases

  • Getting ISO calendar values for a date:
l_date = frappe.utils.getdate("2024-05-12")
iso_parts = l_date.isocalendar()
  • Extracting just the ISO year:
iso_year = l_date.isocalendar()[0]  # → 2024
  • Extracting week number for reports:
week_num = l_date.isocalendar()[1]  # → 19

Sample Output

(2024, 19, 7)

Creating a List from List of Dictionaries

  • Extracts values for a specific field (key) from a list of Frappe document dictionaries
  • Commonly used after fetching data using frappe.db.get_all() or frappe.get_all()

Prerequisite :

# Get a list of Sales Invoices with their customer names
la_invoices = frappe.get_all("Sales Invoice", fields=["name", "customer"])

Command Syntax

[field_dict["field_name"] for field_dict in list_of_dicts]

Parameters & Options

ParameterTypeDescription
list_of_dictsList of DictsOutput from frappe.get_all() or any list of dicts
field_nameString (key)Field to extract from each dictionary

Common Patterns or Use Cases

  • Create a list of customer names from Sales Invoices
la_customer_list = [inv["i_customer"] for inv in ia_invoices]

Sample Output :

['John Grey', 'Bonnie', 'John Grey','Sara']

Removing Duplicates from List in Frappe

  • Use set() to remove duplicate entries from a list
  • Useful for getting distinct values like unique customers or items
  • Convert the set back to a list using list() for further use

Prerequisite :

# Assume this came from Sales Invoice records
la_customer_list = ['John Grey', 'Bonnie', 'John Grey','Sara']

Command Syntax

list(set(your_list))

Parameters & Options

ParameterTypeDescription
your_listListA list that may contain duplicates (e.g., customers)

Common Patterns or Use Cases

  • Get unique customer names from invoice list
la_unique_customers = list(set(la_customer_list))

Sample Output :

['John Grey', 'Bonnie','Sara']

Passing Parameters in frappe.db.sql()

  • Safe SQL in Frappe with Parameter Dicts
  • When using frappe.db.sql(), always pass parameters using a dictionary to prevents SQL injection, easier to read and lets you reuse queries dynamically

Prerequisite :

# Sample filter dictionary from a report
filters={
"from_date":"2024-10-23"
"to_date": "2025-10-26"}

Command Syntax

frappe.db.sql(query, parameters, as_dict=1)

Parameters & Options

ParameterTypeDescription
parametersDictKey-value pairs to safely insert into the query

Common Patterns or Use Cases

  • Pass dynamic values like date or item code
filters = {
    "from_date": "2024-01-01",
    "to_date": "2024-01-31"
}
result = frappe.db.sql(
    "SELECT name FROM `tabSales Order` WHERE transaction_date BETWEEN %(from_date)s AND %(to_date)s",
    filters
)

Sample Output :

[('SO-0001',), ('SO-0002',)]

as_dict

  • Use as_dict=1 to return SQL query results as dictionaries instead of tuples
  • Makes it easier to access fields by name (e.g., row["name"]) instead of by index
  • Improves readability and reduces bugs in report scripts

Prerequisite :

# A sample SQL query using frappe.db.sql
query = "SELECT name, status FROM `tabSales Order`"

Command Syntax

frappe.db.sql(query, parameters, as_dict=1)

Parameters & Options

ParameterTypeDescription
as_dictBooleanReturn results as dictionaries if True

Common Patterns or Use Cases

  • Retrieve results as dictionaries for easy key-based access
ld_result = frappe.db.sql(
    "SELECT name FROM `tabSales Order`",
    as_dict=1
)

Sample Output :

[{"name": "SO-0001"}, {"name": "SO-0002"}]

frappe.db.sql()

  • Use frappe.db.sql() when frappe.get_all() cannot handle advanced queries. frappe.get_all() is easy and safe, but it has limitations:
  • It doesn’t handle BETWEEN filters well.
  • You can’t write custom JOINs or complex logic.
  • You can’t use SQL-specific features like GROUP BY, ORDER BY, or subqueries.

Prerequisite :

# Sample filter dictionary from a report
filters = {
    "start": "2024-01-01",
    "end": "2024-01-31"
}

Command Syntax

frappe.db.sql(query, parameters, as_dict=1)

Parameters & Options

ParameterTypeDescription
queryStringYour SQL query string with placeholders
parametersDictKey-value pairs to safely insert into the query
as_dictBooleanReturn results as dictionaries if True

Common Patterns or Use Cases

  • Filter sales orders within a date range
ld_result = frappe.db.sql(
    "SELECT name FROM `tabSales Order` WHERE transaction_date BETWEEN %(from_date)s AND %(to_date)s",
    {"from_date": "2024-01-01", "to_date": "2024-01-31"},
    as_dict=1
)

Sample Output :

[
    {"name": "SO-0001"},
    {"name": "SO-0002"}
]