def itemlist(self,index):
     Zone = self.w.Place.itemText(index)
     database_name = parseOutputconf()['spatialitedb']
     db_connection = None
     try :
         db_connection = sqlite3.connect(database_name)
     except :
         self.worningmessage('spatialitedb not found')
     if db_connection is not None:
         db_connection = sqlite3.connect(database_name)
         db_cursor = db_connection.cursor()
         try :
             listatabelle = db_cursor.execute("SELECT name,latitude,longitude FROM %s ;" % (Zone))
             tabelle = listatabelle.fetchall()
             tablelist = []
             allist = []
             for i in tabelle:
                 tablelist.append(i[0])
                 allist.append(i[0]+' '+str(i[1])+' '+str(i[2]))
             allist.sort()
             tablelist.sort()
             self.w.placezone.clear()
             self.w.placezone.addItems(allist)
             db_connection.commit()
         except :
             print 'reload sqlite'

def connect_db(db_file):
    """
    连接数据库
    """
    if os.path.isfile(db_file):
        if int(time.time()) - int(os.stat(db_file).st_mtime) >= 86400:
            cx = sqlite.connect(db_file)
            cu = cx.cursor()
            return (cu,cx,"old")
        else:
            cx = sqlite.connect(db_file)
            cu = cx.cursor()
            return (cu,cx,"nothing")
    else:
        #create
        cx = sqlite.connect(db_file)
        cu = cx.cursor()
        cu.execute('''create table stock(
            id integer primary key,
            s_date varchar(50),
            s_open varchar(10),
            s_high varchar(10),
            s_low varchar(10),
            s_close varchar(10),
            s_volume INTEGER
            )''')
        return (cu,cx,"new")

    def __init__(self, path, params={}):
        assert have_pysqlite > 0
        self.cnx = None
        if path != ':memory:':
            if not os.access(path, os.F_OK):
                raise TracError, u'Base de données "%s" non trouvée.' % path

            dbdir = os.path.dirname(path)
            if not os.access(path, os.R_OK + os.W_OK) or \
                   not os.access(dbdir, os.R_OK + os.W_OK):
                from getpass import getuser
                raise TracError, u"L'utilisateur %s a besoin des permissions " \
                                 u"en lecture _et_ en écriture sur la base de " \
                                 u"données %s ainsi que sur le répertoire " \
                                 u"dans lequel elle est située." \
                                 % (getuser(), path)

        if have_pysqlite == 2:
            self._active_cursors = weakref.WeakKeyDictionary()
            timeout = int(params.get('timeout', 10.0))
            cnx = sqlite.connect(path, detect_types=sqlite.PARSE_DECLTYPES,
                                 check_same_thread=sqlite_version < 30301,
                                 timeout=timeout)
        else:
            timeout = int(params.get('timeout', 10000))
            cnx = sqlite.connect(path, timeout=timeout, encoding='utf-8')
            
        ConnectionWrapper.__init__(self, cnx)

    def __init__(self, path, log=None, params={}):
        assert have_pysqlite > 0
        self.cnx = None
        if path != ':memory:':
            if not os.access(path, os.F_OK):
                raise TracError('Database "%s" not found.' % path)

            dbdir = os.path.dirname(path)
            if not os.access(path, os.R_OK + os.W_OK) or \
                   not os.access(dbdir, os.R_OK + os.W_OK):
                raise TracError('The user %s requires read _and_ write ' \
                                'permissions to the database file %s and the ' \
                                'directory it is located in.' \
                                % (getuser(), path))

        if have_pysqlite == 2:
            self._active_cursors = weakref.WeakKeyDictionary()
            timeout = int(params.get('timeout', 10.0))
            self._eager = params.get('cursor', 'eager') == 'eager'
            # eager is default, can be turned off by specifying ?cursor=
            if isinstance(path, unicode): # needed with 2.4.0
                path = path.encode('utf-8')
            cnx = sqlite.connect(path, detect_types=sqlite.PARSE_DECLTYPES,
                                 check_same_thread=sqlite_version < 30301,
                                 timeout=timeout)
        else:
            timeout = int(params.get('timeout', 10000))
            cnx = sqlite.connect(path, timeout=timeout, encoding='utf-8')
            
        ConnectionWrapper.__init__(self, cnx, log)

    def connect_db(self, dbname=None):
        """
        Connect to a database and return the connection and cursor objects.
        """
        if dbname is None:
            conn = lite.connect(':memory')
        else:
            conn = lite.connect(dbname)
        c = conn.cursor()

        # JobEnd
        self.code_JobEnd = ['@97', '@96']
        # MR include MR and @95
        lines = c.execute("SELECT code FROM activitycode WHERE item IN ('MR', '@95')")
        self.code_MR = [item[0] for item in lines]
        # Production
        lines = c.execute("SELECT code FROM activitycode WHERE item='Prod'")
        self.code_Prod = [item[0] for item in lines]
        # Wash ups
        lines = c.execute("SELECT code, oeepoint FROM activitycode WHERE item='W-up'")
        lookup = {'ON': 1, 'OFF': -1, '': 0}
        self.code_Wup = dict([(item[0], lookup[item[1]]) for item in lines])
        # production downtime, named as Process
        pd = ('Plate','Cplate','Stock','Customer','Process','Org','Dry')
        lines = c.execute("SELECT code, oeepoint FROM activitycode WHERE oeepoint IN ('%s','%s','%s','%s','%s','%s','%s')" % pd)
        self.code_Process = dict([(item[0],item[1]) for item in lines])
        # Non-Production Downtime, named as Maintenance for historical reasons
        nonpd = ('Clean-up','Maintenance-I','Maintenance-H','Training','Nowork','Breakdown','Other')
        lines = c.execute("SELECT code, oeepoint FROM activitycode WHERE oeepoint IN ('%s','%s','%s','%s','%s','%s','%s')" % nonpd)
        self.code_Maintenance = dict([(item[0],item[1]) for item in lines])

        return conn, c

