Snaql. Raw SQL in Python projects

In the last year, I have a new rule - every 3 months to learn a new programming language and its ecosystem. There are several reasons for this: new paradigms, concepts, tools, and it’s just interesting what is there, on the other side of Python, which has become awesome over the years. This simple rule allowed studying modern hipster Go, Clojure and Rust for the current year, imbued with their ideas and best practices, which, by the way, has a very positive effect on the style and quality of the code when I write in my main language.

Looking at the Luminus stack, I came across a simple and at the same time chic, for my taste, Yesql library for organizing SQL queries in a project on Clojure and I did not see something similar for Python (maybe I was looking bad). The idea of ​​this library is simple - do not fool yourself, use ordinary SQL queries, you have the possibility of naming these queries and mapping to the corresponding dynamic functions. All this looks like a set of micro-templates with SQL and their rendering for some context. Simply, effectively, I want this in my project in Python.


')
In general, lately I have been impressed by the idea that ORMs are not needed. They overcomplicate, in fact, work with relational databases, hide “hellish” SQL behind the screen of complex constructions of their own objects, and often produce an extremely inefficient result. Surely someone will argue with this conclusion, but my practice has shown that Django ORM is terribly simple a little more than ever (and is only available if you use Django, of course), SQLAlchemy is terribly complex, Peewee has never met in the wild. the same a little more and he will become like Alchemy on his own threshold of entry. SQL is in itself a powerful and expressive DSL, you don't need another level of abstraction over it, seriously. From a different angle, I was thinking about the feasibility of ORM during the next Tornado project. Alchemy miraculously alchemically kills all the asynchronous execution of the handler with blocking calls to the database. And the options except how to use the same Momoko with raw queries, I did not see.

All we need for complete happiness is the dilution of SQL strings and Python code in different angles and some flexibility in constructing constructions by conditions or context. Well, stop being afraid to write SQL, of course. Studying SQL to the required level is really easier than all the nuances of Alchemy for the same result.

After trying and rethinking Yesql a little bit, I had a tiny Snaql library that solves the problem described above, albeit in a slightly different way. I decided not to bind clients to the databases at all and use Jinja2 as an engine for parsing and rendering templates with SQL blocks (with all the ensuing possibilities to use its template logic). Here's what it looks like.

1. We put Snaql.

$ pip install snaql 

2. Create in your project a folder where we will add files with SQL blocks. Or several such folders.

 /queries users.sql 

3. In users.sql we have, for example, all queries related to the user's identity.

 {% sql 'users_by_country', note='counts users' %} SELECT count(*) AS count FROM user WHERE country_code = ? {% endsql %} 

As you can guess, SQL is placed inside the {% sql%} {% endsql%} block, “users_by_country” is the name of the function that this SQL is attached to (it is created dynamically), and “note” is the docstring to this function, it is optional.

There can be any number of such blocks in one file. The main thing is that their names are unique.

4. Now we need a factory that parses such files and creates a set of functions of the same name.

 from snaql.factory import Snaql #   root_location = os.path.abspath(os.path.dirname(__file__)) #     snaql_factory = Snaql(root_location, 'queries') #    SQL- # users_queries = snaql_factory.load_queries('users.sql') 

Extract the necessary SQL in the code, you can now simply call

 your_sql = users_queries.users_by_country() # SELECT count(*) AS count # FROM user # WHERE country_code = ? 

In fact, this may already be enough. But not in the case of the generated query terms. In this case, you can add to the template all the logic that Jinja provides. For example:

 {% sql 'users_select_cond', note='select users with condition' %} SELECT * FROM user {% if users_ids %} WHERE user_id IN ({{ users_ids|join(', ') }}) {% endif %} {% endsql %} 

If you call a function without context:

 your_sql = users_queries.users_select_cond() # SELECT * # FROM user 

And if with context:

 your_sql = users_queries.users_select_cond(users_ids=[1, 2, 3]) # SELECT * # FROM user # WHERE user_id IN (1, 2, 3) 

Having received the generated SQL, the rest is a matter of technique. It seems not bad, right? In any case, write your pros and cons in the comments, I’m interested in the opinion of the community, as far as it can be convenient for someone other than me.

GitHub , PyPi

UPD: Thanks for the constructive comments. Now I have a way to form a roadmap on 0.2. Feel free to send issues and requests to GitHub.

UPD2: Thanks to your constructive notes, I updated Snaql to version 0.2, there are now guards and conditions blocks, extended support for interpreter versions to 2.6, 2.7, 3.3, 3.4, 3.5.