In this 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 wrote it, how I thought about it, and what other options I considered:
- why I needed 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
- 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?
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, you 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)
This is my first planned series, and still a work in progress.
This means you get a say in it. Email me your questions or comments, and I'll do my best to address them in one of the future articles.
This is part of a series: