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 put(self):
class_id = self.get_body_argument('class_id', None)
name = self.get_body_argument('name', None)
session = DBSession()
article_class = session.query(ArticleClass).filter_by(id=class_id).first()
if not article_class:
return self.finish(json.dumps({'code': -1, 'msg': '该分类不存在'}))
now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
data = {
'name': name,
'write_date': now
}
log.info('修改文章分类：class_id ' + class_id)
log.info('修改文章分类：name ' + article_class.name + ' => ' + name)
try:
session.query(ArticleClass).filter_by(id=class_id).update(data)
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 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 query_biz_circle(cls, districts):
""" 查商圈 """
session = DBSession()
query = session.query(CommunityInfo.biz_circle) \
.filter(CommunityInfo.district.in_(districts)) \
.all()
session.commit()
session.close()
result = list(set([x[0] for x in query]))
result.sort()
return result
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()
session.close()
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 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 query_community(cls, districts=None, biz_circle=None):
""" 查小区 """
session = DBSession()
if districts:
query = session.query(CommunityInfo.community) \
.filter(CommunityInfo.district.in_(districts)) \
.all()
elif biz_circle:
query = session.query(CommunityInfo.community) \
.filter(CommunityInfo.biz_circle.in_(biz_circle)) \
.all()
else:
query = [[]]
logging.exception("@query_community: query condition un-defined.")
session.commit()
session.close()
result = list(set([x[0] for x in query]))
result.sort()
return result
def put(self):
article_id = self.get_body_argument('article_id', None)
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)
session = DBSession()
article = session.query(Article).filter_by(id=article_id).first()
if not article:
return self.finish(json.dumps({'code': -1, 'msg': '该文章不存在'}))
now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
data = {
'class_id': class_id,
'title': title,
'note': note,
'content': content,
'write_date': now
}
log.info('修改文章：article_id ' + article_id)
log.info('修改文章：class_id ' + str(article.class_id) + ' => ' + class_id)
log.info('修改文章：title ' + article.title + ' => ' + title)
log.info('修改文章：note ' + article.note + ' => ' + note)
try:
image_url = image_url.lstrip(domain_name)
if image_url != article.image_url:
data['image_url'] = image_url
if article.image_url:
os.remove(article.image_url)
session.query(Article).filter_by(id=article_id).update(data)
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 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 get(self):
class_sql = """
select ac.id, ac.name
from article_class as ac
where ac.id in (select distinct class_id from article)
order by ac.id
"""
session = DBSession()
cursor = session.execute(class_sql)
class_data = cursor.fetchall()
session.close()
class_result = []
for d in class_data:
class_result.append({'id': d.id, 'name': d.name})
self.render("404.html", data=class_result)
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()
session.close()
return ret
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 handle_message(event):
user_profile = line_bot_api.get_profile(event.source.user_id)
if event.message.text == 'Hello':
line_bot_api.reply_message(
event.reply_token,
TextSendMessage(text='Hello ! ' + user_profile.display_name))
elif event.message.text == 'How are you':
line_bot_api.reply_message(event.reply_token,
TextSendMessage(text='Good.'))
elif event.message.text == "godfrey8581":
db = DBSession()
user_ids = db.query(userinfo.user_id).all()
if user_ids != []:
for user_id in user_ids:
line_bot_api.push_message(user_id[0],
TextSendMessage(text="WWWW"))
db.commit()
db.close()
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 get_stock_list_from_db():
session = DBSession()
stock_list = session.query(Stock).all()
session.close()
return stock_list
def get(self, article_id):
new_sql = """
select a.id, a.class_id, ac.name, a.title, a.author, a.note, a.content, a.write_date
from article a
inner join article_class ac on ac.id = a.class_id
where a.id = %d
""" % int(article_id)
hot_sql = """
select a.id, ac.name, a.image_url, a.title, a.author, a.note, a.write_date
from article a
inner join article_class ac on ac.id = a.class_id
where ac.id = 1
order by id desc
limit 5
"""
class_sql = """
with cla_count as (
select class_id, count(id) as total
from article
group by class_id
)
select ac.id, ac.name, cc.total
from article_class as ac
inner join cla_count as cc on cc.class_id = ac.id
order by ac.id
"""
session = DBSession()
cursor = session.execute(new_sql)
new_data = cursor.fetchall()
cursor = session.execute(hot_sql)
hot_data = cursor.fetchall()
cursor = session.execute(class_sql)
class_data = cursor.fetchall()
session.close()
new_result = {}
for d in new_data:
new_result = {
'id': d.id,
'class_id': d.class_id,
'class_name': d.name,
'title': d.title,
'author': d.author,
'note': d.note,
'content': d.content,
'write_date': d.write_date.strftime('%Y-%m-%d %H:%M:%S'),
}
hot_result = []
for d in hot_data:
image_url = d.image_url
if not image_url:
image_url = 'article/images/default.png'
hot_result.append({
'id':
d.id,
'class_name':
d.name,
'image_url':
domain_name + image_url,
'title':
d.title,
'author':
d.author,
'note':
d.note,
'write_date':
d.write_date.strftime('%Y-%m-%d %H:%M:%S'),
})
class_result = []
for d in class_data:
class_result.append({'id': d.id, 'name': d.name, 'total': d.total})
data = {
'class_data': class_result,
'new_data': new_result,
'hot_data': hot_result
}
self.render("show.html", data=data)
def get(self, class_id):
cur_page = self.get_argument('page', '1')
page_size = 10
class_id = int(class_id) if class_id else -1
new_sql = """
select a.id, a.class_id, ac.name, a.image_url, a.title, a.author, a.note, a.write_date
from article a
inner join article_class ac on ac.id = a.class_id
where ac.id = %d
order by id desc
limit %d offset %d
""" % (class_id, page_size, (int(cur_page) - 1) * page_size)
hot_sql = """
select a.id, ac.name, a.image_url, a.title, a.author, a.note, a.write_date
from article a
inner join article_class ac on ac.id = a.class_id
where ac.id = 1
order by id desc
limit 5
"""
class_sql = """
with cla_count as (
select class_id, count(id) as total
from article
group by class_id
)
select ac.id, ac.name, cc.total
from article_class as ac
inner join cla_count as cc on cc.class_id = ac.id
order by ac.id
"""
session = DBSession()
cursor = session.execute(new_sql)
new_data = cursor.fetchall()
cursor = session.execute(hot_sql)
hot_data = cursor.fetchall()
cursor = session.execute(class_sql)
class_data = cursor.fetchall()
session.close()
new_result = []
for d in new_data:
image_url = d.image_url
if not image_url:
image_url = 'article/images/default.png'
new_result.append({
'id': d.id,
'class_id': d.class_id,
'class_name': d.name,
'image_url': domain_name + image_url,
'title': d.title,
'author': d.author,
'note': d.note,
'write_date': d.write_date.strftime('%Y-%m-%d %H:%M:%S'),
})
hot_result = []
for d in hot_data:
image_url = d.image_url
if not image_url:
image_url = 'article/images/default.png'
hot_result.append({
'id': d.id,
'class_name': d.name,
'image_url': domain_name + image_url,
'title': d.title,
'author': d.author,
'note': d.note,
'write_date': d.write_date.strftime('%Y-%m-%d %H:%M:%S'),
})
class_result = []
for d in class_data:
class_result.append({
'id': d.id,
'name': d.name,
'total': d.total
})
next_page = int(cur_page) + 1
data = {
'class_data': class_result,
'new_data': new_result,
'hot_data': hot_result,
'next_page': next_page,
'cur_class': class_id
}
self.render("list.html", data=data)
def get(self):
cur_page = self.get_argument('page', '1')
page_size = 10
new_sql = """
select a.id, a.class_id, ac.name, a.image_url, a.title, a.author, a.note, a.write_date
from article a
inner join article_class ac on ac.id = a.class_id
order by id desc
limit %d offset %d
""" % (page_size, (int(cur_page) - 1) * page_size)
hot_sql = """
select a.id, ac.name, a.image_url, a.title, a.author, a.note, a.write_date
from article a
inner join article_class ac on ac.id = a.class_id
where ac.id = 1
order by id desc
limit 5
"""
class_sql = """
select ac.id, ac.name
from article_class as ac
where ac.id in (select distinct class_id from article)
order by ac.id
"""
session = DBSession()
cursor = session.execute(new_sql)
new_data = cursor.fetchall()
cursor = session.execute(hot_sql)
hot_data = cursor.fetchall()
cursor = session.execute(class_sql)
class_data = cursor.fetchall()
session.close()
new_result = []
for d in new_data:
image_url = d.image_url
if not image_url:
image_url = 'article/images/default.png'
new_result.append({
'id':
d.id,
'class_id':
d.class_id,
'class_name':
d.name,
'image_url':
domain_name + image_url,
'title':
d.title,
'author':
d.author,
'note':
d.note,
'write_date':
d.write_date.strftime('%Y-%m-%d %H:%M:%S'),
})
hot_result = []
for d in hot_data:
image_url = d.image_url
if not image_url:
image_url = 'article/images/default.png'
hot_result.append({
'id':
d.id,
'class_name':
d.name,
'image_url':
domain_name + image_url,
'title':
d.title,
'author':
d.author,
'note':
d.note,
'write_date':
d.write_date.strftime('%Y-%m-%d %H:%M:%S'),
})
class_result = []
for d in class_data:
class_result.append({'id': d.id, 'name': d.name})
next_page = int(cur_page) + 1
data = {
'class_data': class_result,
'new_data': new_result,
'hot_data': hot_result,
'next_page': next_page
}
self.render("index.html", data=data)