def bot_quote(mess, nick, botCmd):
    """Get quotes from database. Use !quote for a random quote, and !quote <num> for a particular quote."""
    message = None
    if (len(botCmd) == 1):
        connection = sqlite.connect(dbPath)
        cursor = connection.cursor()
        cursor.execute("SELECT rowid, * FROM quotes ORDER BY RANDOM() LIMIT 1")
        quote = cursor.fetchone()
        message = u'Epic time [' + str(quote[0]) + u']：' + quote[1] + ' (set by ' + quote[2] + ')'
    elif (len(botCmd) == 2):
        connection = sqlite.connect(dbPath)
        cursor = connection.cursor()
        cursor.execute("SELECT max(rowid) FROM quotes")
        maxQuote = cursor.fetchone()
        if (re.match(r'[0-9]+', botCmd[1])):
                if (int(botCmd[1]) <= maxQuote[0]):
                    roll = botCmd[1]
                    cursor.execute("SELECT * FROM quotes WHERE rowid =" + str(roll))
                    quote = cursor.fetchone()
                    message = u'Epic time[' + str(roll) + u']：' + quote[0] + ' (set by ' + quote[1] + ')'
                else:
                    message = 'Max quote: ' + str(maxQuote[0])
        else:
            message = 'Max quote: ' + str(maxQuote[0])
    return message

def connect_trade_db(db_file):
    """
    连接历史交易数据库
    """
    if os.path.isfile(db_file):
        #if int(time.time()) - int(os.stat(db_file).st_mtime) >= 43200:
        #time_now = int(time.time())
        #global tradedb_lastupdate_time
        #if time_now - tradedb_lastupdate_time >= 43200:
        #if time_now - tradedb_lastupdate_time >= 10:
        cx = sqlite.connect(db_file)
        cu = cx.cursor()
        #tradedb_lastupdate_time = time_now
        return (cu,cx,"old")
        #else:
        #    cx = sqlite.connect(db_file)
        #    cu = cx.cursor()
        #    return (cu,cx,"nothing")
    else:
        #create
        cx = sqlite.connect(db_file)
        cu = cx.cursor()
        cu.execute('''create table stock(
            id integer primary key,
            s_date varchar(50),
            s_open varchar(10),
            s_high varchar(10),
            s_low varchar(10),
            s_close varchar(10),
            s_volume INTEGER
            )''')
        return (cu,cx,"new")

	def __init__(self, dbfile):
		if not os.path.exists(dbfile):
			self.conn = sqlite.connect(dbfile)
			self.cursor = self.conn.cursor()
			self.create_table()
		self.conn = sqlite.connect(dbfile)
		self.cursor = self.conn.cursor()

