def register(): form = RegisterForm(request.form) if request.method =='POST' and form.validate(): username = form.username.data email = form.email.data password = form.password.data # connect_db connect_db = DoSQL().get_conn() # confirm username is duplicate result,content = DoSQL().S_db("SELECT id FROM users WHERE username = %s",(username),1,connect_db) if result > 0: flash('Username is duplicated','danger') return render_template('register.html',form=form) # Not duplicate , insert db and going to homepage else: DoSQL().IUD_db("INSERT INTO users(username,email,password) VALUES(%s, %s, %s)",(username,email,password),1,connect_db) # is logged in session['login_in'] = True session['username'] = username flash('You are now registered','success') # close_db DoSQL().close_conn(connect_db) return redirect(url_for('map_view')) return render_template('register.html',form = form)
def scrap_PM25_toDB(self): # DownloadPM25 to dict CSV_URL = 'https://opendata.epa.gov.tw/ws/Data/ATM00625/?$format=csv' download = requests.get(CSV_URL,verify=False) download = download.content.decode("utf-8") reader = csv.reader(download.split('\n'), delimiter=',') data = [] for row in reader: data.append(row) county_pm25 = [] now_time = datetime.now(config['GetlocaltimeConfig'].tz).strftime("%Y-%m-%d %H:%M:%S") for i in range(0,len(county_name)): num_pm25 = [] pm = {} for j in range(1,len(data)-1): if county_name[i] == data[j][1]: if data[j][2] != '': num_pm25.append(int(data[j][2])) pm['time'] = str(now_time) pm['county'] = county_name[i] if not num_pm25: pm['pm'] = 0 else: pm['pm'] = max(num_pm25) county_pm25.append(pm) # connect_db connect_db = DoSQL().get_conn() #county_pm25 = scrap_PM25_toDB() #爬 pm25 result,data = DoSQL().S_db("SELECT county FROM PM25",None,2,connect_db) #確認table是否為空 hour = datetime.now(config['GetlocaltimeConfig'].tz).hour hour = hour%9 # 9小時統計 ex 13:00 % 9 = 4 if result == 0: SQL = "INSERT INTO PM25(county,pm" + str(hour) + ",c_time) VALUES(%(county)s, %(pm)s ,%(time)s)" #SQL = "INSERT INTO PM25(county,pm" + str(hour) + ",current_time) VALUES(%(county)s, %(pm)s ,%(current_time)s)" DoSQL().IUD_db(SQL,county_pm25,2,connect_db) else: SQL = 'UPDATE PM25 SET pm' + str(hour) + '=%(pm)s,c_time=%(time)s WHERE county=%(county)s' #SQL = "UPDATE PM25 SET pm" + str(hour) + "=%(pm)s,current_time=%(current_time)s WHERE county=%(county)s" DoSQL().IUD_db(SQL,county_pm25,2,connect_db)
def exInfo(county_name): # connect_db connect_db = DoSQL().get_conn() # Get one PM25 county_pm,time = Get_PM25(connect_db).Get_one_PM25(county_name) county = county_name county_pm = county_pm[0][time] # find county_past_pm25 county_past_pm = Get_PM25(connect_db).Get_past_pm25(county_name) # find county_exinfo county_exinfo = Get_exinfo(connect_db).Get_county_exinfo(county_name) if request.method == 'POST': # 先判斷有無重複,再塞入forloop,再insert # 缺 massage告訴使用者她選了甚麼 result,user_id = DoSQL().S_db("SELECT id FROM users WHERE username = %s",session['username'],1,connect_db) user_ex_id = request.form.getlist('exinfo_id_list') print(user_ex_id) #避免沒打勾 if len(user_ex_id)>0: #判斷重複的ex_id sql = "SELECT ex_id FROM user_favorite_exinfo AS u1 WHERE exists(SELECT id FROM users AS u2 WHERE u2.id=%s and u1.ID=u2.ID and u1.ex_id=%s )" ex_id_repeat = [] for i in range(len(user_ex_id)): result,content = DoSQL().S_db(sql,(user_id['id'],user_ex_id[i]),2,connect_db) if result > 0: ex_id_repeat.append(content[0]["ex_id"]) #排除重複值 for i in range(0,len(ex_id_repeat)): user_ex_id.remove(str(ex_id_repeat[i])) #dict insert比較快 ex_id = [] for i in range(0,len(user_ex_id)): ex_id.append({'id':str(user_id['id']),'ex_id':user_ex_id[i]}) SQL = "INSERT INTO user_favorite_exinfo VALUES(%(id)s,%(ex_id)s)" DoSQL().IUD_db(SQL,ex_id,2,connect_db) #flash('Add'+str(user_ex_id)+ 'success'+'but repeat:'+str(ex_id_repeat),'success') flash('Add bookmark success,please checked private page','success') # close_db DoSQL().close_conn(connect_db) else: return render_template('exInfo.html',county=county , pm=county_pm ,exinfo=county_exinfo,past_pm=county_past_pm) return render_template('exInfo.html',county=county , pm=county_pm ,exinfo=county_exinfo,past_pm=county_past_pm)
def map_view(): # connect_db connect_db = DoSQL().get_conn() # Get PM25 PM25_value = Get_PM25(connect_db).Get_PM25() # close_db DoSQL().close_conn(connect_db) dt = datetime.now(config['GetlocaltimeConfig'].tz) # dt = datetime.now() return render_template('map_view.html',value=PM25_value,dt=dt)
def LoginToSQL(username, password): username = request.form[username] password_candidate = request.form[password] result, data = DoSQL().S_db("SELECT * FROM users WHERE username = %s", [username], 1) return result, data, username, password_candidate
def add_articleToSQL(title, body, session_name): title = title.data body = body.data DoSQL().IUD_db( "INSERT INTO articles(title,body,author) VALUES(%s,%s,%s)", (title, body, session_name))
def ScrapyExinfo(): json_url = 'https://cloud.culture.tw/frontsite/trans/SearchShowAction.do?method=doFindTypeJ&category=6' download = requests.get(json_url, verify=False) download = download.content.decode("utf-8") Exinfo = json.loads(download) Exinfolist = [] for i in range(0, len(Exinfo)): Exinfolist.append({ 'title': Exinfo[i]['title'], 'location': Exinfo[i]['showInfo'][0]['location'], 'locationName': Exinfo[i]['showInfo'][0]['locationName'], 'endTime': Exinfo[i]['showInfo'][0]['endTime'], 'county': Exinfo[i]['showInfo'][0]['location'][:3] }) sql = 'INSERT INTO exinfo (title, location,locationName,endTime,county) VALUES (%(title)s, %(location)s, %(locationName)s, %(endTime)s, %(county)s)' params = Exinfolist DoSQL().I_executmany(sql, (params))
def RegisterToSQL(name, email, username, password): name = name.data email = email.data username = username.data password = sha256_crypt.encrypt(str(password.data)) DoSQL().IUD_db( "INSERT INTO users(name,email,username,password) VALUES(%s, %s, %s, %s)", (name, email, username, password))
def dashboard(): result,articles = DoSQL().S_db("SELECT * FROM articles",None,0) if result > 0: return render_template('dashboard.html',articles = articles) else: msg = 'No Articles Found' return render_template('dashboard.html',msg = msg)
def Get_one_PM25(self, county): #hour = datetime.now().hour #hour = hour % 9 SQL = "SELECT county," + self.cur_pm + " FROM PM25 WHERE county = %s" result, content = DoSQL().S_db(SQL, county, 2, self.connect_db) time = self.cur_pm if result == 0: return None else: return content, time
def articles(): result , articles = DoSQL().S_db("SELECT * FROM articles",None,0) #print(articles) #listarticles #for article in articles: if result > 0:
def login(): if request.method =='POST': username = request.form['username'] password = request.form['password'] # connect_db connect_db = DoSQL().get_conn() result,data = DoSQL().S_db("SELECT id,password FROM users WHERE username = %s",[username],1,connect_db) if result > 0 : # 找這個使用者的 password user_password = data['password'] # Compare Passwords if password == user_password: session['login_in'] = True session['username'] = username # close_db DoSQL().close_conn(connect_db) flash('You are now logged in','success') return redirect(url_for('map_view')) else: flash('Invalid login','danger') #error = 'Invalid login' return render_template('login.html') else: flash('Username not found','danger') #error = 'Username not found' return render_template('login.html') return render_template('login.html')
def articles(): result , articles = DoSQL().S_db("SELECT * FROM articles",None,0) #print(articles) #listarticles #for article in articles: if result > 0: return render_template('articles.html',articles = articles) else: msg = 'No Articles Found' return render_template('articles.html',msg = msg)
def user_private(): # connect_db connect_db = DoSQL().get_conn() #userdata : user detail _,userdata = DoSQL().S_db("SELECT id,username,email FROM users WHERE username=%s",session['username'],2,connect_db) favorite_exinfo = Get_exinfo(connect_db).Get_user_exinfo(userdata[0]['id']) #單獨select不重複的county值 # _,select_county = DoSQL().S_db("select distinct county from exinfo as e1 where exists(select * from user_favorite_exinfo as u1 where u1.ex_id=e1.ex_id and u1.id=%s)",userdata[0]["id"],2,connect_db) #刪除方法 if request.method == 'POST': try: #delete_exinfo =[] 存放要刪除的選項value delete_exinfo = request.form.getlist('exinfo_id_list') Get_exinfo(connect_db).Delete_user_exinfo(userdata[0]['id'],delete_exinfo) favorite_exinfo = Get_exinfo(connect_db).Get_user_exinfo(userdata[0]['id']) # _,select_county = DoSQL().S_db("select distinct county from exinfo as e1 where exists(select * from user_favorite_exinfo as u1 where u1.ex_id=e1.ex_id and u1.id=%s)",userdata[0]["id"],2,connect_db) # close_db DoSQL().close_conn(connect_db) except: # return render_template('user_private.html',userdata=userdata,favorite_exinfo=favorite_exinfo,favorite_county=select_county) flash('Please check your options','danger') return render_template('user_private.html',userdata=userdata,favorite_exinfo=favorite_exinfo) flash('Update success','success') # return render_template('user_private.html',userdata=userdata,favorite_exinfo=favorite_exinfo,favorite_county=select_county) return render_template('user_private.html',userdata=userdata,favorite_exinfo=favorite_exinfo) # return render_template('user_private.html',userdata=userdata,favorite_exinfo=favorite_exinfo,favorite_county=select_county) return render_template('user_private.html',userdata=userdata,favorite_exinfo=favorite_exinfo)
def edit_article(id): result , article = DoSQL().S_db("SELECT * FROM articles WHERE id = %s",[id],1) # Get form form = ArticleForm(request.form) # Populate article form fields form.title.data = article['title'] form.body.data = article['body'] if request.method =='POST' and form.validate(): DataToSQL.edit_articleToSQL('title','body',id) flash('Article Updated','success') return redirect(url_for('dashboard')) return render_template('edit_article.html',form=form)
def split(): test_size = float(request.form['test_size']) random_state = int(request.form['random_state']) model_name = request.form['model_name'] model_select = request.form.getlist('model_select') col_choosed = ','.join(train_cols) datset_path = 'User_dataset/' + session['username'] +'/' + f_name+'.csv' # read data train_set = pd.read_csv(datset_path) y = train_set[label_cols] X = train_set[train_cols] X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=test_size, random_state=random_state) #print(type(model_select)) clf = classifier() start_time = time.time() model = getattr(clf, model_select[0])(X_train,y_train) training_time = time.time() - start_time model_path = 'User_dataset/' + session['username'] +'/' + model_name+'.pickle' clf.modelsave(model_path,model) #print(model.predict(X_test)) acc_log = round(model.score(X_test,y_test)*100,2) DoSQL().IUD_db("INSERT INTO HelpUTrainlog(username,training_time,dataset_name,model_name,model_choosed,col_choosed,test_size,random_state,val_acc) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)", (session['username'],training_time,f_name,model_name,model_select[0],col_choosed,test_size,random_state,acc_log)) flash('Training success','success') return render_template('dataload.html')
def dashboard(): result,articles = DoSQL().S_db("SELECT * FROM articles",None,0) if result > 0:
def Passvalue_Exinfo(addr): addr = DoSQL.Select_db("SELECT ...") # 選取用戶位置 Exinfo = DoSQL().Select_db( "SELECT TITLE,LOCATION,LOCATIONNAME,ENDTIME WHERE COUNTY=%S", addr) #將用戶位置與展場搭配 return Exinfo
def delete_article(id): DoSQL().IUD_db("DELETE FROM articles WHERE id = %s",[id]) flash('Article Deleted','success') return redirect(url_for('dashboard'))
def article(id): result , article = DoSQL().S_db("SELECT * FROM articles WHERE id=%s",[id],1) return render_template('article.html', article = article)
def Get_county_exinfo(self, county_name): SQL = "SELECT ex_id,title,county,location,locationName,endTime FROM exinfo WHERE county = %s" result, content = DoSQL().S_db(SQL, county_name, 2, self.connect_db) return content
def Delete_user_exinfo(self, user_id, delete_exinfo): sql = "DELETE FROM user_favorite_exinfo WHERE id=%s and ex_id IN %s" DoSQL().IUD_db(sql, (user_id, delete_exinfo), 1, self.connect_db)
'location':j[i]['showInfo'][0]['location'], 'locationName':j[i]['showInfo'][0]['locationName'][:-1], 'endTime':j[i]['showInfo'][0]['endTime'][:10], 'county':j[i]['showInfo'][0]['location'][:3]}) else: show.append({'title': j[i]['title'], 'location':j[i]['showInfo'][0]['location'], 'locationName':j[i]['showInfo'][0]['locationName'], 'endTime':j[i]['showInfo'][0]['endTime'][:10], 'county':j[i]['showInfo'][0]['location'][:3]}) # ----------Do SQL-------------------- # connect_db connect_db = DoSQL().get_conn() SQL = "INSERT INTO exinfo(title,county,location,locationName,endTime) VALUES(%(title)s, %(county)s, %(location)s, %(locationName)s, %(endTime)s)" DoSQL().IUD_db(SQL,show,2,connect_db) DoSQL().close_conn(connect_db)
def delete_article(id): DoSQL().IUD_db("DELETE FROM articles WHERE id = %s",[id])
def edit_article(id): result , article = DoSQL().S_db("SELECT * FROM articles WHERE id = %s",[id],1)
def Get_past_pm25(self, county): SQL = "SELECT * FROM PM25 WHERE county = %s" result, content = DoSQL().S_db(SQL, county, 2, self.connect_db) return content
def edit_articleToSQL(title, body, id): title = request.form[title] body = request.form[body] DoSQL().IUD_db("UPDATE articles SET title= %s, body=%s WHERE id=%s", (title, body, id))
def Get_user_exinfo(self, user_id): # sql = "select * from exinfo as e1 where exists(select * from user_favorite_exinfo as u1 where u1.ex_id=e1.ex_id and u1.id=%s)" sql = "select ex_id,title,county,location,locationName,endTime from user_private_exinfo where id=%s" result, content = DoSQL().S_db(sql, (user_id), 2, self.connect_db) return content