Efficient filtering of SQL tables with generator expressions.
Project description
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'}
Note that each row in the table is a dictionary. We can filter this using a generator expression:
>>> 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.
Project details
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.