Python db.DB類代碼示例

class FileManager(object):

    def __init__(self):
        self.db = DB()

    def add_files(self, files):
        #  sql = 'insert into songs (name, path) values '
        sql = 'insert into songs (name, path, title, album, artist, genre, track, date) values'
        for file in files:
            info = cp_get_metadata_py(file.decode('utf-8').encode('utf-8'))
            file_name = file.rsplit('/')[-1]
            info['name'] = file_name
            info['path'] = file
            sql += '("{name}", "{path}", "{title}", "{album}", "{artist}", "{genre}", "{track}", "{date}"),'.format(**info)
        sql = sql[:-1]
        self.db.execute(sql)

    def del_files(self, ids):
        """
        require: ids: list, tuple, set
        """
        if not ids:
            return
        query = str(tuple(ids))
        if len(ids)<2:
            query = query.replace(',', '')
        sql = 'delete from songs where id in {0}'.format(query)
        self.db.execute(sql)

    def save_stats(self):

        n_data = len(self.p)        
        n_good = len( np.where( self.p == 0 )[0] )
        self.accuracy = float(n_good)/n_data

        print '------data.save_stats-----'
        print 'accuracy:', self.accuracy


        Utility.report_status('.', '.')
        for entry in self.entries:
            i = np.arange( entry.offset, entry.offset+entry.length )
            #y = self.y[ i ]
            p = self.p[ i ]
            n_data = len(p)
            n_good = len( np.where( p == 0 )[0] )
            score = 0.0 if n_good == 0 else float(n_good)/n_data

            #print np.bincount( self.p ), np.bincount( p ), n_good
            #print len(p), '/', len(self.p)

            DB.storeTrainingScore( self.project.id, entry.name, score )
            Utility.report_status('%s'%(entry.name), '%.2f'%(score))
            #print 'image (%s)(%.2f)'%(entry.name, score)
        Utility.report_status('.', '.')

def configure():
    if os.path.isfile(logging_config_file):
        with open(logging_config_file, 'rt') as f:
            config = json.load(f)
            logging.config.dictConfig(config)

    logger = logging.getLogger(__name__)
    logging.getLogger("urllib3").setLevel(logging.WARNING)
    logging.getLogger("requests").setLevel(logging.WARNING)
    sys.modules[__name__].__dict__['logger'] = logger

    smtp_handler = logging.getLogger().handlers[2]
    assert isinstance(smtp_handler, logging.handlers.SMTPHandler)
    config_smtp_handler(smtp_handler)

    if restart:
        DB.drop_qa_collections()

    validate_config()

    if not validate_cookie(test_cookie):
        logger.error("invalid cookie")

    def handle_exception(exc_type, exc_value, exc_traceback):
        if issubclass(exc_type, KeyboardInterrupt):
            sys.__excepthook__(exc_type, exc_value, exc_traceback)
            return

        logger.critical("Uncaught exception",
                        exc_info=(exc_type, exc_value, exc_traceback))

    sys.excepthook = handle_exception

	def hashtags(self):
		db=DB()
		#Query definition
		query = 'SELECT c."idChannel", c."channelNumber", c."channelName", ch."channelHashtagText" \
		FROM "tbl_Channel" c JOIN "tbl_ChannelHashtag" ch \
		ON c."idChannel"=ch."idChannel";'
		registros=db.readDB(query)

		#Local Array
		hashTags=[]

		#Reorganize DB response
		i=0
		add=1
		while i<len(registros):
			idChannel=registros[i][0]
			channelNumber=registros[i][1]
			channelName=registros[i][2]
			ht=registros[i][3]
			if registros[i][0]==registros[i-1][0] and i!=0:
				row.append(registros[i][3])
				add=0
			else:
				row = [i, idChannel, channelNumber, channelName, ht]
				add=1
			if add:
				hashTags.append(row)
			i+=1
		return hashTags

	def channelRank(self, idTimeSlice='SELECT last_value FROM "tbl_Timeslice_idTimeslice_seq"'):
		db=DB()
		query='SELECT count(*) "channelNewTwits", t."idChannel", c."channelName", c."channelNumber", c."channelImage" \
			FROM "tbl_Twit" t JOIN "tbl_Channel" c \
			ON t."idChannel" = c."idChannel" \
			JOIN "tbl_ChannelHashtag" ch \
			ON c."idChannel" = ch."idChannel" \
			AND ch."channelHastagEnabled"=TRUE \
			AND t."idTimeslice" = (%s)-1 \
			GROUP BY t."idChannel", c."channelName", c."channelNumber", c."channelImage" \
			ORDER BY "channelNewTwits" desc \
			LIMIT 10;' % (idTimeSlice)
		rank=db.readDB(query)
		i=0
		print '\n\n*********************'
		print '* Trending Channels *'
		print '*********************\n\n'
		while i<len(rank):
			twits=rank[i][0]
			idChannel=rank[i][1]
			channelName=rank[i][2]
			channelNumber=rank[i][3]
			print '%d) %s %s' % (i+1, channelName, channelNumber)
			i+=1
		print '\n\n'
		return rank

    def add_congregation(self):
        """
        Prepares user entered data for a new congregation before sending it to
        the db module for insertion into the database.
        """

        values = (self.name,
                  self.phone,
                  self.email,
                  self.street,
                  self.city,
                  self.state,
                  self.zip,
                  self.week,
                  self.time,
                  self.long,
                  self.lat,
                  self.note,
                  self.visibility)

        # REVIEW long and lat: Leading zeros may be removed.

        dup_congregation = Congregation.__check_for_dup(self, values[0])
        missing_fields = Congregation.__check_required_fields(self)

        if dup_congregation == "Passed" and missing_fields == "Passed":
            DB.add_item(None, 'Congregation', Congregation.columns, values)
        else:
            if dup_congregation != "Passed":
                print("A duplicate entry was found: {}".format(
                    dup_congregation[1]))
            else:
                print("A required field was missing: {}".format(
                    missing_fields[1]))

