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)