Python PySQLPool类代码示例

	def run(self):

		with Connection.Connection(self.socket, self.name, self.debug) as conn:
			while conn.get_message():

				answer = self.default_answer

				if conn["sender"] != "" and conn["recipient"] != "":
					if self.debug:
						logging.debug("Mail from {0} to {1} with SASL: {2}".format(conn["sender"], conn["recipient"], conn["sasl_username"]))
					for flt in self.flts:
						with self._mutex:
							try:
								flt_answer = flt.check(conn)
							except:
								logging.error("Error in checking policy {0}. Traceback: \n{1}\n".format(flt, traceback.format_exc()))
						if flt_answer:
							break

					if flt_answer:
						answer = flt_answer

					if self.debug:
						logging.debug("Answer for mail {0} to {1} was: {2}".format(conn["sender"], conn["recipient"], answer))

				if conn["request"] == "smtpd_access_policy":
					conn.answer(answer)
				try:
					PySQLPool.cleanupPool()
				except:
					logging.warn('Error in cleaning SQL pool. Traceback: \n{0}\n'.format(traceback.format_exc()))
		if self.debug:
			stop_time = time.time()
			logging.debug("Process named {0} started {1}, stopped {2}. Working {3} seconds.".format(self.name, time.strftime("%d.%m.%y - %H:%M:%S", time.localtime(self.start_time)), time.strftime("%d.%m.%y - %H:%M:%S", time.localtime(stop_time)), (stop_time - self.start_time)))

def set_conninfo(conn_info, max_pool_count = 3):
  conn = PySQLPool.getNewConnection(
           host     = conn_info["hostname"],
           username = conn_info["username"],
           password = conn_info["password"],
           schema   = conn_info["schema"]
         )
  PySQLPool.getNewPool().maxActiveConnections = max_pool_count

  return conn

def TestConnect(sAddr, nPort, sUser, sPasswd):
    try:
        testConn = PySQLPool.getNewConnection(username=sUser, password=sPasswd, host=sAddr, port=nPort, db='mysql', charset='utf8')
        query = PySQLPool.getNewQuery(testConn)
        query.query(r'select * from user')

        return True, '成功'
    except Exception,e:
        print e
        return False,e

	def testDBConnection(self):
		"""
		Test actual connection to Database
		"""
		connDict = {
					"host":self.host,
					"user":self.username,
					"passwd":self.password,
					"db":self.db}
		connection = PySQLPool.getNewConnection(**connDict)
		query = PySQLPool.getNewQuery(connection)
		query.Query("select current_user")
		result = str(query.record[0]['current_user']).split('@')[0]
		self.assertEqual(result, 'unittest', "DB Connection Failed")

	def testQuickQueryCreation(self):
		"""
		Quick Query Creation
		"""
		try:
			connDict = {
						"host":self.host,
						"user":self.username,
						"passwd":self.password,
						"db":self.db}
			connection = PySQLPool.getNewConnection(**connDict)
			query = PySQLPool.getNewQuery(connection)
		except Exception, e:
			self.fail('Failed to create PySQLQuery Object')

 def fillQueue(self):
     query = PySQLPool.getNewQuery(self._db)
     query.execute("SELECT id FROM `mc_caching` WHERE last_crawl <= DATE_SUB(NOW(),INTERVAL 1 MINUTE) and `aktiv`='1' and `flag_delete`='0' ORDER BY `last_crawl` ASC")
     for row in query.record:
         if not row['id'] in self._queue.queue:
             self._queue.put(row["id"])
     return

    def get_uuid(self):
        """Create the Database Querys"""
        query = PySQLPool.getNewQuery(self._db)
        query.execute("SELECT * FROM `mc_caching` WHERE `id`=%s and `flag_delete`='0' and `aktiv`='1'", (self._entry))

        for row in query.record:
            return row["mc_uuid"]

def regionID(id):
    query = PySQLPool.getNewQuery(db)
    query.Query("""SELECT regionID from eve.mapRegions where regionName = %s""", (id,))
    if len(query.record) != 1:
        return None
    for row in query.record:
        return row['regionID']

	def _loadsql(self, users):
		try:
			sql_1 = "SELECT `user_id`, `token`, `action` FROM `white_list_users`"
			sql_2 = "DELETE FROM `white_list_users` WHERE `user_id` = '{0}'"

			res={}
			rules={}
			clean_rules=list()

			for uid in users:
				if users[uid] in self._exclude_mails:
					continue
				res[users[uid]] = {}

			query = PySQLPool.getNewQuery(self._sql_pool, True)

			query.Query(sql_1)
			for row in query.record:
				tmp = {}
				tmp[row["token"].lower()] = row["action"]
				if users.has_key(str(int(row["user_id"]))):
					res[users[str(int(row["user_id"]))]].update(tmp)
				else:
					if not self._keep_rules:
						clean_rules.append(row["user_id"])
						
			if not self._keep_rules:
				clean_rules = list(set(clean_rulse))
				for id in clean_rulse:
					query.Query(sql_2.format(id))
			return res
		except:
			if self._debug:
				logging.debug("Error in getting SQL data for UserLdap policy. Traceback: \n{0}\n".format(traceback.format_exc()))
			return None