class AddEvent(tornado.web.RequestHandler):
	def initialize(self):
		self.webdatabase = DB()
	def get(self):
		token    = self.get_argument('token', None)
		platform = self.get_argument('platform', None)
		low      = self.get_argument('low', None)
		high     = self.get_argument('high', None)
		language = self.get_argument('language', '')
		if self.webdatabase.exists(token, platform):
			d = dict()
			if low:
				d['low'] = float(low)
			if high:
				d['high'] = float(high)
			if language:
				d['language'] = language
			try:
				self.webdatabase.update(token, platform, d)
				self.write('success')
			except Exception as e:
				self.write(str(e))
		else:
			if not low:
				low = -1.0
			if not high:
				high = -1.0
			try:
				self.webdatabase.insert(token, platform, low, high, language)
				self.write('success')
			except Exception as e:
				self.write(str(e))

    def getuuid(self, projectId, imageId, guid):
        data = {}
        project = DB.getProject( projectId )
        task = DB.getImage( projectId, imageId )

        expiration = project.syncTime*4

        if task.annotationLockId == guid:
            data['uuid'] = DB.lockImage( projectId, imageId )
            now = datetime.now()
            annotationTime = datetime.strptime(task.annotationTime, '%Y-%m-%d %H:%M:%S')
            diff = now - annotationTime
            print 'diff: ', diff.total_seconds()
        elif task.annotationStatus == 1:
            now = datetime.now()
            annotationTime = datetime.strptime(task.annotationTime, '%Y-%m-%d %H:%M:%S')
            diff = now - annotationTime
            diff = diff.total_seconds()
            print 'time diff:', diff
            if diff > expiration:
                data['uuid'] = DB.lockImage( projectId, imageId )
        else:
            data['uuid'] = DB.lockImage( projectId, imageId )

        return Utility.compress(json.dumps( data ))

    def __init__(self, file_list_projects):
        self.target_folders = str(time.time())

        # Creating folder for the processes logs
        self.logs_folder   = os.path.join(self.PATH_logs,self.target_folders)
        if os.path.exists( self.logs_folder ):
            logging.error('Folder [%s] already exists!' % self.logs_folder )
            sys.exit(1)
        else:
            os.makedirs(self.logs_folder)

        # Create folder for processes output
        self.output_folder = os.path.join(self.PATH_output,self.target_folders)
        if os.path.exists( self.output_folder ):
            logging.error('Folder [%s] already exists!' % self.output_folder )
            sys.exit(1)
        else:
            os.makedirs(self.output_folder)

        # Logging code
        FORMAT = '[%(levelname)s] (%(asctime)-15s) %(message)s'
        logging.basicConfig(level=logging.DEBUG,format=FORMAT)
        file_handler = logging.FileHandler( os.path.join(self.logs_folder,'tokenizer.log') )
        file_handler.setFormatter(logging.Formatter(FORMAT))
        logging.getLogger().addHandler(file_handler)

        self.read_config()

        db = DB('pribeiro','CPP','pass',logging)
        logging.info('Database \''+self.DB_name+'\' successfully initialized')
        db.close()

        self.proj_paths = self.read_file_paths(file_list_projects)

    def __init__(self,pollId=None):
        DB.__init__(self)
        table = 'autoid'
        if pollId:
            table = 'autoid_%s'%pollId

        self.table = self.database[table]

