def deleteStock(): requestJsonData = request.json db_class = dbModule.Database() userId = check_session() if userId is None: app.logger.info("::::::: Session End ::::::: \n") return 'End' # sql = "DELETE FROM UserStockList \ # WHERE user_Id='" + userId + "' AND list_Acode='" + requestJsonData['id'] + "'" sql = "UPDATE UserStockList \ SET status='D'\ WHERE user_Id='" + userId + "' AND list_Acode='" + requestJsonData[ 'id'] + "'" db_class.execute(sql) db_class.commit() db_class.close() return ""
def interBook(page): # get 방식으로 page를 받아 페이징을 함 db_class = dbModule.Database() print(page) results = db_class.load(page) results = json.loads(results) pprint(results) return jsonify(results)
def home(): '''sesssion control''' ''' if not session.get('logged_in'): print(session.get('logged_in')) print("여기요1") return render_template('MMG.html') else: print("여기요2") if request.method=='POST': username=request.form['id'] return render_template('MMG.html',id=username) return render_template('MMG.html') ''' if request.method == 'GET': print("여기요3") return render_template('login.html') elif request.method == 'POST': username = request.form['id'] password = request.form['pwd'] db_class = dbModule.Database() # SELECT * FROM user where password = 12345678 sql = "SELECT * FROM testDB.user WHERE user_name = '" + username + "' AND password = " + password print(sql) user_list = db_class.executeAll(sql) print(user_list) try: if user_list is not None: return redirect(url_for('recommendation', id=username)) else: return redirect(url_for('home')) except: return redirect(url_for('home'))
def getCurrentValue(): requestJsonData = request.json db_class = dbModule.Database() userId = check_session() if userId is None: app.logger.info("::::::: Session End ::::::: \n") return 'End' app.logger.info("::::::: getCurrentValue ::::::: \n %s", userId) sql = "INSERT INTO UserStockList (user_Id, list_Code, list_Acode, list_Name, status, date) \ SELECT user_Id, '" + requestJsonData[ 'displayedCode'] + "' as 'list_Code', '" + requestJsonData[ 'id'] + "' as 'list_Acode', '" + requestJsonData[ 'value'] + "' as 'list_Name', \ 'I' as 'status', NOW() as 'date' \ FROM User WHERE user_Id = '" + userId + "'" print(sql) db_class.execute(sql) db_class.commit() db_class.close() return generateChartDivTag(requestJsonData)
def dep_modal_submit(): if request.method == 'POST': flight_id = request.form['bookId'] url = 'http://openapi.airport.kr/openapi/service/StatusOfPassengerFlights/getPassengerDepartures?ServiceKey=66063YTIfEiCLfP6GQaefJOoCIAGlG79bHmFOdBZ%2BrGEZ%2B6syoDTCimut6%2F%2FIXO2WF%2FP5m6KrYPnF16DvsPgfQ%3D%3D&from_time=0000&to_time=2400&airport=&' url += 'flight_id=' url += flight_id req = requests.get(url) html = req.text soup = BeautifulSoup(html, 'html.parser') airline = '' flightid = '' scheduledatetime = '' airport = '' chkinrange = '' gatenumber = '' remark = '' terminalid = '' if soup.find('airline') != None: airline = soup.find('airline').text if soup.find('flightid') != None: flightid = soup.find('flightid').text if soup.find('scheduledatetime') != None: scheduledatetime = soup.find('scheduledatetime').text if soup.find('airport') != None: airport = soup.find('airport').text if soup.find('chkinrange') != None: chkinrange = soup.find('chkinrange').text if soup.find('gatenumber') != None: gatenumber = soup.find('gatenumber').text if soup.find('remark') != None: remark = soup.find('remark').text if soup.find('terminalid') != None: terminalid = soup.find('terminalid').text else: flight_id = None airplane = None db_class = dbModule.Database() p_id = session.get('logged_in') sql = "UPDATE Airport.Departure \ SET flightid=%s, airline=%s, scheduledatetime=%s, airport=%s, chkinrange=%s, gatenumber=%s, remark=%s, terminalid=%s \ WHERE f_id=%s" db_class.execute(sql, (flightid, airline, scheduledatetime, airport, chkinrange, gatenumber, remark, terminalid, p_id)) db_class.commit() sql = "SELECT * FROM Airport.Departure WHERE f_id=%s" row = db_class.executeAll(sql, (p_id)) db_class.commit() return render_template('index.html', resultData=row[0])
def search(): db_class = dbModule.Database() results = db_class.find(request.form['keyword']) # form 태그에서 넘어온 keyword로 find함수 실행 try: results = json.loads(results) pprint(results) return render_template("search.html", json=results)# json 변수에 결과값을 담아 search.html로 이동 except UndefinedError: # 검색 결과가 없을때 예외처리 return "검색어를 잘못 입력하셨습니다."
def mypage_flight(): db_class = dbModule.Database() p_id = session.get('logged_in') sql = "SELECT * FROM Airport.Departure WHERE f_id=%s" row = db_class.executeAll(sql, (p_id)) db_class.commit() return render_template('reg_flight.html', resultData=row[0])
def mypage_route(): db_class = dbModule.Database() p_id = session.get('logged_in') sql = "SELECT * FROM Airport.route WHERE r_id=%s" row = db_class.executeAll(sql, (p_id)) db_class.commit() row_len = len(row) return render_template('reg_route.html', resultData=row, row_len=row_len)
def insert(): db_class = dbModule.Database() sql = "INSERT INTO testDB.testTable(test) VALUES('%s')" % ('testData') db_class.execute(sql) db_class.commit() return render_template('test.html', result='insert is done!', resultData=None, resultUPDATE=None)
def select(): db_class = dbModule.Database() sql = "SELECT idx, test " \ " FROM testDB.testTable" row = db_class.executeAll(sql) print(row) return render_template('/test/test.html', result=None, resultData=row[0], resultUPDATE=None)
def home(): if not session.get('logged_in'): return render_template('login.html') else: db_class = dbModule.Database() p_id = session.get('logged_in') sql = "SELECT * FROM Airport.Departure WHERE f_id=%s" row = db_class.executeAll(sql, (p_id)) db_class.commit() return render_template('index.html', resultData=row[0])
def index(): db_class = dbModule.Database() sql = "SELECT title, content, url \ FROM nlpg$nlpg.news \ ORDER By id DESC \ LIMIT 10;" row = db_class.executeAll(sql) print(row) return render_template('index.html', resultData=row[:11])
def logout(): db_class = dbModule.Database() p_id = session.get('logged_in') sql = "DELETE FROM Airport.Departure WHERE f_id=%s" row = db_class.executeAll(sql, (p_id)) db_class.commit() sql = "DELETE FROM Airport.customer WHERE p_id=%s" row = db_class.executeAll(sql, (p_id)) db_class.commit() session.pop('logged_in', None) return render_template('login.html')
def register(): if request.method == 'POST': user = request.form['materialLoginFormPhone'] if user != None: session['logged_in'] = user db_class = dbModule.Database() sql = "INSERT IGNORE INTO Airport.customer VALUES(%s);" db_class.execute(sql, (user)) db_class.commit() sql = "INSERT IGNORE INTO Airport.Departure(f_id) VALUES(%s)" db_class.execute(sql, (user)) db_class.commit() return redirect(url_for('home'))
def getRandomFoodList(): db_class = dbModule.Database() sql = "SELECT * FROM testDB.food_list" row = db_class.executeAll(sql) random_var = random.randrange(1,len(row)) #print(random_var) result = [] count = 0 while(count<20): random_var = random.randrange(1, len(row)) if row[random_var] not in result: result.append(row[random_var]) count = count+1 for i in range(len(result)): result[i] = result[i]['name'] return result
def update(): db_class = dbModule.Database() sql = "UPDATE testDB.testTable" \ " SET test='%s'" \ " WHERE test='testData'" % 'update_Data' db_class.execute(sql) db_class.commit() sql = "SELECT idx, test" \ " FROM testDB.testTable" row = db_class.executeAll(sql) return render_template('/test/test.html', result=None, resultData=None, resultUPDATE=row[0])
def del_route(): if request.method == 'POST': r_id = session.get('logged_in') route = request.form['route'] db_class = dbModule.Database() sql = "DELETE FROM Airport.route WHERE r_id=%s and route=%s" db_class.execute(sql, (r_id, route)) db_class.commit() sql = "SELECT * FROM Airport.route WHERE r_id=%s" row = db_class.executeAll(sql, (r_id)) db_class.commit() row_len = len(row) return render_template('reg_route.html', resultData=row, row_len=row_len)
def recentHum(): choice_id = request.args.get('choice_id') #작물선택 기본키 # 가장 최근 기록된 습도 반환 db_class = dbModule.Database() sql = "select r.rb_humidity from raspberry as r \ join plant_choice p \ on " + choice_id + " = r.choice_id \ where p.grow_flag = 1 \ order by r.rb_create limit 1" row = db_class.executeAll(sql) print("/recent-hum 결과", row) return row[0]
def foodEvaluation(): # print(guser['list']) esti = request.form['score'] print("여기를 지납니다만/evaluation") temp_user = {'user_name': guser['id'],'food_name':guser['foodname'],'esti_time': guser['time'], 'cloud':guser['weather'], 'RorS': guser['RorS'], 'temp': guser['temperature'], 'emotion': guser['emotion'],'esti':esti} db_class = dbModule.Database() sql = "INSERT INTO testDB.food_evaluation(user_name, food_name, esti_time, cloud, RorS, temp, emotion, esti) " \ "VALUES('" + guser['id'] + "','" +guser['foodname']+ "','"+ repr(guser['time'])+ "','"+repr(guser['weather'])+ \ "','"+guser['RorS']+ "','"+repr(guser['temperature'])+ "','"+repr(guser['emotion'])+ "','"+esti+ "')" print(sql) db_class.executeAll(sql) db_class.commit() esti_time = datetime.now().hour real_location = guser['location'] temperature = guser['temperature'] weather = guser['weather'] # return render_template('foodforme.html', recommendation_result=result,id=guser['id']) #return redirect(url_for('recommendation_onemore', recommendation_result=rec_result_temp, id=guser['id'], user_location=real_location,temperature=temperature, weather=weather, count=guser['count'], cur_time=esti_time)) return render_template('foodforme.html', recommendation_result='', id=guser['id'], user_location=real_location, temperature=temperature, weather=weather,count = guser['count'],cur_time = esti_time)
def tempAndHum(): choice_id = request.args.get('choice_id') #작물선택 기본키 # 온/습도/시간 반환 db_class = dbModule.Database() sql = "select r.rb_temperature, r.rb_humidity, r.rb_create from raspberry as r \ join plant_choice p \ on " + choice_id + " = r.choice_id \ where p.grow_flag = 1 \ order by r.rb_create" row = db_class.executeAll(sql) for datas in row: t = datas["rb_create"] datas["rb_create"] = t.strftime('%Y-%m-%d %H:%M:%S') print("결과", row) result = json.dumps(row, indent=3) return result
def supplyWaterLogs(): choice_id = request.args.get('choice_id') #작물선택 기본키 # 가장 최근 급수 시간 출력 db_class = dbModule.Database() sql = "select w.watering_time from watering_log as w \ join plant_choice p \ on " + choice_id + " = w.choice_id \ where p.grow_flag = 1 \ order by w.watering_time desc \ limit 1" row = db_class.executeAll(sql) t = row[0]["watering_time"] row[0]["watering_time"] = t.strftime('%Y-%m-%d %H:%M:%S') print("/supply-water-logs의 결과", row) return row[0]
def recentImgs(): choice_id = request.args.get('choice_id') # 작물선택 기본키 # 가장 최근에 찍힌 사진 10개만 리턴 db_class = dbModule.Database() sql = "select r.rb_img from raspberry as r \ join plant_choice p \ on " + choice_id + " = r.choice_id \ where p.grow_flag = 1 \ order by r.rb_create desc \ limit 10" row = db_class.executeAll(sql) for data in row: data['rb_img'] = "/static/data/img/" + data['rb_img'] + ".jpg" print(data['rb_img']) print("/recent-imgs의 결과", row) return json.dumps(row)
def save_route(): if request.method == 'POST': r_id = session.get('logged_in') route = request.form['route'] db_class = dbModule.Database() sql = "INSERT INTO Airport.route(r_id,route) VALUES(%s, %s)" db_class.execute(sql, (r_id, route)) db_class.commit() global route_start global route_end global way_length global num_middle global send return render_template('map1.html', route_start=route_start, route_end=route_end, way_length=way_length, num_middle=num_middle, send=send)
def home(): userId = check_session() if userId is None: app.logger.info("::::::: Session End ::::::: \n") return render_template('login.html') db_class = dbModule.Database() sql = "SELECT list_Code as displayedCode, list_Name as value, list_Acode as id, user_Id as auth \ FROM UserStockList\ WHERE " if userId == 'admin': sql += "status='I'" else: sql += "user_Id = '" + userId + "' AND status='I'" sql += "ORDER BY user_Id" row = db_class.executeAll(sql) tags = '' for r in row: r['displayedCode'] = r['displayedCode'].decode('utf8', 'ignore') r['value'] = r['value'].decode('utf8', 'ignore') r['id'] = r['id'].decode('utf8', 'ignore') r['auth'] = r['auth'].decode('utf8', 'ignore') r['userId'] = userId tags += generateChartDivTag(r) app.logger.info("::::::: result for UserStockList table ::::::: \n %s", row) app.logger.info("::::::: result for Generated Tags ::::::: \n %s", tags) db_class.close() return render_template('home.html', resultData=tags, userid=userId)
def dayImgs(): choice_id = request.args.get('choice_id') # 작물선택 기본키 day = request.args.get('day') # 가장 최근에 찍힌 사진 10개만 리턴 print("날짜", day) db_class = dbModule.Database() sql = '''select r.rb_img from raspberry as r \ join plant_choice p \ on ''' + choice_id + ''' = r.choice_id \ where p.grow_flag = 1 \ and r.rb_img LIKE '%%%%%s%%%%' \ order by r.rb_create desc''' % (day) print(sql) row = db_class.executeAll(sql) for data in row: data['rb_img'] = "/static/data/img/" + data['rb_img'] + ".jpg" print(data['rb_img']) print("/day-imgs의 결과", row) return json.dumps(row)
def login(): '''Login Form''' if request.method == 'GET': print("여기요3") return render_template('login.html') elif request.method == 'POST': print("여기요4") username = request.form['id'] password = request.form['pwd'] db_class = dbModule.Database() #SELECT * FROM user where password = 12345678 sql = "SELECT * FROM testDB.user WHERE user_name = '" + username + "' AND password = " + password print(sql) user_list = db_class.executeAll(sql) print(user_list) try: if user_list is not None: session['logged_in'] = True return redirect(url_for('home', id=username)) else: return redirect(url_for('home')) except: return redirect(url_for('home'))
def getFoodEvaluationlist():#to make dictionary db_class = dbModule.Database() sql = "SELECT * FROM testDB.food_evaluation" row = db_class.executeAll(sql) local_food_evaluation_list = row # print(local_food_evaluation_list) sql = "SELECT distinct user_name FROM testDB.food_evaluation" row = db_class.executeAll(sql) user_list = row # print(user_list) temp = {} #print(temp) # 함수화 시켜서 따로 빼도록 하자 # 사용자별로 구분하여 딕셔너리로 만들기 for each_user in user_list: temp[each_user['user_name']] = {} keys_of_list = ['food_name', 'esti_time', 'cloud', 'RorS', 'temp', 'emotion', 'esti'] for each_esti in local_food_evaluation_list: temp[each_esti['user_name']][each_esti['food_name']] = {} for i in keys_of_list: temp[each_esti['user_name']][each_esti['food_name']][i] = each_esti[i] return temp
def __init__(self, id, password, username='******', userclass='none', userclasscode='02', userphone='none', useremail='none'): self.user_id = id self.user_name = username self.password = password self.user_class = userclass self.user_class_code = userclasscode self.user_phone = userphone self.user_email = useremail self.conn = dbModule.Database()
def index(): print("index(), /") guser['location']=None guser['count']=1 guser['eval_history']=None guser['id']=(request.form['id']) db_class = dbModule.Database() sql = "SELECT * FROM testDB.user WHERE user_name = '" + guser['id'] + "'" user_list = db_class.executeAll(sql) if len(user_list)==0: sql = "INSERT INTO testDB.user(user_name) VALUES('" +guser['id']+ "')" db_class.executeAll(sql) db_class.commit() sql = "SELECT * FROM testDB.food_evaluation WHERE user_name = '" + guser['id'] + "'" eval_user = db_class.executeAll(sql) guser['eval_history']=eval_user getStatus() esti_time = datetime.now().hour#현재시간 html로 전달 real_location = guser['location'] temperature = guser['temperature'] weather = guser['weather'] return render_template('foodforme.html', id=guser['id'],user_location = real_location,temperature=temperature,weather=weather,count=guser['count'],cur_time = esti_time)
def __init__(self): self.db_class = dbModule.Database()