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