class XPriceHistory(XBase):
    def __init__(self):
        super(XPriceHistory, self).__init__()
        self.db=DB()
        self.days=[]
        self.ranges =[]
        for i in self.cfg.readlines():
            i = i.rstrip()
            if i:
                print "%s"%i
                (day,value) = i.split("=")
                self.days.append(timedelta(days=int(day)))
                self.ranges.append([int(x) for x in value.split(',')])
    def filter(self,strs):
        return True

    def format(self,str_list):
        """
        str_list symbol date ....
        format return list[x1,x2,x3...]
        """
        rlist=[]
        if(not self.filter(str_list)):
            return None

        symbol=str_list[0]
        date = str_list[1]
        current = self.db.get_pre(symbol,date)
        if None == current:
            logging.error("No current data symbol %s date %s"%(symbol,date))
            return None

        i = 0
        for days_interval in self.days:
            pre_day = date - days_interval
            pre_one = self.db.get_pre(symbol, pre_day)

            if None == pre_one:
                logging.error("No next one data symbol %s date %s, pre_day %s"%(symbol,date,pre_day))
                return None

            # long time no trade
            if(date  - pre_one[1] >
                   (days_interval*2 if days_interval > timedelta(days=2) else timedelta(days=3))):
                logging.error("Too long before trade symbol %s date %s, pre_day %s\n current %s \n pre one%s " %
                              (symbol,date,pre_day,current,pre_one))
                return None

            perfor = self.get_performance(pre_one,current,self.ranges[i])
            rlist.append(perfor)
            i = i+1

        return rlist

    def get_performance(self, pre, next_one,criteria):
        a = 100*(next_one[7] - pre[7])//pre[7]
        for i in range(0,len(criteria)):
            if a < criteria[i]:
                return i
        return len(criteria)

class ChromaTest(unittest.TestCase):

    def setUp(self):
        logging.basicConfig(level=logging.DEBUG)
        conf = MockConf(dbstring="sqlite://")
        self.db = DB(conf)
        self.db.create_all()
        self.sess = self.db.session()
        self.sess.add_all(Region.create_from_json(TEST_LANDS))

        self.sess.commit()
        # Create some users
        self.alice = self.create_user("alice", 0)
        self.bob = self.create_user("bob", 1)

    def create_user(self, name, team):
        newbie = User(name=name, team=team, loyalists=100, leader=True)
        self.sess.add(newbie)
        cap = Region.capital_for(team, self.sess)
        newbie.region = cap
        self.sess.commit()
        return newbie

    def get_region(self, name):
        name = name.lower()
        region = self.sess.query(Region).filter_by(name=name).first()
        return region

def valveJob(setting): #(valve, onDuration)
    print 'OPENING VALVE'
    tft.markActiveJob(setting['id'], True);
    durationLeft = int(setting['on_duration']) + 2
    #binaryValveList = map(int, list(format(setting['valve'], '08b')))
    #print binaryValveList
    pump = Relay()
    pump.on()
    time.sleep(1)
    #valves = Shiftregister()
    #shiftreg.outputList(binaryValveList)
    valves.outputDecimal(setting['valve'])
    #valves.enable()
    while durationLeft > 2:
        time.sleep(1)
        durationLeft -= 1
        print 'TIME LEFT: %i' % (durationLeft - 1)
    print 'CLOSING VALVE'
    pump.off()
    print 'reset shift register 1'
    #valves.disable()
    valves.reset()
    time.sleep(1)

    #valves.reset()
    tft.markActiveJob(setting['id'], False);
    db = DB()
    db.addLogLine(setting, datetime.now())
    return

    def populate_list(self):
        """Populates the talk_list widget with the outlines

        Format of outline_list: [(DB ID, number, title, visibility), ...]
        """
        db = DB()
        self.table_outline.clearContents()
        sql_number_sort = "SELECT * FROM Talk WHERE visibility='True' ORDER " \
                          "BY CAST (number AS INTEGER)"
        sql_title_sort = "SELECT * FROM Talk WHERE visibility='True' ORDER BY" \
                         " title ASC"

        if self.radio_number.isChecked():
            outline_list = DB.return_sql(None, sql_number_sort)
        else:
            outline_list = DB.return_sql(None, sql_title_sort)

        self.table_outline.setColumnCount(2)
        self.table_outline.setRowCount(db.count_rows('Talk', True))
        self.sorted_list = []  # Table IDs of items added sorted to the table

        index = 0  # Index of table_outline widget
        for item in outline_list:
            number = QtGui.QTableWidgetItem(item[1])
            title = QtGui.QTableWidgetItem(item[2])
            self.table_outline.setItem(index, 0, number)
            self.table_outline.setItem(index, 1, title)
            self.sorted_list.append(item[0])
            index += 1

    def add_brother(self):
        """
        Adds a new brother to the database.

        :return:
        """

        values = (self.first_name,
                  self.middle_name,
                  self.last_name,
                  self.email,
                  self.phone,
                  self.congregation,
                  self.responsibility,
                  self.speaker,
                  self.chairman,
                  self.coordinator,
                  self.note,
                  self.visibility)

        missing_fields = Brother.__check_required_fields(self)

        if missing_fields == "Passed":
            DB.add_item(None, 'Brother', Brother.columns, values)
        else:
            print("The following are missing: ", missing_fields)