[Dos and Don'ts #1] frappe.db.get_value

Frappe Framework
, ,
1

In an effort to increase the code quality of contributions to ERPNext and other frappe apps, I’ll start to post some examples of code I found and how it could be done better.

My code is of course not perfect. You’re welcome to comment suggestions on how it could be improved.

Don’t

def get_from_and_to_date(fiscal_year):
	from_and_to_date_tuple = frappe.db.sql("""select year_start_date, year_end_date
		from `tabFiscal Year` where name=%s""", (fiscal_year))[0]

	from_and_to_date = {
		"from_date": from_and_to_date_tuple[0],
		"to_date": from_and_to_date_tuple[1]
	}

	return from_and_to_date

Only use SQL queries if the world is about to end and you need to save it. Frappe provides convenient methods for data retrieval.

Source: frappe/erpnext

Do

def get_from_and_to_date(fiscal_year):
	fields = [
		"year_start_date as from_date",
		"year_end_date as to_date"
	]
	return frappe.db.get_value("Fiscal Year", fiscal_year, fields, as_dict=1)

Use frappe.db.get_value to retrieve specific fields of a doctype. With the field_name as custom_name syntax you can rename the keys as needed.

29 Likes
2

Great contribution and have to agree! People should be aware Frappe has put tons of hours to built a framework so we don’t have to write manual SQL just like you don’t go building all the Js code for a view, you shouldn’t go writing SQL queries on a whim.

This is nice content to have in the documentation!

1 Like
3

Thanks for taking the initiative!

There is plenty of code which can be improved in both Frappe and ERPNext and the community’s inputs are invaluable.

We are working internally to improve code quality too, so I’ll keep an eye on this thread and push these changes when I get time. :+1:

1 Like
4

fix:
https://github.com/frappe/erpnext/pull/24050

5

Avoid mutable default arguments. Example fixes:

2 Likes
6

Hi all,
May be not the right place but how to use or can be done:
Frappe.db.sql(“select name from tabSales Invoice where created = (select max(created) from Sales invoice)”)

By using
frappe.db.get_value ?

7

With the max(), you are leveraging the power if sql.
In this case, you’ll need more some python code with a frappe.get_all()
And yes, then frappe.db.sql() with embedded sql functions becomes more efficient.
Just my 2cts, definitely not an expert on the subject.

1 Like
8

For sure will search more on using frappe.get_all with max() and be part of the Dos not Don’ts.

Thanks

9
  • Use frappe.db.multisql if queries differ for postgres and mariadb.
  • In both functions pass values=tuple() separately instead of preparing the query as string.
2 Likes
10

Hi @Helio_Jesus, you can achieve your particular query like this:

most_recent_invoice = frappe.get_list('Sales Invoice', order_by='creation DESC', limit=1)
most_recent_invoice_name = most_recent_invoice[0].name
1 Like
11

Thanks for the tip.

12

@rmeyer, good one!! I didn’t think of it that way. It does work :wink:

Similarly, how would you find an average or a sum using the frappe.get_list() or frappe.get_all() commands. It seems it can be done with frappe.db.sql() command using the AVG, SUM, COUNT.

I am bringing this up as I am trying to generate some reports that bring up vizualisations on average.

Thanks.

13

You can use many SQL functions even within frappe’s wrapper functions (the dangerous ones are blacklisted).

fields = [
    "COUNT(*) as count",
    "AVG(grand_total) as avg",
    "SUM(grand_total) as sum"
]
frappe.get_list("Sales Invoice", fields=fields)
# [{'count': 10, 'avg': 100.0, 'sum': 1000.0}]]
4 Likes
14

@rmeyer , thank you so much !!!
Working in education, fields I was really looking forward to these xtra sql commands.
Where did you find documentation for these?

Btw: lived in Leipzig for 2 years in 2003-2005, and absolutely loved my time there.

1 Like
15

This was added recently Code Security Guidelines · frappe/erpnext Wiki · GitHub

4 Likes
16

Super helpful! thank you very much.

17

What about something such as SELECT UNIQUE…

frappe.db.sql("SELECT UNIQUE client FROM `tabTCM Appraisal` WHERE appraisee = '" + appraisee + "' ORDER BY client", as_list=1);

I get as far as

frappe.db.get_list("TCM Appraisal",{fields:["client"],filters:{"appraisee":appraisee},limit:50},order_by='client',unique=1);

but have no idea how to cater for the UNIQUE constraint. Since frappe.db.sql is not defined client-side, I’m currently returning the entire dataset and then remove duplicates in JS. It’ll be lovely to remove duplicates with the API call.

18

@EugeneP my examples were in Python, your code seems to be JavaScript. SQL queries shouldn’t be possible from the client side (in JavaScript) in the first place.

Also there’s no “SELECT UNIQUE” in MariaDB, so this wouldn’t work as well.

But you can try the parameter distinct=1 for frappe.get_list:

frappe.get_list("TCM Appraisal", filters={"appraisee": appraisee}, fields=["client"], distinct=1)
1 Like
19

Thanks @rmeyer

Client-side this works for me

options = frappe.db.get_list("TCM Appraisal", {fields: ["client"], filters: {"appraisee": appraisee}, distinct: 1, order_by: 'client'});