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!
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 0x7fc953e60640> >>> 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: