def __init__(self, collectionName):
try:
self.__collection = DBClient(
collectionName).getCollectionInstance()
except errors.PyMongoError as e:
raise errors.ConnectionFailure(
'Error in connection to Database %s' % (e.message))
def __init__(self, config):
self.activetable = config.get_database_configuration()['activetable']
self.rawtable = config.get_database_configuration()['rawtable']
self.srv_ip = config.get_jsonserver_configuration()['ip']
self.srv_port = int(config.get_jsonserver_configuration()['port'])
self.db = DBClient(config)
self.probeid = self._get_client_id_from_db()
def addEntry(entityId, filePath):
# if '\'' in entityId:
# entityId = str.replace(entityId, '\'', '\\\'')
# if '\'' in filePath:
# filePath = str.replace(filePath, '\'', '\\\'')
# sqlStr = ("""INSERT INTO """ + WikiPageUploadConfig.wikiPageUploadTableName +
# """ ( """ + WikiPageUploadConfig.wikiPageUploadTableColumns['pageId'] +
# """, """ + WikiPageUploadConfig.wikiPageUploadTableColumns['filePath'] + """ )""" +
# """ VALUES ( \'""" + entityId + """\', \'""" + filePath + """\' )""")
sqlStr = """INSERT INTO """ + WikiPageUploadConfig.wikiPageUploadTableName + """ ( """ + WikiPageUploadConfig.wikiPageUploadTableColumns['pageId'] + """, """ + WikiPageUploadConfig.wikiPageUploadTableColumns['filePath'] + """ ) VALUES ( %s, %s )"""
print DBClient.directExeQuery(sqlStr, entityId, filePath)
class Monitor(object):
def __init__(self, config):
self.config = config
self.db = DBClient(config)
self.inserted_sid = self.db.get_inserted_sid_addresses()
logger.info('Started active monitor: %d session(s).' % len(self.inserted_sid))
def run_active_measurement(self):
tot = {}
probed_ip = {}
for sid, dic in self.inserted_sid.iteritems():
if sid not in tot.keys():
tot[sid] = []
url = dic['url']
ip_addrs = dic['address']
for ip in ip_addrs:
found = False
if ip not in probed_ip.keys():
probed_ip[ip] = []
probed_ip[ip].append(sid)
if len(probed_ip[ip]) > 1:
logger.debug('IP address [%s] already computed, skipping new ping/trace' % ip)
c_sid = probed_ip[ip][0]
logger.debug('First sid found for IP [%s] : %d' % (ip, c_sid))
logger.debug('Found %d measurements. ' % len(tot[c_sid]))
ping = tot[c_sid][0]['ping']
logger.debug('Ping for IP [%s] : %s' % (ip, str(ping)))
trace = tot[c_sid][0]['trace']
logger.debug('Trace for IP [%s] : %s' % (ip, str(trace)))
found = True
else:
c_sid = sid
ping = Ping(ip)
trace = Traceroute(ip)
logger.info('Running: %s ' % ping.get_cmd())
ping.run()
logger.info('Running: %s ' % trace.get_cmd())
trace.run()
logger.debug('current %d, inserted from %d' % (sid, c_sid))
if not found:
tot[sid].append({'url': url, 'ip': ip, 'ping': ping.get_result(), 'trace': trace.get_result()})
else:
tot[sid].append({'url': url, 'ip': ip, 'ping': ping, 'trace': trace})
probed_ip[ip].append(sid)
logger.info('Computed Active Measurement for %s in session %d' % (ip, sid))
self.db.insert_active_measurement(tot)
logger.info('ping and traceroute saved into db.')
def __init__(self, Settings, BoardCode):
# 설정값을 통해 초기화를 한다.
self.workers = Settings["RW_WORKERS"]
# 읽기는 Pool로 처리한다. 몇 개가 들어오는지 예측이 불가하기 때문이다.
self.readPool = Pool(self.workers)
# 쓰기는 정해진 개수로 처리하므로 Group으로 몪는다.
self.writePool = Group()
# readPool 과 writePool 간의 통신을 위한 Queue 클래스
self.ch = Queue()
# 보드코드를 저장한다.
self.bsid = BoardCode["bsid"]
self.bun = BoardCode["bun"]
# DB 연결을 시작한다.
DBClient.__init__(self, Settings, BoardCode)
def save(self):
db = DBClient()
#将新抓取的代理存入raw_proxy
for value in self.ip:
db.put(value[0], value[1])
#存入verified_proxy
db.changeTable('verified_proxy')
for value in self.ip:
#如果verified_proxy已有此代理，则不再覆盖写入
if (not db.isKeyExists(value[0])):
db.put(value[0], '0')
class CountyDataAccessor:
dbClient = None
conId = None
def __init__(self, conId):
self.dbClient = DBClient()
self.conId = conId
def getAllWellsInCounty(self, conName, stateName):
sqlStr = 'SELECT WELL_API_COUNTY_ID FROM well_complete WHERE WELL_COUNTY = %s'
return self.dbClient.directExeQuery(sqlStr, conName)
def getInfoForWell(self, wellId):
wellDataAcc = WellDataAccessor(wellId)
return wellDataAcc.getWellPrimaryData()
def getConSecondaryData(self):
finalDataDict = {}
conName = str.split(self.conId, ',')[0].strip()
stateName = str.split(self.conId, ',')[1].strip()
for dataType in cdc.conDataDBTables:
dataDict = {}
if dataType == 'wells':
wellsInCounty = self.getAllWellsInCounty(conName, stateName)
for well in wellsInCounty:
dataDict[well[0]] = self.getInfoForWell(well[0])
finalDataDict[dataType] = dataDict
return finalDataDict
def getConPrimaryData(self):
pass
def __init__(self, config):
self.activetable = config.get_database_configuration()['activetable']
self.rawtable = config.get_database_configuration()['rawtable']
self.srv_ip = config.get_jsonserver_configuration()['ip']
self.srv_port = int(config.get_jsonserver_configuration()['port'])
self.db = DBClient(config)
self.probeid = self._get_client_id_from_db()
def update_data():
"""
更新数据库文件，估计要传个id值过来，
"""
sql = "select 1"
with DBClient() as db:
db.execute(sql)
class OperatorDataAccessor:
dbClient = None
operatorID = None
def __init__(self, operatorID):
self.dbClient = DBClient()
self.operatorID = operatorID
def getAllWellsFromOperator(self, operatorName):
sqlStr = 'SELECT WELL_API_COUNTY_ID FROM well_complete WHERE OPERATOR_NAME = %s'
return self.dbClient.directExeQuery(sqlStr, operatorName)
def getInfoForWell(self, wellId):
wellDataAcc = WellDataAccessor(wellId)
return wellDataAcc.getWellPrimaryData()
def getOperatorSecondaryData(self):
finalDataDict = {}
for dataType in odc.operatorDataDBTables:
if dataType == 'wells':
dataDict = {}
wellsInCounty = self.getAllWellsFromOperator(self.operatorID)
for well in wellsInCounty:
dataDict[well[0]] = self.getInfoForWell(well[0])
finalDataDict[dataType] = dataDict
else:
sqlStr = 'SELECT ' + odc.operatorDataSelectedColumnsFromTables[odc.operatorDataDBTables[dataType]] + ' FROM ' + \
odc.operatorDataDBTables[dataType] + \
' WHERE ' + odc.operatorDataDBTableIDs[odc.operatorDataDBTables[dataType]] + ' = \'' + self.operatorID + '\''
finalDataDict[dataType] = self.dbClient.executeQuery(sqlStr)
return finalDataDict
def getOperatorPrimaryData(self):
dataDict = {}
for table in odc.operatorDetailsTables:
try:
sqlStr = 'SELECT ' + odc.operatorDetailsTablesSelectionColumns[table] + ' FROM ' + table + ' WHERE ' + \
odc.operatorDataDBTableIDs[table] + ' = \'' + self.operatorID + '\''
except KeyError:
pass
try:
data = self.dbClient.executeDictionaryQuery(sqlStr)[0]
except IndexError:
data = {x.strip(): 'NULL' for x in str.split(odc.operatorDetailsTablesSelectionColumns[table], ',')}
dataDict.update(data)
return dataDict
def getNewIds():
newIds = {}
for entity in ManagerPageTableConfig.entityList:
sqlStr = 'SELECT ' + ManagerPageTableConfig.managerPageTableColumns['pageTitle'] + \
' FROM ' + ManagerPageTableConfig.managerPageTableName + ' WHERE ' + \
ManagerPageTableConfig.managerPageTableColumns['typeColumn'] + ' = \'' + entity + '\' AND ' + \
ManagerPageTableConfig.managerPageTableColumns['needsUpdate'] + ' = 1'
newIds[entity] = [x[0] for x in DBClient.executeQuery(sqlStr)]
return newIds
def recipe_save(name="Mystery Recipe"):
# curl -X PUT --data-urlencode "json=sample.json" http://10.73.212.155:8080/recipes/marlowe
# curl -X PUT --data-urlencode "json=cand_sf1.json" http://10.73.212.155:8080/recipes/marlowe
apiPath = os.getcwd()
path = apiPath + "/../Data/"
jsonName = request.forms.get("json")
# print jsonName
filepath = path + jsonName
f = open(filepath, 'r')
for line in f:
try:
content = line.strip()
print content
content = content.replace("\'", "\"")
client = DBClient()
resultID = client.put(content)
except Exception, em:
continue
def save_data(m: dict):
"""
保存数据到数据库，
"""
sql = "INSERT INTO `g_divcoverdata` (`type`, `name`, `suffix`, `sourcepath`, `checknum`, `status`, `dtime`)\
VALUES ( {type}, {name}, {suffix}, {sourcepath}, {checknum}, {status}, {datetime}"\
.format(m['type'], m[''], m['suffix'], m['sourcepath'], m['checknum'], m['status'],
time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
with DBClient() as db:
db.execute(sql)
def get(self, page, perView):
keys = self.keys(sort=True)
start = len(keys) - page * perView
end = len(keys) - (page - 1) * perView
start = 0 if start < 0 else start
end = 0 if end < 0 else end
query = []
for k in keys[start:end]:
query.append(DBClient.get(self, k))
query.reverse()
return json.dumps(query, ensure_ascii=False)
class WellDataAccessor:
dbClient = None
wellID = None
def __init__(self, wellID):
self.dbClient = DBClient()
self.wellID = wellID
def getWellSecondaryData(self):
finalDataDict = {}
for dataType in wdc.wellDataDBTables:
sqlStr = 'SELECT ' + wdc.wellDataSelectedColumnsFromTables[wdc.wellDataDBTables[dataType]] + ' FROM ' + \
wdc.wellDataDBTables[dataType] + \
' WHERE ' + wdc.wellDataDBTableIDs[wdc.wellDataDBTables[dataType]] + ' = \'' + self.wellID + '\''
finalDataDict[dataType] = self.dbClient.executeQuery(sqlStr)
return finalDataDict
def getWellPrimaryData(self):
dataDict = {}
for table in wdc.wellDetailsTables:
try:
sqlStr = 'SELECT ' + wdc.wellDetailsTablesSelectionColumns[table] + ' FROM ' + table + ' WHERE ' + \
wdc.wellDataDBTableIDs[table] + ' = \'' + self.wellID + '\''
except KeyError:
### Error by municipality or county
pass
##### Municipality need to be handled seperately as the name of each need to be unique.
if table == 'municipality':
sqlStr = 'SELECT CONCAT(municipality_name_long, \', \', county_name, \', \', state_code) AS \'MUNICIPALITY_FULL_NAME\' FROM municipality WHERE (COUNTY_NAME, MUNICIPALITY_NAME_LSAD) = (SELECT WELL_COUNTY, WELL_MUNICIPALITY FROM unconventional WHERE WELL_API_COUNTY_ID = \'' + self.wellID + '\')'
if table == 'county':
sqlStr = 'SELECT CONCAT(county_name, \', \', state_code) AS \'COUNTY_FULL_NAME\' FROM municipality WHERE (COUNTY_NAME, MUNICIPALITY_NAME_LSAD) = (SELECT WELL_COUNTY, WELL_MUNICIPALITY FROM unconventional WHERE WELL_API_COUNTY_ID = \'' + self.wellID + '\')'
try:
data = self.dbClient.executeDictionaryQuery(sqlStr)[0]
except IndexError:
data = {x.strip(): 'NULL' for x in str.split(wdc.wellDetailsTablesSelectionColumns[table], ',')}
dataDict.update(data)
return dataDict
def main():
# TODO: parameterize
HOST_NAME = DEF_HOST_NAME
PORT_NUMBER = DEF_PORT_NUMBER
server_class = MetricsServer #nice touch they did in the examples
dbc = DBClient()
metricd = server_class(dbc, (HOST_NAME, PORT_NUMBER), MetricsServerHandler)
print(time.asctime(), "Server Starts - %s:%s" % (HOST_NAME, PORT_NUMBER))
try:
# MAIN LOOP will make it a step
metricd.serve_forever()
###
except KeyboardInterrupt:
pass
metricd.server_close()
print(time.asctime(), "Server Stops - %s:%s" % (HOST_NAME, PORT_NUMBER))
class ActiveMonitor():
def __init__(self, config):
self.raw_table_name = config.get_database_configuration()['rawtable']
self.db = DBClient(config)
self.session_dic = self._get_inserted_sid_addresses()
logger.debug('Loaded %d sessions.' % len(self.session_dic.keys()))
def _get_inserted_sid_addresses(self):
result = {}
q = '''select distinct on (sid, session_url, remoteaddress) sid, session_url, remoteaddress
FROM %s where sid not in (select distinct sid from active)''' % self.raw_table_name
res = self.db.execute_query(q)
for tup in res:
cur_sid = tup[0]
cur_url = tup[1]
cur_addr = tup[2]
if cur_addr == '0.0.0.0':
continue
if cur_sid in result.keys():
if result[cur_sid]['url'] == cur_url:
result[cur_sid]['address'].append(cur_addr)
else:
result[cur_sid]['url'] = cur_url
result[cur_sid]['address'].append(cur_addr)
else:
result[cur_sid] = {'url': cur_url, 'address' : [cur_addr]}
#print 'result _get_inserted_sid_addresses', result
return result
# build trace object from trace files
# tracefile = traceroute files
# mtrfname = mtr file
def build_trace(self, target, tracefile, mtrfile):
t = TracerouteParser(target)
t.parse_traceroutefile(tracefile)
try:
os.remove(tracefile) # remove packed trace file as root
except OSError, e:
logger.error('Error in removing packed tracefile %s' % tracefile)
t.parse_mtrfile(mtrfile)
return json.dumps(t.get_results())
class MunicipalityDataAccessor:
dbClient = None
munId = None
def __init__(self, munId):
self.dbClient = DBClient()
self.munId = munId
def getMunSecondaryData(self):
finalDataDict = {}
munName = str.split(self.munId, ',')[0].strip()
countyName = str.split(self.munId, ',')[1].strip()
for dataType in mdc.munDataDBTables:
if dataType == 'wells':
sqlStr = 'SELECT well_api_county_id, operator_name, first_permit_issued_date, spud_date, unconventional, horizontal_well, well_status, violation_count FROM (SELECT well_complete.well_api_county_id, permit.operator_name, well_complete.first_permit_issued_date, well_complete.spud_date, well_complete.unconventional, well_complete.horizontal_well, unconventional.well_status, well_complete.violation_count, county_name, municipality_name_long FROM well_complete join unconventional on well_complete.well_api_county_id = unconventional.well_api_county_id join permit on unconventional.well_api_county_id = permit.well_api_county_id join municipality on unconventional.well_county = municipality.county_name and unconventional.well_municipality = municipality.municipality_name_lsad) AS temp WHERE county_name= %s AND municipality_name_long = %s'
finalDataDict[dataType] = self.dbClient.directExeQuery(sqlStr, countyName, munName)
return finalDataDict
def getMunPrimaryData(self):
pass
class ActiveMonitor():
def __init__(self, config):
self.raw_table_name = config.get_database_configuration()['rawtable']
self.db = DBClient(config)
self.session_dic = self._get_inserted_sid_addresses()
logger.debug('Loaded %d sessions.' % len(self.session_dic.keys()))
def _get_inserted_sid_addresses(self):
result = {}
q = '''select distinct on (sid, session_url, remoteaddress) sid, session_url, remoteaddress
FROM %s where sid not in (select distinct sid from active)''' % self.raw_table_name
res = self.db.execute_query(q)
for tup in res:
cur_sid = tup[0]
cur_url = tup[1]
cur_addr = tup[2]
if cur_addr == '0.0.0.0':
continue
if cur_sid in result.keys():
if result[cur_sid]['url'] == cur_url:
result[cur_sid]['address'].append(cur_addr)
else:
result[cur_sid]['url'] = cur_url
result[cur_sid]['address'].append(cur_addr)
else:
result[cur_sid] = {'url': cur_url, 'address': [cur_addr]}
#print 'result _get_inserted_sid_addresses', result
return result
# build trace object from trace files
# tracefile = traceroute files
# mtrfname = mtr file
def build_trace(self, target, tracefile, mtrfile):
t = TracerouteParser(target)
t.parse_traceroutefile(tracefile)
try:
os.remove(tracefile) # remove packed trace file as root
except OSError, e:
logger.error('Error in removing packed tracefile %s' % tracefile)
t.parse_mtrfile(mtrfile)
return json.dumps(t.get_results())
def setUploadSuccess(tableId):
sqlStr = ('UPDATE ' + WikiPageUploadConfig.wikiPageUploadTableName + ' SET ' +
WikiPageUploadConfig.wikiPageUploadTableColumns['needsUpload'] + ' = 0 WHERE ' +
WikiPageUploadConfig.wikiPageUploadTableColumns['tableId'] + ' = \'' + str(tableId) + '\'')
print DBClient.executeQuery(sqlStr, numOfResults=1)
def __init__(self, wellID):
self.dbClient = DBClient()
self.wellID = wellID
def __init__(self, config):
self.config = config
self.db = DBClient(config)
self.inserted_sid = self.db.get_inserted_sid_addresses()
logger.info('Started active monitor: %d session(s).' % len(self.inserted_sid))
def __INIT__(self, Setting, BoardCode):
DBClient.__INIT__(self, Setting, BoardCode)
def dbLogin():
setting = generateDefaultSetting()
return DBClient(setting, HAKSA)
class JSONClient():
def __init__(self, config):
self.activetable = config.get_database_configuration()['activetable']
self.rawtable = config.get_database_configuration()['rawtable']
self.srv_ip = config.get_jsonserver_configuration()['ip']
self.srv_port = int(config.get_jsonserver_configuration()['port'])
self.db = DBClient(config)
self.probeid = self._get_client_id_from_db()
def _get_client_id_from_db(self):
q = 'select distinct on (probe_id) probe_id from %s ' % self.rawtable
r = self.db.execute_query(q)
assert len(r) == 1
return int(r[0][0])
def prepare_and_send(self):
query = 'select * from %s where not sent' % self.activetable
res = self.db.execute_query(query)
sids = list(set([r[0] for r in res]))
local_data = {
'clientid': self.probeid,
'local': self._prepare_local_data(sids)
}
str_to_send = "local: " + json.dumps(local_data)
logger.info('sending local data... %s' %
self.send_to_srv(str_to_send, is_json=True))
for row in res:
active_data = {'clientid': self.probeid, 'ping': None, 'trace': []}
count = 0
sid = int(row[0])
session_url = row[1]
remoteaddress = row[2]
ping = json.loads(row[3])
trace = json.loads(row[4])
for steps in trace:
if len(steps) > 1:
empty_targets = [
t for t in steps if t[0] == '???' or t[1] == []
]
for empty in empty_targets:
steps.remove(empty)
count += 1
active_data['ping'] = {
'sid': sid,
'session_url': session_url,
'remoteaddress': remoteaddress,
'min': ping['min'],
'max': ping['max'],
'avg': ping['avg'],
'std': ping['std']
}
for step in trace:
step_nr = step['hop_nr']
step_addr = step['ip_addr']
step_rtt = step['rtt']
step_alias = step['endpoints']
'''
@TODO
Consider different endpoints
'''
active_data['trace'].append({
'sid': sid,
'remoteaddress': remoteaddress,
'step': step_nr,
'step_address': step_addr,
'rtt': step_rtt
})
#logger.debug('Removed %d empty step(s) from secondary path to %s.' % (count, remoteaddress))
logger.info('sending ping/trace data about [%s]: %s ' %
(remoteaddress, self.send_to_srv(active_data)))
for sent_sid in sids:
update_query = '''update %s set sent = 't' where sid = %d''' % (
self.activetable, int(sent_sid))
self.db.execute_update(update_query)
logger.info('updated sent sid on %s' % self.activetable)
def _prepare_local_data(self, sids):
l = LocalDiagnosisManager(self.db, self.probeid, sids)
return l.do_local_diagnosis()
def send_to_srv(self, data, is_json=False):
s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s.connect((self.srv_ip, self.srv_port))
if not is_json:
s.sendall(json.dumps(data) + "\n")
else:
s.sendall(data + "\n")
result = json.loads(s.recv(1024))
s.close()
return result
def send_request_for_diagnosis(self, url, time_range=6):
data = {'clientid': self.probeid, 'url': url, 'time_range': time_range}
str_to_send = 'check: ' + json.dumps(data)
s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s.connect((self.srv_ip, self.srv_port))
s.send(str_to_send + "\n")
result = json.loads(s.recv(1024))
s.close()
return result
def getUploadFiles():
sqlStr = 'SELECT ' + WikiPageUploadConfig.wikiPageUploadTableColumns['tableId'] + ', ' + \
WikiPageUploadConfig.wikiPageUploadTableColumns['filePath'] + \
' FROM ' + WikiPageUploadConfig.wikiPageUploadTableName + ' WHERE ' + \
WikiPageUploadConfig.wikiPageUploadTableColumns['needsUpload'] + ' = 1'
return [(x[0], x[1]) for x in DBClient.executeQuery(sqlStr)]
from DBClient import DBClient
#test insert and retrieval
test = DBClient('localhost', 27017)
post = {'author': 'Tom', 'text': 'test server'}
test.insertData(post)
print(test.getAllResults())
def get_data():
sql = "select * from g_divcoverdata"
with DBClient() as db:
db.execute(sql)
return db.fetchall()
class Database(object):
def __init__(self, collectionName):
try:
self.__collection = DBClient(
collectionName).getCollectionInstance()
except errors.PyMongoError as e:
raise errors.ConnectionFailure(
'Error in connection to Database %s' % (e.message))
def insertOneDocument(self, data):
try:
created_id = self.__collection.insert_one(data).inserted_id
return str(created_id)
except errors.PyMongoError as e:
raise errors.WriteError('Error in Creating Document %s' %
(e.message))
def getById(self, _id, fields={}):
if fields == {}:
fields = None
result = self.__collection.find_one({'_id': ObjectId(_id)})
if result is not None:
result['_id'] = str(result['_id'])
return result
def getByEmail(self, email):
result = self.__collection.find_one({'email': email})
if result is not None:
result['_id'] = str(result['_id'])
return result
def customGet(self, fields):
result = self.__collection.find_one(fields)
if result is not None:
result['_id'] = str(result['_id'])
return result
def updateById(self, id, data):
try:
self.__collection.update({'_id': ObjectId(id)}, {'$set': data})
return True
except errors.PyMongoError as e:
raise errors.WriteError('Error in updating Document %s' %
(e.message))
def deleteById(self, _id):
try:
self.__collection.delete_one({'_id': ObjectId(_id)})
return True
except errors.PyMongoError as e:
raise errors.WriteError('Error in deleting Document %s' %
(e.message))
def getDocumentsCount(self, query={}):
if query != {}:
return self.__collection.find(query).count()
else:
return self.__collection.count()
def paginationQuery(self,
filters={},
fields={},
skip=0,
limit=100,
sortParams=['_id', 1]):
if fields == {}:
fields = None
return list(
self.__collection.find(filters,
fields).skip(skip).limit(limit).sort(
sortParams[0], sortParams[1]))
def __init__(self):
obj = GetProxy()
obj.getPage()
obj.save()
self.db = DBClient()
self.nextline = 0
class ManageProxy:
def __init__(self):
obj = GetProxy()
obj.getPage()
obj.save()
self.db = DBClient()
self.nextline = 0
#添加新代理
def refreshProxy(self):
#TODO 写入日志
print('{} start refresh...'.format(time.strftime("%Y-%m-%d %H:%M:%S")))
obj = GetProxy()
obj.getPage()
obj.save()
#获取raw_proxy中的所有内容，返回一个list(仅包含ip和port和protocol)
def getAllRawProxy(self):
self.db.changeTable('raw_proxy')
ip = self.db.getAll()
return ip
def getAllVerifiedProxy(self):
self.db.changeTable('verified_proxy')
ip = self.db.getAll()
return ip
#检验代理可用性
def checkAll(self, speed=30):
print('{} start check...'.format(time.strftime("%Y-%m-%d %H:%M:%S")))
raw_proxy = self.getAllRawProxy()
verified_proxy = self.getAllVerifiedProxy()
thread_list = []
#多线程
for i in range(speed):
t = threading.Thread(target=self.doCheck)
thread_list.append(t)
for t in thread_list:
t.setDaemon(True)
t.start()
for t in thread_list:
t.join()
print('{} end check...'.format(time.strftime("%Y-%m-%d %H:%M:%S")))
return
#从读一条数据到写入一条数据的一个完整的操作
def doCheck(self):
global lock
lock = threading.Lock()
data = self.getNextLine()
if (data == ''):
return
proxy = data.split(' ')[0]
flag = data.split(' ')[1]
raw_proxy = self.getAllRawProxy()
if (not raw_proxy[proxy]):
#raw_proxy中没有对应的记录，删除verified_proxy中的此项记录
self.doCheck()
protocol = raw_proxy[proxy].lower()
flag = self.checkProxyValid(proxy, protocol, flag)
#更新proxy对应的flag值
self.db.update('verified_proxy', proxy, flag)
self.doCheck()
#检查单条代理记录是否有效，若无效则修改verified_proxy，将对应key的flag+1或置为-1
def checkProxyValid(self, proxy, protocol='http', flag=0):
ip = proxy.split(':')[0]
port = proxy.split(':')[1]
flag = int(flag)
if (not checkIpValid(ip, port, protocol)):
#ip无效，将flag+1
if (flag == 0):
flag = 2
elif (flag < 5):
flag += 1
else:
flag = 1
flag = str(flag)
return flag
def getNextLine(self):
global lock
verified_proxy = self.getAllVerifiedProxy()
index = 0
lock.acquire()
cur = self.nextline
self.nextline = self.nextline + 1
lock.release()
for key, value in verified_proxy.items():
if (index == cur):
break
index = index + 1
if (index == len(verified_proxy)):
return ''
else:
return key + ' ' + value
#获得所有可用代理(flag==1)
def getValidProxy(self):
verified_proxy = self.getAllVerifiedProxy()
valid_proxy = []
for key, value in verified_proxy.items():
if (value == '1'):
valid_proxy.append(key)
return valid_proxy
def getRandomValidProxy(self):
return choice(self.getValidProxy())
def recipe_show(name="Mystery Recipe"):
# return "SHOW RECIPE " + name
client = DBClient()
# print name
response.content_type = 'application/json'
return dumps(client.get(name))
def __init__(self, config):
self.scrpyer = Scrpyer()
self.mqClient = MqClient(host=config.get('mq').get('host'))
self.dbClient = DBClient(config=config.get('db'))
return
class JSONClient():
def __init__(self, config):
self.activetable = config.get_database_configuration()['activetable']
self.rawtable = config.get_database_configuration()['rawtable']
self.srv_ip = config.get_jsonserver_configuration()['ip']
self.srv_port = int(config.get_jsonserver_configuration()['port'])
self.db = DBClient(config)
self.probeid = self._get_client_id_from_db()
def _get_client_id_from_db(self):
q = 'select distinct on (probe_id) probe_id from %s ' % self.rawtable
r = self.db.execute_query(q)
assert len(r) == 1
return int(r[0][0])
def prepare_and_send(self):
query = 'select * from %s where not sent' % self.activetable
res = self.db.execute_query(query)
sids = list(set([r[0] for r in res]))
local_data = {'clientid': self.probeid, 'local': self._prepare_local_data(sids)}
str_to_send = "local: " + json.dumps(local_data)
logger.info('sending local data... %s' % self.send_to_srv(str_to_send, is_json=True))
for row in res:
active_data = {'clientid': self.probeid, 'ping': None, 'trace': []}
count = 0
sid = int(row[0])
session_url = row[1]
remoteaddress = row[2]
ping = json.loads(row[3])
trace = json.loads(row[4])
for steps in trace:
if len(steps) > 1:
empty_targets = [t for t in steps if t[0] == '???' or t[1] == []]
for empty in empty_targets:
steps.remove(empty)
count += 1
active_data['ping'] = {'sid': sid, 'session_url': session_url, 'remoteaddress': remoteaddress, 'min': ping['min'], 'max': ping['max'], 'avg': ping['avg'], 'std': ping['std']}
for step in trace:
step_nr = step['hop_nr']
step_addr = step['ip_addr']
step_rtt = step['rtt']
step_alias = step['endpoints']
'''
@TODO
Consider different endpoints
'''
active_data['trace'].append({'sid': sid, 'remoteaddress': remoteaddress, 'step': step_nr,
'step_address': step_addr, 'rtt': step_rtt})
#logger.debug('Removed %d empty step(s) from secondary path to %s.' % (count, remoteaddress))
logger.info('sending ping/trace data about [%s]: %s ' % (remoteaddress, self.send_to_srv(active_data)))
for sent_sid in sids:
update_query = '''update %s set sent = 't' where sid = %d''' % (self.activetable, int(sent_sid))
self.db.execute_update(update_query)
logger.info('updated sent sid on %s' % self.activetable)
def _prepare_local_data(self, sids):
l = LocalDiagnosisManager(self.db, self.probeid, sids)
return l.do_local_diagnosis()
def send_to_srv(self, data, is_json=False):
s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s.connect((self.srv_ip, self.srv_port))
if not is_json:
s.sendall(json.dumps(data) + "\n")
else:
s.sendall(data + "\n")
result = json.loads(s.recv(1024))
s.close()
return result
def send_request_for_diagnosis(self, url, time_range=6):
data = {'clientid': self.probeid, 'url': url, 'time_range': time_range}
str_to_send = 'check: ' + json.dumps(data)
s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s.connect((self.srv_ip, self.srv_port))
s.send(str_to_send + "\n")
result = json.loads(s.recv(1024))
s.close()
return result
def recipe_update(resturant, dish, up, value):
client = DBClient()
client.update(resturant, dish, up, value)
def __init__(self, config):
self.raw_table_name = config.get_database_configuration()['rawtable']
self.db = DBClient(config)
self.session_dic = self._get_inserted_sid_addresses()
logger.debug('Loaded %d sessions.' % len(self.session_dic.keys()))
def __init__(self, conId):
self.dbClient = DBClient()
self.conId = conId
def __init__(self, munId):
self.dbClient = DBClient()
self.munId = munId
def main() -> None:
""" Code to execute.
Parameters:
Returns:
"""
# OS AND PYTHON VERSION STUFF
os, py_version_major, py_version_minor = os_python_version_info()
# GET DATABASE CONNECTION INFO TO USE.
db_type1 = SQLITE
if db_type1 not in c.DB_TYPES:
print('UNKNOWN DATABASE TYPE.')
exit(1)
db_path1 = sample_db_path[db_type1]
username1 = sample_username[db_type1]
password1 = sample_password
hostname1 = sample_hostname
port_num1 = sample_port_num[db_type1]
instance1 = sample_instance[db_type1]
# CONNECT TO DATABASE INSTANCE SPECIFIED ABOVE.
print('\nCONNECTING TO DATABASE...')
db_instance1 = DBInstance(os, db_type1, db_path1, username1, password1,
hostname1, port_num1, instance1)
db_instance1.print_all_connection_parameters()
# CREATE DATABASE CLIENT OBJECT.
print('\nGETTING DATABASE CLIENT OBJECT...')
my_db_client = DBClient(db_instance1)
# COLUMN SEPARATOR FOR OUTPUT.
my_colsep = '|'
# SEE THE COLUMNS AND INDEXES OF ONE TABLE ACCESSIBLE TO GIVEN LOGIN.
print('\nSEE THE COLUMNS AND INDEXES OF ONE TABLE...')
my_db_client.db_table_schema(colsep=my_colsep)
print()
# SEE THE DEFINITION AND COLUMNS OF ONE VIEW ACCESSIBLE TO GIVEN LOGIN.
print('\nSEE THE DEFINITION AND COLUMNS OF ONE VIEW...')
my_db_client.db_view_schema(colsep=my_colsep)
print()
# SQL TO EXECUTE AT COMMAND LINE *AND* USING PYTHON DB API 2.0 LIBRARY
query = ("SELECT actor, title, price, categoryname\n"
"FROM PRODUCTS p INNER JOIN CATEGORIES c\n"
"ON p.category = c.category\n"
"WHERE actor = {0}\n"
"AND price < {1}{terminator}\n")
# CONSTRUCT COMMANDS AROUND QUERY TO RUN IN DATABASE COMMAND-LINE CLIENT.
pre_cmd = ''
query1 = ''
post_cmd = ''
db_client_exe = c.DB_CLIENT_EXES[db_type1].upper()
if db_client_exe == '':
z = '{} DOES NOT HAVE A COMMAND LINE INTERFACE.'
print(z.format(db_type1).upper())
elif db_type1 == MYSQL:
pre_cmd = ("SET @actor := 'CHEVY FOSTER';\n" "SET @price := 35.0;\n")
query1 = query.format('@actor', '@price', terminator=';')
elif db_type1 == ORACLE:
pre_cmd = ('SET SQLPROMPT ""\n'
'SET SQLNUMBER OFF\n'
'SET TRIMOUT ON\n'
'SET TAB OFF\n'
'SET NEWPAGE NONE\n'
'SET LINESIZE 256\n'
'SET WRAP OFF\n'
'SET COLSEP "{}"\n'
'VARIABLE actor VARCHAR2(50)\n'
'VARIABLE price NUMBER\n'
'BEGIN\n'
" :actor := 'CHEVY FOSTER';\n"
" :price := 35.0;\n"
'END;\n'
'/\n')
query1 = query.format(':actor', ':price', terminator=';')
post_cmd = 'exit\n'
elif db_type1 == POSTGRESQL:
pre_cmd = ("\\pset footer off\n"
"\\pset fieldsep {}\n"
"PREPARE x9q7z (text,numeric) AS\n")
query1 = query.format('$1', '$2', terminator=';')
post_cmd = ("EXECUTE x9q7z ('CHEVY FOSTER',35.0);\n" "\\quit\n")
elif db_type1 == SQLITE:
# .echo off Set command echo off
# .separator "|" Set column separator to pipe character
# .headers on Put in column headings (column names)
# .parameter set :x 3 Create variable "x", set it to 3
pre_cmd = ('.echo off\n'
'.separator "{}"\n'
'.headers on\n'
".parameter set :actor 'CHEVY FOSTER'\n"
".parameter set :price 35.0\n")
query1 = query.format(':actor', ':price', terminator=';')
post_cmd = '.exit\n'
elif db_type1 == SQLSERVER:
pre_cmd = (":Setvar SQLCMDCOLSEP {}\n"
"SET NOCOUNT ON\n"
"DECLARE @actor AS varchar(50);\n"
"SET @actor = 'CHEVY FOSTER';\n"
"DECLARE @price AS money;\n"
"SET @price = 35.0;\n")
query1 = query.format('@actor', '@price', terminator='')
post_cmd = ('go\n' 'exit\n')
# ASSEMBLE COMMAND PARTS INTO COMPLETE COMMAND.
if pre_cmd.find('{}') > -1:
pre_cmd = pre_cmd.format(my_colsep)
client_cmds = pre_cmd + query1 + post_cmd
# RUN ABOVE COMMANDS IN DATABASE COMMAND-LINE CLIENT.
if db_client_exe != '':
z = '\nRUNNING THESE COMMANDS IN {} COMMAND-LINE CLIENT:\n{}'
print(z.format(db_client_exe, client_cmds))
cmdline_list = db_instance1.get_cmdline_list()
sql_out = sql_cmdline(cmdline_list, client_cmds)
# SHOW OUTPUT FROM RUNNING ABOVE COMMANDS IN DB COMMAND-LINE CLIENT.
# Don't use write_rows, output often not all columnar, will cause crash.
if len(sql_out) > 0:
z = '\nTHE OUTPUT FROM RUNNING COMMANDS IN {} COMMAND-LINE CLIENT:'
print(z.format(db_client_exe))
for line in sql_out:
print(line)
print(
'ALL DONE WITH {} COMMAND-LINE CLIENT.'.format(db_client_exe))
# DONE WITH COMMANDS IN DATABASE COMMAND-LINE CLIENT.
# START CONSTRUCTING QUERY AND BIND VARIABLES TO RUN IN DB API 2.0 LIBRARY.
if db_client_exe != '':
print("\nLET'S RUN THE SAME SQL THROUGH A DB API 2.0 LIBRARY")
else:
print("\nLET'S RUN SOME SQL THROUGH A DB API 2.0 LIBRARY")
paramstyle2 = db_instance1.get_paramstyle()
# SQL & BIND VARIABLES TO EXECUTE THROUGH DB API 2.0 LIBRARY.
query2 = ''
bind_vars2 = ''
if paramstyle2 == c.NOBINDVARS:
# MS Access does not support bind variables/parameterization.
query2 = query.format("'CHEVY FOSTER'", "35.0", terminator='')
elif paramstyle2 == c.NAMED:
# oracle/cx_Oracle and sqlite/sqlite3.
query2 = query.format(':actor', ':price', terminator='')
bind_vars2 = {'actor': 'CHEVY FOSTER', 'price': 35.0}
elif paramstyle2 == c.PYFORMAT:
# mysql/pymysql and postgresql/psycopg2.
query2 = query.format("%(actor)s", "%(price)s", terminator='')
bind_vars2 = {'actor': 'CHEVY FOSTER', 'price': 35.0}
elif paramstyle2 == c.QMARK:
# sqlserver/pyodbc.
query2 = query.format('?', '?', terminator='')
bind_vars2 = ('CHEVY FOSTER', 35.0)
# SHOW THE SQL & BIND VARIABLES TO EXECUTE THROUGH DB API 2.0 LIBRARY.
print("\nHERE'S THE SQL:\n{}".format(query2))
my_db_client.set_sql(query2)
if len(bind_vars2) > 0:
print('HERE ARE THE BIND VARIABLES:\n{}'.format(bind_vars2))
my_db_client.set_bind_vars(bind_vars2)
else:
print('NO BIND VARIABLES.\n')
my_db_client.set_bind_vars(bind_vars2)
# EXECUTE THE SQL & BIND VARIABLES THROUGH DB API 2.0 LIBRARY.
print('\nGETTING THE OUTPUT OF THAT SQL:')
col_names1, rows1, row_count1 = my_db_client.run_sql()
# SET UP TO WRITE OUTPUT OF SQL EXECUTED THROUGH DB API 2.0 LIBRARY.
print('\nPREPARING TO FORMAT THAT OUTPUT, AND PRINT OR WRITE IT TO FILE.')
writer = OutputWriter(out_file_name='', align_col=True, col_sep=my_colsep)
writer.get_align_col()
writer.get_col_sep()
writer.get_out_file_name()
# WRITE OUTPUT OF SQL & BIND VARS EXECUTED THROUGH DB API 2.0 LIBRARY.
print("\nHERE'S THE OUTPUT...")
writer.write_rows(rows1, col_names1)
# CLEAN UP.
writer.close_output_file()
del writer
my_db_client.clean_up()
del my_db_client
print()
db_instance1.close_connection(del_cursors=True)
print(db_instance1.get_connection_status())
del instance1
