death and gravity: SQL query builder in 150 lines of Python (preview)

In this upcoming series, we'll look at an SQL query builder I wrote for my feed reader library. Yup, you read that right, the whole thing fits in 150 lines!

Read on for a preview.

---

While the code is interesting in its own right (if for no other reason other than the size), in the first few articles we'll discuss why I did it, what I didn't do, and how I thought about it:

• why does one need a query builder in the first place
• why I decided to write my own
  • what alternatives I considered, and why I didn't use an existing library
  • how I knew it wouldn't become too big and/or a maintenance burden
• how I modeled the problem, and how I got the idea for it

After that, we'll rewrite it from scratch, iteratively, and talk about:

• API design
• metaprogramming
• worse ways of doing things
• why I removed a bunch of features
• trade-offs, and knowing when to be lazy

---

So, what does it look like?

You call KEYWORD methods on a Query object to append text:

>>> query = Query()
>>> query.SELECT('url')
<builder.Query object at 0x7fad40935a30>
>>> print(query)
SELECT
    url

You can chain the calls for convenience (order doesn't matter):

>>> query.FROM('feeds').SELECT('title', 'updated')
<builder.Query object at 0x7fc953e60640>
>>> print(query)
SELECT
    url,
    title,
    updated
FROM
    feeds

To get the SQL, convert the query to a string:

>>> str(query)
'SELECT\n    url,\n    title,\n    updated\nFROM\n    feeds\n'

Other common things work as well:

>>> print(Query().SELECT(('alias', 'long(expression)')))
SELECT
    long(expression) AS alias

>>> print(Query().WHERE('condition', 'another condition'))
WHERE
    condition AND
    another condition

>>> print(Query().FROM('first').LEFT_JOIN('second USING (column)'))
FROM
    first
LEFT JOIN
    second USING (column)

If you want to take a peek at the code right now, you can find the final version here and the tests here. The type-annotated version used by reader is here.

---

New articles will be linked on this page as they get published; you can also get updates via email (below) or Atom feed.

This is my first planned series, and still a work in progress.

This means you get a say in it. Send me your questions or comments via email, and I'll do my best to address them in one of the future articles.

from Planet Python
via read more