python sqlite

sqlite3.py

import os
import re
import glob

def initdb(base=os.getcwd(), ext="*.db3"):
    '''get db file list,
        use current directory as base,
        and db3 as extention
    '''
    try:
        files=glob.glob(os.path.join(base, ext))
    except:
        print 'Error! please check out the directory and db files'
        raise
    try:
        import sqlite3
    except:
        from pysqlite2 import dbapi2 as sqlite3
    # Connect to DB
    try:
        db = sqlite3.connect( ':memory:', timeout = 5000 )
        fileregex=re.compile(r"""(?<=/)[^/]+\.[^/]+$""")
        for file in files:
            nick = fileregex.findall(file)[0]
            db.execute("ATTACH DATABASE ? AS ?", ( file, nick ) )
        return db
    except:
        print 'Error connecting to database'
        raise

def createTable(db):
    c=db.cursor()
    c.execute('''create table stocks
        (date text, trans text, symbol text,
        qty real, price real)''')
def Insert(db):
    c.execute("""insert into stocks
          values ('2006-01-05','BUY','RHAT',100,35.14)""")
    db.commit()

    #secure search    
    t = (symbol,)
    c.execute('select * from stocks where symbol=?', t)

    # inserting many
    for t in [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
              ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00),
              ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
             ]:
        c.execute('insert into stocks values (?,?,?,?,?)', t)
    db.commit()



def fetchData(db, interval):

    c = db.cursor()
    table_name='some_table'
    sql="SELECT a, b, c FROM %s WHERE 1" % ( table_name)
    c.execute(sql)

    for row in c:
        col1, col2, col3 = row
        #some action

    expr="some regex"
    regexp_func = lambda expr, item: re.compile(expr).search(item) is not None
        # Create function in SQLite for REGEXP operator
    db.create_function( "regexp", 2, regexp_func )

    c.execute("SELECT a, b FROM tablex WHERE `variable` REGEXP ?", ( regexp, ) )
    for row in c:
        col1, col2, col3 = row
        #some action

db=initdb()