def __init__(self, DBfn=None, DBcolumns=None, DB_DEBUG=False):
     self.DB_DEBUG = DB_DEBUG
     self.DBfn = DBfn
     self.DBcolumns = DBcolumns
     if self.DBfn is None:
         self.DBfn = os.path.join(os.path.expanduser('~'), 'Desktop', "MagicDB", __sqlext__)
         print("WARNING, creating/using a default database: {}".format(self.DBfn))
     if not os.path.isdir(os.path.dirname(self.DBfn)):
         os.makedirs(os.path.dirname(self.DBfn))
     sqlite3.register_converter("json", json.loads)
     sqlite3.register_adapter(list, json.dumps)
     sqlite3.register_adapter(dict, json.dumps)
     self.con = sqlite3.connect(self.DBfn, detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
     self.con.row_factory = sqlite3.Row
     self.con.text_factory = sqlite3.OptimizedUnicode
     self.cur = self.con.cursor()
     self.newDB = False
     # check that tables exist. if not, make them
     for t, v in self.DBcolumns.viewitems():
         if not self.cur.execute('''PRAGMA table_info ('{}')'''.format(t)).fetchall():
             self.cur.execute(v)
             self.con.commit()
             print("Created new table: {}".format(t))
             self.newDB = True
         else:
             print("using existing table: {} ".format(t))
         print("in file: {}".format(self.DBfn))
     self.tables = [a[0] for a in
                    self.cur.execute('''SELECT name FROM sqlite_master WHERE type='table' ''').fetchall()]

 def __init__(self):
     # Boolean to record whether the database exists. We need this because
     # we need to check if the db file exists in the file system before we
     # connect to the database.
     exists = False
     
     # Check if the database exists in the file system.
     if os.path.isfile('spellingaid.db'):
         exists = True
     
     # Connect to the database and create a cursor.
     self.db = sqlite.connect('spellingaid.db', detect_types = sqlite.PARSE_DECLTYPES)
     self.db.text_factory = str
     self.c = self.db.cursor()
     
     # If the database didn't exist, initialise the tables.
     if not exists:
         self.db.executescript(INIT)
     
     # Register adapters and converters to let the database work with User
     # and Word objects.
     sqlite.register_adapter(User, lambda u : u.serialise())
     sqlite.register_adapter(Word, lambda w : w.serialise())
     
     sqlite.register_converter('User', User.deserialise)
     sqlite.register_converter('Word', Word.deserialise)
     
     self.listeners = []

def custom_sort( db_filename ):
    # register the functions for manipulating the type
    sqlite3.register_adapter(MyDataObject, quiet_adapter_function)
    sqlite3.register_converter("MyDataObject", quiet_converter_function)
    
    with closing( sqlite3.connect( 
        db_filename,
        detect_types = sqlite3.PARSE_DECLTYPES
    )) as conn:
        
        # define the collation
        conn.create_collation('unpickle', collation_function)
        
        # clear the table and insert new values
        conn.execute("""
        delete from obj
        """)
        conn.executemany("""
        insert into obj (data)
        values (?)
        """, [ ( MyDataObject(i), ) for i in xrange(5, 0, -1) ], )
        
        # query the db for the objects just saved
        print "Querying:"
        cursor = conn.cursor()
        cursor.execute("""
        select id, data from obj
        order by data collate unpickle
        """)
        for obj_id, obj in cursor.fetchall():
            print obj_id, obj

def load_manager(filename=None):
	"""Connects to the SQLite database with the given filename.

	If this is None, then it connects to an in-memory database (used for
	testing).
	"""
	from passage_list import PassageList, PassageListManager
	sqlite3.register_adapter(PassageList, lambda self: self.id)
	sqlite3.register_adapter(PassageListManager, lambda self: self.id)
	global connection, previous_filename
	if filename is None:
		filename = ":memory:"
	assert connection is None or previous_filename == filename
	previous_filename = filename
	manager = PassageListManager()
	try:
		if connection is None:
			connection = sqlite3.connect(filename)
		_maybe_setup_database(manager)
		_load_topic_children(manager)
		manager.parent = None
	except sqlite3.Error, e:
		import os
		manager.has_error_on_loading = True
		print "SQLITE loading error"
		import traceback
		traceback.print_exc()

def classify(eigvects, name):
    print 'eigvects: ', eigvects
    # Converts np.array to TEXT when inserting
    sqlite3.register_adapter(np.ndarray, cvtNparr.adapt_array)
    # Converts TEXT to np.array when selecting
    sqlite3.register_converter("array", cvtNparr.convert_array)

    conn = sqlite3.connect("/home/wlw/oliverProjects/3DClassification/classification.db", detect_types=sqlite3.PARSE_DECLTYPES)
    cur = conn.cursor()

    cur.execute("select eigvects, id from model where type='flat'")
    lists = cur.fetchall()
    for lis in lists:
        # lis是一个tuple
        #print 'lis[0]: ', lis[0]
        #print type(lis[0])

        res = lis[0] - eigvects
        summ = 0
        for r in res:
            d = math.sqrt(sum(math.pow(value, 2) for value in r))
            summ += d

        similarity = summ / 3.0
        print '%s\'s  similarity with %s is %f ' % (lis[1], name, similarity)

    conn.close()

 def __init__(self, smarthome):
     self._sh = smarthome
     self._version = 1
     sqlite3.register_adapter(datetime.datetime, self.timestamp)
     logger.debug("SQLite {0}".format(sqlite3.sqlite_version))
     self.connected = True
     self._fdb = sqlite3.connect(smarthome.base_dir + '/var/db/smarthome.db', check_same_thread=False)
     self._fdb_lock = threading.Lock()
     self._fdb_lock.acquire()
     common = self._fdb.execute("SELECT * FROM sqlite_master WHERE name='common' and type='table';").fetchone()
     if common is None:
         self._fdb.execute("CREATE TABLE common (version INTEGER);")
         self._fdb.execute("INSERT INTO common VALUES (:version);", {'version': self._version})
         self._fdb.execute(self._create_db)
         self._fdb.execute(self._create_index)
         version = self._version
     else:
         version = int(self._fdb.execute("SELECT version FROM common;").fetchone()[0])
     if version < self._version:
         logger.debug("update database")
         self._fdb.execute("UPDATE common SET version=:version;", {'version': self._version})
     self._fdb.commit()
     self._fdb_lock.release()
     minute = 60 * 1000
     hour = 60 * minute
     day = 24 * hour
     week = 7 * day
     month = 30 * day
     year = 365 * day
     self._frames = {'i': minute, 'h': hour, 'd': day, 'w': week, 'm': month, 'y': year}
     self._times = {'i': minute, 'h': hour, 'd': day, 'w': week, 'm': month, 'y': year}
     # self.query("alter table history add column power INTEGER;")
     smarthome.scheduler.add('sqlite', self._pack, cron='2 3 * *', prio=5)

    def _tell_sqlite_about_numpy(self):

        for t in (np.int8, np.int16, np.int32, np.int64,
                  np.uint8, np.uint16, np.uint32, np.uint64):
            sqlite3.register_adapter(t, long)
        for f in (np.float, np.float32, np.float64):
            sqlite3.register_adapter(f, float)

def _save_as_sqlite(packages, absolute_path):
    """Save a list of packages as an SQLite3 binary file.
    
    Arguments:
    packages -- a list of TLPackage objects
    absolute_path -- output path for the database
    
    An existing file at this path will be removed before writing, to ensure that
    you end up with a consistent database.  This is mainly for symmetry with the
    plist writing method.
    
    Not all values are saved to sqlite.  Notably runfiles and other dictionary
    types are not written at present, since they should probably be in a separate
    table.
    
    """
    import sqlite3
    import os
    import errno
    
    def _adapt_list(lst):
        if lst is None or len(lst) == 0:
            return None
        return buffer("\0".join(lst).encode("utf-8"))

    sqlite3.register_adapter(list, _adapt_list)
    sqlite3.register_adapter(tuple, _adapt_list)
    
    # plistlib will overwrite the previous file, so do the same with sqlite
    # instead of adding rows
    try:
        os.remove(absolute_path)
    except OSError, e:
        if e.errno != errno.ENOENT:
            raise e

    def __init__(self, dbpath):
        self.dbpath = dbpath
        # !!!: Remember that you must update the self._format_parameters method
        # if you update the self type property.
        self.type = {
            'int': 'INTEGER'
            ,'float': 'REAL'
            ,'str': 'TEXT'
            ,'bytes': 'BLOB'
            ,'prim': 'PRIMARY KEY'
            ,'intPrim': 'INTEGER PRIMARY KEY'
            ,'bool': 'BOOLEAN'
            ,'date': 'DATE'
            ,'datetime': 'TIMESTAMP'
        }
        self.validTypes = set(self.type.keys())
        self.bindingDict = {}

        # Adapters and converters for the bool type
        sqlite3.register_adapter(bool, int)
        sqlite3.register_converter("BOOLEAN", lambda v: bool(int(v)))
        self.connection = Connection(
            self.dbpath
            ,check_same_thread=False
            ,detect_types=sqlite3.PARSE_DECLTYPES)
        atexit.register(self._finalize)

def _register_adapter(value, key):
    """Register an adapter if the type of value is unknown."""
    # Assuming no storage of non-simple types on channel 'resumed_from'
    if (not isinstance(value, (type(None), int, float, six.string_types,
                               bytes, numpy.ndarray)) and
            key != 'resumed_from'):
        sqlite3.register_adapter(type(value), adapt_obj)

def initDB(db_file, db_dirPath):
    #
    # Database

    #    db_conn = connect(db_file)
    db_conn = connect(db_file, check_same_thread=False)

    db_conn.row_factory = namedtuple_factory
    #    db_conn.isolation_level = None

    # SQLite tune-ups
    db_conn.execute("PRAGMA synchronous = OFF;")
    db_conn.execute("PRAGMA temp_store = MEMORY;")

    # Force enable foreign keys check
    db_conn.execute("PRAGMA foreign_keys = ON;")

    # Store data in UNIX timestamp instead ISO format (sqlite default)
    # and None objects as 'NULL' strings
    from datetime import datetime
    from time import mktime
    from sqlite3 import register_adapter

    def adapt_datetime(ts):
        return mktime(ts.timetuple())

    register_adapter(datetime, adapt_datetime)

    #
    # antiORM

    return Sqlite(db_conn, db_dirPath, False, True)

    def __init__(self, database_name):
        sql.register_adapter(np.ndarray, self.adapt_array)
        sql.register_converter("array", self.convert_array)
        self.conn = sql.connect('database/'+str(database_name)+'.db', isolation_level=None, detect_types=sql.PARSE_DECLTYPES, check_same_thread=False)

        if TYPE == 1:
            self.conn.execute("CREATE TABLE IF NOT EXISTS `files` ("
                              "`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
                              "`file_path` TEXT NOT NULL)")

            self.conn.execute("CREATE TABLE IF NOT EXISTS `features` ("
                              "`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
                              "`file_id` INTEGER NOT NULL,"
                              "`frame` INTEGER NOT NULL,"
                              "`feature` array NOT NULL,"
                              "`class` TEXT NOT NULL)")

            self.conn.execute("CREATE TABLE IF NOT EXISTS `final_weight` ("
                              "`vectors` array NOT NULL,"
                              "`class` TEXT NOT NULL)")

        else:
            self.conn.execute("CREATE TABLE IF NOT EXISTS `output_classes` ("
                              "`id`	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
                              "`file_path` TEXT NOT NULL,"
                              "`class` TEXT NOT NULL)")

            self.conn.execute("CREATE TABLE IF NOT EXISTS `feature_sets` ("
                              "`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
                              "`output_class_id` INTEGER NOT NULL,"
                              "`frame` INTEGER NOT NULL,"
                              "`features` array NOT NULL)")

 def _connectToDb(self):
     """ Opens a db connection
     """
     self.con = sqlite3.connect(self.db_path, detect_types=sqlite3.PARSE_DECLTYPES)
     sqlite3.register_adapter(bool, int)
     sqlite3.register_converter("BOOLEAN", lambda v: bool(int(v)))
     self.con.row_factory = self._dictFactory

def get_db(config):
    db = sqlite3.connect(config.get("CONNECTION", "SQLITE_DB"))
    db.row_factory = sqlite3.Row
    sqlite3.register_adapter(bool, int)
    sqlite3.register_converter("BOOLEAN", lambda v: bool(int(v)))

    return db

 def _new_database(self):
     """
 create and connect to a new sqlite database. 
 raise an error if there already is a database in place, asking the
 user to manually remove the database (for safety reasons)
 """
     # TODO: remove next two lines after testing -> don't automatically remove
     if os.path.exists(self.database):
         os.remove(self.database)
     if os.path.exists(self.database):
         message = "Database already exists, please remove manually: %s" % self.database
         logger.error(message)
         raise IOError(message)
     else:
         logger.info("Database not found, creating database %s" % self.database)
         try:
             self.connection = sqlite3.connect(
                 self.database, detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES
             )
         except:
             message = "Failed to create database: %s" % self.database
             logger.error(message)
             raise sqlite3.OperationalError(message)  # re-raise error
         self._create_dbstructure()
         sqlite3.register_adapter(bool, int)
         sqlite3.register_converter("BOOLEAN", lambda v: bool(int(v)))
         # tuples
         self.connection.row_factory = sqlite3.Row

    def _sqlite3(self, name):
        """Open/create a sqlite3 DB file"""
        def dict_factory(cursor, row):
            d = {}
            for idx, col in enumerate(cursor.description):
                d[col[0]] = row[idx]
            return d
        def converter(data):
            return json.loads(data.decode('utf-8'))

        sqlite3.register_adapter(list, json.dumps)
        sqlite3.register_adapter(dict, json.dumps)
        sqlite3.register_converter("json", converter)
        conn = sqlite3.connect(self.name,
                    detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES,
                    isolation_level=None)
        conn.row_factory = dict_factory
        sqlscript = """
            create table if not exists doit (
                task_id text not null primary key,
                task_data json
            );"""
        try:
            conn.execute(sqlscript)
        except sqlite3.DatabaseError as exception:
            new_message = (
                'Dependencies file in %(filename)s seems to use '
                'an bad format or is corrupted.\n'
                'To fix the issue you can just remove the database file(s) '
                'and a new one will be generated.'
                'Original error: %(msg)s'
                % {'filename': repr(self.name), 'msg': str(exception)})
            raise DatabaseException(new_message)
        return conn

    def compute_allpairs_sim(self, interact_type, data_type):
        mat_sim = compute_allpairs_sim_mat(self.get_nodes_list(should_have_interactions=True),
                interact_type, data_type)
        # think of transactions, prepared statements, without rowid, pragma etc. 
        import numpy as np
        for dt in (np.int64, np.int32):
            sqlite3.register_adapter(dt, long)
       
        conn = sqlite3.connect(':memory:')
        c = conn.cursor()
        print "Starting inserting data"
        """
        c.execute('''CREATE TABLE stocks
                         (i integer, j integer, value real, PRIMARY KEY(i,j))''')
        oneM = 1000000
        for i in range(0,318000000,oneM):
            print "Done", i
            c.executemany("INSERT INTO stocks VALUES (?, ?, ?)", izip(mat_sim.row[i:i+oneM], mat_sim.col[i:i+oneM], mat_sim.data[i:i+oneM]))
        """
        c.execute('''CREATE TABLE stocks
                         (ij integer PRIMARY KEY, value real)''')
        oneM = 1000000
        for i in range(0,318000000,oneM):
            print "Done", i
            #TODO CAUTION: this is a hack. Make sure to multiply by atleast 32 and control for overflow:would not work for 300k data 
            row_64 = mat_sim.row[i:i+oneM].astype("int64")
            IJ = (row_64<<32)+ mat_sim.col[i:i+oneM]
            c.executemany("INSERT INTO stocks VALUES (?, ?)", izip(IJ, mat_sim.data[i:i+oneM]))
        conn.commit()
        print "Inserted data"

        #conn.close()
        self.sim_mat = conn
        return self.sim_mat

def main():
    # 2012 Illinois ACS PUMS file with NO pre-processing.
    filename = 'ss12pil.csv'

    # OnePerson class from week 3, store the 100th row in the object.
    p = OnePerson([])
    p.read_line(filename, 100)

    # tell sqlite3 library that we will be using our custom functions
    # adapt_person and convert_person as adapter and converter, respectively.
    sqlite3.register_adapter(OnePerson, adapt_person)
    sqlite3.register_converter('person', convert_person)

    # use memory for testing, you can change it to a filename and store it
    # note that we are using DECLARED TYPES.
    conn = sqlite3.connect(':memory:', detect_types = sqlite3.PARSE_DECLTYPES)
    cur = conn.cursor()

    # call our custom functions.
    create_table(cur)
    insert_person(cur, p)
    print_head(cur)

    # close connection.
    cur.close()
    conn.close()

 def __init__(self, database=None, **kwargs):
     if database is None:
         database = config.sqlite_database
     self.database = database
     self.conn = sqlite3.connect(database)
     sqlite3.register_adapter(numpy.ndarray, adapt_ndarray)
     with self.conn:
         self.conn.execute(
             """CREATE TABLE IF NOT EXISTS entries (
                                uuid TEXT NOT NULL,
                                time INT NOT NULL,
                                "key" TEXT NOT NULL,
                                value,
                                PRIMARY KEY(uuid, time, "key")
                              );"""
         )
         self.conn.execute(
             """CREATE TABLE IF NOT EXISTS status (
                                uuid TEXT NOT NULL,
                                "key" text NOT NULL,
                                value,
                                PRIMARY KEY(uuid, "key")
                              );"""
         )
     self.status = SQLiteStatus(self)
     super(SQLiteLog, self).__init__(**kwargs)

def populate_data(db_path, data_file_path):
    print 'populate data from [{}] ...'.format(data_file_path),
    sql = '''
INSERT INTO [bills]
            (
                [bill_date],
                [peak_time_power],
                [valley_time_power],
                [peak_time_price],
                [valley_time_price],
                [price]
            )
     VALUES (?, ?, ?, ?, ?, ?)
'''
    db = sqlite3.connect(db_path)

    def adapter_Decimal(d):
        return str(d)

    sqlite3.register_adapter(decimal.Decimal, adapter_Decimal)

    with open(data_file_path) as f:
        for line in f:
            line = line.strip()
            if line[0] == '#':
                continue
            args = line.split(',')
            args = [i.strip() for i in args]
            args[0] = datetime.date(*map(int, args[0].split('-')))
            args[1:3] = map(int, args[1:3])
            args[3:] = map(decimal.Decimal, args[3:])
            db.execute(sql, args)
    db.commit()
    db.close()
    print 'done'