SQL query templating shorthand


License
SSPL-1.0
Install
pip install querypp==0.1.2

Documentation

querypp

Deprecation Notice

querypp has been deprecated in favor of vanilla jinja2. After porting it to jinja2, I realized that none of querypp was actually necessary. Migrating docs here.

v0.0.3 is the last version that doesn't use jinja2, in case you want a more lightweight version.


querypp is a SQL query[1] templating system based on jinja2.

[1]: Although it is trivially adapted to other languages, as the only SQL-specific assumption is the line comment syntax.

Take an example:

-- :query users
SELECT *
FROM users
-- :qblock profiles
	LEFT JOIN profiles USING (user_id)
	-- :block login_history
		LEFT JOIN login_history USING (profile_id)
	-- :endqblock
-- :endqblock
-- :qblock user_id WHERE user_id = $1
-- :endquery

A Query template can be called:

  • with no block names to return the query without any blocks
  • with one or more block names to return the query with only those block names.

In this case, q('profiles', 'user_id') would return the query with the login_history JOIN removed.

Additionally, any jinja2 templating features, such as variables and macro functions, can be used, using either the line syntax (e.g. -- :include 'foo.sql') or the block syntax (e.g. {% if x == 1 %}).

Usage

env = querypp.QueryEnvironment('sql/')
queries = env.get_template('queries.sql')  # opens sql/queries.sql
db.execute(queries.users('login_history', 'user_id'), user_id)

Motivation

After moving all my SQL queries to separate files, I noticed that I was duplicating some of them except for one extra clause. I created this to allow me to deduplicate such queries.

License

Public domain, see COPYING