def crawl_transaction_by_search(self, args):
""" 依据商圈或小区 爬取一页历史成交房源 """
search_key, page = args
url_page = self.base_url + f"chengjiao/pg{page}rs{search_key}/"
content = self.request_fn(url_page)
soup = BeautifulSoup(content, self.bs4_parser)
logging.debug('@crawl_transaction_by_search: {0} - page - {1}: {2}'.format(search_key, page, url_page))
session = DBSession()
for ul_tag in soup.find_all("ul", class_="listContent"):
for item_tag in ul_tag.find_all("li"):
try:
info_dict = self.parse_transaction_content(item_tag)
query = session.query(TransactionInfo).filter(TransactionInfo.id == info_dict['id'])
if query.first():
query.update(info_dict)
else:
session.add(TransactionInfo(**info_dict))
session.commit()
logging.debug('@crawl_transaction_by_search: {0} - page - {1}: {2}'.format(
search_key, page, info_dict))
except Exception as e:
logging.exception('@crawl_transaction_by_search: {0} - page - {1}: {2}'.format(
search_key, page, e))
time.sleep(3)
logging.info('@crawl_transaction_by_search: {0} - page - {1} complete.'.format(search_key, page))
def crawl_community_by_district(self, args):
""" 根据区县爬取一页小区信息 """
district, page = args
url_page = self.base_url + f"xiaoqu/{district}/pg{page}/"
content = self.request_fn(url_page)
soup = BeautifulSoup(content, self.bs4_parser)
logging.debug('@crawl_community_by_district: {0} - page - {1}: {2}'.format(district, page, url_page))
session = DBSession()
for ul_tag in soup.find_all("ul", class_="listContent"):
for item_tag in ul_tag.find_all("li"):
try:
info_dict = self.parse_community_content(item_tag)
query = session.query(CommunityInfo).filter(CommunityInfo.id == info_dict['id'])
if query.first():
query.update(info_dict)
else:
session.add(CommunityInfo(**info_dict))
session.commit()
logging.debug('@crawl_community_by_district: {0} - page - {1}: {2}'.format(district, page, info_dict))
except Exception as e:
session.rollback()
logging.exception('@crawl_community_by_district: {0} - page - {1}: {2}'.format(district, page, e))
time.sleep(3)
session.close()
logging.info('@crawl_community_by_district: {0} - page - {1} complete.'.format(district, page))
def crawl_sale_by_search(self, args):
""" 根据商圈或社区爬取一页在售房源 """
search_key, page = args
url_page = self.base_url + f"ershoufang/pg{page}rs{search_key}/"
content = self.request_fn(url_page)
soup = BeautifulSoup(content, self.bs4_parser)
logging.debug('@crawl_sale_by_search: {0} - page - {1}: {2}'.format(search_key, page, url_page))
session = DBSession()
for ul_tag in soup.find_all("ul", class_="sellListContent"):
for item_tag in ul_tag.find_all("li"):
try:
info_dict = self.parse_sale_content(item_tag)
logging.debug('@crawl_sale_by_search: {0} - page - {1}: {2}'.format(search_key, page, info_dict))
sale_info = SaleInfo(**info_dict)
if not sale_info.house_id or not sale_info.community_id or not sale_info.district:
continue
session.add(sale_info)
except Exception as e:
session.rollback()
logging.exception('@crawl_sale_by_search: {0} - page - {1}: {2}'.format(search_key, page, e))
time.sleep(3)
session.commit()
session.close()
logging.info('@crawl_sale_by_search: {0} - page - {1} complete.'.format(search_key, page))
def post(self):
class_id = self.get_body_argument('class_id', None)
title = self.get_body_argument('title', None)
image_url = self.get_body_argument('image_url', None)
note = self.get_body_argument('note', None)
content = self.get_body_argument('content', None)
now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
data = {
'class_id': class_id,
'title': title,
'image_url': image_url,
'note': note,
'content': content,
'author': 'LiJiaF',
'create_date': now,
'write_date': now
}
log.info('添加文章：' + json.dumps(data))
try:
session = DBSession()
new_article = Article(**data)
session.add(new_article)
session.commit()
session.close()
except Exception as e:
log.error(e)
return self.finish(json.dumps({'code': -1, 'msg': '添加失败'}))
return self.finish(json.dumps({'code': 0, 'msg': '添加成功'}))
def create_entry(data):
"""Write data to database
"""
data = map_data(data)
entry = Entry(date=datetime.now(), **data)
DBSession.add(entry)
DBSession.commit()
log.debug("Write entry %r", data)
def set_password(self, new_password):
salt = random_str()
password_md5 = md5(new_password.encode('utf-8')).hexdigest()
password_final = md5((password_md5 + salt).encode('utf-8')).hexdigest()
session = DBSession()
self.salt = salt
self.password = password_final
session.add(self)
session.commit()
def flush(self, oracleCursor):
# execute in oracle VIEW
oracleCursor.execute('select * from MEP_ASSETREPORT')
self.all = self.cursor.fetchall()
for eachline in range(len(self.all)):
DBSession.add(ASSET(eachline, self.all[eachline][1], self.all[eachline][2], self.all[eachline][3], self.all[eachline][4], self.all[eachline][5], self.all[eachline][6], self.all[eachline][7], self.all[eachline][8], self.all[eachline][9], self.all[eachline][10], self.all[eachline][11], self.all[eachline][13], self.all[eachline][14], self.all[eachline][15], self.all[eachline][17], self.all[eachline][22]))
DBSession.commit()
def set_password(self, new_password):
salt = random_str()
password_md5 = md5(new_password.encode('utf-8')).hexdigest()
password_final = md5((password_md5 + salt).encode('utf-8')).hexdigest()
session = DBSession()
self.salt = salt
self.password = password_final
session.add(self)
session.commit()
def save_stock_list(stock_list):
session = DBSession()
for stock in stock_list:
symbol = stock["symbol"]
name = stock["name"]
stock_model = Stock(symbol, name)
session.add(stock_model)
session.commit()
session.close()
return True
def new(cls, username, password):
salt = random_str()
password_md5 = md5(password.encode('utf-8')).hexdigest()
password_final = md5((password_md5 + salt).encode('utf-8')).hexdigest()
level = USER_LEVEL.ADMIN if cls.count() == 0 else USER_LEVEL.NORMAL # 首个用户赋予admin权限
the_time = int(time.time())
session = DBSession()
ret = cls(username=username, password=password_final, salt=salt, level=level, key=random_str(32),
key_time = the_time, reg_time = the_time)
session.add(ret)
session.commit()
return ret
def add_article(request):
if 'form.submitted' in request.POST:
with transaction.manager:
article = Article(title=request.POST['title'],
body=request.POST['body'])
DBSession.add(article)
return HTTPFound(location=route_url('article_list', request))
else:
return render_to_response(
'templates/add_article.pt',
{'back_url': route_url('article_list', request)},
request=request)
def new(cls, username, password):
salt = random_str()
password_md5 = md5(password.encode('utf-8')).hexdigest()
password_final = md5((password_md5 + salt).encode('utf-8')).hexdigest()
level = USER_LEVEL.ADMIN if cls.count() == 0 else USER_LEVEL.NORMAL # 首个用户赋予admin权限
the_time = int(time.time())
session = DBSession()
ret = cls(username=username, password=password_final, salt=salt, level=level, key=random_str(32),
key_time = the_time, reg_time = the_time)
session.add(ret)
session.commit()
return ret
def new(cls, username, password):
username = username.lower()
salt = random_str()
password_md5 = md5(password.encode('utf-8')).hexdigest()
password_final = md5((password_md5 + salt).encode('utf-8')).hexdigest()
state = USER_STATE.ADMIN if cls.count() == 0 else USER_STATE.NORMAL # first user is admin
the_time = int(time.time())
session = DBSession()
ret = cls(username=username, password=password_final, salt=salt, state=state, key=random_str(32),
key_time = the_time, reg_time = the_time)
session.add(ret)
session.commit()
return ret
def save_comment_list(symbol, comment_list):
session = DBSession()
for comment in comment_list:
id = comment["id"]
title = comment["title"]
text = comment["text"]
description = comment["description"]
comment_model = Comment(id, symbol, title, text, description)
session.add(comment_model)
session.commit()
session.close()
return True
def add_article(request):
if 'form.submitted' in request.POST:
with transaction.manager:
article = Article(
title=request.POST['title'],
body=request.POST['body']
)
DBSession.add(article)
return HTTPFound(
location=route_url('article_list', request)
)
else:
return render_to_response(
'templates/add_article.pt',
{'back_url': route_url('article_list', request)},
request=request
)
def publish():
try:
title = request.form['title']
subtitle = request.form['subtitle']
description = request.form['description']
content = request.form['content']
now = int(time.time())
session = DBSession()
new_blog = Blog(title=title,
description=description,
subtitle=subtitle,
content=content,
time=now)
session.add(new_blog)
session.commit()
session.close()
except Exception, e:
app.logger.error(e)
def main(global_config, **settings):
config = Configurator(settings=settings)
config.add_route('article_list', '/')
config.add_route('show_article', '/article/{article}')
config.add_route('add_article', '/add_article')
config.scan()
# SQL Alchemy stuff.
engine = engine_from_config(settings, 'sqlalchemy.')
DBSession.configure(bind=engine)
try:
Base.metadata.create_all(engine)
with transaction.manager:
article = Article(title='Test article', body="Test test test")
DBSession.add(article)
except IntegrityError:
print "Skipping creating, integrity error was thrown"
return config.make_wsgi_app()
class DbHelper:
def __init__(self):
self.session = DBSession()
# Method retrieves all countries from database.
def get_countries(self):
return self.session.query(Country)
# Method retrieves a specific country by its ID.
def get_country_by_id(self, country_id):
return self.session.query(Country).filter_by(id=country_id).first()
# Method retrieves all attractions associated to a specific country.
def get_attractions(self, country_id):
return self.session.query(Attraction).filter_by(
country_id=country_id).all()
# Method retrieves a specific attractions by its ID.
def get_attraction_by_id(self, attraction_id):
return self.session.query(Attraction).filter_by(id=attraction_id) \
.first()
# Method adds entry to the database.
def add_to_db(self, element):
self.session.add(element)
self.session.commit()
# Method removes an entry from the database,
def delete_from_database(self, element):
self.session.delete(element)
self.session.commit()
# Method removes all attractions associated to a specific country.
def delete_attractions_by_country_id(self, country_id):
self.session.query(Attraction).filter_by(
country_id=country_id).delete()
self.session.commit()
# Method retrieves a specific user by its e-mail.
def get_user_by_email(self, email):
return self.session.query(User).filter_by(email=email).first()
def new(cls, username, password):
username = username.lower()
salt = random_str()
password_md5 = md5(password.encode('utf-8')).hexdigest()
password_final = md5((password_md5 + salt).encode('utf-8')).hexdigest()
state = USER_STATE.ADMIN if cls.count(
) == 0 else USER_STATE.NORMAL # first user is admin
the_time = int(time.time())
session = DBSession()
ret = cls(username=username,
password=password_final,
salt=salt,
state=state,
key=random_str(32),
key_time=the_time,
reg_time=the_time)
session.add(ret)
session.commit()
return ret
def reply_text_and_get_user_profile(event):
# 取出消息內User的資料
user_profile = line_bot_api.get_profile(event.source.user_id)
date = strftime("%Y-%m-%d", gmtime())
db = DBSession()
userIds = db.query(userinfo.user_id).all()
if userIds == []:
db = DBSession()
user = userinfo(date, user_profile.display_name,
user_profile.picture_url, user_profile.status_message,
user_profile.user_id)
db.add(user)
db.commit()
db.close()
line_bot_api.push_message(user_profile.user_id,
TextSendMessage(text='Hello'))
def getFlushoftable(self, signal='ALL'):
# No.1 clear table of gamename
localGameName = DBSession.query(Gameinform).all()
if len(localGameName) != 0:
for eachGamename in range(len(localGameName)):
DBSession.delete(localGameName[eachGamename])
DBSession.commit()
# No.2 get each Game information & fill in table
self.flush = Urlex().getInformationMultiple(signal)
for key,value in self.flush.items():
if key != 'NULL':
DBSession.add(Gameinform(self.count, key, value['ipaddress'], value['port'], value['dbname']))
self.count += 1
DBSession.commit()
def _save_payment_transaction(self, tx_hash, miners, amounts):
amounts_str = []
for amount in amounts:
amounts_str.append(str(amount))
db_session = DBSession()
try:
pt = PaymentTransaction()
data = {
"miners": miners,
"amounts": amounts_str,
}
pt.transaction_data = json.dumps(data)
pt.transaction_hash = tx_hash
# 0: failed, 1: success, 2: pending
pt.transaction_status(2)
db_session.add(pt)
db_session.commit()
except Exception as e:
self._logger.warning(f'save payment transaction fail! err:{e}')
finally:
db_session.rollback()
def crawling(company_short_name):
param = {'first': 'false', 'pn': 0, 'kd': ''}
param = {'first': 'false', 'pn': page, 'sortField': 0, 'havemark': 0}
log = "[{}]\t抓取第 {} 页完毕, 当前页职位个数{}, 累计已抓{}, 该公司总计{}"
count = 0
for i in range(max_iter):
param['pn'] += 1
req = requests.post(job_url, data=param)
info = json.loads(req.content)
total_count = int(info['content']['positionResult']['totalCount'])
job_list = info['content']['positionResult']['result']
count += len(job_list)
print log.format(datetime.now().strftime("%Y-%m-%d %H:%M:%S"), param['pn'],
len(job_list), count, total_count)
session = DBSession()
for job in job_list:
c = Job(
company_id=company.get("companyId"),
company_full_name=company.get("companyFullName"),
company_short_name=company.get("companyShortName"),
city=company.get("city"),
position_num=company.get("positionNum"),
city_score=company.get("cityScore"),
finance_stage=company.get("financeStage"),
industry_field=company.get("industryField"),
country_score=company.get("countryScore"),
company_features=company.get("companyFeatures"),
process_rate=company.get("processRate"),
interview_remark_num=company.get("interviewRemarkNum"),
approve=company.get("approve"),
create_time=datetime.now()
)
session.add(c)
session.commit()
session.close()
time.sleep(3)
if len(job_list) == 0: break
return count
def post(self):
name = self.get_body_argument('name', None)
now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
data = {
'name': name,
'create_date': now,
'write_date': now
}
log.info('添加文章分类：' + json.dumps(data))
try:
session = DBSession()
new_class = ArticleClass(**data)
session.add(new_class)
session.commit()
session.close()
except Exception as e:
log.error(e)
return self.finish(json.dumps({'code': -1, 'msg': '添加失败'}))
return self.finish(json.dumps({'code': 0, 'msg': '添加成功'}))
email = Column(String(50), default='')
username = Column(String(50), unique=True)
password = Column(String(200))
class SSHKey(BaseModel):
__tablename__ = 'ssh_key'
id = Column(Integer, primary_key=True, autoincrement=True)
username = Column(String(200))
create_time = Column(DateTime, default=datetime.now())
private_key = Column(Text)
public_key = Column(Text)
key_password = Column(String(200), default=None, nullable=True)
cache_dir = Column(String(200), default=None, nullable=True)
def init_db():
BaseModel.metadata.create_all(bind=db)
if __name__ == "__main__":
init_db()
session = DBSession()
username = '******'
password = '******'
user = User(username=username,
password=hashlib.sha256(password).hexdigest())
session.add(user)
session.commit()
session.close()
def initdata():
# Packtype insert
getLength = initcheck(Packtype)
if int(getLength) == 0:
DBSession.add(Packtype(0,0,1,1,0))
DBSession.add(Packtype(1,1,0,1,0))
DBSession.add(Packtype(2,2,1,1,1))
DBSession.add(Packtype(3,3,0,1,1))
DBSession.add(Packtype(4,4,1,0,0))
DBSession.add(Packtype(5,5,0,0,0))
DBSession.add(Packtype(6,6,1,0,1))
DBSession.add(Packtype(7,7,0,0,1))
# CommandType insert
getCommandLength = initcheck(CommandType)
if int(getCommandLength) == 0:
DBSession.add(CommandType(0,'10001','ClientHeartBeatCmd'))
DBSession.add(CommandType(1,'10002','ServerHeartBeatCmd'))
DBSession.add(CommandType(2,'11000','ClientLoginCmd'))
DBSession.add(CommandType(3,'11001','ServerLoginCmd'))
DBSession.add(CommandType(4,'11100','ClientAllServerInformationCmd'))
DBSession.add(CommandType(5,'11101','ServerAllServerInformationCmd'))
DBSession.add(CommandType(10,'11300','ClientNumberofCurves'))
DBSession.add(CommandType(11,'11301','ServerNumberofCurves'))
DBSession.add(CommandType(12,'11400','ClientNumberofCurvesPeople'))
DBSession.add(CommandType(13,'11401','ServerNumberofCurvesPeople'))
DBSession.add(CommandType(14,'11500','ClientVerifyKeyRequest'))
DBSession.add(CommandType(15,'11501','ServerVerifyKeyRequest'))
DBSession.add(CommandType(16,'11600','ClientHistoryofCurvesPeople'))
DBSession.add(CommandType(17,'11601','ServerHistoryofCurvesPeople'))
DBSession.add(CommandType(100,'19000','ClientADUserLoginCmd'))
DBSession.add(CommandType(101,'19001','ServerADUserLoginCmd'))
DBSession.add(CommandType(110,'19500','ClientCompanySMSCmd'))
DBSession.add(CommandType(111,'19501','ServerCompanySMSCmd'))
DBSession.add(CommandType(120,'19800','ClientAutoOCinform'))
DBSession.add(CommandType(121,'19801','ServerAutoOCinform'))
DBSession.add(CommandType(150,'20000','PassEvent'))
DBSession.add(CommandType(151,'20001','PassEventReturn'))
DBSession.add(CommandType(180,'21000','AlarmUnifity'))
DBSession.add(CommandType(181,'21001','AlarmUnifityReturn'))
DBSession.add(CommandType(182,'21100','TestAlarmUnifity'))
DBSession.add(CommandType(183,'21101','TestAlarmUnifityReturn'))
DBSession.add(CommandType(190,'30000','CaptureEvent'))
DBSession.add(CommandType(191,'30001','CaptureEventReturn'))
DBSession.add(CommandType(200,'31000','EventReceive'))
DBSession.add(CommandType(201,'31001','EventReturn'))
DBSession.add(CommandType(202,'31100','TestEventReceive'))
DBSession.add(CommandType(203,'31101','TestEventReturn'))
getCommandRelation = initcheck(CommandTypeRelation)
if int(getCommandRelation) == 0:
DBSession.add(CommandTypeRelation(0,1,0))
DBSession.add(CommandTypeRelation(2,101,100))
DBSession.add(CommandTypeRelation(1,3,2))
DBSession.add(CommandTypeRelation(3,5,4))
DBSession.add(CommandTypeRelation(4,11,10))
DBSession.add(CommandTypeRelation(5,13,12))
DBSession.add(CommandTypeRelation(6,15,14))
DBSession.add(CommandTypeRelation(7,17,16))
DBSession.add(CommandTypeRelation(10,121,120))
DBSession.add(CommandTypeRelation(11,151,150))
DBSession.add(CommandTypeRelation(12,111,110))
DBSession.add(CommandTypeRelation(13,181,180))
DBSession.add(CommandTypeRelation(14,183,182))
DBSession.add(CommandTypeRelation(15,201,200))
DBSession.add(CommandTypeRelation(16,203,202))
DBSession.add(CommandTypeRelation(17,191,190))
# Validate
getValidateLength = initcheck(Validate)
if int(getValidateLength) == 0:
DBSession.add(Validate(0,'crc32'))
DBSession.add(Validate(1,'md5'))
# Encrypt
getEncryptLength = initcheck(Encrypt)
if int(getEncryptLength) == 0:
DBSession.add(Encrypt(0,0,'make encrypt with data'))
DBSession.add(Encrypt(1,1,'make encrypt without data'))
# Compress
getCompressLength = initcheck(Compress)
if int(getCompressLength) == 0:
DBSession.add(Compress(0,0,'make Zlib with data'))
DBSession.add(Compress(1,1,'make Zlib without data'))
# Translate
getTranslate = initcheck(Translate)
if int(getTranslate) == 0:
DBSession.add(Translate(0,'ZR','真如'))
DBSession.add(Translate(1,'NJ','南京'))
DBSession.add(Translate(2,'TJ','天津'))
DBSession.add(Translate(3,'WH','武汉'))
DBSession.add(Translate(4,'BJ','北京'))
DBSession.add(Translate(5,'LG','龙岗'))
DBSession.add(Translate(100,'Agent','服务器'))
DBSession.add(Translate(101,'Server','服务器'))
DBSession.add(Translate(102,'Switch','交换机'))
# NodeList
getNodeList = initcheck(NodeList)
if int(getNodeList) == 0:
DBSession.add(NodeList(1,1,'ZR','ZR-Node1-Agent','192.168.82.89',16010,'',1))
DBSession.add(NodeList(2,1,'ZR','ZR-Node2-Agent','192.168.82.89',16011,'',1))
DBSession.add(NodeList(3,1,'ZR','ZR-Node3-Agent','192.168.82.89',16012,'',1))
# Type
getTypes = initcheck(TypeVarify)
if int(getTypes) == 0:
DBSession.add(TypeVarify(1,'agentClient',1,0,'from AgentClient pass DATA'))
DBSession.add(TypeVarify(2,'agent',1,1,'from Agent pass DATA'))
DBSession.add(TypeVarify(3,'node',1,0,'from Node pass DATA'))
DBSession.add(TypeVarify(4,'main',1,0,'from Main pass DATA'))
DBSession.add(TypeVarify(5,'switch',0,1,'from switch pass DATA'))
DBSession.add(TypeVarify(6,'interface',1,1,'from interfaceServer pass and receive DATA'))
# GameList
gameList = initcheck(GameList)
if int(gameList) == 0:
DBSession.add(GameList(1,'ZT','征途游戏',1))
DBSession.add(GameList(2,'web','web社区、充值',0))
DBSession.add(GameList(4,'JR','巨人游戏',1))
DBSession.add(GameList(6,'ZTSJB','征途时间版',1))
DBSession.add(GameList(7,'ZTHJB','征途怀旧版',1))
DBSession.add(GameList(8,'JRT','巨人双版',1))
DBSession.add(GameList(10,'WWZW3','万王之王3',1))
DBSession.add(GameList(11,'JRDD','巨人嘟嘟',1))
DBSession.add(GameList(12,'XJJH','仙境江湖',1))
DBSession.add(GameList(14,'HJGD','黄金国度',1))
DBSession.add(GameList(15,'WDXSG','我的小傻瓜',1))
DBSession.add(GameList(16,'LH','龙魂',0))
DBSession.add(GameList(17,'ZT2','征途2',1))
DBSession.add(GameList(18,'LS','乱世',1))
DBSession.add(GameList(19,'XT','仙途',1))
DBSession.add(GameList(20,'LSZT','征途绿色版',1))
DBSession.add(GameList(22,'JRQZ','巨人前传',0))
DBSession.add(GameList(24,'JZWM','九州文明',0))
DBSession.add(GameList(25,'AEZG','艾尔之光',1))
DBSession.add(GameList(27,'PMZL','飘渺之旅',1))
DBSession.add(GameList(28,'DDT','弹弹堂',1))
DBSession.add(GameList(29,'HT','皇途',1))
DBSession.add(GameList(30,'WSZN','巫师之怒',1))
DBSession.add(GameList(31,'RXWC','热血王朝',1))
DBSession.add(GameList(32,'SGZH','三国战魂',1))
DBSession.add(GameList(33,'RRDZ','绒绒大战',0))
DBSession.add(GameList(34,'HSWOL','海商王OL',1))
DBSession.add(GameList(35,'XXSJ','仙侠世界',1))
DBSession.add(GameList(36,'WS','万神',1))
DBSession.add(GameList(37,'QJ','千军',1))
DBSession.add(GameList(1026,'CSJZ','创世九州',1))
DBSession.add(GameList(1027,'CK','苍空',1))
DBSession.add(GameList(10000,'Unknown','未知 项目',1))
# EventRelation
relationEvent = initcheck(EventRelation)
if int(relationEvent) == 0:
DBSession.add(EventRelation(1,'1.1.2.2.3.3.4.6',0,'disk,memory'))
DBSession.add(EventRelation(2,'1.1.6.0.0.1.2.34.21',1,''))
DBSession.add(EventRelation(3,'1.1.6.0.0.1.2.34.21.1',0,'process'))
DBSession.add(EventRelation(4,'1.1.6.0.0.1.2.34.21.2',0,'internet'))
DBSession.add(EventRelation(5,'1.1.6.0.0.1.2.34.21.3',0,'streamlineprocess'))
# Template
templates = initcheck(Template)
if int(templates) == 0:
DBSession.add(Template(10,'agent发送服务器资产信息','agent','','1.2',30,5,'agent',1))
DBSession.add(Template(100,'人数曲线','numberofcurves','','1.1.6.0.0.1.2.34.21',30,5,'agent',1))
DBSession.add(Template(200,'测试','test','','1.1.2.2.3.3.4.6',30,5,'agent',1))
DBSession.add(Template(300,'进程获取及判断','processInform','','1.1.2.0.1.1.7',30,5,'agent',1))
# OIDrequest
oidget = initcheck(OidRequest)
if int(oidget) == 0:
DBSession.add(OidRequest('1.1.6.0.0.1.2.34.21', 'gameid', 'timepass', 'Y'))
DBSession.add(OidRequest('1.1.6.0.0.1.2.34.22', 'gameid', 'timestamp', 'N'))
# OIDvariable
oidvarget = initcheck(OIDVariable)
if int(oidvarget) == 0:
DBSession.add(OIDVariable('gameid','String','list','all'))
DBSession.add(OIDVariable('timepass','Integer','int','5'))
DBSession.add(OIDVariable('timestamp','Integer','int','1000000000'))
# CURVES
getCurves = initcheck(CURVES)
if int(getCurves) == 0:
DBSession.add(CURVES(1,'DUDU','192.168.100.171',3315,'InfoServer_DoDo'))
# alarmuser
getAlarmuser = initcheck(AlarmUser)
if int(getAlarmuser) == 0:
DBSession.add(AlarmUser(0,'majian',1,'*****@*****.**','13817992612','*****@*****.**'))
DBSession.add(AlarmUser(1,'feijun',1,'*****@*****.**','18616193305','*****@*****.**'))
DBSession.add(AlarmUser(2,'jiangchao',0,'*****@*****.**','13601674151','*****@*****.**'))
DBSession.add(AlarmUser(3,'zhaoyu',1,'*****@*****.**','13671733680','*****@*****.**'))
DBSession.add(AlarmUser(4,'changzonghui',1,'*****@*****.**','18623759189','*****@*****.**'))
# alarmgroup
getAlarmgroup = initcheck(AlarmGroup)
if int(getAlarmgroup) == 0:
DBSession.add(AlarmGroup(0,'新业务',1,1))
DBSession.add(AlarmGroup(1,'其它',-1,1))
DBSession.add(AlarmGroup(2,'基础运维',1,0))
DBSession.add(AlarmGroup(3,'监控值班',-1,1))
DBSession.add(AlarmGroup(10,'征途II项目组',1,1))
# projecttogroup
getprojecttogroup = initcheck(ProjecttoGroup)
if int(getprojecttogroup) == 0:
DBSession.add(ProjecttoGroup(0,32,0))
DBSession.add(ProjecttoGroup(1,14,2))
DBSession.add(ProjecttoGroup(2,17,10))
# alarmRelation
getAlarmrelation = initcheck(AlarmRelation)
if int(getAlarmrelation) == 0:
DBSession.add(AlarmRelation(0,0,5,0))
DBSession.add(AlarmRelation(1,1,1,0))
DBSession.add(AlarmRelation(2,1,5,1))
DBSession.add(AlarmRelation(3,2,1,2))
DBSession.add(AlarmRelation(4,3,5,0))
DBSession.add(AlarmRelation(5,3,1,1))
DBSession.add(AlarmRelation(6,4,5,0))
DBSession.add(AlarmRelation(7,0,4,10))
DBSession.add(AlarmRelation(8,1,4,10))
DBSession.add(AlarmRelation(9,2,4,10))
DBSession.add(AlarmRelation(10,3,4,10))
# thresRelation
getthresRelation = initcheck(ThresRelation)
if int(getthresRelation) == 0:
DBSession.add(ThresRelation(0,10,'processlist',0))
DBSession.add(ThresRelation(1,32,'numberofcurves',2))
DBSession.add(ThresRelation(2,32,'processlist',3))
DBSession.add(ThresRelation(3,36,'numberofcurves',4))
DBSession.add(ThresRelation(4,36,'areajudge',5))
DBSession.add(ThresRelation(5,32,'areajudge',6))
DBSession.add(ThresRelation(6,17,'numberofcurves',11))
DBSession.add(ThresRelation(7,17,'areajudge',10))
# thresNumber
getthresnumber = initcheck(ThresNumber)
if int(getthresnumber) == 0:
DBSession.add(ThresNumber(2,1,'10000','10%'))
DBSession.add(ThresNumber(3,5,'1000','50%'))
DBSession.add(ThresNumber(4,5,'5000','15%'))
DBSession.add(ThresNumber(5,5,'10000','20%'))
DBSession.add(ThresNumber(6,3,'10000','25%'))
DBSession.add(ThresNumber(10,3,'10000','35%'))
DBSession.add(ThresNumber(11,3,'10','0%'))
# eventRelationof Grade
geteventgrade = initcheck(EventGradeRelation)
if int(geteventgrade) == 0:
DBSession.add(EventGradeRelation(-1, 1, 0, 0))
DBSession.add(EventGradeRelation(1, 1, 1, 1))
DBSession.add(EventGradeRelation(2, 1, 1, 1))
DBSession.add(EventGradeRelation(3, 1, 0, 1))
DBSession.add(EventGradeRelation(4, 1, 0, 1))
DBSession.add(EventGradeRelation(5, 1, 0, 0))
# EventCircultStatus Basic
getcircultStatus = initcheck(EventCircultStatus)
if int(getcircultStatus) == 0:
DBSession.add(EventCircultStatus(0,'未处理'))
DBSession.add(EventCircultStatus(1,'已分派'))
DBSession.add(EventCircultStatus(2,'已接手'))
DBSession.add(EventCircultStatus(3,'已处理'))
DBSession.add(EventCircultStatus(4,'已验收'))
DBSession.add(EventCircultStatus(5,'已结束'))
DBSession.add(EventCircultStatus(6,'已作废'))
DBSession.add(EventCircultStatus(7,'已回滚'))
DBSession.add(EventCircultStatus(10,'已归档'))
# change infoserver name
getinfoservername = initcheck(InfoCheckGameName)
if int(getinfoservername) == 0:
DBSession.add(InfoCheckGameName('NULL','ZT'))
DBSession.add(InfoCheckGameName('JR','JR'))
DBSession.add(InfoCheckGameName('HuaiJiu','ZTHJB'))
DBSession.add(InfoCheckGameName('KOK3','WWZW3'))
DBSession.add(InfoCheckGameName('XSG','WDXSG'))
DBSession.add(InfoCheckGameName('DoDo','JRDD'))
DBSession.add(InfoCheckGameName('HuangJin','HJGD'))
DBSession.add(InfoCheckGameName('XT','XT'))
DBSession.add(InfoCheckGameName('ZTLS','LSZT'))
DBSession.add(InfoCheckGameName('XSG_ALL','XSG'))
DBSession.add(InfoCheckGameName('LH','LH'))
DBSession.add(InfoCheckGameName('ZTII','ZT2'))
DBSession.add(InfoCheckGameName('LS','LS'))
DBSession.add(InfoCheckGameName('DDT','DDT'))
DBSession.add(InfoCheckGameName('ELS','AEZG'))
DBSession.add(InfoCheckGameName('TKZC','XJJH'))
DBSession.add(InfoCheckGameName('SGZH','SGZH'))
DBSession.add(InfoCheckGameName('WSZN','WSZN'))
DBSession.add(InfoCheckGameName('WS','WS'))
DBSession.add(InfoCheckGameName('XXSJ','XXSJ'))
DBSession.add(InfoCheckGameName('CSJZ','CSJZ'))
DBSession.add(InfoCheckGameName('360','QJ'))
DBSession.add(InfoCheckGameName('CK','CK'))
# process hostname
getprocessofhostname = initcheck(HostnameToProcess)
if int(getprocessofhostname) == 0:
DBSession.add(HostnameToProcess(1, 'ZR-Agent-4.x-test', 'GateServer5'))
DBSession.add(HostnameToProcess(2, 'ZR-Agent-4.x-test', 'GateServer3'))
DBSession.add(HostnameToProcess(3, 'ZR-Agent-4.x-test', 'GateServer2'))
DBSession.add(HostnameToProcess(4, 'ZR-Agent-4.x-test', 'GateServer8'))
DBSession.add(HostnameToProcess(5, 'ZR-Agent-4.x-test', 'GateServer1'))
DBSession.add(HostnameToProcess(6, 'ZR-Agent-4.x-test', 'CharServer10'))
DBSession.add(HostnameToProcess(7, 'ZR-Agent-4.x-test', 'CharServer11'))
DBSession.add(HostnameToProcess(8, 'ZR-Agent-4.x-test', 'CharServer14'))
DBSession.add(HostnameToProcess(9, 'ZR-Agent-4.x-test', 'CharServer12'))
DBSession.add(HostnameToProcess(10, 'ZR-Agent-4.x-test', 'CharServer13'))
DBSession.add(HostnameToProcess(11, 'ZR-Agent-4.x-test', 'LoginServer'))
DBSession.add(HostnameToProcess(12, 'ZR-Agent-4.x-test', 'LogServer'))
# Event Level
geteventlevel = initcheck(EventLevel)
if int(geteventlevel) == 0:
DBSession.add(EventLevel(0, '保留位'))
DBSession.add(EventLevel(1, '重大事件'))
DBSession.add(EventLevel(2, '特大事件'))
DBSession.add(EventLevel(3, '高级事件'))
DBSession.add(EventLevel(4, '中级事件'))
DBSession.add(EventLevel(5, '低级事件'))
DBSession.add(EventLevel(6, '普通事件'))
# Game to Group addition
getgametogroup = initcheck(GameGroupRelation)
if int(getgametogroup) == 0:
DBSession.add(GameGroupRelation(1,17,0))
DBSession.add(GameGroupRelation(2,18,1))
# Event operation
geteventoperation = initcheck(EventOperation)
if int(geteventoperation) == 0:
DBSession.add(EventOperation(0,'ALLControl','全部操作权限'))
DBSession.add(EventOperation(1,'PartView','部分查看权限'))
DBSession.add(EventOperation(2,'PartSelect','部分查询权限'))
# Event transport define
geteventtransportdefine = initcheck(EventTransportDefine)
if int(geteventtransportdefine) == 0:
DBSession.add(EventTransportDefine(0,0,-1,-1,1,-1))
DBSession.add(EventTransportDefine(1,0,-1,-1,2,-1))
DBSession.add(EventTransportDefine(2,1,2,0,3,-1))
DBSession.add(EventTransportDefine(3,2,1,0,3,-1))
DBSession.add(EventTransportDefine(4,3,-1,1,4,1))
DBSession.add(EventTransportDefine(5,3,-1,1,6,1))
DBSession.add(EventTransportDefine(6,4,-1,3,5,1))
DBSession.add(EventTransportDefine(7,4,-1,3,6,1))
DBSession.add(EventTransportDefine(8,5,-1,4,10,1))
DBSession.add(EventTransportDefine(9,6,-1,3,10,-1))
DBSession.add(EventTransportDefine(10,6,-1,4,10,-1))
DBSession.add(EventTransportDefine(11,10,-1,5,100,-1))
DBSession.add(EventTransportDefine(12,10,-1,6,100,-1))
DBSession.add(EventTransportDefine(100,100,-1,-1,-1,-1))
# responibility user
getresponibilityuser = initcheck(ResonibilityUser)
if int(getresponibilityuser) == 0:
DBSession.add(ResonibilityUser(0,1,'jiangchao','姜超',13601674151,'*****@*****.**','*****@*****.**','运维负责人','True'))
DBSession.add(ResonibilityUser(1,1,'changzonghui','常宗辉',18623759189,'*****@*****.**','*****@*****.**','运维人员','False'))
DBSession.add(ResonibilityUser(2,1,'majian','赵俊杰',13817992612,'*****@*****.**','*****@*****.**','运维人员','False'))
# responibility group
getresponibilitygroup = initcheck(ResponibilityGroup)
if int(getresponibilitygroup) == 0:
DBSession.add(ResponibilityGroup(1,'yunwei','运维部'))
# responibility relation
getresponibilityrelation = initcheck(ResponibilityRelation)
if int(getresponibilityrelation) == 0:
DBSession.add(ResponibilityRelation(1,300,1))
DBSession.commit()
def refresh_key(self):
session = DBSession()
self.key = random_str(32)
self.key_time = int(time.time())
session.add(self)
session.commit()
from model import DBSession
from model.attraction import Attraction
from model.country import Country
from model.user import User
''' populatedb is used to populate the db with some information related to
countries and their attraction points.
'''
# Create the admin user. All countries and attractions created here will be
# associated with him.
session = DBSession()
admin = User(name='Bilbo Baggins', email="*****@*****.**")
session.add(admin)
# Create Brazil.
brazil = Country(
user=admin,
name="Brazil",
description="Brazil, officially the Federative Republic of Brazil, "
"is the largest country in both South America and Latin "
"America. At 8.5 million square kilometers (3.2 million "
"square miles) and with over 208 million people, Brazil is "
"the world's fifth-largest country by area and the "
"sixth-most populous. The capital is Brasília, and the "
"most-populated city is São Paulo. It is the largest country "
"to have Portuguese as an official language and the only one "
"in the Americas. Bounded by the Atlantic Ocean on the east, "
"Brazil has a coastline of 7,491 kilometers (4,655 mi). It "
def _save_payments_info(self, tx_receipt, miners, amounts, miner_pool_amount):
db_session = DBSession()
try:
# update transaction status
pt = db_session.query(PaymentTransaction)\
.filter_by(transaction_hash=self._web3.toHex(tx_receipt["transactionHash"])).first()
pt.transaction_status(tx_receipt["status"])
db_session.add(pt)
# save payments
if pt.status == PaymentTransaction.SUCCESS:
miner_payments = db_session.query(PaymentSummary).all()
miner_round_payments = db_session.query(RoundPaymentSummary)\
.filter_by(mining_round = config.MINING_ROUND).all()
payments_map = {}
for payment in miner_payments:
payments_map[payment.holder] = payment
round_payments_map = {}
for round_payment in miner_round_payments:
if round_payments_map.get(round_payment.pool_name) is None:
round_payments_map[round_payment.pool_name] = {}
round_payments_map[round_payment.pool_name][round_payment.holder] = round_payment
for miner, rewards in miner_pool_amount.items():
for pool_name, reward in rewards.items():
# save round payments
rp = RoundPayment()
rp.mining_round = config.MINING_ROUND
rp.pool_name = pool_name
rp.holder = miner.lower()
rp.amount = Decimal(reward)
rp.transaction_id = pt.id
db_session.add(rp)
# update round payment summaries
pool_round_summary = round_payments_map.get(rp.pool_name)
miner_round_summary = None
if pool_round_summary is not None:
miner_round_summary = pool_round_summary.get(rp.holder)
if miner_round_summary is not None:
miner_round_summary.paid_amount += rp.amount
else:
miner_round_summary = RoundPaymentSummary()
miner_round_summary.pool_name = pool_name
miner_round_summary.mining_round = rp.mining_round
miner_round_summary.holder = rp.holder
miner_round_summary.paid_amount = rp.amount
db_session.add(miner_round_summary)
for i in range(len(miners)):
# save payments
p = Payment()
p.holder = miners[i].lower()
p.amount = amounts[i]
p.pay_time = datetime.datetime.utcnow()
p.transaction_id = pt.id
db_session.add(p)
# update payment summaries
payment_summary = payments_map.get(p.holder)
if payment_summary is not None:
payment_summary.paid_amount += p.amount
else:
payment_summary = PaymentSummary()
payment_summary.holder = p.holder
payment_summary.paid_amount = p.amount
db_session.add(payment_summary)
else:
self._logger.warning(
f"transaction not success! tx_receipt:{tx_receipt}")
db_session.commit()
except Exception as e:
self._logger.warning(f'save payment info fail! err:{e}')
# raise exception for _check_pending_transactions
raise
finally:
db_session.rollback()
def refresh_key(self):
session = DBSession()
self.key = random_str(32)
self.key_time = int(time.time())
session.add(self)
session.commit()
def DetailforEachOid(self, name, message):
# Oid = 1.1
if name == 'insert':
''' Step 1. All information about input detail '''
tmpProjectName = ""
tmpProjectFunc = ""
tmpKernel = ""
tmpCpuCoreNum = ""
tmpSerialNum = ""
tmpZCBM = ""
tmpMemory = ""
tmpCpuType = ""
tmpModel = ""
tmpHostName = ""
tmpOS = ""
tmpManufacturer = ""
tmpEthInfo = {}
tmpTimestamp = int(round(time.time()))
Resultofbody = {}
for key,value in message.items():
if key == 'Status':
if value != 'Success':
return dict(Status='False', msg='Message check Failed.')
if type(message['Result']).__name__ == 'str':
Resultofbody = changeDict().strtodict(message['Result'])
else:
Resultofbody = message['Result']
for keys,values in Resultofbody.items():
if keys == 'Project':
for K,V in Resultofbody[keys].items():
if K == 'Name':
tmpProjectName = self.changestr(V)
elif K == 'Func':
tmpProjectFunc = self.changestr(V)
elif keys == 'HwInfo':
for KK,VV in Resultofbody[keys].items():
if KK == 'Kernel':
tmpKernel = self.changestr(VV)
elif KK == 'CpuCoreNum':
tmpCpuCoreNum = self.changestr(VV)
elif KK == 'SN':
tmpSerialNum = self.changestr(VV)
elif KK == 'ZCBM':
tmpZCBM = self.changestr(VV)
elif KK == 'Memory':
tmpMemory = self.changestr(VV)
elif KK == 'CpuType':
tmpCpuType = self.changestr(VV)
elif KK == 'Model':
tmpModel = self.changestr(VV)
elif KK == 'HostName':
tmpHostName = self.changestr(VV)
elif KK == 'OS':
tmpOS = self.changestr(VV)
elif KK == 'Manufacturer':
tmpManufacturer = self.changestr(VV)
elif keys == 'EthInfo':
for eachline in Resultofbody[keys]:
tmpStatus = ''
tmpip = ''
tmpmask = ''
tmpethname = ''
for KKK, VVV in eachline.items():
if KKK == 'status':
tmpStatus = self.changestr(VVV)
elif KKK == 'ip':
tmpip = self.changestr(VVV)
elif KKK == 'mask':
tmpmask = self.changestr(VVV)
elif KKK == 'ethname':
tmpethname = self.changestr(VVV)
tmpEthInfo[tmpethname] = dict(status=tmpStatus, ip=tmpip, mask=tmpmask)
if tmpZCBM == '':
return dict(Status='False', msg='Input Server has not ZCBM.')
''' get eth detail '''
tmpEthDict = {}
for key,value in tmpEthInfo.items():
if key == 'eth0':
getSearchofeth = DBSession.query(Ethdetail).filter(Ethdetail.ip == value['ip'], Ethdetail.mask == value['mask']).first()
if getSearchofeth:
tmpEthDict['eth0'] = getSearchofeth.eid
else:
getethcount = DBSession.query(Ethdetail).count()
getethcount = (getethcount + 1)
DBSession.add(Ethdetail(getethcount,value['status'],value['ip'],value['mask'],'eth0'))
tmpEthDict['eth0'] = getethcount
elif key == 'eth1':
getSearchofethone = DBSession.query(Ethdetail).filter(Ethdetail.ip == value['ip'], Ethdetail.mask == value['mask']).first()
if getSearchofethone:
tmpEthDict['eth1'] = getSearchofethone.eid
else:
getethcountone = DBSession.query(Ethdetail).count()
getethcountone = (getethcountone + 1)
DBSession.add(Ethdetail(getethcountone,value['status'],value['ip'],value['mask'],'eth1'))
tmpEthDict['eth1'] = getethcountone
''' Step 2. check server information exist. '''
getSearchofHardware = DBSession.query(AssetForAgent).filter(AssetForAgent.ZCBM == tmpZCBM).first()
if getSearchofHardware:
try:
if int(getSearchofHardware.Timestamp) < message['SendTime']:
DBSession.delete(getSearchofHardware)
DBSession.commit()
tmpeth0 = ""
tmpeth1 = ""
for key,value in tmpEthDict.items():
if key == 'eth0':
tmpeth0 = value
elif key == 'eth1':
tmpeth1 = value
getCountofeth = DBSession.query(EthInfo).count()
getCountofeth = (getCountofeth + 1)
DBSession.add(EthInfo(getCountofeth,tmpeth0,tmpeth1,'None','None'))
DBSession.add(AssetForAgent(tmpProjectName, tmpProjectFunc, tmpKernel, tmpCpuCoreNum, tmpSerialNum, tmpZCBM, tmpMemory, tmpCpuType, tmpModel, tmpHostName, tmpOS, tmpManufacturer, message['SendTime']))
DBSession.commit()
getTmpid = DBSession.query(AssetForAgent).filter_by(ZCBM = tmpZCBM).first()
if getTmpid:
Tmpid = getTmpid.Hid
else:
DBSession.rollback()
return dict(Status='False', msg='flush assetforagent Error.')
getCountofrelation = DBSession.query(AssetidtoEid).count()
getCountofrelation = int(getCountofrelation + 1)
DBSession.add(AssetidtoEid(getCountofrelation, Tmpid, getCountofeth))
DBSession.commit()
return dict(Status='Success')
else:
return dict(Status='Success', msg='Input Hostname Need not fresh.')
except Exception, e:
DBSession.commit()
return dict(Status='False', msg=str(e))
else:
try:
tmpHidforinsert = ""
''' insert into table'''
DBSession.add(AssetForAgent(tmpProjectName, tmpProjectFunc, tmpKernel, tmpCpuCoreNum, tmpSerialNum, tmpZCBM, tmpMemory, tmpCpuType, tmpModel, tmpHostName, tmpOS, tmpManufacturer, message['SendTime']))
DBSession.commit()
getHid = DBSession.query(AssetForAgent).filter_by(ZCBM = tmpZCBM).first()
if getHid:
tmpHidforinsert = getHid.Hid
else:
DBSession.rollback()
return dict(Status='False', msg='insert into assetforagent error.')
for key,value in tmpEthDict.items():
if key == 'eth0':
tmpeth0 = value
elif key == 'eth1':
tmpeth1 = value
getCountofeth = DBSession.query(EthInfo).count()
getCountofeth = (getCountofeth + 1)
DBSession.add(EthInfo(getCountofeth,tmpeth0,tmpeth1,'None','None'))
getCountofrelation = DBSession.query(AssetidtoEid).count()
getCountofrelation = int(getCountofrelation + 1)
DBSession.add(AssetidtoEid(getCountofrelation, tmpHidforinsert, getCountofeth))
except Exception, e:
DBSession.rollback()
return dict(Status='False', msg=str(e))
DBSession.commit()
return dict(Status='Success')
for q_tag in tags:
# 尝试从cache中取出标签
c_tag = get_tag_from_cache(q_tag)
if c_tag:
# 不空表示在cache中，取出tag，count++
# 这个能不能修改cache里面的值啊，不知道c_tag返回的是副本还是引用
c_tag['count'] += 1
else:
# 不在缓存中，尝试从数据库中取出
query = session.query(Tag).filter(Tag.tag_name == q_tag,
Tag.date == date)
r_db_counter += 1
if query.all(): # tag在数据库中
tag_in_db = query.first()
count = tag_in_db.tag_count # 从数据库中取出的count，+1
count += 1
query.update({Tag.tag_count: count}, synchronize_session=False)
else:
# 不在数据库中，新建写入
n_db_tag = Tag(tag_name=q_tag, tag_count=1, date=date)
session.add(n_db_tag)
session.commit()
w_db_counter += 1
# 清空cache
pop_cache_tag()
session.close()
print '%s 统计完成' % current_time()
print 'cache命中 %d' % cache_hit
print '读数据库次数 %d' % r_db_counter
print '写数据库次数 %d' % w_db_counter
