Python-сообщество

import os, logging, sqlite3

class PerformanceTest:

    def __init__(self, dbname):
        self.dbname = dbname

    def generateFakeData(self):
        if not self.open():  # database connect
            return False
            
        logging.info('generateFakeData()...')
        
        self.query("create table Files(fileId integer primary key AUTOINCREMENT, foId integer, fname text, fsize integer, ctime text, wtime text)")
        self.query("create table FilesMD5(fileId integer primary key, md5 text, calcTime text)")
        
        fileId = 0
        for foId in range(1, 1000):
            sql1 = sql2 = ""
            for i in range(1, 20):
                fileId += 1
                sql1 += (" union all " if sql1 else " insert into Files (foId, fname, fsize, ctime, wtime) ")
                sql1 += " select %i, 'fname%i', %i, '2011.12.12', '2011.12.13' " % (foId, fileId, i*1000)
                sql2 += (" union all " if sql2 else " insert into FilesMD5 (fileId, md5, calcTime) ")
                sql2 += " select %i, 'XX00XX00XX00', '2011.12.13' " % fileId
            self.query(sql1)
            self.query(sql2)
        
        logging.info('generateFakeData() done!')
        return True

    def executeQueryTest(self):
        if not self.open():  # database connect
            return False
        
        logging.info('start...')
        
        for foId in range(1, 1000):
            q2 = self.query("select ff.fname, ff.fsize, ff.wtime, md.md5 "
                            " from Files ff left join FilesMD5 md on ff.fileId = md.fileId "
                            " where ff.foId = %i order by ff.fname" % foId )
            if q2 == None:
                logging.error('q2 for foId = %i failed' % foId)
                return False
            
        logging.info('done!')

        return True

    def query(self, queryStr):
        try:
            cur = self.con.cursor()
            cur.execute(queryStr)
            rows = cur.fetchall()
            return rows
        except sqlite3.Error, e:
            logging.exception(e.args[0])
            return None

    def open(self):
        try:
            self.con = sqlite3.connect(self.dbname)
            self.con.text_factory    = unicode
            self.con.isolation_level = None
            return True
        except sqlite3.Error, e:
            logging.exception(e.args[0])
            return False

if __name__ == '__main__':
    try:
        logging.basicConfig(level=logging.DEBUG, format="%(asctime)s %(levelname)-8s %(message)s")
        
        dbname = 'temp1.sqlite'
        
        if not os.path.exists(dbname):
            PerformanceTest(dbname).generateFakeData()
            
        PerformanceTest(dbname).executeQueryTest()
        
    except:
        logging.exception("main-PerformanceTest")

# Windows 7 (64), Python 2.5.4  =>  9 seconds
#   2011-12-13 22:13:29,407 INFO     start...
#   2011-12-13 22:13:38,611 INFO     done!

# Xubuntu 11.10 (32), Python 2.7.2 => 62 seconds  // the same results for 1) 64-bit Ubuntu; 2) Python 2.6; 3) ntfs/ext4/:memory:
#   2011-12-13 22:24:07,011 INFO     start...
#   2011-12-13 22:25:09,484 INFO     done!