How to Retrieve value from frappe.db.sql where date conditions

ERPNext
, ,
1

Hi All

I want to to receive amount from frappe.db.sql

Amt= frappe.db.sql( """select amount
From ...... 
Where from_date = '{0}' ... """.format(self.from_date) )

But the results do not appear،
So , What is the appropriate format for comparing the date in SQL with the existing date self.from_date ?
Thanks.

1 Like
2

Try this syntax. Especially when your queries get larger or complex, it’s easier to read and understand %(variable_name)s instead of {0} and {1}


my_query = """ SELECT amount FROM ... WHERE from_date = %(some_from_date)s """
my_filters = { "some_from_date": self.from_date }

results = frappe.db.sql(query, values=filters)
if results and results[0]:
    amt = results[0][0]
else:
    amt = None
1 Like
3

@brian_pond
thank you very much sir
so, how to handle two condition

filters_emp = {"employee": self.employee}
        filters_date1 = { "date1": self.from_date }

 results = frappe.db.sql(my_query, values={filters_emp,filters_date1})
4

@brian_pond
I did it

and tssa.employee = %s
                                    and ifnull(tas.from_date,tas.payroll_date) <= %s"""
        results = frappe.db.sql(salary, values=(self.employee,self.from_date))
        

        if results and results[0]:
            salary = results[0][0]
        else:
            salary = frappe.throw(_("No salary was found no deduction applied"))

thanks

5

Just use 1 dictionary, with multiple keys:

filters = {
    "employee": self.employee,
    "date1": self.from_date
}

results = frappe.db.sql(my_query, values=filters)
2 Likes
6

Thanks Sir @brian_pond

7

It’s often easier not to use direct SQL, but instead to use frappe.get_all or similar.

Edit: just seen that this is what Brian Pond recommended, but I’ve already written it now and it might explain why you should do this rather than string interpolation.

It is also always a bad idea to use string interpolation in SQL for parameters. That’s how you get SQL injection attacks if you fail to sanitize your data…

For example, you have:

frappe.db.sql(
    """SELECT salary_rate FROM `tabEmployee Salary` WHERE user_id="{}";""".format(user_id)
)

All very well and good, but what happens if I put my name as my_name" OR 1 OR "?
Now you are making an SQL query:

frappe.db.sql(
    """SELECT salary_rate FROM `tabEmployee Salary` WHERE user_id="my_name" OR 1 OR "";
)

which will return everyone’s salary rate…

What you should do is used a parametrized query, namely:

user_id = 'my_name" OR 1 OR "'
frappe.db.sql(
    """SELECT salary_rate FROM `tabEmployee Salary` WHERE user_id=%(user_id)s;""",
    {'user_id': user_id}
)

Here the parameters (defined in the SQL string as %(name)s and accessed from a dictionary) are inserted at query time, so are not vulnerable to SQL injection and don’t need to be escaped. You can also use %s and pass a tuple/list instead of a dictionary. You can only do this for the parameters, not the structure of the query, so you can’t use it for syntax (like ‘SELECT’, ‘OR’ etc.) or table/database names.

1 Like
8

In general it will usually be better to instead of, for example, writing:

frappe.db.sql(
    """SELECT amount FROM `tabMy Doctype` WHERE date = %(date)s;""",
    {'date': my_date}, as_dict=True
)

writing

frappe.get_all('My Doctype', fields=['amount'], filters={'date': my_date})

Not that I’ve used as_dict=True on the SQL, because then it returns what get_all returns (a list of frappe._dict objects).

Using the abstracted Frappe calls should be more robust as it is the published API, and doesn’t depend on underlying details of the DB implementation (e.g. MariaDB vs PostgreSQL).

1 Like
9

Hello bro @Andrew_McLeod

Thank you for the work method that you provided to me, that I needed to get the total salary based on the date in the leave Application(from date, to date), I saw that it was called by complex SQL, so I will use what he gave me.