skip to navigation
skip to content

Not Logged In

simpleQL 0.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
import sqlite3
from simpleql.table import Table

conn = sqlite3.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

This will print:

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

This will print:

SELECT a, b FROM test WHERE (1<a) AND (a<3);
{'a': 2, 'b': u'b'}

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'])]
print filtered

Which outputs:

SELECT a, b FROM test WHERE b LIKE "%a%";
[{'a': 1, 'b': u'a'}]

Note that since the module has to analyse the source code it doesn’t work on the interactive shell.

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.

 
File Type Py Version Uploaded on Size
simpleQL-0.2-py2.6.egg (md5) Python Egg 2.6 2011-04-11 12KB
simpleQL-0.2.tar.gz (md5) Source 2011-04-11 5KB
  • Downloads (All Versions):
  • 24 downloads in the last day
  • 149 downloads in the last week
  • 845 downloads in the last month