simpleQL 0.1.2

Efficient filtering of SQL tables with generator expressions.

This module allows you to access a (DB API 2) SQL table using nothing but Python to build the query:

>>> import re
>>> from pysqlite2 import dbapi2 as sqlite
>>> from simpleql.table import Table

>>> conn = sqlite.connect(":memory:")
>>> curs = conn.cursor()
>>> curs.execute("CREATE TABLE test (a integer, b char(1))")
>>> curs.executemany("INSERT INTO test (a, b) VALUES (?, ?)", ([1,'a'], [2,'b'], [3,'c']))
>>> conn.commit()

>>> table = Table(conn, "test", verbose=1)
>>> for row in table:
...     print row
...
SELECT a, b FROM test;
{'a': 1, 'b': u'a'}
{'a': 2, 'b': u'b'}
{'a': 3, 'b': u'c'}

>>> aspan = (1, 3)
>>> for row in (t for t in table if min(aspan) < t.a < max(aspan)):
...     print row
...
SELECT a, b FROM test WHERE (1<a) AND (a<3);
{'a': 2, 'b': u'b'}

(This is a fake example, the filtering does not work in interactive mode.)

As you can see, the query string is built from a generator expression. You can also use list comprehensions. Regular expressions are supported by the use of the re.search method:

>>> filtered = [t for t in table if re.search('a', t.b)]
SELECT a, b FROM test WHERE b LIKE "%a%";
>>> print filtered
[{'a': 1, 'b': u'a'}]

The advantage of this approach over the similar recipe is that if the (efficient) query builder fails when it encounters a complex filter the data will still be filtered (unefficiently) by the generator expression.