Mysql Simple Query Builder

Following the rule simple easy, complex possible, the package provides API for simple queries, nested transactions and aid for complex query building and profiling. It’s a small wrapper around a Python MySQL driver.

The package is written on the following assumptions:

  1. SQL is feasible and representative DSL
  2. Simple SQL is simple but tedious to write by hand
  3. Complex SQL is possible and should be written by hand or constructed elaborately
  4. Unit/integration testing of domain logic against database is necessary
  5. Database abstraction in the age of SaaS is a waste

Scope

What is simple SQL? Here are signatures for the query methods:

select(self, fields, table, where = None, order = None, limit = None)
one(self, fields, table, where = None, order = None)
count(self, table, where = None)
insert(self, table, values)
update(self, table, values, where)
delete(self, table, where)

What is complex SQL? Here are signatures of query construction:

cursor(self, type = tuple)
quote(self, value)
query(self, sql, where = None, order = None, limit = None)

Short argument description follows. For detailed description read Reference.

  • fields is str sequence, e.g. ('film_id', 'title')
  • table is str, e.g. 'film'
  • where is dict, e.g. {'rating': ('R', 'NC-17'), 'release_year': 2006}
  • order is tuple sequence of field and sort order, e.g. [('release_year', 'asc'), ('length', 'desc')]
  • limit is int of row limit or tuple with offset and row limit, e.g. 10 or (100, 10)
  • values is dict, e.g. {'title': 'My New Title', 'length': 99}

Accordingly, simple SELECT query is a query to single table with conjunction of conditions whose values are literal, set of fields to order by and row offset and limit. Methods for INSERT, UPDATE and DELETE queries are similar. Complex SQL is everything else.

That is to say the package doesn’t even try to abstract away from SQL. But rather it takes away the need of writing really simple queries by hand. These method calls have obvious corresponding SQL, so obvious are their execution plans.

The library was written for web application use case when dealing with domain objects’ representations is more natural order of things than classical domain simulation with domain objects per se. Thus if you work on a classical environment with long-living in-memory domain objects, taking philosophical problem of object-relational mismatch [1] serious, or just developing a shrink-wrap, unlikely you will find the library useful. If that’s the case it’s worth looking at SQLAlchemy [2], peewee [3] or the like.

[1]http://en.wikipedia.org/wiki/Object-relational_impedance_mismatch
[2]http://www.sqlalchemy.org/
[3]https://peewee.readthedocs.org/