Frappe.get_value() with between-filter

Frappe Framework App Development
1

Hello Gentlemen,
I needed to execute this query:

SELECT distinct count (distinct holiday_date) from tabHoliday where holiday_date between '2022-01-01' AND '2022-01-03'

So I gave frappe.get_value() a try:

	min_date = datetime.date(2022, 1, 1)
	max_date = datetime.date(2022, 1, 3)
	holidays = frappe.get_value("Holiday", 
		filters={'holiday_date': ['between', [min_date, max_date]]}, 
		fieldname="count(distinct holiday_date)")

And was rewarded with this error:

pymysql.err.OperationalError: (4078, "Illegal parameter data types date and row for operation '='")

I solved this by using frappe.db.get_list() instead:

	min_date = datetime.date(2022, 1, 1)
	max_date = datetime.date(2022, 1, 3)
	holidays = frappe.db.get_list("Holiday", 
		filters=[ ['holiday_date', 'between', [min_date, max_date]] ], 
		fields="count(distinct holiday_date)")
	holidays = list(holidays[0].values())[0]

I would like the approach with frappe.get_value() better, but frappe.get_value() can’t either handle the between-condition or I’m getting the statement wrong.

Can anyone confirm this or contribute a working sample for using “between” with frappe.get_value()?

2

@thkr_ituc no it can not . but you can make it easier and faster . just remove the fields option from the get_list query . and the count is len(holidays).
the is a frappe.db.count() function , but it also can’t handle between option

3

Thank you for confirming my suspicions and the hint.

As for making the expression easier: this won’t do in my case. Our table contains three entries for just two holidays: 01.01. Saturday, 01.01. New Year, 02.01. Sunday.
The fields-option "“count(distinct holiday_date)” removes the duplicates, and the tradeoff is the more complex way to get the desired result.