def createpo(data):
# 搜尋進度狀態
for i in data:
# sqlstr = "select id from status where status = \"%s\"" % data["billStatus"]
statusiidd = {
"退回程序": 1,
"審查完畢": 2,
"交付審查": 3,
"排入院會": 4,
"三讀": 5,
"逕付二讀": 6,
"撤案": 7,
"逕付二讀(委員會抽出)": 8,
"排入程序": 9,
}.get(i["billStatus"], "error")
# DB.execution(DB.select, sqlstr)
updateT = dt.now()
sqlstrs = []
sqlstr = (
"insert into proposal(`id`,`term`,`session_Period`,`session_Time`,`title`,`status_id`,`pdfUrl`,`docUrl`,`updateTime`) values('%s','%s','%s','%s','%s','%s','%s','%s','%s')"
%
(i["billNo"], i["term"], i["sessionPeriod"], i["sessionTimes"],
i["billName"], statusiidd, i["pdfUrl"], i["docUrl"], updateT))
sqlstrs.append({"sql": sqlstr, "name": i["billNo"]})
print(sqlstr + ";")
DB.execution(DB.create, sqlstrs)
def msg(account, mes, article_id, parent_id):
sqlstr = "insert into message(user_id,content,proposal_id,parent_id) values(\"%s\",\"%s\",\"%s\",\"%s\");" % (
account, mes, article_id, parent_id)
def findMessage():
sqlstr = "select id,content from message"
return DB.execution(DB.select, sqlstr)
def returnMessage(postive, iid):
sqlstr = "insert into message set postive = %s where id=\"%s\"" % (
postive, iid)
print(sqlstr)
return DB.execution(DB.create, sqlstr)
datas = findMessage()
def stringToList(string):
listRes = list(string.split(" "))
return listRes
for i in datas["data"]:
a = str(i["content"], encoding='utf-8')
stringToList(a)
s = SnowNLP(a)
if s.sentiments <= 0.4:
#f1.write(a+'\t'+str(s.sentiments)+'\n')
returnMessage(s.sentiments, i["id"])
else:
#f2.write(a + '\t' + str(s.sentiments) + '\n')
returnMessage(s.sentiments, i["id"])
return DB.execution(DB.create, sqlstr)
def returnProposer(data):
#如果爬蟲提案的id=資料庫proposal表的id，就將爬蟲的提案人與政治人物編號比對 將id傳回提案人資料表
if web.createpo(data["id"]) = proposal(data["id"]):
return web.createpo(data["billProposer"])
sqlstr = ("insert into proposer(proposal_id, politician_id ) values('%s','%s')" %(
data["proposal_id"],data["politician_id"]))
print(sqlstr)
DB.execution(DB.create, sqlstr)
def create(data):
sqlstr = "select id from status where status = \"%s\"" % data[
"billStatus"]
statusiidd = DB.execution(DB.select, sqlstr)
sqlstr = (
"insert into proposal(`id`,`term`,`session_Period`,`title`,`status_id`,`pdfUrl`,`docUrl`,`updateTime`) values('%s','%s','%s','%s','%s','%s','%s','%s')"
% (data["billNo"], data["term"], data["session_Period"],
data["title"], statusiidd["data"][0]["id"], data["pdfUrl"],
data["docUrl"], data["updateTime"]))
print(sqlstr)
DB.execution(DB.create, sqlstr)
def report(user_id, message_id, remark, rule):
sql_Remark = []
DB.execution(
DB.create, f"insert into report (`user_id`,`message_id`,`remark`) values(\"{user_id}\",{message_id},\"{remark}\")")
report_id = DB.execution(
DB.select, f"select id from(select * from report order by id) y where id in ( select max(id) from report where user_id=\"{user_id}\" )")
report_id = report_id["data"][0]["id"]
for i in rule:
sql_Remark.append(
{"sql": f"insert into `report_rule`(`report_id`,`rule_id`) values({report_id},{i});"})
return DB.execution(DB.create, sql_Remark)
def po():
soup = forClear(URL)
if soup != "error":
soup.encoding = 'utf-8'
b = list(soup.text)
data = json.loads(soup.text)
sql = []
for i in data:
if i["createDateStr"][0:3] == "110": #篩選今年的公告
c = gg(DETAIL + i["publishUrl"] + '/' + str(i["contentId"]))
c = re.sub(r'"', "'", c) #把雙引號取代成單引號
c = re.sub(r"\n", '', c) #把換行取代成空白
title = re.sub(r"'", '"', i["title"])
date = re.sub(r"\.", "-", i["createDateStr"]) #日期格式修改避免資料庫格式跑掉
sql.append({
"sql":
"insert into article (title,content,type,createTime) VALUES(\"%s\", \"%s\", %s,\"%s\")"
% (title, c, 2, "2021" + date[3:]),
"name":
i["contentId"]
})
return DB.execution(DB.create, sql)
def reportCheck(check, report_id, manager_id, time):
sqlstr = "update report set check=%s where id=\"%s\"" % (check, report_id)
if (check == 1):
sqlstr.append(
"insert into freezen (manager_id,report_id,time) values(%s,%s,%s)"
% (manager_id, report_id, time))
return DB.execution(DB.select, sqlstr)
def politician_user(p_id):
sqlstr = [{
"sql":
"SELECT p.id,p.term,f.name,p.photo,a.name as a_n,p.experience,p.degree,p.tel,pa.name as p_name,e.name as e_n,e.remark"
+
" FROM db.politician as p join electorate as e on p.electorate_id=e.id join figure as f on p.figure_id=f.id join area as a on e.area_id=a.id join party as pa on p.party_id=pa.id"
+ f" where p.id=\" {p_id} \" order by e.area_id,p.term,f.name",
"name":
"detail"
}, {
"sql":
"".join(["SELECT * FROM count_score where id =\"", p_id, "\""]),
"name":
"count_score"
}, {
"sql":
f"SELECT * FROM db.policy_top where politician_id={p_id} limit 3",
"name": "policy"
}, {
"sql": f"SELECT * FROM db.proposal_top where id={p_id} limit 3",
"name": "proposal"
}, {
"sql":
f"select sum(postive)/count(*) as score from message as m join proposal as p on m.proposal_id=p.id join proposer as er on p.id=er.proposal_id where er.politician_id={p_id}",
"name": "message"
}, {
"sql":
f"select s.*,count(s.id) as score from user_proposal as up join proposal as p on p.id =up.proposal_id join proposer as er on er.proposal_id=p.id right join stand as s on stand_id=s.id where er.politician_id={p_id} group by s.id",
"name": "stand"
}, {
"sql":
f"SELECT le.committee_id,le.politician_id,com.name FROM db.legislatorcol as le join politician as p on le.politician_id = p.id join committee as com on le.committee_id=com.id where politician_id = {p_id} group by politician_id",
"name": "committee"
}]
return DB.execution(DB.select, sqlstr)
def list(data):
strCond = ""
if (isinstance(data, dict)):
for i in data.keys():
strCond += f" {i} =\"{data[i]}\" and"
result = []
sqlstr = 'SELECT p.id,p.term,f.name,a.name as a_n ,p.experience,p.degree,p.tel FROM db.politician as p join electorate as e on p.electorate_id=e.id join figure as f on p.figure_id=f.id join area as a on e.area_id=a.id order by e.area_id'
rows = DB.execution(DB.select, sqlstr)
# return rows["data"]
position = rows["data"][0]["postition"]
area = rows["data"][0]["area"]
term = rows["data"][0]["term"]
pList = []
tList = []
dList = []
for i in rows["data"]:
if (term != i["term"]):
tList.append({"name": term, "d": dList})
aList = []
term = i["term"]
if (position != i["postition"]):
pList.append({"name": position, "d": tList})
position = i["postition"]
dList.append(i)
return pList
def changeProfile(data, account):
strCond = ""
if (isinstance(data, dict)):
for i in data.keys():
strCond += f" {i} = \"{data[i]}\" ,"
sqlstr = f"update user set {strCond[0:len(strCond)-1]} where id=\"{account}\""
return DB.execution(DB.update, sqlstr)
def msgList(proposal_id, user_id):
sqlstr = [
{"sql": "select * from message where proposal_id=\"%s\"" %
(proposal_id), "name": "msg"},
{"sql": " select p.id,p.title,p.pdfUrl ,s.status,f.name ,h.hashtag_name from proposal as p %s %s %s %s %s where p.id=\"%s\" " %
("left join proposer as er on er.proposal_id=p.id",
"left join figure as f on er.politician_id=f.id",
"left join status as s on p.status_id=s.id",
"left join proposal_category as pc on p.id=pc.propsoal_id",
"left join hashtag as h on pc.category_id=h.id",
proposal_id),
"name": "detail"},
{"sql": "select * from favorite where user_id=\"%s\" and proposal_id=\"%s\"" %
(proposal_id, user_id), "name": "heart"},{"sql":"select * from rule","name":"rule"}
]
rows = DB.execution(DB.select, sqlstr)
result = {}
category = set()
proposer = set()
print(rows)
for i in rows["data"][1]["data"]:
category.add(i["hashtag_name"])
proposer.add(i["name"])
rows["data"][1]["data"][0]["name"]=proposer
rows["data"][1]["data"][0]["category"]=category
rows["data"][1]["data"]=rows["data"][1]["data"][0]
return rows
def home():
sqlstr = [{"sql": "select * from home_policy limit 5", "name": "policy"},
{"sql": "select * from home_mes limit 5", "name": "mes"},
{"sql": "select * from home_proposal limit 5", "name": "proposal"},
{"sql": "select cs.name,round(cs.score,2) as score,p.photo from count_score as cs join politician as p on cs.id=p.id order by score desc limit 3", "name": "rank"}]
result = DB.execution(DB.select, sqlstr)
return result
def change(data, id):
strCond = ""
if(isinstance(data, dict)):
for i in data.keys():
strCond += " %s = \"%s\" ," % (i, data[i])
sqlstr = "update article set %s where id=\"%s\"" % (
strCond[0:len(strCond)-1], id)
return DB.execution(DB.update, sqlstr)
def report(user_id, message_id, remark, rule):
sql_Remark = ""
for i in rule:
sql_Remark += "insert into `report_rule`(`report_id`,`rule_id`) values(@v_report_id,%s);" % i
sqlstr = (
" set @v_report_id = 0;call db.report(\"%s\", %s, \"%s\", @v_report_id);"+sql_Remark) % (user_id, message_id, remark)
return DB.execution(DB.create, sqlstr)
def changeProfile(data, account):
strCond = ""
if (isinstance(data, dict)):
for i in data.keys():
strCond += " %s = \"%s\" ," % (i, data[i])
sqlstr = "update user set %s where id=\"%s\"" % (strCond[0:len(strCond) -
1], account)
return DB.execution(DB.update, sqlstr)
def getCond():
sqlstr = [{
"sql": "select term as name from proposal group by term;",
"name": "屆別"
}, {
"sql":
"select s.id,s.status as name from proposal as p join status as s on p.status_id=s.id group by status_id;",
"name": "狀態"
}]
return DB.execution(DB.select, sqlstr)
def msg(account, mes, article_id, parent_id):
try:
s = SnowNLP(mes)
sqlstr = f"insert into message(user_id,content,proposal_id,parent_id,postive) values(\"{account}\",\"{mes}\",\"{article_id}\",{0 if parent_id==None else parent_id},\"{s.sentiments}\");"
except ValueError:
sqlstr = f"insert into message(user_id,content,proposal_id,parent_id) values(\"{account}\",\"{mes}\",\"{article_id}\",{0 if parent_id==None else parent_id});"
print("what")
print(ValueError)
return DB.execution(DB.create, sqlstr)
def getCond():
sqlstr = [{
"sql": "SELECT name FROM db.area order by id;",
"name": "地區"
}, {
"sql": "SELECT name FROM db.figure group by name;",
"name": "姓名"
}, {
"sql": "SELECT term as name FROM db.politician group by term;",
"name": "屆別"
}]
return DB.execution(DB.select, sqlstr)
def report():
sqlstr = [{
"name":
"already",
"sql":
"select r.*,m.content from report as r join message as m on r.message_id= m.id where r.check =1"
}, {
"name":
"not_yet",
"sql":
"select r.*,m.content from report as r join message as m on r.message_id= m.id where r.check=0"
}]
return DB.execution(DB.select, sqlstr)
def getUser():
sqlstr = [{
"sql": "select * from user where identity=1",
"name": "user"
}, {
"sql": "select * from user where identity=2",
"name": "manager"
}, {
"sql": "select * from user where identity=3",
"name": "politician"
}, {
"sql": "select * from identity",
"name": "identity_type"
}]
return DB.execution(DB.select, sqlstr)
def setCateogry(user_id, add, remove):
sqlstrs = []
if len(add) > 0:
for i in add:
sqlstrs.append({
"sql":
f"insert into user_category (user_id,category_id) values(\"{user_id}\",{i}) ; "
})
removes = []
if len(remove) > 0:
for i in remove:
removes.push(f"{i}")
delStr = f"delete user_category where user_id=\"{user_id}\" and category_id in ({' , '.join(removes)})"
sqlstrs.append({"sql": delStr})
return DB.execution(DB.create, sqlstrs)
def list(data):
strCond = ""
if (isinstance(data, dict)):
for i in data.keys():
strCond += " %s =\"%s\" and" % (i, data[i])
sqlstr = "select p.*,s.status ,pc.category_id,h.hashtag_name,f.name %s %s %s %s %s %s %s " % (
"from proposal as p join `status` as s on p.status_id=s.id",
"join (select * from proposal group by id having term =10 limit 50) as t on p.id=t.id",
"left join proposal_category as pc on p.id=pc.propsoal_id",
"left join hashtag as h on pc.category_id=h.id",
"left join proposer as er on p.id=er.proposal_id",
"left join figure as f on er.politician_id=f.id",
"where " + strCond[0:len(strCond)-3] if len(strCond) > 0 else "")
rows = DB.execution(DB.select, sqlstr)
result = []
pdf = set()
category = set()
proposer = set()
term = rows["data"][0]["term"]
session_Period = rows["data"][0]["session_Period"]
session_Time = rows["data"][0]["session_Time"]
title = rows["data"][0]["title"]
status = rows["data"][0]["status"]
now = rows["data"][0]["id"]
for i in rows["data"]:
if now != i["id"]:
result.append({"id": now, "title": title, "pdfUrl": pdf, "category": category, "proposer": proposer,
"session_period": session_Period, "session_Time": session_Time, "status": status})
now = i["id"]
pdf = set()
category = set()
proposer = set()
term = i["term"]
session_Period = i["session_Period"]
session_Time = i["session_Time"]
status = i["status"]
title = i["title"]
pdf.add(i["pdfUrl"])
proposer.add(i["name"])
category.add(i["hashtag_name"])
result.append({"id": now, "title": title, "pdfUrl": pdf, "category": category, "proposer": proposer,
"session_period": session_Period, "session_Time": session_Time, "status": status})
return (result)
def msgList(proposal_id, user_id):
sqlstr = [
{"sql": "select * from rule", "name": "rule"},
{"sql":
" ".join([
"select m.*,u.name , not isnull(r.user_id) as 'risk' ,not isnull(pa.user_id) as 'isFigure' from message as m ",
" join user as u on m.user_id=u.id ",
" left join risk as r on u.id=r.user_id ",
" left join politician_account as pa on u.id=pa.user_id ",
f"where proposal_id=\"{proposal_id}\""
]),
"name": "msg"
},
{
"sql":
" ".join([
"select p.id,p.title,p.pdfUrl ,s.status,f.name as f_name ,c.name as c_name from proposal as p ",
" left join proposer as er on er.proposal_id=p.id ",
" left join politician as polit on polit.id=er.politician_id ",
" left join figure as f on polit.figure_id=f.id ",
" left join status as s on p.status_id=s.id ",
" left join proposal_category as pc on p.id=pc.propsoal_id ",
" left join category as c on pc.category_id=c.id "
f" where p.id=\"{proposal_id}\" "
]),
"name": "detail"
},
{
"sql":
f"select * from favorite where user_id=\"{user_id}\" and proposal_id=\"{proposal_id}\"",
"name": "heart"
},
{
"sql": f"SELECT * FROM db.proposal_vote where id=\"{proposal_id}\"",
"name": "vote"
}
]
rows = DB.execution(DB.select, sqlstr)
rows["data"]["detail"] = group(rows["data"]["detail"], [
"f_name", "c_name"], "id")
return rows
def getList(data):
strCond = ""
if (isinstance(data, dict)):
for i in data.keys():
c = (f"{i} in (")
for j in data[i]:
c += f" '{j}' ,"
strCond += f" { c[0:len(c)-1]} )"
sqlstr = "".join([
" SELECT p.id,p.term,f.name,p.photo,a.name as a_n,p.experience,p.degree,p.tel ,cs.score ",
" FROM db.politician as p join electorate as e on p.electorate_id=e.id join figure as f on p.figure_id=f.id join area as a on e.area_id=a.id ",
" join count_score as cs on p.id=cs.id "
f" {' where '+strCond if len(strCond) > 0 else ''} ",
" order by e.area_id,p.term,f.name"
])
rows = DB.execution(DB.select, sqlstr)
return rows
def list(data):
strCond = ""
if (isinstance(data["cond"], dict)):
for i in data["cond"].keys():
if isinstance(data["cond"][i], type(list)):
for j in data["cond"][i]:
strCond += " `%s` =\"%s\" and " % (i, j)
else:
strCond += " %s =\"%s\" and" % (i, data["cond"][i])
page = int(data["page"]) if data["page"] != None else 0
# if (isinstance(data, dict)):
# for i in data.keys():
# strCond += " %s =\"%s\" and" % (i, data[i]) if i!="page" else ""
sqlstr = [{
"sql":
"select p.*,s.status ,pc.category_id,h.hashtag_name,f.name %s %s %s %s %s %s %s "
%
("from proposal as p join `status` as s on p.status_id=s.id",
"join (select * from proposal group by id having term =10 %s limit %d,20) as t on p.id=t.id"
% ("and " + strCond[0:len(strCond) - 3] if len(strCond) > 0 else "",
page * 20),
"left join proposal_category as pc on p.id=pc.propsoal_id",
"left join hashtag as h on pc.category_id=h.id",
"left join proposer as er on p.id=er.proposal_id",
"left join politician as po on po.id=er.politician_id",
"left join figure as f on po.figure_id=f.id"),
"name":
"list"
}, {
"sql":
"select count(*)/20 as n from proposal as p where term=10 %s " %
("and" + strCond[0:len(strCond) - 3] if len(strCond) > 0 else ""),
"name":
"page"
}]
rows = DB.execution(DB.select, sqlstr)
result = group(rows["data"][0]["data"], ["hashtag_name", "name"], "id")[0]
return ({
"list": result,
"page": math.ceil(rows["data"][1]["data"][0]["n"])
})
def getList(data):
strCond = ""
if (isinstance(data, dict)):
for i in data.keys():
c = ("%s in (" % i)
for j in data[i]:
c += " '%s' ," % j
strCond += " %s )" % c[0:len(c)-1]
# print(data)
result = []
# print(strCond)
sqlstr = "SELECT p.id,p.term,f.name,p.photo,a.name as a_n,p.experience,p.degree,p.tel %s %s order by e.area_id,p.term,f.name" % (
"FROM db.politician as p join electorate as e on p.electorate_id=e.id join figure as f on p.figure_id=f.id join area as a on e.area_id=a.id",
"where %s " % strCond if len(strCond) > 0 else ""
)
# print(sqlstr)
rows = DB.execution(DB.select, sqlstr)
return rows
def getDetail(data):
strCond = ""
if (isinstance(data, dict)):
for i in data.keys():
strCond += " %s =\"%s\" and" % (i, data[i])
result = []
sqlstr = [{
"sql": "SELECT p.id,p.term,f.name,p.photo,a.name as a_n,p.experience,p.degree,p.tel,pa.name as p_name,e.name as e_n,e.remark %s where p.id=\"%s\" order by e.area_id,p.term,f.name" % (
"FROM db.politician as p join electorate as e on p.electorate_id=e.id join figure as f on p.figure_id=f.id join area as a on e.area_id=a.id join party as pa on p.party_id=pa.id",
data["id"]), "name":"detail"
},
{
"sql": "select p.id,p.content,c.name ,c.id as c_id from policy as p join policy_category as pc on pc.policy_id=p.id join category as c on pc.category_id=c.id where politician_id=%s order by p.id" % data["id"], "name":"policy"}]
print(sqlstr)
rows = DB.execution(DB.select, sqlstr)
temp = []
# for i in rows.data[1]["data"]:
# print(i)
# print(rows)
return rows
def pList(data):
strCond = ""
if (isinstance(data["cond"], dict)):
for i in data["cond"].keys():
if isinstance(data["cond"][i], type(list)):
for j in data["cond"][i]:
if j == "title":
strCond += " '"+j+"` like\"%"+j+"%\" and"
else:
strCond += f" `{i}` =\"{j}\" and"
else:
if i == "title":
strCond += " "+i+" like \"%"+data["cond"][i]+"%\" and"
else:
conds = "( "
for j in str(data["cond"][i]).split(","):
conds += j + " ,"
strCond += " "+i+" in "+conds[:len(conds)-1]+")"+" and"
page = int(data["page"]) if data["page"] != None else 0
sqlstr = [{"sql": "".join([
"select p.*,s.status ,pc.category_id,f.name,IFNULL(goodc,0) as good ,ifnull(medc,0) as med ,ifnull(badc,0) as bad ,c.name as c_name ",
"from proposal as p join `status` as s on p.status_id=s.id ",
f" join (select * from proposal group by id having term =10 { ' and ' + strCond[0:len(strCond)-3] if len(strCond) > 0 else ''} limit {page*20},20) as t on p.id=t.id ",
" left join proposal_category as pc on p.id=pc.propsoal_id ",
" left join category as c on pc.category_id=c.id ",
" left join proposer as er on p.id=er.proposal_id ",
" left join politician as po on po.id=er.politician_id ",
" left join figure as f on po.figure_id=f.id "
" left join proposal_vote as pv on p.id=pv.id"
]), "name":"list"},
{"sql": f"select count(*)/20 as n from proposal as p where term=10 {'and'+strCond[0:len(strCond)-3] if len(strCond) > 0 else ''} ",
"name": "page"}]
rows = DB.execution(DB.select, sqlstr)
result = group(rows["data"]["list"], ["name", "c_name"], "id")
return ({"data": {"list": result, "page": math.ceil(rows["data"]["page"][0]["n"]), }, "success": True})
def user(user_id):
sqlstr = [{
"sql":
"SELECT u.id,u.name,nick_name,degree,a.name as a_n ,gender,birthday FROM db.user as u join area as a on u.area_id=a.id where u.id=\"%s\""
% user_id,
"name":
"user"
}, {
"sql": "select * from area",
"name": "area"
}, {
"sql":
("select * from favorite as f join proposal as p on f.proposal_id=p.id join status as s on p.status_id=s.id where user_id=\"%s\""
% user_id),
"name":
"save"
}, {
"sql":
"select m.*,p.title from message as m join proposal as p on m.proposal_id = p.id where user_id=\"%s\""
% (user_id),
"name":
"msg"
}]
return DB.execution(DB.select, sqlstr)
def findPolitican():
sqlstr = "select policy,id from politician"
return DB.execution(DB.select, sqlstr)