We’ve wanted to add a more Pythonic way to manage queries in Frappe for a while. So, we finally got around and did it this month .
So you want to fetch some data from your database, but you need something more powerful than
frappe.get_all? This is when most resort to using
frappe.db.sql, and the brave-hearts use multiple
frappe.get_all calls along with
pandas, along with a
for loop or two. With our query builder, we aim to ease this pain a bit. Here’s how it’d look like to run a select with or, and and filters:
customer = frappe.qb.DocType('Customer') filtered_data = ( frappe.qb.from_(customer) .select(customer.name, customer.fname, customer.lname, customer.phone) .where((customer.fname == 'Max') | customer.id.like('RA%')) .where(customer.lname == 'Mustermann') ).run(as_dict=True)
Okay, this is a relatively simpler query. But, you could write much complex queries; subqueries, CTEs, recursive, joins too. I didn’t add an example for those here because that’d get a bit too much for this light-hearted post.
One particularly neat thing about our query builder is that it evaluates your queries to whatever DBMS your site’s running on. Yup, you can write cross-database apps without having to use
frappe.db.multisql. In fact, we tried to get rid of all the multisql calls in Frappe itself…there’s still a couple left though.
The above query object, evaluates differently on Postgres and MariaDB as you can see here:
# Postgres query SELECT "name","fname","lname","phone" FROM "tabCustomer" WHERE ("fname"='Max' OR "id" LIKE 'RA%') AND "lname"='Mustermann' # MariaDB query SELECT `name`,`fname`,`lname`,`phone` FROM `tabCustomer` WHERE (`fname`='Max' OR `id` LIKE 'RA%') AND `lname`='Mustermann'
We’ve documented the available APIs so that you can get started writing queries with
frappe.qb. You can read further about it here:
Once you’ve familiarised yourself with what Frappe sugar is available, you can find comprehensive docs about all possible usages, check out PyPika’s official docs and their even more comprehensive README:
Here’s the PRs that introduced this feature in Frappe. It’s currently on
develop and will be a part of Frappe Version 14. Meanwhile, we’re going to try and convert as many raw queries to this notation.
Apart from PyPika, we considered a few other query builders and ORMs. SQLAlchemy seemed to be a strong contender in the early stages. We soon faced a barrage of issues as we started implementing it. Some of our work on it is documented in this PR:
Apart from these, we’ve been making active efforts in extending the existing Database APIs to make them more powerful. We aim to make it easier for everyone to write Frappe Apps, and reduce the interfaces for bugs while we’re at it .
We converted close to 80 raw queries to use the Database APIs. This enabled us to further improve on the low level
frappe.db.delete API and introduce a new
We’re also trying to expand the feature set in
DatabaseQuery it’s the engine that supports
frappe.get_all, list views, etc. Support for pure column or data comparisons, and more Pythonic APIs for using Pseudo columns and better handling for SQL functions is coming soon…
Feel free to drop your suggestions or ideas for improvements