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
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.

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.2.tar.gz (5.6 kB view details)

Uploaded Source

Built Distribution

simpleQL-0.2-py2.6.egg (12.8 kB view details)

Uploaded Source

File details

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

File metadata

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

File hashes

Hashes for simpleQL-0.2.tar.gz
Algorithm Hash digest
SHA256 d6b1ec591068b1faa4500d12c76a71d04723cf5230f784dc3643b505090f6941
MD5 bfb5ace31e94d9680134d835a500285a
BLAKE2b-256 06382cef04b5ee06a10fdf746f5a31b7fece84420b6de745ec6491830854763e

See more details on using hashes here.

Provenance

File details

Details for the file simpleQL-0.2-py2.6.egg.

File metadata

  • Download URL: simpleQL-0.2-py2.6.egg
  • Upload date:
  • Size: 12.8 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No

File hashes

Hashes for simpleQL-0.2-py2.6.egg
Algorithm Hash digest
SHA256 93228658b1f39ff2f36681dec26367c228d98ea91cccf5eb9b5e1e9be7824f16
MD5 0b59573ceea62b8c08f61bf91da8c04d
BLAKE2b-256 71a684592535886f35b2140fc9510d7cff06c9a8659131929b4f5c44be0030a2

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