def admin_edit_cities_state(message, user, is_entry=False): current_cities = session.query(City.name).all() current_cities = [i for obj in current_cities for i in obj] if is_entry: bot.send_message( message.chat.id, 'Щоб змінити список міст, скопіюйте наступне повідомлення, та відредагуйте його.\nПоточний список міст:', reply_markup=get_back_keyboard(language=user.language)) bot.send_message(message.chat.id, '\n'.join(current_cities)) else: if message.text == DICTIONARY[user.language]['back_btn']: return True, 'admin_settings_state' else: new_cities = [] for city in message.text.split('\n'): if city not in current_cities: new_cities.append({ 'name': city, 'is_regional_center': False }) session.execute( insert(City).values(new_cities).on_conflict_do_nothing()) session.commit() bot.send_message(message.chat.id, 'Список міст оновлено') return True, 'admin_settings_state' return False, ''
def delete(self): request = Category.parser.parse_args() #delete에 필요한 값인 category pk가 없으면 400 반환 if request['category_pk'] == None: return Response(status=400) sql = f"delete from categories where category_pk = {request['category_pk']}" session.execute(sql) session.commit() session.close() return Response(status=200)
def delete(self): request = Option.parser.parse_args() #post에 필요한 값인 'option_pk' 가 없으면 400 반환 if request['option_pk'] == None: return Response(status=400) session.execute("DELETE FROM OPTIONS WHERE option_pk = {}".format( request['option_pk'])) session.commit() session.close() return Response(status=200)
def delete(self): request = Menu.parser.parse_args() #delete에 필요한 값인 'pk'가 없으면 400 반환 if request['pk'] == None: return Response(status=400) sql = f"delete from menus where menu_pk = {request['pk']}" session.execute(sql) session.commit() session.close() return Response(status=200)
def delete(self): request = Menu.parser.parse_args() print(request) if request['pk'] == None: return Response(status=400) sql = f"delete from menus where menu_pk = {request['pk']}" session.execute(sql) # menu = session.query(models.Menu).filter(models.Menu.menu_pk == request['pk']).first() # session.delete(menu) session.commit() return Response(status=200)
def parce_events(html): soup = BeautifulSoup(html, 'html.parser') items = soup.find_all('article', class_='b-postcard') events = [] for item in items: url = item.find('h2', class_='title') id_site = str(url.find('a').get('href')).split('/')[4] name = item.find('a').get_text().rstrip().lstrip() date = item.find('span', class_='date').get_text() block = item.find('div', class_='when-and-where') if len(block.find_all('span')) == 2: price = block.find_all('span')[1].get_text().rstrip().lstrip() else: price = '' event_type = block.get_text().replace(date, '').replace( price, '').rstrip().lstrip() description = item.find('p', class_='b-typo').get_text().rstrip().lstrip() block = item.find('div', class_='more') if block.find('span') is not None: tags = block.get_text().replace(block.find('span').get_text(), '').rstrip().lstrip() else: tags = block.get_text().rstrip().lstrip() tags = str(tags).split(', ') event_type = str(event_type).split(', ') # print(f'name: {name}') # print(f'date: {date}') # print(f'price: {price}') # print(f'event_type: {event_type}') # print(f'description: {description}') # print(f'tags: {tags}') # print(f'id_site: {id_site}') events.append({ 'id_site': id_site, 'name': name, 'date': date, 'price': price, 'type': event_type, 'description': description, 'tags': tags }) session.execute(insert(Event).values(events).on_conflict_do_nothing()) session.commit()
def get(self): data = Order.parser.parse_args() result = None if data['pk']: # 만약 특정 pk 쿼리라면 order_sql = """ SELECT ORD.order_pk, DATE_FORMAT(order_time, '%Y-%m-%d %H:%i:%s') as order_time, completed, total_price, menu_name, quantity, option_name FROM ORDERS ORD JOIN ORDER_PRODUCTS ORD_PRD USING(order_pk) LEFT JOIN ORDER_OPTIONS ORD_OP USING (product_pk) JOIN MENUS M ON (M.menu_pk = ORD_PRD.order_menu_pk) LEFT JOIN OPTIONS USING (option_pk) WHERE ORD.order_pk = {pk} """.format(pk=data['pk']) else: order_sql = """ SELECT ORD.order_pk, DATE_FORMAT(order_time, '%Y-%m-%d %H:%i:%s') as order_time, completed, total_price, menu_name, quantity, ORD_PRD.product_pk, option_name FROM ORDERS ORD JOIN ORDER_PRODUCTS ORD_PRD USING(order_pk) LEFT JOIN ORDER_OPTIONS ORD_OP USING (product_pk) JOIN MENUS M ON (M.menu_pk = ORD_PRD.order_menu_pk) LEFT JOIN OPTIONS USING (option_pk) WHERE ORD.order_time between DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY), '%Y-%m-%d') and DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 SECOND), '%Y-%m-%d %H:%i:%s') ORDER BY ORD.order_pk, M.menu_pk; """ result = session.execute(order_sql).fetchall() session.close() result = query_to_dict(result) if not result[0]: # 쿼리 결과가 없을 경우 return Response(status=404) result = many_to_one(result) return {'orderList': result}, 200
def present(username): query = "SELECT * FROM user_by_username WHERE username = '******';".format( username) result = session.execute(query) for a in result: return True return False
def cleanup_data(): try: session.execute(text("SET FOREIGN_KEY_CHECKS=0;")) session.commit() session.execute(text("TRUNCATE TABLE comment;")) session.commit() session.execute(text("TRUNCATE TABLE post;")) session.commit() session.execute(text("TRUNCATE TABLE user;")) session.commit() session.execute(text("SET FOREIGN_KEY_CHECKS=1;")) session.commit() LOGGER.success("Successfully reset all data.") except IntegrityError as e: LOGGER.error(e.orig) raise e.orig except SQLAlchemyError as e: LOGGER.error(f"Unexpected error when resetting data: {e}") raise e
def get_stop(): query = request.args.get('stopid', type=int) select = bus_stops.select(bus_stops.c.stop_id==query) try: stop = session.execute(select).fetchall()[0] except IndexError: return jsonify({'error': 'stopid not found'}) return jsonify(stop)
def get_poll(username, title): # Performs a GET request for a poll try: query = "SELECT * FROM poll_by_username_and_title WHERE username = '******' AND title = '{}';".format(username, title) result = session.execute(query) return create_json_response(result) except IndexError: return create_response("Poll with username and title not found", 404) except Exception: return create_response("Internal Server Error", 500)
def get_total_people(): station_id = request.args.get("station_id") from_instant = request.args.get("from") to_instant = request.args.get("to") last = request.args.get("last") query = session.query(StationsHistory) if station_id is not None: if session.query(Stations).get(station_id) is None: return "station does not exists", 404 query = query.filter_by(station_id=station_id) if from_instant is not None: try: from_instant = int(from_instant) except: return "from instant is not a integer", 400 if from_instant > int(datetime.now().timestamp()): return "from_instant in the future", 400 query = query.filter( StationsHistory.instant > datetime.utcfromtimestamp(from_instant)) if to_instant is not None: try: to_instant = int(to_instant) except: return "to instant is not a integer", 400 if from_instant is not None and to_instant < from_instant: return "from_instant is greater than to_instant", 400 query = query.filter( StationsHistory.instant < datetime.utcfromtimestamp(to_instant)) current_app.logger.debug(query) if last is not None and (last == 'true' or last == 'True'): sql = text( "SELECT station_id as s_id,instant as i,total_people as tp FROM `stations_history` GROUP BY station_id,instant,total_people HAVING instant = (SELECT instant FROM `stations_history` WHERE station_id = s_id ORDER BY instant DESC LIMIT 1) " ) db_result = session.execute(sql) result = [] for row in db_result: result.append({ 'instant': row[1], 'station_id': row[0], 'total_people': row[2] }) return jsonify(result) return jsonify([record.serialize() for record in query.all()])
def add_user(): # Performs a POST request to add a user try: json = request.json print(json) username = json["username"] password = generate_password_hash(json["password"]) if present(username): return create_response( "User with provided username already exists", 401) query = "INSERT INTO user_by_username (id, username, password) VALUES (uuid(), '{}', '{}');".format( username, password) print(query) session.execute(query) return create_response("User added successfully", 201) except (AttributeError, KeyError, TypeError): return create_response("Bad request with improper/incomplete fields", 403) except Exception: return create_response("Internal server serror", 500)
def add_poll(): # Performs a POST request for adding a poll try: json = request.json username = json["username"] title = json["title"] options = json["options"] votes = json["votes"] if present(username, title): return create_response("Poll with provided username and title already exists", 401) query = "INSERT INTO poll_by_username_and_title (username, title, id, options, votes) " query = query + "VALUES ('{}', '{}', uuid(), {}, {});".format(username, title, options, votes) session.execute(query) return create_response("Poll Successfully added", 201) except (KeyError, AttributeError, TypeError): return create_response("Bad request with improper/incomplete fields", 403) except Exception: return create_response("Internal Server Error", 500)
def update_db(now, pos_now, neg_now): p_col = "pos" + "{0:02d}".format(now.minute) n_col = "neg" + "{0:02d}".format(now.minute) now_id = int(now.strftime("%Y%m%d%H")) timedata = session.query(TimeData).filter(TimeData.id == now_id).scalar() if timedata is None: new_timedata = TimeData() new_timedata.id = now_id new_timedata.__dict__[p_col] = pos_now new_timedata.__dict__[n_col] = neg_now session.add(new_timedata) session.commit() else: str_sql_pos = "UPDATE negaposi SET " + p_col + " = " + str( pos_now) + "WHERE id = " + str(now_id) str_sql_neg = "UPDATE negaposi SET " + n_col + " = " + str( neg_now) + "WHERE id = " + str(now_id) update_pos = text(str_sql_pos) update_neg = text(str_sql_neg) session.execute(update_pos) session.execute(update_neg) session.commit() return
def get(self): data = Statistic.parser.parse_args() # 조회 날짜 설정 start_date = data['startDate'] end_date = data['endDate'] # 전체 주문 데이터 통계 stat_sql = f""" SELECT DATE_FORMAT(order_time, '%Y-%m-%d') AS '날짜', count(order_pk) AS '주문 건수', CAST(SUM(total_price) AS signed integer) AS '매출' FROM ORDERS ORD WHERE ORD.order_time between DATE_FORMAT(\'{start_date}\', '%Y-%m-%d 00:00:00') and DATE_FORMAT(\'{end_date}\', '%Y-%m-%d 23:59:59') AND ORD.completed = TRUE GROUP BY DATE_FORMAT(order_time, '%Y-%m-%d') WITH ROLLUP; """ if data['menu']: # 메뉴 데이터 통계 stat_sql = f""" SELECT menu_name AS '메뉴', CAST(SUM(quantity) AS signed integer) AS '개수', CAST(AVG(menu_price) AS signed integer) * CAST(SUM(quantity) AS signed integer) AS '매출' FROM ORDERS ORD JOIN ORDER_PRODUCTS ORD_PRD USING(order_pk) JOIN MENUS M ON (M.menu_pk = ORD_PRD.order_menu_pk) WHERE ORD.order_time between DATE_FORMAT(\'{start_date}\', '%Y-%m-%d 00:00:00') and DATE_FORMAT(\'{end_date}\', '%Y-%m-%d 23:59:59') GROUP BY menu_name WITH ROLLUP; """ elif data['option']: # 옵션 데이터 통계 stat_sql = f""" SELECT option_name AS '메뉴', CAST(SUM(quantity) AS signed integer) AS '개수', CAST(AVG(option_name) AS signed integer) * CAST(SUM(quantity) AS signed integer) AS '매출' FROM ORDERS ORD JOIN ORDER_PRODUCTS ORD_PRD USING(order_pk) JOIN MENUS M ON (M.menu_pk = ORD_PRD.order_menu_pk) JOIN ORDER_OPTIONS ORD_OP USING (product_pk) JOIN OPTIONS USING (option_pk) WHERE ORD.order_time between DATE_FORMAT(\'{start_date}\', '%Y-%m-%d 00:00:00') and DATE_FORMAT(\'{end_date}\', '%Y-%m-%d 23:59:59') AND ORD.completed = TRUE GROUP BY option_name WITH ROLLUP; """ result = session.execute(stat_sql).fetchall() session.close() result = query_to_dict(result) return {'data': result}, 200
def get_all_stations(): #result = [skipass.serialize() for skipass in session.query(Stations).all()] stations = session.query(Stations).all() sql = text( "SELECT station_id as s_id,instant as i,total_people as tp FROM `stations_history` GROUP BY station_id,instant,total_people HAVING instant = (SELECT instant FROM `stations_history` WHERE station_id = s_id ORDER BY instant DESC LIMIT 1) " ) db_result = session.execute(sql) db_result = list(db_result) result = [] for station in stations: temp = None for s in db_result: if station.id == s[0] and s[1].date() >= date.today(): temp = station.serialize() temp['totalPeople'] = s[2] if temp is None: temp = station.serialize() temp['totalPeople'] = 0 result.append(temp) return jsonify({"data": result})
def verify_user(): # Performs a POST request to verify a user try: json = request.json username = json["username"] password = json["password"] query = "SELECT * FROM user_by_username WHERE username = '******';".format( username) result = session.execute(query) for entry in result: if check_password_hash(entry.password, password): return create_response("User Verfied", 200) else: return create_response("User not verfied", 404) return create_response("User not found", 404) except (KeyError, AttributeError, TypeError): return create_response("Bad request with improper/incomplete fields", 403) except Exception: return create_response("Internal Server Error", 500)
def put(): schema = { 'type': 'object', 'properties': { 'walk_id': { 'type': 'integer', 'minimum': 0 } }, 'required': ['walk_id'] } # return jsonify({'hoge': request.json['walk_id']}), 200 try: validate(request.json, schema) except ValidationError as e: return jsonify({'msg': e.message}), 400 walk = session.query(Walk).filter( Walk.id == request.json['walk_id']).one_or_none() if walk is None: return jsonify({'msg': 'Walk Not Found'}), 404 walk.ended_at = datetime.now() session.commit() session.close() user_id = walk.user_id sql = 'select from_unixtime(round(unix_timestamp(created_at) div(5 * 60)) *(5 * 60)) as timekey, count(*) from pull, walk where pull.user_id = %s and pull.created_at BETWEEN (SELECT started_at FROM `walk` WHERE user_id = %s order by started_at desc LIMIT 1) and (SELECT ended_at FROM `walk` WHERE user_id = %s order by ended_at desc LIMIT 1) group by timekey;' % ( user_id, user_id, user_id) sql_res = session.execute(sql) results = [] for res in sql_res: results.append({'time': str(res[0]), 'count': res[1]}) return jsonify({'results': results}), 200
def get_detail(id): if request.args.get('user_id') is None: return jsonify({'msg': 'user_idがない!!'}), 400 user = session.query(User).filter(User.id == request.args.get('user_id')).one_or_none() if user is None: return jsonify({'msg': 'User Not Found'}), 404 walk = session.query(Walk).filter(Walk.id == id).one_or_none() if walk is None: return jsonify({'msg': 'Walk Not Found'}), 404 sql = 'select from_unixtime(round(unix_timestamp(created_at) div(5 * 60)) *(5 * 60)) as timekey, count(*) from pull, walk where pull.user_id = %s and pull.created_at BETWEEN (SELECT started_at FROM `walk` WHERE user_id = %s AND id = %s) and (SELECT ended_at FROM `walk` WHERE user_id = %s AND id = %s) group by timekey;' % (user.id, user.id, walk.id, user.id, walk.id) sql_res = session.execute(sql) results = [] for res in sql_res: results.append({'time': '{0:%H:%M}'.format(res[0]), 'count': int(res[1] / 10)}) return jsonify({'results': results}), 200
from database import Base, engine, session import csv csv_file = 'event_log.csv' CsvFile = csv.writer(open(csv_file, 'w'), 'excel') for row in session.execute('SELECT * FROM event_log'): print(row) CsvFile.writerow(row) csv_file = 'event_types.csv' CsvFile = csv.writer(open(csv_file, 'w'), 'excel') for row in session.execute('SELECT * FROM event_types'): print(row) CsvFile.writerow(row) csv_file = 'repository_list.csv' CsvFile = csv.writer(open(csv_file, 'w'), 'excel') for row in session.execute('SELECT * FROM repository_list'): print(row) CsvFile.writerow(row)
def get_7days_from(start_id): sql = "SELECT * FROM negaposi WHERE id >= " + str( start_id) + " ORDER BY id ASC FETCH FIRST 168 ROWS ONLY" rows = session.execute(sql) res = json.dumps([(dict(row.items())) for row in rows]) return res
def present(username, title): query = "SELECT * FROM poll_by_username_and_title WHERE username = '******' AND title = '{}';".format(username, title) result = session.execute(query) for a in result: return True return False
def wrap(*args, **kwargs): result = func(*args, **kwargs) session.execute(result) session.commit()
session.rollback() # ------------- Set event types into db ----------- event_types = ['commit', 'issue', 'pull_request', 'comment', 'review'] for i in range(0, len(event_types)): event_type = Event_type() event_type.event_type = event_types[i] try: session.add(event_type) session.commit() except exc.IntegrityError as err: session.rollback() # --------------Load events from DB into a dictionary------------------------ select_event = select([Event_type]) event_result = session.execute(select_event) event_dict = {} for event_rows in event_result: event_dict[event_rows['event_type']] = event_rows['id'] # --------------- Fetch all repo from database-------------------------- select_repo = select([Repo]) repo_result = session.execute(select_repo) # --------- Loop through fetched repos-------------- for repo_row in repo_result: repo_name = repo_row['name'] repo_organization = repo_row['organization']