Skip to main content

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.

Source Distribution

simpleQL-0.1.2.2.tar.gz (5.7 kB view details)

Uploaded Source

Built Distribution

simpleQL-0.1.2.2-py2.4.egg (12.9 kB view details)

Uploaded Source

File details

Details for the file simpleQL-0.1.2.2.tar.gz.

File metadata

  • Download URL: simpleQL-0.1.2.2.tar.gz
  • Upload date:
  • Size: 5.7 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No

File hashes

Hashes for simpleQL-0.1.2.2.tar.gz
Algorithm Hash digest
SHA256 1afb1eb639ebeb75453aa63ac600504f3135a63de1019bf1e162ab848c118964
MD5 00aa8221a906932ee4f7cb0f03159448
BLAKE2b-256 46174946a9f89a43faccbc05d7127301a40e894923978650d9e7f22c0fdee219

See more details on using hashes here.

Provenance

File details

Details for the file simpleQL-0.1.2.2-py2.4.egg.

File metadata

File hashes

Hashes for simpleQL-0.1.2.2-py2.4.egg
Algorithm Hash digest
SHA256 92109d39f1fe8084543c3313e5199b1d287824ed75b0f9fd9714506b317ef4e9
MD5 31cee499e70ff4bfe52617fffd69646b
BLAKE2b-256 9ce0d0478a677ff0917a474941ef455091500f37195e472b3fe4f16ef42eacc3

See more details on using hashes here.

Provenance

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page