Python DB.commit方法代碼示例

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import commit [as 別名]
def get_ugc(county, state):
    '''
    Return the UGC code(s) for the given county/state.

    For more info on UGC format, see: http://www.nws.noaa.gov/emwin/winugc.htm
    '''
    try:
        db = DB()
        db.execute("SELECT state,zone FROM ugc WHERE countyname = '%s' AND state = '%s';" % (county, state))
        ugc = []
        if db.cur is not None:
            for record in db.cur:
                code = record[0] + 'Z' + record[1]
                ugc.append(code)
        db.commit()
    except Exception as e:
        print e
        db.rollback()

    if len(ugc) > 0:
        print "UGC(s) found for %s, %s:" % (county, state)
        ugc = ','.join(ugc)
    else:
        print "UGC(s) not found for %s, %s" % (county, state)
        ugc = None

    return ugc

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import commit [as 別名]
	def partRepair(self, nntp, groupArr):
		n = self.n

		mdb = DB()
		missingParts = mdb.query("SELECT * FROM partrepair WHERE groupID = %s AND attempts < 5 ORDER BY numberID ASC LIMIT %s", (groupArr['ID'], self.partrepairlimit))
		partsRepaired = partsFailed = 0

		if len(missingParts) > 0:
			print 'Attempting to repair %d parts.' % len(missingParts)

			# loop through each part to group into ranges
			ranges = dict()
			lastnum = lastpart = 0
			for part in missingParts:
				if lastnum+1 == part['numberID']:
					ranges[lastpart] = part['numberID']
				else:
					lastpart = part['numberID']
					ranges[lastpart] = part['numberID']
				lastnum = part['numberID']

			num_attempted = 0

			# download missing parts in ranges.
			for partfrom, partto in ranges.iteritems():
				self.startLoop = time.time()

				num_attempted += partto - partfrom + 1
				# print some output here

				# get article from newsgroup
				nntp.group(groupArr['name'])
				self.scan(nntp, groupArr, partfrom, partto, 'partrepair')

				# check if articles were added
				if partfrom == partto:
					articles = partfrom
				else:
					articles = ','.join("%d" % i for i in range(partfrom, partto))
	
				sql = "SELECT pr.ID, pr.numberID, p.number from partrepair pr LEFT JOIN parts p ON p.number = pr.numberID WHERE pr.groupID=%s AND pr.numberID IN (%s) ORDER BY pr.numberID ASC"

				result = mdb.queryDirect(sql, (groupArr['ID'], articles))
				for r in result:
					if r['number'] == r['numberID']:
						partsRepaired += 1
						# article was added, delete from partrepair
						mdb.query('DELETE FROM partrepair WHERE ID=%s', (r['ID'],))
					else:
						partsFailed += 1
						# article was not added, increment attempts:
						mdb.query('UPDATE partrepair SET attempts=attempts+1 WHERE ID = %s', (r['ID'],))

			print '%d parts repaired.' % (partsRepaired)

		# remove articles that we can't fetch after 5 attempts
		mdb.query('DELETE FROM partrepair WHERE attempts >= 5 AND groupID = %s', (groupArr['ID'],))
		mdb.commit()

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import commit [as 別名]
def update_cmd(update_type = "AppData"):
	print("Updating " + update_type)

	_set_env_variables()
	
	db = DB(DB_LOCATION)

	metadata = parseFDroidRepoData()
	
	for app in metadata.keys():
		if is_app_valid(metadata[app]):
			db.update_app(metadata[app], commit_on_call = False)

	db.commit()

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import commit [as 別名]
def read_git_history(metadata):

	db = DB(DB_LOCATION)

	repos = os.listdir(GIT_CLONE_LOCATION)

	repos.remove('fdroiddata')
	print("Starting git history collection")
	commit_metadata = {}

	count = 1
	total_count = len(repos)
	for package_name in repos:
		print_processing(count, total_count)
		path = GIT_CLONE_LOCATION + "/" + package_name
		#print(path)

		history = getGitHistory(path)
		history = codecs.decode(history, "utf-8", "ignore") #Ignore any errors

		# Regex to identify commit
		commit_regex = re.compile("(.*\n.*\n)", re.UNICODE)

		# Regex for each item in the commit line
		fields_regex = re.compile("(^.*)( .*)( <.*>)( \d*)(\s*.*)", re.UNICODE)
		commits = []
		for commit_line in commit_regex.findall(history):
			fields = fields_regex.findall(commit_line)

			if len(fields) == 0:
				continue

			commit_data = {}

			fields = fields[0]

			commit_data["commit"] 	= fields[0]
			commit_data["author"] 	= fields[1]
			commit_data["email"]	= fields[2]
			commit_data["time"] 	= fields[3]
			commit_data["summary"] 	= fields[4].strip()

			commits.append(commit_data)

			db.add_commit_item(metadata[package_name], commit_data, commit_on_call=False)

		commit_metadata[package_name] = commits
		count += 1
	
	db.commit()

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import commit [as 別名]
def get_fips(county,state):
    '''
    Return the FIPS6 county code for the given county/state.
    '''
    fips = None
    try:
        db = DB()
        db.execute("SELECT fips FROM fips WHERE countyname = '%s' AND state = '%s';" % (county, state))
        fips = db.cur.fetchone()[0]
        db.commit()
        print "FIPS %s found for %s, %s" % (fips, county, state)
    except Exception as e:
        print "FIPS not found for %s, %s" % (county, state)
        print e
        db.rollback()
    return fips

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import commit [as 別名]
def init_cmd():
	print("Downloading all metadata and source control repositories")
	dev_mode = False

	if len(sys.argv) >= 3:
		print("Running in production mode")
		#if not(len(sys.argv) == 4 and (sys.argv[3] == "-q" or sys.argv[3] == "--quiet")):
			#input("Press enter to continue (Crt+C to Cancel)")
	else:
		print("Running in development mode, no source control repos cloning")
		dev_mode = True

	_set_env_variables()

	# Only print stats in dev mode
	if dev_mode:
		getAppStats()

	print("Cloning FDroid metadata repository")
	#getFDroidRepoData()
	metadata = parseFDroidRepoData()
	print("Clone completed")
	
	print("Saving data to db")
	db = DB(DB_LOCATION)
	db.create_db()
	for app in metadata.keys():
		if is_app_valid(metadata[app]):
			db.add_new_app(metadata[app], commit_on_call = False)
	db.commit()
	print("Getting source control urls to retrieve")
	if not dev_mode:
		cloneRepos(metadata)
	else:
		cloneRepos(metadata, dry_run = True)
	
	if dev_mode:
		getAPKs(metadata, dry_run = True)
	else:
		getAPKs(metadata)

	print("Checkout out of source control complete!")

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import commit [as 別名]
class ChainManager(StoppableLoopThread):

    """
    Manages the chain and requests to it.
    """

    def __init__(self):
        super(ChainManager, self).__init__()
        # initialized after configure
        self.miner = None
        self.blockchain = None
        self._children_index = None

    def configure(self, config, genesis=None):
        self.config = config
        logger.info('Opening chain @ %s', utils.get_db_path())
        self.blockchain = DB(utils.get_db_path())
        self._children_index = indexdb.Index('ci')
        if genesis:
            self._initialize_blockchain(genesis)
        logger.debug('Chain @ #%d %s', self.head.number, self.head.hex_hash())
        self.log_chain()
        self.new_miner()

    @property
    def head(self):
        if 'HEAD' not in self.blockchain:
            self._initialize_blockchain()
        ptr = self.blockchain.get('HEAD')
        return blocks.get_block(ptr)

    def _update_head(self, block):
        bh = block.hash
        self.blockchain.put('HEAD', block.hash)
        self.blockchain.commit()
        self.new_miner()  # reset mining

    def get(self, blockhash):
        assert isinstance(blockhash, str)
        assert len(blockhash) == 32
        return blocks.get_block(blockhash)

    def has_block(self, blockhash):
        assert isinstance(blockhash, str)
        assert len(blockhash) == 32
        return blockhash in self.blockchain

    def __contains__(self, blockhash):
        return self.has_block(blockhash)

    def _store_block(self, block):
        self.blockchain.put(block.hash, block.serialize())
        self.blockchain.commit()

    def _initialize_blockchain(self, genesis=None):
        logger.info('Initializing new chain @ %s', utils.get_db_path())
        if not genesis:
            genesis = blocks.genesis()
        self._store_block(genesis)
        self._update_head(genesis)

    def synchronize_newer_blockchain(self):
        logger.info('sync newer request for head %r', self.head)
        signals.remote_chain_requested.send(
            sender=None, parents=[self.head.hash], count=NUM_BLOCKS_PER_REQUEST)

    def synchronize_older_blockchain(self, block_number):
        # block_number: for block we search the parent for
        # seek 1st possible branching block
        logger.info('sync older request for parent of block #%r', block_number)
        blk = self.head
        while blk.number > block_number:
            blk = blk.get_parent()

        # collect blocks
        requested = []
        while len(requested) < NUM_BLOCKS_PER_REQUEST and blk.has_parent():
            blk = blk.get_parent()
            requested.append(blk)
        logger.debug('requesting %d blocks', len(requested))
        # newest first, GetChain, will try to answer w/ older ones if the
        # the newest is not in the canonical chain
        # expected answer is the first here known block in the canonical chain
        signals.remote_chain_requested.send(sender=None,
                                            parents=[b.hash for b in requested], count=NUM_BLOCKS_PER_REQUEST)

    def loop_body(self):
        ts = time.time()
        pct_cpu = self.config.getint('misc', 'mining')
        if pct_cpu > 0:
            self.mine()
            delay = (time.time() - ts) * (100. / pct_cpu - 1)
            time.sleep(min(delay, 1.))
        else:
            time.sleep(.01)

    def new_miner(self):
        "new miner is initialized if HEAD is updated"
        uncles = self.get_uncles(self.head)
        miner = Miner(self.head, uncles, self.config.get('wallet', 'coinbase'))