def SqliteConnectNoArch(filename): # return either a sqlite3/2/1 connection
	if not os.path.exists(filename):
		logError("Could not find SQLite3 db file: %s" % filename)
		sys.exit(0);
	try:
		from pysqlite2 import dbapi2 as sqlite
		logInfo('Using sqlite-2')
		return sqlite.connect(filename)
	except:
		pass
#		logWarn("from pysqlite2 import dbapi2 failed")
	try:
		import sqlite3 as sqlite;
		logInfo('Using sqlite-3')
		return sqlite.connect(filename)
	except:
		pass
#		logWarn("import sqlite3 failed")
	try:
		import sqlite
		logInfo('Using sqlite-1')
		return sqlite.connect(filename)
	except:
		pass
#		logWarn("import sqlite failed")
	return None

def main(argv):
    try:
        source_file, dest_file, new_component = argv
    except ValueError:
        print "Usage: %s source.db target.db new_component" % os.path.basename(sys.argv[0])
        return 1

    # connect to databases
    source_conn = sqlite.connect(source_file)
    source_cur = source_conn.cursor()
    dest_conn = sqlite.connect(dest_file)
    dest_cur = dest_conn.cursor()

    qmarks = lambda seq: ",".join(["?" for r in seq])
    try:
        # go through tickets in source
        tickets = source_cur.execute("SELECT * FROM ticket;")
        for ticket in tickets:
            # delete the id column - will get a new id
            old_id = ticket[0]
            ticket = list(ticket[1:])
            # reset values of component and milestone rows
            ticket[4 - 1] = new_component  # component
            ticket[11 - 1] = None  # milestone
            # insert ticket into target db
            print "copying ticket #%s" % old_id
            dest_cur.execute(
                "INSERT INTO ticket "
                + "("
                + (",".join([f[0] for f in source_cur.description[1:]]))
                + ") "
                + "VALUES("
                + qmarks(ticket)
                + ")",
                ticket,
            )
            new_id = dest_cur.lastrowid

            # parameters: table name, where clause, query params, id column index, table repr, row repr index
            def copy_table(table, whereq, params, id_idx, trepr, rrepr_idx):
                cur = source_conn.cursor()
                try:
                    cur.execute("SELECT * FROM %s WHERE %s" % (table, whereq), params)
                    for row in cur:
                        row = list(row)
                        row[id_idx] = new_id
                        print "\tcopying %s #%s" % (trepr, row[rrepr_idx])
                        dest_cur.execute("INSERT INTO %s VALUES(%s)" % (table, qmarks(row)), row)
                finally:
                    cur.close()

            # copy ticket changes
            copy_table("ticket_change", "ticket=?", (old_id,), 0, "ticket change", 1)
            # copy attachments
            copy_table("attachment", 'type="ticket" AND id=?', (old_id,), 1, "attachment", 2)

        # commit changes
        dest_conn.commit()
    finally:
        dest_conn.close()

def main(argv=None):
    if argv is None:
        argv = sys.argv

    if  not os.path.exists(USERDETAILSDB):
        con=sqlite.connect(USERDETAILSDB)
        con.execute("create table userdetails(username varchar(100), entity varchar(10), content varchar(100) )")
        con.close()

    con = sqlite.connect(USERDETAILSDB) 
   
    if (len(argv)<2):
        print "Not enough arguments"
        return -1
    if (argv[1]=="GET"):
	#returns "username password" in cleartext. CHANGE FOR LDAP
        username=argv[2]
        try:
            res=con.execute("select * from userdetails where username=(?)", (username, ))
            found=0
            for row in res:
                found+=1
                print row[1], row[2]
            if (found==0):
                print "NOT_FOUND"
        except sqlite.Error, e:
            print "ERROR ", e.args[0]
            sys.exit()  

