Subscribe to out mailing list Google group:


Constraints are a mean to limit browsing of data to a limited set. It’s a different concept from filtering, as this enforces a limitation while filtering allows the user to change the values at will.

Django-like syntax

A constraint can be as simple as:

t = SqlTable('movie', dbproxy=db)
t.add_constraints(actors__country='ITA', genres__name='drama',
                  year__gte=1950, year__lte=1970)

And browsing of movies will be constrained into dramas with at least one Italian actor, produced between 1950 and 1970. The double underscore ‘__’ will allow spanning from one table to a related one (provided a relation was established at sqlalchemy level) and from attribute name to operator to be used.


This syntax is inspired to django‘s ORM syntax. The same syntax can be used to add filters to the sqlwidget, to the completion and when .reload() method is used.


limitation acts on self.query directly. Self query can be modified in any way accepted by sqlalchemy syntax


these operators are recognized:

    'notlike'    : 'not like',
    'like'       : 'like',
    'ilike'      : 'ilike',
    'notilike'   : 'not ilike',
    'iregexp'    : '~*',
    'regexp'     : '~',
    'notiregexp' : '!~*',
    'notregexp'  : '!~',
    'lt'         : '<',
    'gt'         :  '>',
    'lte'        : '<=',
    'gte'        : '>=',
    'eq'         : '=',
    'neq'        : '!=',
    'equal'      : '=',
    'in'         : 'IN',
    'notin'      : 'NOT IN',
    'null'       : 'IS NULL',
    'notnull'    : 'IS NOT NULL',
    'emptynull'  : 'IS NULL or EMPTY',

and icontains that at the moment map to regexp operators if they exist or like with % on both sides.


In the example all conditions have been AND ed. It’s possible to use OR operator adding or_=True argument.


You can get a sqlalchemy ClauseList object starting from a django-like expression using django2sqlalchemy function:

from sqlkit.db.django_syntax import django2sqlalchemy
clause_list, join_paths = django2sqlalchemy(mapper, *args)

where args can be or_=boolean or any django-like query. Django2sqlalchemy returns a tuple: (clause_list, join_path):


the list of column expression connected with AND or OR according to or_ argument


the list of path needed to add to query via query.join() to have all the fields the query needs to apply the filters in clause_list

A possibility is to add:

for path in join_path:

Filter simplified syntax

Django syntax utilities

This module provides some functions to allow use of django syntax in filters. You can look an introduction on lookup filters on django’s site but mind that we don’t even share the same syntax. We just borrowed the idea that the operator and possibly the join can be wired in the keyword name. The aim is not at all compatibility with django (that has nothing to do with this framework) but a way to:

  • make it easy to add filters and constraints
  • make it easy to store queries (that need to store filters and constraints)

These function are used in sqlfilter and in completion beside .add_constraint(...) method of sqlwidget.

You never need to use these functions. If you prefere you can changed the query directly with SQLAlchemy syntax, and surely there are situations where that’s necesssary, nevertheless there are situations where this syntax allows to obtain the same result in less and more readeable code, probably just one line.

All of these functions act on a mapper becouse it has info on PropertyLoaders. In completion, the mapper is used to follow the join_path even if the query (a, will not be issued on a session.query object

supported lookup
like/ilike:use like operator, ilike if available
 negate like operator, ilike if available
regexp/iregexp:now ~/~* for postgres and REGEX for mysql like again for the rest
 now !~/!~* (only for postgresql)
lt/gt:less than / grater than (<, >)
lte/gte:less than equal/ grater than equal(<=, >=)
eq/neq:equal / not equal ( = , !=)
in/notin:IN (argument must be a list)/ negated
null:IS NULL / IS NOT NULL (depending if arg is True/False)
emptynull:IS NULL or EMPTY

django2sqlalchemy() and django2query() connect all arguments with AND operator by default. It’s possible to use OR operator adding OR=True argument.

sqlkit.db.django_syntax.django2sqlalchemy(mapper=None, table=None, OR=False, **kw)

Return a tuple (ClauseList, join path).

  • mapper – the mapper that will be used in the query
  • table – the mapper that will be used in the query
  • OR – if True the conditions will be ORed
  • kw – the conditions as per django-like syntax
sqlkit.db.django_syntax.django2query(query, mapper_or_class, OR=False, aliased=False, OUTER=False, **kw)

return a new query with new constraints expressed in ‘django-style’

  • query – the query to which filters will be applied
  • mapper_or_class – a mapper or a class from which the class will be devised
  • aliased – passed to DjangoParser
  • OUTERDjangoParser will be instantiated as an OUTER join

Native sqlalchemy constraint

The django syntax is in no way the only possibility. If that is not sufficient to express the constraints you need you can just use any filter on the query directly:

tk_tbl = ticket.Ticket.__table__
my_id = self.db.get_session().query(ticket.User).filter_by(username=setup.USERNAME).one().id

t = sk.SqlTable(ticket.Ticket, field_list=field_list, order_by='priority', **self.meta)
t.query = t.query.filter(or_(tk_tbl.c['assigned_by_id'] == my_id ,
                             tk_tbl.c['assigned_to_id'] == my_id , ))

this example shows how to force a constraint on ticket requiring that the ticket be assigned by or to USERNAME. Any further filter applied interactively will be applied on top of this constraint. This is equivalent to writing:

t.add_constraint(assigned_by_id=my_id, assigned_to_id=my_id, OR=True)

At the moment of this writing it’s not possible to write:


as the foreign key relation is not followed in this context.

aliased constraints

Sometimes you need to have aliased constraints in order to ha sqlalchemy build an aliased join to related classes on which you may want to set constraints. This may be necessary if you want to use constraints along with order_by and possibly filters on foreign keys.


Suppose you have an ‘address’ table with a user_id field that is a ForeignKey to a column. Suppose you want to open a SqlTable on table addresses, ordered on user_id (well... surely you don’t want to order by the id value, you probably have a first_name field that is much more appropriate for sorting).

Now suppose you also have an active field on the user_table. You can achieve this (simple) in the following way:

t = SqlTable('address', order_by='user_id__first_name', ...)
t.add_constraint(user_id__active=True, aliased=True)

the order_by argument implicitly have built a join with the user table, the same stands for the constraint user_id__active. Sqlalchemy would have complained that the table ‘user’ was already present, so you need to alias it. I’ll probably make this the default in a future version.