#.........這裏部分代碼省略.........

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import commit [as 別名]
class ChainManager(StoppableLoopThread):

    """
    Manages the chain and requests to it.
    """

    def __init__(self):
        super(ChainManager, self).__init__()
        # initialized after configure
        self.miner = None
        self.blockchain = None

    def configure(self, config):
        self.config = config
        logger.info('Opening chain @ %s', utils.get_db_path())
        self.blockchain = DB(utils.get_db_path())
        logger.debug('Chain @ #%d %s', self.head.number, self.head.hex_hash())
        self.log_chain()
        self.new_miner()

    @property
    def head(self):
        if 'HEAD' not in self.blockchain:
            self._initialize_blockchain()
        ptr = self.blockchain.get('HEAD')
        return blocks.get_block(ptr)

    def _update_head(self, block):
        bh = block.hash
        self.blockchain.put('HEAD', block.hash)
        self.blockchain.commit()
        self.new_miner()  # reset mining

    def get(self, blockhash):
        return blocks.get_block(blockhash)

    def has_block(self, blockhash):
        return blockhash in self.blockchain

    def __contains__(self, blockhash):
        return self.has_block(blockhash)

    def _store_block(self, block):
        self.blockchain.put(block.hash, block.serialize())
        self.blockchain.commit()
        assert block == blocks.get_block(block.hash)

    def _initialize_blockchain(self):
        logger.info('Initializing new chain @ %s', utils.get_db_path())
        genesis = blocks.genesis()
        self._store_block(genesis)
        self._update_head(genesis)
        self.blockchain.commit()

    def synchronize_blockchain(self):
        logger.info('synchronize requested for head %r', self.head)

        signals.remote_chain_requested.send(
            sender=None, parents=[self.head.hash], count=30)

    def loop_body(self):
        ts = time.time()
        pct_cpu = self.config.getint('misc', 'mining')
        if pct_cpu > 0:
            self.mine()
            delay = (time.time() - ts) * (100. / pct_cpu - 1)
            time.sleep(min(delay, 1.))
        else:
            time.sleep(.1)

    def new_miner(self):
        "new miner is initialized if HEAD is updated"
        miner = Miner(self.head, self.config.get('wallet', 'coinbase'))
        if self.miner:
            for tx in self.miner.get_transactions():
                miner.add_transaction(tx)
        self.miner = miner

    def mine(self):
        with self.lock:
            block = self.miner.mine()
            if block:
                # create new block
                self.add_block(block)
                logger.debug("broadcasting new %r" % block)
                signals.send_local_blocks.send(
                    sender=None, blocks=[block])  # FIXME DE/ENCODE

    def receive_chain(self, blocks):
        old_head = self.head
        # assuming chain order w/ newest block first
        for block in blocks:
            if block.hash in self:
                logger.debug('Known %r', block)
            else:
                if block.has_parent():
                    # add block & set HEAD if it's longest chain
                    success = self.add_block(block)
                    if success:
                        logger.debug('Added %r', block)
