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 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 register_types(self):
     sqlite.register_adapter(dt.datetime, self.adapt_datetime)
     sqlite.register_adapter(dict, self.adapt_json)
     sqlite.register_adapter(list, self.adapt_json)
     sqlite.register_adapter(tuple, self.adapt_json)
     sqlite.register_converter('datetime', self.convert_datetime)
     sqlite.register_converter('json', self.convert_json)

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 __prepare_db(self, dbfile):
        (DB_SOURCE, DB_HOST, DB_PORT, DB_USERNAME, DB_PASSWORD, DB_DATABASE) = dbfile
        self.DB_SOURCE = DB_SOURCE

        if DB_SOURCE == "sqlite":
            self.IntegrityError = sqlite3.IntegrityError
            sqlite3.register_converter("pickle", cPickle.loads)
            self.dbcon = sqlite3.connect(DB_HOST, detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
            self.dbcon.text_factory = unicode  # This is the default, but we set it explicitly, just to be sure.
            self.dbcur = self.dbcon.cursor()
            self.dbcur.execute(
                "CREATE TABLE IF NOT EXISTS %s(id INTEGER PRIMARY KEY AUTOINCREMENT, item pickle, item_key CHAR(32))"
                % (self.table)
            )
            self.dbcur.execute("CREATE UNIQUE INDEX IF NOT EXISTS unique_key ON %s (item_key)" % (self.table))
        elif DB_SOURCE == "mysql":
            self.IntegrityError = MySQLdb.IntegrityError
            self.dbcon = MySQLdb.connect(
                host=DB_HOST, port=DB_PORT, user=DB_USERNAME, passwd=DB_PASSWORD, db=DB_DATABASE, charset="utf8"
            )
            self.dbcur = self.dbcon.cursor()
            self.dbcur.execute(
                "CREATE TABLE IF NOT EXISTS %s(id INT NOT NULL AUTO_INCREMENT, item TEXT, item_key VARCHAR(32), PRIMARY KEY (id), UNIQUE INDEX unique_key (item_key))"
                % (self.table)
            )
        else:
            self.logger.error("Invalid DB_SOURCE detected")

        self.dbcon.commit()

    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 __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 __init__(self, path):
     # Constants
     self.FORMATS = {}
     self.FORMATS['runtime'] = [('item','TEXT'), ('value','TEXT')]
     self.FORMATS['conf'] = [('sec','TEXT'), ('opt','TEXT'), 
         ('val', 'TEXT')]
     self.FORMATS['io'] = [('hid','INTEGER'), ('pid','INTEGER'),
         ('tid','INTEGER'), ('fsize', 'INTEGER'), ('bsize', 'INTEGER'),
         ('elapsed', 'BLOB'), ('sync', 'REAL'),
         ('agg', 'REAL'), ('aggnoclose', 'REAL'),
         ('opavg', 'REAL'), ('opmin', 'REAL'), ('opmax', 'REAL'),
         ('opstd', 'REAL')]
     self.FORMATS['meta'] = [('hid','INTEGER'), ('pid','INTEGER'),
         ('tid','INTEGER'), ('opcnt', 'INTEGER'), ('factor', 'INTEGER'),
         ('elapsed', 'BLOB'), ('sync', 'REAL'),
         ('agg', 'REAL'), ('opavg', 'REAL'),
         ('opmin', 'REAL'), ('opmax', 'REAL'), ('opstd', 'REAL')]
     self.FORMATS['aggdata'] = [('hostid','INTEGER'), ('pid','INTEGER'),
         ('tid','INTEGER'), ('oper','TEXT'), ('optype', 'INTEGER'), 
         ('min','REAL'), ('max','REAL'), ('avg','REAL'), ('agg','REAL'), 
         ('std','REAL'), ('time', 'REAL')]
     
     self.FORMATS_LEN = {}
     for k, v in self.FORMATS.items():
         self.FORMATS_LEN[k] = len(self.FORMATS[k])
     
     sqlite3.register_converter("BLOB", lambda s:cPickle.loads(str(s)))
     sqlite3.register_adapter(list, cPickle.dumps)
     sqlite3.register_adapter(dict, cPickle.dumps)
     self.db = sqlite3.connect(path, detect_types=sqlite3.PARSE_DECLTYPES)
     self.cur = self.db.cursor()
     self.tables = []    # all tables in database

 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 add_to_base():
    """
    add_to_base()
    
    This function get news array and forms the database. The main function.
    """
    r = get_news()
    if (r.status_code == requests.codes.OK):
        news = json.loads(r.content)
    else:
        print r.headers
        return -1
    sqlite3.register_converter("json", json.loads)
    db = sqlite3.connect(db_name, detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
    cur = db.cursor()
    cur.execute('''create table if not exists RSS (id INTEGER PRIMARY KEY AUTOINCREMENT, Date, Author, EventType, Summary json)''')
    cur.execute('''SELECT max(id),Date FROM RSS''')
    record = cur.fetchall()
    if  (not record[0][0]): 
        last_date_from_db = ""
    else:
        last_date_from_db = record[0][1]
    count = 0
    while (count < len(news) and news[count]['created_at'] > last_date_from_db):
        count += 1
    while (count > 0):
        count -= 1
        summary = json.dumps(news[count])
        cur.execute('''insert into RSS (id, Date, Author, EventType, Summary) VALUES (NULL,?,?,?,?)'''\
        , (news[count]['created_at'], news[count]['actor']['login'], news[count]['type'], summary))        
    db.commit()
    db.close 

 def __prepare_db(self, dbfile):
     sqlite3.register_converter("pickle", cPickle.loads)
     self.dbcon = sqlite3.connect(dbfile, detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
     self.dbcon.text_factory = unicode # This is the default, but we set it explicitly, just to be sure.
     self.dbcur = self.dbcon.cursor()
     self.dbcur.execute("CREATE TABLE IF NOT EXISTS %s(id INTEGER PRIMARY KEY AUTOINCREMENT, item pickle)" % (self.table))
     self.dbcon.commit()

    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 prepdb(self, dbname, debug=False):
     self.database = sqlite3.connect(database=dbname, timeout=1.0,detect_types=sqlite3.PARSE_DECLTYPES)
     sqlite3.register_converter('BOOL',convert_bool)
     self.database.create_function('distance', 2, absDist)
     self.database.create_function('bearing', 2, absBear)
     self.database.row_factory = sqlite3.Row
     cur = self.database.cursor()
     try:
         cur.execute("select version from version")
         row = cur.fetchone()
         vnum = row[0]
         vname = 'statements_v%03d' % vnum
         logging.debug('Database version name is: %s' % vname)
         for stgrp in self.allstatements[self.allstatements.index(vname)+1:]:
             stmts = Database.__dict__[stgrp]
             self.sqlexec(stmts)
             vnum = int(stgrp[-3:])
             cur.execute("UPDATE version SET version=?", (vnum, ))
             self.database.commit()
         logging.debug('Database version is now %i' % vnum)
     except sqlite3.OperationalError:
         logging.debug('Trying database build from scratch')
         for stgrp in self.allstatements:
             stmts = Database.__dict__[stgrp]
             self.sqlexec(stmts)
             vnum = int(stgrp[-3:])
             cur.execute("UPDATE version SET version=?", (vnum, ))
             self.database.commit()
         logging.debug('Database version is now %i' % vnum)

 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 _connect(self):
     self._database = self._db_args["db"]
     sqlite3.register_converter("DATE", adapt_converter)
     self._conn = sqlite3.connect(self._database, detect_types=sqlite3.PARSE_DECLTYPES)
     self._conn.isolation_level = None
     # self._conn.row_factory = dict_factory
     self._cursor = self._conn.cursor()

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 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 __init__(self, fname):
     import sqlite3
     self.db = sqlite3.connect(fname,
                 detect_types = sqlite3.PARSE_DECLTYPES)
     sqlite3.register_converter('timestamp', convert_datetime)
     ## @todo remove self.cursor
     self.cursor = self.db.cursor()

 def create_db(self):
     """ Check if the database is stored in the path """
     if config.create_config_dir(self.user_path):
         self.conn = sqlite3.connect(self.user_path + self.filename,
                                     detect_types=sqlite3.PARSE_DECLTYPES)
         sqlite3.register_adapter(bool, int)
         sqlite3.register_converter("BOOLEAN", lambda v: int(v) != 0)