"""SQLITE
"""
import sqlite3
class SQLite3(object):
    def __init__(self,dbfile):
        self.dbfile=dbfile
        self.conn = False

    def _connect(self):
        if not self.conn:
            self.conn = sqlite3.connect(':memory:')
            self.conn.cursor().execute("ATTACH DATABASE '%(dbfile)s' AS HSS;" % {'dbfile':self.dbfile})
            self.conn.commit()
        return self

    def query(self,querytxt):
        self._connect()
        result=[]
        try:
            cur = self.conn.cursor()
            cur.execute(querytxt)
            if cur.description:
                col = [ attr[0] for attr in cur.description ]
                result = [ dict(zip(col,row)) for row in cur.fetchall()]
        except Exception as e:
            print(e)
        return result

    def update(self,querytxt):
        self._connect()
        try:
            cur = self.conn.cursor()
            cur.execute(querytxt)
            self.conn.commit()
        except Exception as e:
            print(e)
        return self

    def script(self,querytxt):
        self._connect()
        try:
            cur = self.conn.cursor()
            cur.executescript(querytxt)
            self.conn.commit()
        except Exception as e:
            print(e)
        return self

import time
class Bench(object):
    def __init__(self):
        self.timestamp = time.time()

    def lap(self):
        newstamp = time.time()
        diff = newstamp - self.timestamp
        self.timestamp = newstamp
        return diff*1000

if __name__ == '__main__':
    bench = Bench()
    filename = 'test.db'
    testdb = SQLite3(filename)
    print "init : ",bench.lap()
    testdb.script("""
        CREATE TABLE tbl (
            id PRIMARY KEY,
            name TEXT,
            value INTEGER
        );
        INSERT INTO tbl (name,value) VALUES ('bob',33);
        INSERT INTO tbl (name,value) VALUES ('lucie',42);
        INSERT INTO tbl (name,value) VALUES ('stonehenge',150);
    """)
    print "update then select 10 times : ",bench.lap()
    for n in range(10):
        testdb.update("""
            UPDATE tbl SET value = value + 1;
        """)
        testdb.query("""
            SELECT * FROM tbl WHERE 1=1;
        """)
        print "done in : ",bench.lap()