def create_db():
    if sqlite.version_info > (2, 0):
        if use_custom_types:
            con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_DECLTYPES|sqlite.PARSE_COLNAMES)
            sqlite.register_converter("text", lambda x: "<%s>" % x)
        else:
            con = sqlite.connect(":memory:")
        if use_dictcursor:
            cur = con.cursor(factory=DictCursor)
        elif use_rowcursor:
            cur = con.cursor(factory=RowCursor)
        else:
            cur = con.cursor()
    else:
        if use_tuple:
            con = sqlite.connect(":memory:")
            con.rowclass = tuple
            cur = con.cursor()
        else:
            con = sqlite.connect(":memory:")
            cur = con.cursor()
    cur.execute("""
        create table test(v text, f float, i integer)
        """)
    return (con, cur)

    def __init__(self, database_files={"MESH":"mesh.db", "Cell":"cell.db", 
                                       "Gene":"gene.db", 
                                       "Molecular Roles": "molecular_role.db",
                                       "Mycobacterium_Genes": "mtb_genes.db"}):

        ########################
        ## Creating Databases ##
        ########################
        #MESH database
        self.mesh_db_connection = sqlite.connect(database_files["MESH"])
        self.mesh_cursor = self.mesh_db_connection.cursor()
        self.mesh_cursor.execute("PRAGMA foreign_keys = ON;")

        #Cell database
        self.cell_db_connection = sqlite.connect(database_files["Cell"])
        self.cell_cursor = self.cell_db_connection.cursor()
        self.cell_cursor.execute("PRAGMA foreign_keys = ON;")

        #Gene database
        self.gene_db_connection = sqlite.connect(database_files["Gene"])
        self.gene_cursor = self.gene_db_connection.cursor()
        self.gene_cursor.execute("PRAGMA foreign_keys = ON;")

        #Molecular Role database
        self.molecular_roles_db_connection = sqlite.connect(database_files["Molecular Roles"])
        self.molecular_roles_cursor = self.molecular_roles_db_connection.cursor()
        self.molecular_roles_cursor.execute("PRAGMA foreign_keys = ON;")

        #Mycobacterium Gene database
        self.mtb_db_connection = sqlite.connect(database_files["Mycobacterium_Genes"])
        self.mtb_cursor = self.mtb_db_connection.cursor()
        self.mtb_cursor.execute("PRAGMA foreign_keys = ON;")

 def _getDb(self, channel):
     try:
         from pysqlite2 import dbapi2
     except ImportError:
         raise callbacks.Error, 'You need to have PySQLite installed to ' \
                                'use Karma.  Download it at ' \
                                '<http://pysqlite.org/>'
     filename = plugins.makeChannelFilename(self.filename, channel)
     if filename in self.dbs:
         return self.dbs[filename]
     if os.path.exists(filename):
         self.dbs[filename] = dbapi2.connect(filename)
         return self.dbs[filename]
     db = dbapi2.connect(filename)
     self.dbs[filename] = db
     cursor = db.cursor()
     cursor.execute("""CREATE TABLE karma (
                       id INTEGER PRIMARY KEY,
                       name TEXT,
                       normalized TEXT UNIQUE ON CONFLICT IGNORE,
                       added INTEGER,
                       subtracted INTEGER
                       )""")
     db.commit()
     def p(s1, s2):
         return int(ircutils.nickEqual(s1, s2))
     db.create_function('nickeq', 2, p)
     return db

