gistfile1.pyw

"""
Tests the difference in speed in the following scenarios:

* Selecting all records from a SQLite database and doing processing using 
  regular Python functions (test_python)
* Calling a Python function in the SQL statement itself and returning all
  results (test_sqlite)
"""

from hashlib import sha1
import cProfile
import sqlite3

ITER = 1000

def init(db):
    c = db.cursor()
    c.execute('DROP TABLE IF EXISTS `test_table`')
    c.execute('CREATE TABLE `test_table` (value INTEGER, created TIMESTAMP DEFAULT CURRENT_TIMESTAMP)')
    c.close()

def populate(db):
    c = db.cursor()
    for i in xrange(5000):
        c.execute('INSERT INTO `test_table` (`value`) VALUES (?)', (i,))

    c.close()

def sha(value, created):
    return sha1('%s-%s' % (value, created)).hexdigest()

def test_python(db):
    c = db.cursor()
    for i in xrange(ITER):
        c.execute('SELECT `value`, `created` FROM `test_table`')
        hashes = [sha(*r) for r in c.fetchall()]

    c.close()

def test_sqlite(db):
    c = db.cursor()
    db.create_function('sha1', 2, sha)
    for i in xrange(ITER):
        c.execute('SELECT sha1(`value`, `created`) FROM `test_table`')
        hashes = list(c.fetchall())

    c.close()

if __name__ == '__main__':
    print '>>> Connecting'
    db = sqlite3.connect(':memory:', isolation_level=None)
    print '>>> Initializing'
    init(db)
    print '>>> Populating'
    populate(db)

    print '>>> Testing Python'
    cProfile.run('test_python(db)')

    print '>>> Testing SQLite'
    cProfile.run('test_sqlite(db)')