#.........這裏部分代碼省略.........

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import commit [as 別名]

#.........這裏部分代碼省略.........
        if not key1:
            diverge_node[-1] = value1
        else:
            diverge_node[key1[0]] = self._update(
                [], True, key1[1:], value1, value1_is_node)[0]
        if not key2:
            diverge_node[-1] = value2
        else:
            diverge_node[key2[0]] = self._update(
                [], True, key2[1:], value2, value2_is_node)[0]

        return self._normalize_node(
            self._rlp_encode(diverge_node), True)

    def delete(self, key):
        '''
        :param key: a string with length of [0, 32]
        '''
        if not isinstance(key, (str, unicode)):
            raise Exception("Key must be strings")

        if len(key) > 32:
            raise Exception("Max key length is 32")

        ''' .. note:: value_is_node should be true, or the key will be updated
        with a blank value
        '''
        self.root, _ = self._update(
            self.root,
            True,
            bin_to_nibbles(str(key)),
            BLANK_NODE,
            value_is_node=True)
        self.db.commit()
        return self._rlp_decode(self.root)

    def _get_size(self, node, is_node):
        '''Get counts of (key, value) stored in this and the descendant nodes

        :param node: node or hash
        :is_node: true if node is not a value, other wise false
        '''
        if not is_node:
            return 1

        (node_type, content) = self._inspect_node(node)
        if node_type == NODE_TYPE_BLANK:
            return 0
        elif is_key_value_type(node_type):
            value_is_node = node_type == NODE_TYPE_INNER_KEY_VALUE
            return self._get_size(content[1], value_is_node)
        elif is_diverge_type(node_type):
            return sum(self._get_size(content[x], True) for x in range(16)) \
                + (1 if content[-1] else 0)

    def _to_dict(self, node, is_node):
        '''convert (key, value) stored in this and the descendant nodes
        to dict items.

        :param node: node or hash
        :is_node: true if node is not a value, other wise false

        .. note::

            Here key is in full form, rather than key of the individual node
        '''

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import commit [as 別名]
class LastFM(Worker):
  _title = 'lastfm'
  _key = None
  _lfm = None
  # wait this amount of seconds between lookups
  _lookup_threshold = 10
  _last_lookup = None
  _history_filename = None
  _artist_history = {}
  _track_history = {}
  _db = None

  def __init__(self, config, no_cache=False):
    Worker.__init__(self)
    self._key = config.get('LastFM', 'key', None)
    self._lfm = Api(self._key, no_cache=no_cache)
    self._db = DB(config.get('LastFM', 'path',
        join(config.configdir(), 'echonest.db')))
    self._db.execute(
        u"CREATE TABLE IF NOT EXISTS artist_lookup "\
        "(artist text, timestamp integer, "\
        "PRIMARY KEY(artist))")
    self._db.execute(
        u"CREATE TABLE IF NOT EXISTS track_lookup "\
        "(artist text, title text, timestamp integer, "\
        "PRIMARY KEY(artist, title))")
    self._db.commit()

  def get_artist_timestamp(self, artist):
    self._db.execute(
        u"SELECT timestamp FROM artist_lookup "\
        "WHERE artist=?", (artist,))
    row = self._db.fetchone()
    if row: return row[0]
    return 0

  def set_artist_timestamp(self, artist, timestamp):
    self._db.execute(
        u"REPLACE INTO artist_lookup (artist, timestamp) "\
        "VALUES (?, ?)", (artist, timestamp))
    self._db.commit()

  def get_track_timestamp(self, artist, title):
    self._db.execute(
        u"SELECT timestamp FROM track_lookup "\
        "WHERE artist=? and title=?", (artist, title))
    row = self._db.fetchone()
    if row: return row[0]
    return 0

  def set_track_timestamp(self, artist, title, timestamp):
    self._db.execute(
        u"REPLACE INTO track_lookup (artist, title, timestamp) "\
        "VALUES (?, ?, ?)", (artist, title, timestamp))
    self._db.commit()

  def _delay(self):
    if self._last_lookup:
      last_lookup = (datetime.utcnow() - self._last_lookup)
      if last_lookup.seconds < self._lookup_threshold:
        self._logger.debug(u"sleeping %ss" % (
            self._lookup_threshold - last_lookup.seconds))
        sleep(self._lookup_threshold - last_lookup.seconds)
    self._last_lookup = datetime.utcnow()

  def _track(self, artist, title):
    self._delay()
    result = self._lfm.search_track(track=title, artist=artist)
    # for some tracks (e.g. 'after all' from 'the peacocks' lastfm doesn't
    # return the correct track as first result (for 'peacocks' it works?!))
    # so let's try to find the correct track
    for t in result:
      self._logger.info(u'{} {} vs {} {}'.format(artist, title,
          t.artist.name.lower() ,t.name.lower()))
      if t.artist.name.lower() == artist and t.name.lower() == title:
        return t
    if len(result) == 0:
      return None
    return result[0] # return the first

  def _artist(self, artist):
    self._delay()
    result = self._lfm.search_artist(artist=artist)
    for a in result:
      if a.name.lower() == artist:
        return a
    if len(result) == 0:
      return None
    return result[0]

  def similar_tracks(self, callback, artist, title, threshold):
    self.queue_callback(self._similar_tracks, callback, artist, title,
        threshold)

  def similar_tracks_low(self, callback, artist, title, threshold):
    self.queue_callback_low(self._similar_tracks, callback, artist, title,
        threshold)

  def _similar_tracks(self, callback, artist, title, threshold):
    """
#.........這裏部分代碼省略.........

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import commit [as 別名]
        connectionthread(conn, addr, phones).start()
        i += 1
        if i % 1000 == 0:
            try:
                db = open("webkeyusers.txt", "w");
                mysqldb = DB('114.112.63.174', 3306, 'root', 'doododAAAAA', 'cloudphone')
                tbname = 'tb_device'
                for username in phones:
                    db.write(username + " " + phones[username].random + "\n")
                    mysqldb.insert(tbname, {'device_name': username,
                                            'random_code': phones[username].random,
                                            'collect_time': time.strftime('%Y-%m-%d', time.localtime(time.time())),
                                            'state':1
                                            })
                db.close()
                mysqldb.commit()
                print "SAVED DATABASE"
            except:
                print "SAVING DATABASE FAILED"
            #        print i
    except KeyboardInterrupt:
        try:
            f.close()
        except:
            pass
        break
    except socket.error:
        pass

print "stopping"
db = open("webkeyusers.txt", "w");

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import commit [as 別名]

#.........這裏部分代碼省略.........

			timeCleaning = int(time.time() - self.startCleaning)
			del msg
			maxnum = last
			rangenotreceived = list(set(rangerequested) - set(msgsreceived))

			if stype != 'partrepair':
				print 'Received ', len(msgsreceived), 'articles of', last-first+1, 'requested,', len(msgsblacklisted), 'blacklisted,', len(msgsignored), 'not binary.'					

			if len(rangenotreceived) > 0:
				if stype == 'backfill':
					''' dont add missing articles'''
				else:
					if self.DoPartRepair:
						self.addMissingParts(rangenotreceived, groupArr['ID'])

				if stype != 'partrepair':
					print 'Server did not return %d articles.' % (len(rangenotreceived))

			self.startUpdate = time.time()
			try:
				len(self.message)
			except NameError:
				pass
			else:
				maxnum = first
				# insert binaries and parts into database. When binaries already exists; only insert new parts
				insPartsStmt = "INSERT IGNORE INTO parts (binaryID, number, messageID, partnumber, size) VALUES (%s, %s, %s, %s, %s)"

				lastCollectionHash = ''
				lastCollectionID = -1
				lastBinaryHash = ''
				lastBinaryID = -1

				mdb.setAutoCommit(False)

				for subject, data in self.message.iteritems():
					collectionHash = data['CollectionHash']
					subject = namecleaning.unfuckString(subject)

					if lastCollectionHash == collectionHash:
						collectionID = lastCollectionID
					else:
						lastCollectionHash = collectionHash
						lastBinaryHash = ''
						lastBinaryID = -1

						cres = mdb.queryOneRow("SELECT ID FROM collections WHERE collectionhash = %s", (collectionHash,))
						if cres is None:
							cleanerName = namecleaning.releaseCleaner(subject)
							csql = "INSERT INTO collections (name, subject, fromname, date, xref, groupID, totalFiles, collectionhash, dateadded) VALUES (%s, %s, %s, FROM_UNIXTIME(%s), %s, %s, %s, %s, now())"
							collectionID = mdb.queryInsert(csql, (cleanerName, subject, data['from'], data['Date'], data['xref'], groupArr['ID'], data['MaxFiles'], collectionHash))
						else:
							collectionID = int(cres['ID'])
							cusql = 'UPDATE collections SET dateadded = now() where ID = %s'
							mdb.queryDirect(cusql, (collectionID,))

						lastCollectionID = collectionID
					binaryHash = hashlib.md5(subject+data['from']+str(groupArr['ID'])).hexdigest()

					if lastBinaryHash == binaryHash:
						binaryID = lastBinaryID
					else:
						lastBinaryHash = binaryHash

						bres = mdb.queryOneRow('SELECT ID FROM binaries WHERE binaryhash = %s', (binaryHash,))
						if bres is None:
							bsql = "INSERT INTO binaries (binaryhash, name, collectionID, totalParts, filenumber) VALUES (%s, %s, %s, %s, %s)"
							binaryID = mdb.queryInsert(bsql, (binaryHash, subject, collectionID, data['MaxParts'], round(data['File'])))
						else:
							binaryID = bres['ID']
						lastBinaryID = binaryID

					for partdata in data['Parts'].values():
						pBinaryID = binaryID
						pMessageID = partdata['Message-ID']
						pNumber = partdata['number']
						pPartNumber = round(partdata['part'])
						pSize = partdata['size']
						maxnum = partdata['number'] if (partdata['number'] > maxnum) else maxnum
						params = (pBinaryID, pNumber, pMessageID, pPartNumber, pSize)
						
						try:
							mdb.query(insPartsStmt, params)
						except MySQLdb.Error, e:
							msgsnotinserted.append(partdata['number'])

				if len(msgsnotinserted) > 0:
					print 'WARNING: %d parts failed to insert.' % len(msgsnotinserted)
					if self.DoPartRepair:
						self.addMissingParts(msgsnotinserted, groupArr['ID'])
				mdb.commit()
				mdb.setAutoCommit(True)
			timeUpdate = int(time.time() - self.startUpdate)
			timeLoop = int(time.time() - self.startLoop)

			if stype != 'partrepair':
				print '%ds to download articles, %ds to clean articles, %d to insert articles, %ds total.\n\n' % (timeHeaders, timeCleaning, timeUpdate, timeLoop)
			data, self.message = None, {}
			return maxnum

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import commit [as 別名]
class EchoNest(Worker):
  _title = 'echonest'
  _key = None
  # wait this amount of seconds between lookups
  _lookup_threshold = 10
  _last_lookup = None
  _history_filename = None
  _artist_history = {}
  _track_history = {}
  _db = None

  def __init__(self, config, no_cache=False):
    Worker.__init__(self)
    self._key = config.get('EchoNest', 'key', None)
    en_config.ECHO_NEST_API_KEY = self._key
    self._db = DB(config.get('EchoNest', 'path',
        join(config.configdir(), 'echonest.db')))
    self._db.execute(
        u"CREATE TABLE IF NOT EXISTS artist_lookup "\
        "(artist text, timestamp integer, "\
        "PRIMARY KEY(artist))")
    self._db.execute(
        u"CREATE TABLE IF NOT EXISTS track_lookup "\
        "(artist text, title text, timestamp integer, "\
        "PRIMARY KEY(artist, title))")
    self._db.commit()

  def get_artist_timestamp(self, artist):
    self._db.execute(
        u"SELECT timestamp FROM artist_lookup "\
        "WHERE artist=?", (artist,))
    row = self._db.fetchone()
    if row: return row[0]
    return 0

  def set_artist_timestamp(self, artist, timestamp):
    self._db.execute(
        u"REPLACE INTO artist_lookup (artist, timestamp) "\
        "VALUES (?, ?)", (artist, timestamp))
    self._db.commit()

  def get_track_timestamp(self, artist, title):
    self._db.execute(
        u"SELECT timestamp FROM track_lookup "\
        "WHERE artist=? and title=?", (artist, title))
    row = self._db.fetchone()
    if row: return row[0]
    return 0

  def set_track_timestamp(self, artist, title, timestamp):
    self._db.execute(
        u"REPLACE INTO track_lookup (artist, title, timestamp) "\
        "VALUES (?, ?, ?)", (artist, title, timestamp))
    self._db.commit()

  def _delay(self):
    if self._last_lookup:
      last_lookup = (datetime.utcnow() - self._last_lookup)
      if last_lookup.seconds < self._lookup_threshold:
        self._logger.debug(u"sleeping %ss" % (
            self._lookup_threshold - last_lookup.seconds))
        sleep(self._lookup_threshold - last_lookup.seconds)
    self._last_lookup = datetime.utcnow()

  def similar_tracks(self, callback, artist, title, threshold):
    self.queue_callback(self._similar_tracks, callback, artist, title,
        threshold)

  def similar_tracks_low(self, callback, artist, title, threshold):
    self.queue_callback_low(self._similar_tracks, callback, artist, title,
        threshold)

  def _similar_tracks(self, callback, artist, title, threshold):
    timestamp = now()
    diff = timestamp - self.get_track_timestamp(artist, title)
    if diff < threshold:
      self._logger.debug(u"similar_tracks[%s-%s]: looked up %d seconds ago" %
          (artist, title, diff))
      return
    self.set_track_timestamp(artist, title, timestamp)
    try:
      self._logger.debug(u"similar_tracks[%s-%s]: lookup" % (artist, title))
      self._delay()
      a = en_song.search(title=title, artist=artist)
      try:
        p = en_playlist.static(type='song-radio', song_id=a[0].id, results=100)
        i = 100.0
        self._logger.info(u"similar_tracks[%s-%s]: %d result(s)" % (artist,
            title, len(p)))
        for song in p:
          callback(artist, title, song.artist_name.lower(), song.title.lower(),
              i / 100.0, self._title)
          i -= 1.0
      except IndexError:
        self._logger.info(u"similar_tracks[%s-%s]: no result" % (artist, title))
        return
    except Exception, e:
      self._logger.error(e)
      self._logger.info(u"similar_tracks[%s-%s]: no result" % (artist, title))
      return

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import commit [as 別名]
class Alert:

    def __init__(self):
        """
        Creates alerts table if not exists.

        id is the unique URL where alert info is served.
        updated is the updated timestamp as string. e.g. "2016-02-02T15:08:00-05:00"
        """
        self.db = DB()
        try:
            self.db.execute("CREATE TABLE alerts (id varchar PRIMARY KEY, updated varchar);")
            self.db.commit()
            print "This is the initial run of the script, this will take longer than usual. Building alerts database"
        except Exception as e:
            self.db.rollback()

    def get(self, alert_id):
        """
        Return alert with the given id.
        """
        alert = None
        try:
            self.db.execute("SELECT * FROM alerts WHERE id = '%s';" % (alert_id))
            alert = self.db.cur.fetchone()
            self.db.commit()
        except Exception as e:
            print e
            self.db.rollback()
        return alert
 
    def create_or_update(self, alert_id, updated):
        """
        Create alert if not exists, or if exists update the updated timestamp for the given alert.
        """
        alert = self.get(alert_id)

        try:
            if alert is None:
                self.db.execute("INSERT INTO alerts (id, updated) VALUES ('%s', '%s');" % (alert_id, updated))
                self.db.commit()
                return 'created'
            else:
                current_updated_value = alert[1]
                if current_updated_value != updated:
                    self.db.execute("UPDATE alerts SET updated = '%s' WHERE id = '%s';" % (updated, alert_id))
                    self.db.commit()
                    return 'updated'
        except Exception as e:
            print e
            self.db.rollback()

    def delete(self, alert_id):
        """
        Delete the given alert from the database.
        """
        try:
            self.db.execute("DELETE FROM alerts WHERE id = '%s';" % (alert_id))
            self.db.commit()
        except Exception as e:
            self.db.rollback()