def create_database (driver, database, username = None, password = None, hostname = None):
    if driver == 'sqlite':
        db = SqliteDatabase (database)
        return db
    elif driver == 'mysql':
        db = MysqlDatabase (database, username, password, hostname)
    elif driver == 'postgres':
        # TODO
        raise DatabaseDriverNotSupported
    else:
        raise DatabaseDriverNotSupported

    # Try to connect to database
    try:
        db.connect ().close ()
        return db
    except AccessDenied, e:
        if password is None:
            import sys, getpass

            # FIXME: catch KeyboardInterrupt exception
            # FIXME: it only works on UNIX (/dev/tty),
            #  not sure whether it's bug or a feature, though
            oldout, oldin = sys.stdout, sys.stdin
            sys.stdin = sys.stdout = open ('/dev/tty', 'r+')
            password = getpass.getpass ()
            sys.stdout, sys.stdin = oldout, oldin

            return create_database (driver, database, username, password, hostname)
        raise e

 def __init__(self, path):
     if(os.path.exists(path)):
         self.conn = sqlite.connect(path)
         self.cursor = self.conn.cursor()
     else:
         self.conn = sqlite.connect(path)
         self.cursor = self.conn.cursor()
         self._setupDB()

 def connect(self):
     if not self.connection:
         self.connection = sqlite.connect(self.path)
     try:
         cursor = self.connection.cursor()
     except:
         self.connection = sqlite.connect(self.path)
         cursor = self.connection.cursor()
     return (self.connection, cursor)

    def start(self):
        ''' Process itself '''
        self.shellCommands = []
        self.backupTime = time.localtime()
        backupDirectory = os.path.join(CM_DIR, self.machine, BACKUPS_SUBDIR, time.strftime("%Y%m%d_%H%M%S",self.backupTime))
        if self.verboseLevel : 
            print "backupDirectory = %s"  % backupDirectory

        if self.dryRun : return

        os.makedirs(backupDirectory)  

        snapshotFile = os.path.join(backupDirectory, SNAPSHOT_FILE)
        connection = sqlite.connect(snapshotFile)
        cursor = connection.cursor()
        sql = 'CREATE TABLE pvs (id INTEGER PRIMARY KEY, pvName VARCHAR(50), pvType VARCHAR(50), tolerance VARCHAR(50), subsystem VARCHAR(50), value VARCHAR(50))'
        cursor.execute(sql)

        connection = sqlite.connect(snapshotFile)
        cursor = connection.cursor()

        if self.verboseLevel :
            print "subsystems "
            print self.subsystems
        requestFiles = self.getRequestFiles(self.subsystems)
        for index, subsystem in enumerate(self.subsystems):
            requestFile = requestFiles[index]
            if self.verboseLevel: 
                print "subsystem : %s" % subsystem
                print "requestFile : %s" % requestFile
            if len(requestFile)!=0 and os.path.isfile(requestFile): 
                with open(requestFile,'r') as requestFh:
                    for line in requestFh.readlines():
                        line = line.rstrip('\n')
                        if len(line)==0 : continue
                        if self.verboseLevel : print "line: >%s<" % line
                        pvEntry = line.split(',')
                        pvName = pvEntry[COL_PVNAME]
                        pv = LEpicsPv(pvName)
                        pvValue = pv.caget()
                        pvType = self.getPvType(pvName)
                        pvTolerance = 0
                        if len(pvEntry) >= (COL_TYPE+1):      pvType = pvEntry[COL_TYPE]
                        if len(pvEntry) >= (COL_TOLERANCE+1): pvTolerance = pvEntry[COL_TOLERANCE]
                        sql = "INSERT INTO pvs VALUES (null, '%s','%s', '%d','%s','%s')" % (
                                pvName, pvType, pvTolerance, subsystem, pvValue
                            )
                        if self.verboseLevel : print sql
                        cursor.execute(sql)
                shutil.copy(requestFile,backupDirectory)
        connection.commit()
        cursor.close()
        connection.close()

        shutil.copy(self.getCminfoFile(),os.path.join(backupDirectory,CMSAVE_FILE))
        self.writeBackupMetaFile(backupDirectory,snapshotFile)

def NewDatabase(filename):
 logging.info("Database: Creating new file: %s " % filename)
 if not filename:
  logging.warning("Database: No filename supplied to the database creator.")
  return
 con=sqlite.connect(filename)
 con.close();
 con=sqlite.connect(filename);
 logging.debug("Database: Created new file: %s" % filename)
 return con;

def createDrops():
	connection = sqlite.connect('test.db')
	memoryConnection = sqlite.connect(':memory:')
	cursor = connection.cursor()

	print "Please write what drops you want"
	drop = raw_input("Drop: ")
	cursor.execute('INSERT INTO STASH VALUES (null, ?, 0)', (drop,))
	connection.commit()
	return createDrops()