def repoThread():
    global repoList
    global repoVal
    while len(repoList) > 0:
        row = repoList.pop()
        regions = regionList()
        prices = getMineralBasket()
        refValue = ((row['Tritanium'] * prices['Tritanium']['sellavg']) +
        (row['Pyerite'] * prices['Pyerite']['sellavg']) +
        (row['Mexallon'] * prices['Mexallon']['sellavg']) +
        (row['Isogen'] * prices['Isogen']['sellavg']) +
        (row['Nocxium'] * prices['Nocxium']['sellavg']) +
        (row['Zydrine'] * prices['Zydrine']['sellavg']) +
        (row['Megacyte'] * prices['Megacyte']['sellavg']) +
        (row['Morphite'] * prices['Morphite']['sellavg'])) / row['portion']
        queryValue = PySQLPool.getNewQuery(db)
        stuff = refValue * 1.02
        queryValue.Query("""SELECT region, sellavg, sell, buy, buyavg from prices where itemid = %s""" % (row['typeID'],))
        for rowValue in queryValue.record:
            if rowValue['sellavg'] > stuff:
                continue
            if rowValue['sellavg'] != 0 and refValue/rowValue['sellavg'] * 100 > 100:
                repoVal[regions[rowValue['region']]][itemName(row['typeID'])] = {'sellavg': rowValue['sellavg'], 'sell': rowValue['sell'], 'buy': rowValue['buy'], 'buyavg': rowValue['buyavg'], 'refprice': refValue, 'percentage': refValue/rowValue['sellavg']* 100}
            elif rowValue['sellavg'] == 0 and rowValue['sell'] != 0 and refValue/rowValue['sell'] * 100 > 100:
                repoVal[regions[rowValue['region']]][itemName(row['typeID'])] = {'sellavg': rowValue['sellavg'], 'sell': rowValue['sell'], 'buy': rowValue['buy'], 'buyavg': rowValue['buyavg'], 'refprice': refValue, 'percentage': refValue/rowValue['sell'] * 100}
            else:
                continue

    def _loadsql(self):
        try:
            sql_1 = "SELECT `white_list_users`.`id`, `users`.`username`, `white_list_users`.`token`, `white_list_users`.`action` FROM `users` RIGHT JOIN `white_list_users` ON `users`.`id` = `white_list_users`.`user_id`"
            sql_2 = "DELETE FROM `white_list_users` WHERE `id` = {0}"

            res = {}
            users = {}
            rules = {}
            clean_rulse = list()

            query = PySQLPool.getNewQuery(self._sql_pool, True)

            query.Query(sql_1)
            for row in query.record:
                if not self._keep_rules and row["username"] == None:
                    clean_rulse.append(int(row["id"]))
                if row["username"] == None:
                    continue
                if row["username"].lower() in self._exclude_mails:
                    continue
                if not res.has_key(row["username"].lower()):
                    res[row["username"].lower()] = dict()
                res[row["username"].lower()][row["token"].lower()] = row["action"]

            for iter in clean_rulse:
                query.Query(sql_2.format(iter))

            return res
        except:
            if self._debug:
                logging.debug(
                    "Error in getting SQL data for User policy. Traceback: \n{0}\n".format(traceback.format_exc())
                )

            return None

 def load_database_entry(self):
     """Create the Database Querys"""
     query = PySQLPool.getNewQuery(self._db)
     query.execute("SELECT * FROM `mc_versioning` WHERE `mc_caching_id`=%s and `flag_delete`='0' and `aktiv`='1'", (self._entry))
     #data = query.record
     for data in query.record:
         return data

def get_history_volumn_detail_accord_property(ip_address, property_name, start_time, end_time):
    '''

    '''
    property_name = string.lower(property_name)
    table_name = ''
    sql = ''
    result = []
    if property_name.find('service') != -1:
        table_name = model_redis.get_service_table_accord_ip(ip_address)
        sql = config_mysql.GET_HIS_VOLUMN_ACCORD_SERVICE.format(table_name, ip_address, start_time, end_time)
    else:
        property_type = get_property_type(property_name)
        if property_type != '':
            table_name = config_mysql.PRO_TO_TABLE[property_type]
            sql = config_mysql.HIS_SQL_TEMPLATE.format(property_name, table_name,
                                                       ip_address, start_time, end_time)
        else:
            table_name = model_redis.get_service_table_accord_ip(ip_address)
            sql = config_mysql.GET_HIS_VOLUMN_ACCORD_THE_SERVICE.format(table_name, ip_address, property_name,
                                                                        start_time, end_time)
    if sql != '':
        print sql
        query = PySQLPool.getNewQuery(his_conn)
        query.Query(sql)
        for item in query.record:
            item['time'] = utils.date_to_timestamp_general(item['time'], '%Y%m%d-%H')
            result.append(item)
        print result
        return {
            'ip_address': ip_address,
            'property_name': property_name,
            'property_value': result
        }

def insert_new_important_service(request_data):
    service_name = request_data['Services_name']
    instance = request_data['oop_name']
    business_name = request_data['business_name']
    ip = request_data['ip_address']
    editor = request_data['editor'] if request_data['editor'] else ''
    description = request_data['description'] if request_data['description'] else ''
    import datetime

    date_time = datetime.datetime.strptime(request_data['input_date'], '%Y%m%d')
    sql = config_mysql.ADD_NEW_IMPORTANT_SERVICE.format(service_name, ip, description, editor,
                                                        date_time, business_name, instance)
    print sql
    try:
        query = PySQLPool.getNewQuery(connection)
        query.Query(sql)
        query.Query("commit;")
        row_id = query.lastInsertID
        if query.affectedRows == 1:
            return True
        else:
            return False
    except:
        traceback.print_exc()
        return False

def manytasks(sas):
  connection = PySQLPool.getNewConnection(username='root', password='password', host='localhost', db='sandyfiles')
  

  for i in range(2):
    t = Thread(target=checksamples, args=(i,connection,))
    t.start()