def query_assocmeta_gene_cpg(gene,
dbConnection,
window=250000,
columns="*",
maxpval=0.05):
# get IDs
SQL = """SELECT name,chr,start_pos,stop_pos from gene WHERE name = '{0}'""".format(
gene)
query = PySQLPool.getNewQuery(dbConnection)
query.Query(SQL)
geneinfo = query.record[0]
if len(geneinfo) is 0:
return ()
cols = ",".join(["a." + x for x in columns.split(",")])
SQL = """SELECT {0}, b.name, b.rsid, c.chr, c.pos, b.allele1 AS a1, b.allele2 AS a2 FROM assoc_meta a, snp b, cpg c
WHERE a.snp=b.name
AND a.cpg=c.name
AND c.chr = {1}
AND c.pos >= {2}
AND c.pos <= {3}
AND a.pval < {4}
ORDER BY a.pval""".format(cols, geneinfo['chr'],
geneinfo['start_pos'] - window,
geneinfo['stop_pos'] + window, maxpval)
query = PySQLPool.getNewQuery(dbConnection)
query.Query(SQL)
return query.record
def get_snpid_from_rsid(rsid, dbConnection):
rsids = ",".join(["'" + x + "'" for x in rsid])
SQL = """SELECT name,rsid from snp
WHERE rsid IN ({0})""".format(rsids)
query = PySQLPool.getNewQuery(dbConnection)
query.Query(SQL)
return query.record
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 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 _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 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 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 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 prices():
query = PySQLPool.getNewQuery(connection, commitOnEnd=True)
query.Query("""select * from price_list where price_time = %s""", (ist_today(),))
if int(query.rowcount) > 0:
logging.error("found in db")
price_dict = {}
for record in query.record:
if (record['city']) in price_dict:
fuel_dict = price_dict[record['city']]
fuel_dict[record['type']] = str(record['price'])
else:
fuel_dict = {}
fuel_dict[record['type']] = str(record['price'])
price_dict[record['city']] = fuel_dict
prices_json = {"status": {"message": "Successful", "code": 0},
"data": {"fuelprice": price_dict, "cities": price_dict.keys()}}
else:
prices_json = get_prices_from_iocl_website()
petrol_time = datetime.datetime.fromtimestamp(float(prices_json.get("data", {}).get("timestamp", ist_today())))
for city, price_dict in prices_json.get("data", {}).get("fuelprice", {}).iteritems():
for fuel_type, price in price_dict.iteritems():
query.Query("""insert into price_list (city,price, type, price_time) values (%s,%s, %s, %s) on duplicate key update price=%s """,(city,price,fuel_type,petrol_time,price))
return json.dumps(prices_json)
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 regionStatus(id):
query = PySQLPool.getNewQuery(db)
query.Query(
"""SELECT factionID from eve.mapRegions where regionID = %s and factionID is not null""",
(id, ))
if len(query.record) == 1:
return True
return None
def query(self, sqlQuery):
# cursor = self.db.cursor()
# cursor.execute(sqlQuery)
# rows = cursor.fetchall()
# return rows
query = PySQLPool.getNewQuery(connection = self.db)
query.Query(sqlQuery)
return query.record
def save_data (table, url, node, dns, https, http, http_code, download_size, fst_byte, ping):
global conn, db, hostname, username, password, database
tmp="insert into " + table + " (host, node, dns_time, https_time, http_time, code, size, 1st_byte, ping) values ('%s', '%s', %.3f, %.3f, %.3f, %d, %d, %.3f, %.3f)" %(url[1], node, dns, https, http, http_code, download_size, fst_byte, ping)
print tmp
conn = PySQLPool.getNewQuery(db,commitOnEnd=True)
conn.Query(tmp)
def execute(self, sql, args=None):
'''
Excutes arbitrary sql string in current database connection.
Returns results as PySQLPool query object.
'''
log.debug('SQL.execute ' + sql)
queryobj = PySQLPool.getNewQuery(self.connect())
queryobj.Query(sql, args)
return queryobj
def get_secret(conn, api_key):
res = None
query = PySQLPool.getNewQuery(connection=conn)
r = query.Query("SELECT SVC_SECRETKEY FROM TBMB_ISSVC WHERE SVC_APIKEY = %s", (api_key))
if r == 1:
res = query.record[0]['SVC_SECRETKEY']
return res
def testQuickQueryCreation(self):
"""
Quick Query Creation
"""
try:
query = PySQLPool.getNewQuery(self.connection)
self.assertTrue(isinstance(query, PySQLPool.query.PySQLQuery))
except Exception, e:
self.fail('Failed to create PySQLQuery Object with error: '+str(e))
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 get_important_service_accord_ip(ip_address):
result = []
sql = config_mysql.GET_IMPORTANT_SERVICE_ACCORD_IP.format(ip_address)
query = PySQLPool.getNewQuery(connection)
try:
query.Query(sql)
result = list(set([item['service_name'] for item in list(query.record)]))
except Exception, e:
print e.message
def get_id(conn, api_key):
res = None
query = PySQLPool.getNewQuery(connection=conn)
r = query.Query("SELECT MEM_SQ FROM TBMB_ISSVC WHERE SVC_APIKEY = %s", (api_key))
if r == 1:
res = query.record[0]['MEM_SQ']
return res
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 getResult(self, sql_query):
"""
Get result form database when SQL query statement
:param sql_query: SQL Query
:return data: Object Array of the results
"""
query = PySQLPool.getNewQuery(connection)
query.Query(sql_query)
data = query.record
return data
def get_id(conn, api_key):
res = None
query = PySQLPool.getNewQuery(connection=conn)
r = query.Query("SELECT MEM_SQ FROM TBMB_ISSVC WHERE SVC_APIKEY = %s",
(api_key))
if r == 1:
res = query.record[0]['MEM_SQ']
return res
def getResult(self,sql_query):
"""
Get result form database when SQL query statement
:param sql_query: SQL Query
:return data: Object Array of the results
"""
query = PySQLPool.getNewQuery(connection)
query.Query(sql_query)
data = query.record
return data
def _getSids(self):
try:
query = PySQLPool.getNewQuery(self.connection)
query.Query('select sid, url from cam where dump is true')
self.sids = {}
for row in query.record:
# print( '%s - %s' % ( row[ 'sid' ], row[ 'url' ] ) )
self.sids[row['sid']] = row['url']
self.logger.info("sid list updated")
except:
self.logger.warning("getSids failed")
def getMineralBasket(region = 10000002):
try:
with pool.reserve() as mc:
basket = mc.get("basket" + str(region))
if basket != None:
return basket
except:
pass
query = PySQLPool.getNewQuery(db)
query.Query("""SELECT * from prices where (itemid BETWEEN 34 and 40 or itemid = 11399) and region = '%i'""" % (region))
retVal = {}
for row in query.record:
intQuery = PySQLPool.getNewQuery(db)
intQuery.Query("""SELECT typeName from eve.invTypes where typeID = %i""" % (row['itemid']))
for name in intQuery.record:
typeName = name['typeName']
retVal[typeName] = row
with pool.reserve() as mc:
mc.set("basket" + str(region), retVal, time=600)
return retVal
def get_secret(conn, api_key):
res = None
query = PySQLPool.getNewQuery(connection=conn)
r = query.Query(
"SELECT SVC_SECRETKEY FROM TBMB_ISSVC WHERE SVC_APIKEY = %s",
(api_key))
if r == 1:
res = query.record[0]['SVC_SECRETKEY']
return res
def getResultParamaters(self, sql_query, values):
"""
Get result form database when SQL query and values statement
:param sql_query: SQL Query
:param values: Vales to be inserted into the query before being run
:return data: Object Array of the results
"""
query = PySQLPool.getNewQuery(connection)
query.Query(sql_query, values)
data = query.record
return data
def getResultParamaters(self,sql_query,values):
"""
Get result form database when SQL query and values statement
:param sql_query: SQL Query
:param values: Vales to be inserted into the query before being run
:return data: Object Array of the results
"""
query = PySQLPool.getNewQuery(connection)
query.Query(sql_query,values)
data = query.record
return data
def repoSearch():
global repoList
global repoVal
query = PySQLPool.getNewQuery(db)
prices = getMineralBasket(10000030)
regions = regionList()
for data in regions:
repoVal[regions[data]] = {}
query.Query("SELECT * FROM repromin where rate > 5")
for row in query.record:
repoList.append(row)
threads = []
for i in range(maxThreads):
"""Worker scanning system"""
t = threading.Thread(target=repoThread, args=())
threads.append(t)
time.sleep(.1)
t.start()
while threading.activeCount()>1:
"""Verification of the number of active theads for monitoring purposes."""
time.sleep(1)
print "Active threads: %i/%i" % (threading.activeCount() - 1, maxThreads)
sys.stdout.flush()
output = """<html><head><script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<script type="text/javascript" src="http://snipanet.com/inc/jquery.tablesorter.min.js"></script>
<link rel="stylesheet" href="http://snipanet.com/inc/themes/blue/style.css" type="text/css">
<script type="text/javascript">
$(document).ready(function()
{
"""
for num in range(len(repoVal)):
output += """$("#myTable%i").tablesorter();""" % (num,)
output += """}
);
</script>
</head><body>
"""
incNum = 0;
for region in repoVal:
data = repoVal[region]
if len(data) == 0 or regionStatus(regionID(region)) == None or data[prices]['percentage'] < 100:
continue
output += """Region: %s<br><table id="myTable%i" class="tablesorter"><thead><tr><th>Item Name</th><th>Sell Avg</th><th>Sell Price</th><th>Buy Avg</th><th>Buy Price</th><th>Refine Price</th><th>Refine Percentage</tr></thead><tbody>""" % (region, incNum)
for prices in data:
output += "<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%.2f%%</td></tr>" % (prices, locale.format("%.2f", data[prices]['sellavg'], grouping=True), locale.format("%.2f", data[prices]['sell'], grouping=True), locale.format("%.2f", data[prices]['buyavg'], grouping=True), locale.format("%.2f", data[prices]['buy'], grouping=True), locale.format("%.2f", data[prices]['refprice'], grouping=True), data[prices]['percentage'])
output += "</tbody></table><br><br>"
incNum += 1
return output
def sync_content(urlhash):
query = PySQLPool.getNewQuery(connection)
print urlhash
query.Query('''select html from news_html where url_hash=%s;''',
(urlhash,))
if query.record:
html = query.record[0]['html']
ext_content = ext.get_content(html, True, with_tag=False)
if ext_content != '':
query.Query('update data_news2 set content=%s where url_hash=%s;',
(ext_content, urlhash))
query.Pool.Commit()
return True
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 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 _addrule(self, data, answer="dspam_innocent"):
if data["sasl_username"] != "" and data["sender"] != "" and data["recipient"] != "":
try:
sql = "INSERT IGNORE INTO `white_list_users` (`white_list_users`.`user_id`, `white_list_users`.`token`, `white_list_users`.`action`) SELECT `id` AS `userid`, '{0}' AS `token`, '{1}' AS `action` FROM `users` WHERE `users`.`username` LIKE '{2}';"
query = PySQLPool.getNewQuery(self._sql_pool, True)
query.Query(sql.format(data["recipient"], answer, data["sasl_username"]))
return True
except:
logging.warn(
"Error in creating a rule for UserLdap policy. Traceback: \n{0}\n".format(traceback.format_exc())
)
return False
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 as e:
self.fail('Failed to create PySQLQuery Object')
def getMineralBasket(region=10000002):
try:
with pool.reserve() as mc:
basket = mc.get("basket" + str(region))
if basket != None:
return basket
except:
pass
query = PySQLPool.getNewQuery(db)
query.Query(
"""SELECT * from prices where (itemid BETWEEN 34 and 40 or itemid = 11399) and region = '%i'"""
% (region))
retVal = {}
for row in query.record:
intQuery = PySQLPool.getNewQuery(db)
intQuery.Query(
"""SELECT typeName from eve.invTypes where typeID = %i""" %
(row['itemid']))
for name in intQuery.record:
typeName = name['typeName']
retVal[typeName] = row
with pool.reserve() as mc:
mc.set("basket" + str(region), retVal, time=600)
return retVal
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 itemName(id):
try:
with pool.reserve() as mc:
name = mc.get("itemName" + str(id))
if name != None:
return name
except:
pass
query = PySQLPool.getNewQuery(db)
query.Query("""SELECT typeName from eve.invTypes where typeID = %s""", (id,))
if len(query.record) != 1:
return None
for row in query.record:
with pool.reserve() as mc:
mc.set("itemName" + str(id), row['typeName'])
return row['typeName']
def _delrule(self, data):
sql = "DELETE FROM `white_list_users` WHERE `token` LIKE '{0}' AND `user_id` IN (SELECT `id` AS `user_id` FROM `users` WHERE `username` LIKE '{1}' )"
if data["sender"] != "" and data["recipient"] != "":
try:
query = PySQLPool.getNewQuery(self._sql_pool, True)
query.Query(sql.format(data["recipient"], data["sender"]))
return True
except:
logging.warn(
"Error in deleting a rule for UserLdap policy. Traceback: \n{0}\n".format(traceback.format_exc())
)
return False
def regionList():
try:
with pool.reserve() as mc:
regions = mc.get("regions")
if regions != None:
return regions
except:
pass
query = PySQLPool.getNewQuery(db)
query.Query("""SELECT regionID, regionName from eve.mapRegions""")
retVal = {}
for row in query.record:
retVal[int(row['regionID'])] = row['regionName']
with pool.reserve() as mc:
mc.set("regions", retVal)
return retVal
def regionList():
try:
with pool.reserve() as mc:
regions = mc.get("regions")
if regions != None:
return regions
except:
pass
query = PySQLPool.getNewQuery(db)
query.Query("""SELECT regionID, regionName from eve.mapRegions""")
retVal = {}
for row in query.record:
retVal[int(row['regionID'])] = row['regionName']
with pool.reserve() as mc:
mc.set("regions", retVal)
return retVal
def _loadsql(self):
try:
sql_1 = "SELECT `token`, `action` FROM `white_list_email`"
res = {}
query = PySQLPool.getNewQuery(self._sql_pool, True)
query.Query(sql_1)
for row in query.record:
res[row["token"]] = row["action"].lower()
return res
except:
if self._debug:
logging.warn("Error in getting SQL data for Domain policy. Traceback: \n{0}\n".format(traceback.format_exc()))
return None
def get_changed_ip_accord_busi(business_name):
'''
:param business_name:
:return:ip_info
根据business名称，获得变更实例，时期和涉及ip
'''
sql = config_mysql.CHANGE_EVENT_INFO.format(business_name)
query = PySQLPool.getNewQuery(connection)
change_info = []
result = {}
result_2 = []
try:
query.Query(sql)
change_info = list(query.record)
except Exception, e:
print e.message
def itemName(id):
try:
with pool.reserve() as mc:
name = mc.get("itemName" + str(id))
if name != None:
return name
except:
pass
query = PySQLPool.getNewQuery(db)
query.Query("""SELECT typeName from eve.invTypes where typeID = %s""",
(id, ))
if len(query.record) != 1:
return None
for row in query.record:
with pool.reserve() as mc:
mc.set("itemName" + str(id), row['typeName'])
return row['typeName']
def _delrule(self, data):
sql_1 = "DELETE FROM `white_list_users` WHERE `user_id` = '{0}' AND `mail` = '{1}'"
if data["sasl_username"] != "" and data["sender"] != "" and data["recipient"] != "":
try:
query = PySQLPool.getNewQuery(self._sql_pool, True)
if self._uid_users.has_key(data["sasl_username"]):
tmp = self._uid_users[data["sasl_username"]]
if self._debug:
logging.debug("Deleting SQL request: " + sql_1.format(tmp, data["recipient"]))
query.Query(sql_1.format(tmp, data["recipient"]))
return True
else:
return False
except:
logging.warn("Error in deleting a rule for UserLdap policy. Traceback: \n{0}\n".format(traceback.format_exc()))
return False
def _addrule(self, data, answer = "dspam_innocent"):
if data["sasl_username"] != "" and data["sender"] != "" and data["recipient"] != "":
sql_1 = "INSERT IGNORE INTO `white_list_users` VALUES(NULL, {0}, '{1}', '{2}')"
try:
query = PySQLPool.getNewQuery(self._sql_pool, True)
if self._uid_users.has_key(data["sasl_username"]):
tmp = self._uid_users[data["sasl_username"]]
if self._debug:
logging.debug("Adding SQL request: " + sql_1.format(tmp, data["recipient"], answer))
query.Query(sql_1.format(tmp, data["recipient"], answer))
return True
else:
return False
except:
logging.warn("Error in creating a rule for UserLdap policy. Traceback: \n{0}\n".format(traceback.format_exc()))
return False
def regionData(id):
if regionName(id):
doc = Document()
prices = doc.createElement("prices")
doc.appendChild(prices)
query = PySQLPool.getNewQuery(db)
query.Query(
"""select SQL_NO_CACHE eve.invTypes.typeName as itemName, app.prices.* from app.prices left join eve.invTypes on app.prices.itemid = eve.invTypes.typeID where app.prices.region = %s order by eve.invTypes.typeName asc""",
(id, ))
for row in query.record:
item = doc.createElement("item")
item.setAttribute("name", row['itemName'])
item.setAttribute("buyMean", str(row['buymean']))
item.setAttribute("buyAvg", str(row['buyavg']))
item.setAttribute("sellMean", str(row['sellmean']))
item.setAttribute("sellAvg", str(row['sellavg']))
item.setAttribute("buy", str(row['buy']))
item.setAttribute("sell", str(row['sell']))
prices.appendChild(item)
return doc.toprettyxml(indent="")
def getQueryObject(**kwargs):
"""
Get a new connection from the PySQLPool
@return a new connection, of None if an error has occured
"""
try:
conn = PySQLPool.getNewConnection(host='localhost',
username='******',
password='',
schema='test',
port=3306,
commitOnEnd=True)
query = PySQLPool.getNewQuery(connection=conn)
return query
#something went wrong
except Exception, e:
logging.error("Could not get query object: %s", e)
return None
def query_assocmeta_range_snp(chrrange,
dbConnection,
columns="*",
maxpval=0.05):
# get IDs
temp = chrrange.split(":")
chr = int(temp[0])
temp2 = temp[1].split("-")
p1 = int(temp2[0])
p2 = int(temp2[1])
cols = ",".join(["a." + x for x in columns.split(",")])
SQL = """SELECT {0}, b.name, b.rsid, b.chr, b.pos, b.allele1 AS a1, b.allele2 AS a2 FROM assoc_meta a, snp b
WHERE a.snp=b.name
AND b.chr = {1}
AND b.pos >= {2}
AND b.pos <= {3}
AND a.pval < {4}
ORDER BY a.pval""".format(cols, chr, p1, p2, maxpval)
query = PySQLPool.getNewQuery(dbConnection)
query.Query(SQL)
return query.record
def repoApi():
retVal = {}
doc = Document()
refitems = doc.createElement("refitems")
doc.appendChild(refitems)
query = PySQLPool.getNewQuery(db)
prices = getMineralBasket(10000030)
query.Query("SELECT * FROM repromin where rate > 3")
for row in query.record:
item = doc.createElement("item")
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'] * .95
item.setAttribute("typeID", str(row['typeID']))
refitems.appendChild(item)
item.appendChild(doc.createTextNode(str(refValue)))
return doc.toprettyxml(indent="")
def Update(self, sSql, args=None):
self.Debug(sSql)
update = PySQLPool.getNewQuery(self.pDBConn)
update.query(sSql, args)
return update.affectedRows
def ExcuteSqlBat(self, sSqls, args=None):
inserts = PySQLPool.getNewQuery(self.pDBConn)
inserts.query(sSqls, args)
return inserts.lastInsertID
def ExcuteSql(self, sSql, args=None):
self.Debug(sSql)
insert = PySQLPool.getNewQuery(self.pDBConn)
insert.query(sSql, args)
return insert.lastInsertID
def Query(self, sSql, args=None):
self.Debug(sSql)
query = PySQLPool.getNewQuery(self.pDBConn)
query.query(sSql, args)
return query.record
def getQueryBugzilla():
return PySQLPool.getNewQuery(bugzillaCon)
def run(self):
try:
poolQuery = PySQLPool.getNewQuery(self.connection,
commitOnEnd=True)
except:
self.logger.error('can\'t retrieve new pool query for thread')
return
#self.logger.info( 'initiating
#query = 'insert into rec( sid_cam, sid_status, dt ) values( \'%s\', \'created\', \'%s\' )' % ( str( self.sid ), self.starttime )
query = 'insert into rec( sid_cam, sid_status ) values( \'%s\', \'created\' )' % str(
self.sid)
self.recid = self._dbQuery(poolQuery, query, 'insert')
if self.recid == -1:
self.logger.error('database insert error for sid \'%s\'' %
self.sid)
return
self.logger.info('initiating dumping')
r = self._dump()
self.logger.debug('dumping completed with return code %s' % r)
if r != 0:
self.logger.warning(
'rtmdump failed') # don't kill thread in that case
# self.logger.error( 'rtmpdump failed' )
# return
query = 'update rec set sid_status = \'dumped\' where id = \'%s\'' % self.recid
if self._dbQuery(poolQuery, query) == -1:
self.logger.error('database update error for sid \'%s\'' %
self.sid)
return
self.logger.info('initiating indexing')
r = self._index()
self.logger.debug('indexing completed with return code %s' % r)
if r != 0:
self.logger.error('yamdi failed')
return
query = 'update rec set sid_status = \'tagged\' where id = \'%s\'' % self.recid
if self._dbQuery(poolQuery, query) == -1:
self.logger.error('database update error for sid \'%s\'' %
self.sid)
return
self.logger.info('initiating metadata analysis')
r = self._processMeta()
self.logger.debug('metadata analysis completed with return code %s' %
r)
if (r != 0) or ('duration' not in self.keys):
self.logger.error('metadata analysis failed')
return
query = 'update rec set sid_status = \'ready\', duration = \'%s\' where id = \'%s\'' \
% ( int( float( self.keys[ 'duration' ] ) ), self.recid )
if self._dbQuery(poolQuery, query) == -1:
self.logger.error('database update error for sid \'%s\'' %
self.sid)
return
self.logger.info('initiating cleanup')
r = self._cleanup()
self.logger.debug('cleanup completed with return code %s' % r)
self.logger.info(
'all operations completed. thread should be terminated now')