def crawl_problem_plats():
"""
问题平台 https://shuju.wdzj.com/problem-1.html
"""
url = "https://shuju.wdzj.com/problem-list-all.html"
params = {"year": ""}
response = requests.get(url, params=params, headers=HEADERS)
json_data = response.json()
problem_plats = json_data.get('problemList')
for problem_plat in problem_plats:
session = DBSession()
plat_id = problem_plat.get('platId')
wdzj_id = problem_plat.get('wdzjPlatId')
plat_name = problem_plat.get('platName')
if wdzj_id != 0:
session.execute(
"""
INSERT INTO products
(plat_id, wdzj_id, name)
select
'{plat_id}', '{wdzj_id}', '{plat_name}'
WHERE not EXISTS (SELECT *
FROM products
WHERE plat_id = '{plat_id}');
""".format(
plat_id=plat_id, wdzj_id=wdzj_id, plat_name=plat_name
)
)
new_problem_plat = ProblemPlat(
plat_id=problem_plat.get('platId'), # plat_id
wdzj_id=problem_plat.get('wdzjPlatId'), # wdzj_id
plat_name=problem_plat.get('platName'), # plat_name
area=problem_plat.get('area'), # 地区
oneline_time=problem_plat.get('onlineTime'), # 上线时间
problem_date=problem_plat.get('problemTime'), # 问题时间
event_type=problem_plat.get('type'), # 事件类型
people_num=problem_plat.get('peopleNumber'),
status1=problem_plat.get('status1'), # 保留字段status1
status2=problem_plat.get('status2') # 保留字段status2
)
session.add(new_problem_plat)
session.commit()
session.close()
def update_stop(context, request):
stop = json.loads(request.POST["stop"])
session = DBSession()
session.query(Stop).filter(Stop.id == stop["id"]).update(
{
Stop.name: stop["name"].encode("UTF-8"),
Stop.is_bench: None if stop["is_bench"] == "None" else stop["is_bench"],
Stop.is_shelter: None if stop["is_shelter"] == "None" else stop["is_shelter"],
Stop.comment: stop["comment"].encode("UTF-8"),
Stop.panorama_link: stop["panorama_link"],
Stop.check_status_type_id: stop["is_check"],
Stop.is_help: str_to_boolean(stop["is_help"]),
},
synchronize_session=False,
)
# raw sql about https://groups.google.com/forum/?fromgroups=#!topic/geoalchemy/vSAlsuhwWfo
sql = "UPDATE stops SET geom=GeomFromText(:wkt, 4326) WHERE id = :stop_id"
session.execute(sql, {"wkt": "POINT(%s %s)" % (stop["lon"], stop["lat"]), "stop_id": stop["id"]})
sql_routes = sql_generate_for_many_to_many(
session,
stop["routes"],
["stop_id", "route_id"],
{"col": "stop_id", "id": stop["id"]},
"stops_routes",
["stop_id", "route_id"],
)
if sql_routes:
session.execute(sql_routes)
sql_types = sql_generate_for_many_to_many(
session,
stop["stop_types"],
["stop_id", "stop_type_id"],
{"col": "stop_id", "id": stop["id"]},
"stops_stop_types",
["stop_id", "stop_type_id"],
)
if sql_types:
session.execute(sql_types)
log = LogStops()
log.stop_id = stop["id"]
log.user_id = request.session["u_id"]
log.time = datetime.now()
session.add(log)
transaction.commit()
return Response(json.dumps(stop))
def update_stop(context, request):
stop = json.loads(request.POST['stop'])
session = DBSession()
session.query(Stop).filter(Stop.id == stop['id']).update({
Stop.name: stop['name'].encode('UTF-8'),
Stop.is_bench: None if stop['is_bench'] == 'None' else
stop['is_bench'],
Stop.is_shelter: None if stop['is_shelter'] == 'None' else
stop['is_shelter'],
Stop.comment: stop['comment'].encode('UTF-8'),
Stop.panorama_link: stop['panorama_link'],
Stop.check_status_type_id: stop['is_check'],
Stop.is_help: str_to_boolean(stop['is_help'])
}, synchronize_session=False);
# raw sql about https://groups.google.com/forum/?fromgroups=#!topic/geoalchemy/vSAlsuhwWfo
sql = 'UPDATE stops SET geom=GeomFromText(:wkt, 4326) WHERE id = :stop_id'
session.execute(sql, {
'wkt': 'POINT(%s %s)' % (stop['lon'], stop['lat']),
'stop_id': stop['id']
})
sql_routes = sql_generate_for_many_to_many(session, stop['routes'], ['stop_id', 'route_id'],
{'col': 'stop_id', 'id': stop['id']}, 'stops_routes',
['stop_id', 'route_id'])
if sql_routes:
session.execute(sql_routes)
sql_types = sql_generate_for_many_to_many(session, stop['stop_types'], ['stop_id', 'stop_type_id'],
{'col': 'stop_id', 'id': stop['id']}, 'stops_stop_types',
['stop_id', 'stop_type_id'])
if sql_types:
session.execute(sql_types)
log = LogStops()
log.stop_id = stop['id']
log.user_id = request.session['u_id']
log.time = datetime.now()
session.add(log)
transaction.commit()
return Response(json.dumps(stop))
def crawl_plat_data(shuju_date="2020-01-062020-01-12"):
"""
平台成交数据 https://shuju.wdzj.com/platdata-1.html
"""
url = "https://shuju.wdzj.com/plat-data-custom.html"
form_data = {
"type": 0,
"shujuDate": shuju_date
}
response = requests.post(url, data=form_data)
status = response.status_code
if status != 200:
print("crawl failed. (status is not 200)")
raise CrawlFailed('crawl failed')
plats_data = response.json()
for plat_data in plats_data:
plat_id = plat_data.get('platId')
plat_name = plat_data.get('platName')
wdzj_id = plat_data.get('wdzjPlatId')
session = DBSession()
if wdzj_id != 0:
session.execute(
"""
INSERT INTO products
(plat_id, wdzj_id, name)
select
'{plat_id}', '{wdzj_id}', '{plat_name}'
WHERE not EXISTS (SELECT *
FROM products
WHERE plat_id = '{plat_id}');
""".format(
plat_id=plat_id, wdzj_id=wdzj_id, plat_name=plat_name
)
)
new_platdata = PlatData(
plat_id=plat_data.get('platId'),
wdzj_id=plat_data.get('wdzjPlatId'),
plat_name=plat_data.get('platName'),
start_date=plat_data.get('startDate'),
amount=plat_data.get('amount'),
incomeRate=plat_data.get('incomeRate'),
loanPeriod=plat_data.get('loanPeriod'),
netInflowOfThirty=plat_data.get('netInflowOfThirty'),
stayStillOfTotal=plat_data.get('stayStillOfTotal'),
fullloanTime=plat_data.get('fullloanTime'),
regCapital=plat_data.get('regCapital'),
timeOperation=plat_data.get('timeOperation'),
totalLoanNum=plat_data.get('totalLoanNum'),
bidderNum=plat_data.get('bidderNum'),
avgBidMoney=plat_data.get('avgBidMoney'),
top10DueInProportion=plat_data.get('top10DueInProportion'),
borrowerNum=plat_data.get('borrowerNum'),
avgBorrowMoney=plat_data.get('avgBorrowMoney'),
top10StayStillProportion=plat_data.get('top10StayStillProportion'),
developZhishu=plat_data.get('developZhishu'),
background=plat_data.get('background'),
newbackground=plat_data.get('newbackground'),
shuju_date=shuju_date
)
session.add(new_platdata)
session.commit()
session.close()
subject_object_id=new_perspective.object_id)
DBSession.add(new_group)
perspective_ids[str(perspective.client_id) + '_' +
str(perspective.object_id)] = {
'client_id': new_perspective.client_id,
'object_id': new_perspective.object_id
}
DBSession.flush()
old_fields_ids = dict()
fields = dict()
paradigm_persp = dict()
group_to_group = dict()
for table in [Group]:
DBSession.execute(
'select setval (\'%s_id_seq\', %s);' %
(table.__tablename__,
DBSession.query(func.max(table.id).label("id")).one().id + 1))
for field in old_DBSession.query(old_Field).filter_by(
marked_for_deletion=False).all():
parent_ids = perspective_ids.get(
str(field.parent_client_id) + '_' + str(field.parent_object_id), None)
if not parent_ids:
parent_ids = {'client_id': None, 'object_id': None}
continue
new_field = fields.get(field.entity_type, None)
if not new_field:
new_field = Field(
client_id=field.client_id,
translation_gist_client_id=translation_strings[
field.entity_type]['ids']['client_id'],
translation_gist_object_id=translation_strings[
new_group = Group(id=group.id, base_group_id=group.base_group_id,
subject_client_id=new_perspective.client_id,
subject_object_id=new_perspective.object_id)
DBSession.add(new_group)
perspective_ids[str(perspective.client_id) + '_' + str(perspective.object_id)] = {
'client_id': new_perspective.client_id,
'object_id': new_perspective.object_id}
DBSession.flush()
old_fields_ids = dict()
fields = dict()
paradigm_persp = dict()
group_to_group = dict()
for table in [Group]:
DBSession.execute(
'select setval (\'%s_id_seq\', %s);' % (
table.__tablename__, DBSession.query(func.max(table.id).label("id")).one().id + 1))
for field in old_DBSession.query(old_Field).filter_by(marked_for_deletion=False).all():
parent_ids = perspective_ids.get(str(field.parent_client_id) + '_' + str(field.parent_object_id), None)
if not parent_ids:
parent_ids = {'client_id': None, 'object_id': None}
continue
new_field = fields.get(field.entity_type, None)
if not new_field:
new_field = Field(client_id=field.client_id,
translation_gist_client_id=translation_strings[field.entity_type]['ids']['client_id'],
translation_gist_object_id=translation_strings[field.entity_type]['ids']['object_id'],
data_type_translation_gist_client_id=translation_strings[field.data_type]['ids']['client_id'],
data_type_translation_gist_object_id=translation_strings[field.data_type]['ids']['object_id'])
if 'translation' in field.entity_type.lower() or field.entity_type.lower() == 'text': # todo: looks too bad
new_field.is_translatable = True
