def delete(self): """ Train line disable API, **JWT required** The body should be a JSON dictionary including the following attribute(s): **return**: A JSON dictionary with values: - `code`: `int`, equals to 0 if deletion is successful - `error`: `str`, shown if `code != 0` - `result`: `str`, shown if `code == 0` """ session = DBSession() try: body = request.get_json() train_name = body.get('train_name') train = session.query(Train).filter( Train.train_name == train_name, Train.available == True).first() if not train: return jsonify(code=12, error='停用失败,线路不存在或已停用') interval_list = session.query(Interval).filter( Interval.train_id == train.train_id, Interval.available == True).all() for interval in interval_list: interval.available = False train.available = False session.commit() return jsonify(code=0, result='线路停用成功') except: traceback.print_exc() session.rollback() return jsonify(code=12, error='停用失败,请联系运维人员') finally: session.close()
def post(self): """ Login API The body should be a JSON dictionary including the following attribute(s): - `username`: `str` - `password`: `str` **return**: A JSON dictionary with values: - `code`: `int`, equals to 0 if login is successful - `token`: `str` representing JWT token, shown if `code == 0` - `error`: `str`, shown if `code != 0` """ session = DBSession() try: body = request.get_json() user = session.query(User).filter( User.username == body.get('username')).first() if user is None: return jsonify(error='Username not found', code=401) authorized = user.check_password(body.get('password')) if not authorized: return jsonify(error='Wrong password', code=401) expires = datetime.timedelta(days=1) access_token = create_access_token(identity=str(user.user_id), expires_delta=expires) return jsonify(token=access_token, code=0) except: return jsonify(code=10, error='Login error') finally: session.close()
def get(self): """ Payment API **argument**: - `order_id`: `int` **return**: `Purchase succeeded` or `Purchase failed` or `Already paid` """ session = DBSession() try: order_id = request.args.get('order_id') current_order: Order = session.query(Order).filter( Order.order_id == order_id).first() if current_order.order_status == "paid": return "Already paid" current_order.order_status = "paid" session.commit() session.flush() current_ticket: Ticket = session.query(Ticket).filter( Ticket.ticket_id == current_order.ticket_id).first() current_ticket.available = True session.commit() session.flush() return "Purchase succeeded" except: session.rollback() return "Purchase failed" finally: session.close()
def post(self): """ Ticket payment status query API The body should be a JSON dictionary including the following attribute(s): - `order_id`: `int` **return**: A JSON dictionary with values: - `code`: `int`, always equals to 0 - `result`: `str`, `paid` or `unpaid` """ session = DBSession() try: body = request.get_json() order_id = body.get("order_id") current_order: Order = session.query(Order).filter( Order.order_id == order_id).first() if current_order.order_status == "paid": return jsonify(code=0, result="paid") else: return jsonify(code=0, result="unpaid") except: return jsonify(code=10, error='Query error') finally: session.close()
def get(self): depart_place = request.args.get('dep_station') arrival_place = request.args.get('arv_station') session = DBSession() depart_stations = get_nearby_station(depart_place, session) arrival_stations = get_nearby_station(arrival_place, session) depart_stations_id = [ station.station_id for station in depart_stations ] arrival_stations_id = [ station.station_id for station in arrival_stations ] depart_train_id = set([ train.train_id for train in session.query(Interval).filter( Interval.dep_station.in_(depart_stations_id)) ]) arrival_train_id = set([ train.train_id for train in session.query(Interval).filter( Interval.arv_station.in_(arrival_stations_id)) ]) train_id = depart_train_id.intersection(arrival_train_id) trains = session.query(Train).filter(Train.train_id.in_( list(train_id))) resp = [] for train in trains: resp.append(train.train_name) # DEBUG # print(depart_stations_id, arrival_stations_id) # print(depart_train_id, arrival_train_id) # print(train_id) return jsonify(result=resp)
def get(self): """ Train information query API **argument**: - `dep_station`: `str` - `arv_station`: `str` - `DG_only`: `boolean` **return**: A JSON dictionary with values: - `code`: `int`, always equals to 0 - `result`: `list` of dictionaries of train information: - `train_name`: `str` - `first_interval`: `int` - `last_interval`: `int` - `dep_station`: `str` - `dep_time`: `str` - `arv_station`: `str` - `arv_time`: `str` """ session = DBSession() try: dep_place = '%' + urllib.parse.unquote( request.args.get('dep_station')) + '%' arv_place = '%' + urllib.parse.unquote( request.args.get('arv_station')) + '%' dg_only = urllib.parse.unquote( request.args.get('DG_only')).lower() == 'true' train_info_list = fuzzy_query(dep_place, arv_place, dg_only, session) return jsonify(result=train_info_list, code=0) except: return jsonify(code=10, error='Query error') finally: session.close()
def post(self): """ Sign-up API The body should be a JSON dictionary including the following attribute(s): - `username`: `str` - `real_name`: `str` - `password`: `str` - `id_card`: `str` - `phone_number`: `str` - `email`: `str` **return**: A JSON dictionary with values: - `code`: `int`, equals to 0 if sign-up is successful - `error`: `str`, shown if `code != 0` """ session = DBSession() try: body = request.get_json() if session.query(User).filter( User.username == body.get('username')).first() is not None: return jsonify(error='Username already exists', code=406) new_user = User(**body) new_user.hash_password() session.add(new_user) session.commit() return jsonify(code=0) except: session.rollback() return jsonify(code=10, error='Unexpected error when creating user') finally: session.close()
def get(self): """ District information query API **argument**: - `province`: `str`, can be empty - `city`: `str`, can be empty if province is not specified - `district`: `str`, can be empty if city is not specified **return**: A JSON dictionary with values: - `code`: `int`, always equals to 0 - `result`: `list` of dictionaries of provinces/cities/districts: - `province_name`/`city_name`/`district_name`/`station_name`: `str` """ session = DBSession() try: province_name = request.args.get('province') city_name = request.args.get('city') district_name = request.args.get('district') if not province_name: province_list = session.query(Province.province_name).all() return jsonify(code=0, result=list( map(lambda x: dict(zip(x.keys(), x)), province_list))) elif not city_name: city_list = session.query(City.city_name).join(Province, Province.province_id == City.province_id) \ .filter(Province.province_name == province_name).all() return jsonify(code=0, result=list( map(lambda x: dict(zip(x.keys(), x)), city_list))) elif not district_name: district_list = session.query(District.district_name) \ .join(City, City.city_id == District.city_id) \ .join(Province, Province.province_id == City.province_id) \ .filter(Province.province_name == province_name, City.city_name == city_name).all() return jsonify(code=0, result=list( map(lambda x: dict(zip(x.keys(), x)), district_list))) else: station_list = session.query(Station.station_name) \ .join(District, Station.district_id == District.district_id) \ .join(City, City.city_id == District.city_id) \ .join(Province, Province.province_id == City.province_id) \ .filter(Province.province_name == province_name, City.city_name == city_name, District.district_name == district_name, Station.available == True) \ .all() return jsonify(code=0, result=list( map(lambda x: dict(zip(x.keys(), x)), station_list))) except: return jsonify(code=10, error='Query error') finally: session.close()
def test(): session = DBSession() try: arr = session.execute(func.test_func([1, 2, 3])).first() print(arr) print(dict(zip(arr.keys(), arr))) finally: session.close()
def wrapper(*args, **kwargs): session = DBSession() user_id = get_jwt_identity() is_admin = session.query( User.is_admin).filter(User.user_id == user_id).first() session.close() if is_admin[0]: return fun(*args, **kwargs) else: return jsonify(code=10, error='该用户没有管理员权限,无法执行管理员操作')
def get(self): """ Available tickets query API **return**: A JSON dictionary with values: - `code`: `int`, always equals to 0 - `result`: `list` of dictionaries of ticket information: - `seat_type_id`: `int` - `seat_type_name`: `str` - `left_cnt`: `int` - `price`: `float` """ session = DBSession() try: train_name = urllib.parse.unquote(request.args.get('train_name')) first_interval = int( urllib.parse.unquote(request.args.get('first_interval'))) last_interval = int( urllib.parse.unquote(request.args.get('last_interval'))) interval_list = get_interval_list(train_name, session) first_index = session.query(interval_list.c.interval_no) \ .filter(interval_list.c.interval_id == first_interval) \ .first() \ .interval_no last_index = session.query(interval_list.c.interval_no) \ .filter(interval_list.c.interval_id == last_interval) \ .first() \ .interval_no price_list = session.query(Price.seat_type_id, func.sum(Price.price).label('price')) \ .join(interval_list, Price.interval_id == interval_list.c.interval_id) \ .filter(interval_list.c.interval_no <= last_index, interval_list.c.interval_no >= first_index) \ .group_by(Price.seat_type_id) \ .subquery() seats_left = session.query(Seat.seat_type_id, SeatType.name, func.count().label('left_cnt')) \ .join(SeatType, SeatType.seat_type_id == Seat.seat_type_id) \ .join(Train, Train.train_id == Seat.train_id) \ .filter(Train.train_name == train_name, func.cast(func.substring(Seat.occupied, first_index, last_index - first_index + 1), BIGINT) == 0) \ .group_by(Seat.seat_type_id, SeatType.name) \ .subquery() resp = session.query(seats_left.c.seat_type_id, seats_left.c.name.label('seat_type_name'), seats_left.c.left_cnt, price_list.c.price) \ .join(price_list, price_list.c.seat_type_id == seats_left.c.seat_type_id) \ .all() resp = list( sorted(map(lambda x: dict(zip(x.keys(), x)), resp), key=lambda x: x['seat_type_id'])) return jsonify(result=resp, code=0) except: return jsonify(code=10, error='Query error') finally: session.close()
def get(): from_city = '镇江' to_city = '丹阳' dg_only = True session = DBSession() dep_stations = get_nearby_station(from_city, session) dep_intervals = session.query(Interval).filter( Interval.dep_station.in_(dep_stations)) arv_stations = get_nearby_station(to_city, session) arv_intervals = session.query(Interval).filter( Interval.arv_station.in_(arv_stations)) intervals = dep_intervals.union(arv_intervals).cte("intervals") nums = func.count('*').label('c')
def get(self): session = DBSession() try: dep_station = urllib.parse.unquote(request.args.get('dep_station')) arv_station = urllib.parse.unquote(request.args.get('arv_station')) dg_only = urllib.parse.unquote( request.args.get('DG_only')).lower() == 'true' dep_train_info = session.query(Interval.train_id, Interval.dep_station) \ .join(Station, Interval.dep_station == Station.station_id) \ .filter(Station.station_name == dep_station) \ .subquery() arv_train_info = session.query(Interval.train_id, Interval.arv_station) \ .join(Station, Interval.arv_station == Station.station_id) \ .filter(Station.station_name == arv_station) \ .subquery() raw_train_info = session.query(Interval.train_id, Train.train_name, func.min(Interval.interval_id).label('first_interval'), func.max(Interval.interval_id).label('last_interval')) \ .join(Train, Train.train_id == Interval.train_id) \ .join(dep_train_info, Interval.train_id == dep_train_info.c.train_id) \ .join(arv_train_info, Interval.train_id == arv_train_info.c.train_id) \ .filter(or_(Interval.dep_station == dep_train_info.c.dep_station, Interval.arv_station == arv_train_info.c.arv_station)) \ .group_by(Interval.train_id, Train.train_name) \ .subquery() dep_i = aliased(Interval, name='dep_i') arv_i = aliased(Interval, name='arv_i') dep_s = aliased(Station, name='dep_s') arv_s = aliased(Station, name='arv_s') train_info_list = session.query(raw_train_info.c.train_name, raw_train_info.c.first_interval, raw_train_info.c.last_interval, dep_s.station_name.label('dep_station'), func.cast(dep_i.dep_datetime, String).label('dep_time'), arv_s.station_name.label('arv_station'), func.cast(arv_i.arv_datetime, String).label('arv_time')) \ .join(dep_i, dep_i.interval_id == raw_train_info.c.first_interval) \ .join(arv_i, arv_i.interval_id == raw_train_info.c.last_interval) \ .join(dep_s, dep_s.station_id == dep_i.dep_station) \ .join(arv_s, arv_s.station_id == arv_i.arv_station) \ .filter(dep_s.station_name == dep_station, arv_s.station_name == arv_station) \ .order_by(dep_i.dep_datetime) \ .all() train_info_list = list( filter( lambda x: x['train_name'][0] in 'DG' if dg_only else True, map(lambda x: dict(zip(x.keys(), x)), train_info_list))) return jsonify(result=train_info_list, code=0) finally: session.close()
def patch(self): """ Station modification API, **JWT required** The body should be a JSON dictionary including the following attribute(s): - `city_name`: `str` - `district_name`: `str` - `station_name`: `str` - `new_station_name`: `str` **return**: A JSON dictionary with values - `code`: `int`, equals to 0 if modification is successful - `result`: `str` for success message, shown if `code == 0` - `error`: `str`, shown if `code != 0` """ session = DBSession() try: body = request.get_json() city_name = body.get('city_name') district_name = body.get('district_name') station_name = body.get('station_name') new_station_name = body.get('new_station_name') current_station: Station = session.query(Station).filter( Station.station_name == station_name, Station.available == True).first() if not current_station: return jsonify(code=1, error="站点不存在") district = session.query(District).filter( District.district_name == district_name).first() if not district: city = session.query(City).filter( City.city_name == city_name).first() new_district = District(district_name=district_name, city_id=city.city_id) session.add(new_district) session.commit() district = session.query(District).filter( District.district_name == district_name).first() current_station.district_id = district.district_id if new_station_name: current_station.station_name = new_station_name session.commit() return jsonify(code=0, result="修改成功") except: session.rollback() return jsonify(code=1, error='修改失败') finally: session.close()
def get(self): """ Train information query API **argument**: - `train_name`: `str` **return**: A JSON dictionary with values: - `code`: `int`, always equals to 0 - `result`: `list` of dictionaries of passing station information: - `id`: `int` - `district`: `str` - `station`: `str` - `time`: `str` """ session = DBSession() try: train_name = request.args.get('train_name') successive_train_rec = get_interval_list(train_name, session) interval_list = session.query(successive_train_rec.c.interval_no.label('id'), City.city_name.concat(',').concat(District.district_name).label('district'), Station.station_name.label('station'), func.cast(successive_train_rec.c.dep_datetime, String).label('time')) \ .join(Station, Station.station_id == successive_train_rec.c.dep_station) \ .join(District, Station.district_id == District.district_id) \ .join(City, District.city_id == City.city_id) \ .order_by(successive_train_rec.c.interval_id, Station.available == True) \ .all() last_no = interval_list[-1].id resp = list(map(lambda x: dict(zip(x.keys(), x)), interval_list)) last_station = session.query(func.cast(successive_train_rec.c.arv_datetime, String).label('time'), City.city_name.concat(',').concat(District.district_name).label('district'), Station.station_name.label('station'), literal(last_no + 1).label('id')) \ .join(Station, Station.station_id == successive_train_rec.c.arv_station) \ .join(District, Station.district_id == District.district_id) \ .join(City, District.city_id == City.city_id) \ .filter(successive_train_rec.c.interval_no == last_no, Station.available == True) \ .first() if last_station: resp.append(dict(zip(last_station.keys(), last_station))) return jsonify(result=resp, code=0) except: return jsonify(code=10, error='未找到线路') finally: session.close()
def patch(self): """ User information update API, **JWT required** The body should be a JSON dictionary including the following attribute(s): - `username`: `str` - `password`: `str` - `real_name`: `str` - `email`: `str` - `phone_number`: `str` **return**: A JSON dictionary with values: - `code`: `int`, equals to 0 if update is successful - `error`: `str`, shown if `code != 0` - `result`: `str`, shown if `code == 0` """ session = DBSession() try: body = request.get_json() user_id = get_jwt_identity() user = session.query(User).filter(User.user_id == user_id).first() if user is None: return jsonify(error='User not found', code=404) if user.username != body.get('username'): new_username = body.get('username') if session.query(User).filter( User.username == new_username).first() is not None: return jsonify(error='Username already exists', code=406) user.username = new_username user.real_name = body.get('real_name') user.email = body.get('email') user.phone_number = body.get('phone_number') new_password = body.get('password') if new_password: if 8 <= len(new_password) <= 30: user.password = new_password user.hash_password() else: session.rollback() return jsonify(code=1, error='密码长度错误') session.commit() return jsonify(code=0, result='用户信息修改成功') except: session.rollback() return jsonify(code=10, error='Update failed') finally: session.close()
def test(): session = DBSession() try: dep_place = '%南京%' arv_place = '%西安%' dg_only = True dep_train_info = session.query(Interval.train_id, Interval.dep_station) \ .join(Station, Interval.dep_station == Station.station_id) \ .filter(Station.station_name.like(dep_place)) \ .subquery() arv_train_info = session.query(Interval.train_id, Interval.arv_station) \ .join(Station, Interval.arv_station == Station.station_id) \ .filter(Station.station_name.like(arv_place)) \ .subquery() raw_train_info = session.query(Interval.train_id, Train.train_name, func.min(Interval.interval_id).label('first_interval'), func.max(Interval.interval_id).label('last_interval')) \ .join(Train, Train.train_id == Interval.train_id) \ .join(dep_train_info, Interval.train_id == dep_train_info.c.train_id) \ .join(arv_train_info, Interval.train_id == arv_train_info.c.train_id) \ .filter(or_(Interval.dep_station == dep_train_info.c.dep_station, Interval.arv_station == arv_train_info.c.arv_station)) \ .group_by(Interval.train_id, Train.train_name) \ .subquery() dep_i = aliased(Interval, name='dep_i') arv_i = aliased(Interval, name='arv_i') dep_s = aliased(Station, name='dep_s') arv_s = aliased(Station, name='arv_s') train_info_list = session.query(raw_train_info.c.train_name, raw_train_info.c.first_interval, raw_train_info.c.last_interval, dep_s.station_name.label('dep_station'), func.cast(dep_i.dep_datetime, String).label('dep_time'), arv_s.station_name.label('arv_station'), func.cast(arv_i.arv_datetime, String).label('arv_time')) \ .join(dep_i, dep_i.interval_id == raw_train_info.c.first_interval) \ .join(arv_i, arv_i.interval_id == raw_train_info.c.last_interval) \ .join(dep_s, dep_s.station_id == dep_i.dep_station) \ .join(arv_s, arv_s.station_id == arv_i.arv_station) \ .filter(dep_s.station_name.like(dep_place), arv_s.station_name.like(arv_place)) \ .order_by(dep_i.dep_datetime) \ .all() train_info_list = list(filter(lambda x: x['train_name'][0] in 'DG' if dg_only else True, map(lambda x: dict(zip(x.keys(), x)), train_info_list))) return dict(result=train_info_list, code=0) finally: session.close()
def get(self): """ Transfer station query API **argument**: - `dep_station`: `str` - `arv_station`: `str` - `DG_only`: `boolean` **return**: A JSON dictionary with values: - `code`: `int`, always equals to 0 - `result`: `list` of dictionaries of station information: - `stationName`: `str` - `stationId`: `int` """ session = DBSession() try: dep_place = '%' + urllib.parse.unquote( request.args.get('dep_station')) + '%' arv_place = '%' + urllib.parse.unquote( request.args.get('arv_station')) + '%' dg_only = urllib.parse.unquote( request.args.get('DG_only')).lower() == 'true' resp = [] for transfer_station in QueryTransfer.transfer_list: first_list = fuzzy_query(dep_place, transfer_station, dg_only, session) if first_list: second_list = fuzzy_query(transfer_station, arv_place, dg_only, session) if second_list: transfer_id = session.query(Station.station_id) \ .filter(Station.station_name == transfer_station, Station.available == True) \ .first() \ .station_id resp.append( dict(stationName=transfer_station, stationId=transfer_id)) return jsonify(result=resp, code=0) except: return jsonify(code=10, error='Query error') finally: session.close()
def patch(self): """ Train line price information update API for administrator, **JWT required** The body should be a JSON dictionary including the following attribute(s): - `interval_id`: `int` - `price`: `dict` containing: - `seat_type_1`, `str` - `seat_type_2`, `str` - `seat_type_3`, `str` - `seat_type_4`, `str` - `seat_type_5`, `str` - `seat_type_6`, `str` - `seat_type_7`, `str` **return**: A JSON dictionary with values: - `code`: `int`, equals to 0 if update is successful - `error`: `str`, shown if `code != 0` - `result`: `str`, shown if `code == 0` """ session = DBSession() try: body = request.get_json() for raw_id, raw_price in body.get('price').items(): seat_type_id = int(raw_id[-1]) obj_price: Price = session.query(Price) \ .filter(Price.interval_id == body.get('interval_id'), Price.seat_type_id == seat_type_id) \ .first() if obj_price: price = float(raw_price) if price > 0: obj_price.price = price else: raise Exception('') session.commit() return jsonify(code=0, result='修改成功') except: session.rollback() traceback.print_exc() return jsonify(code=10, error='修改失败') finally: session.close()
def get(self): """ User information query API, **JWT required** **return**: A JSON dictionary with values: - `code`: `int`, equals to 0 if query is successful - `result`: `dict` containing user information, shown if `code == 0` - `error`: `str`, shown if `code != 0` """ session = DBSession() try: user_id = get_jwt_identity() user = session.query(User).filter(User.user_id == user_id).first() if user is None: return jsonify(error='User not found', code=404) return jsonify(result=user.to_dict(), code=0) except: return jsonify(code=10, error='User information query error') finally: session.close()
def get(self): """ User existence check API (check by username) **argument**: - `username`: `str` **return**: A JSON dictionary with values: - `code`: `int`, always equals to 0 - `result`: `boolean` indicating if the user exists """ session = DBSession() try: username = request.args.get('username') user = session.query(User).filter( User.username == username).first() return jsonify(result=(user is None), code=0) except: return jsonify(code=10, error='Query error') finally: session.close()
def test(): train_name = 'Test000' allow_unavailable = True session = DBSession() first_id = session.query(Interval.interval_id) \ .join(Train, Train.train_id == Interval.train_id) \ .filter(Train.train_name == train_name, Interval.prev_id == None, or_(literal(allow_unavailable), Interval.available == True)) \ .first() \ .interval_id cte = session.query(Interval.interval_id, Interval.dep_station, Interval.arv_station, Interval.next_id, literal(1).label('interval_no')) \ .filter(Interval.interval_id == first_id) \ .cte(name='cte', recursive=True) cte_alias = aliased(cte, name='c') i_alias = aliased(Interval, name='i') cte = cte.union_all( session.query(i_alias.interval_id, i_alias.dep_station, i_alias.arv_station, i_alias.next_id, cte_alias.c.interval_no + 1).filter(i_alias.interval_id == cte_alias.c.next_id)) cte_table = cte.c dep_s = aliased(Station, name='dep_s') arv_s = aliased(Station, name='arv_s') interval_info_list = session.query(cte_table.interval_id, dep_s.station_name.label('dep_station'), arv_s.station_name.label('arv_station'), cte_table.interval_no) \ .join(dep_s, dep_s.station_id == cte_table.dep_station) \ .join(arv_s, arv_s.station_id == cte_table.arv_station) \ .order_by(cte_table.interval_no) \ .all() # print(json.dumps(list(map( # lambda x: dict(interval_id=x.interval_id, dep_station=x.dep_station, arv_station=x.arv_station, # interval_no=x.interval_no), interval_info_list)), indent=4, ensure_ascii=False)) print( json.dumps(list( map(lambda x: dict(zip(x.keys(), x)), interval_info_list)), indent=4, ensure_ascii=False)) print(interval_info_list[0].keys())
def get(self): depart_place = request.args.get('dep_station') arrival_place = request.args.get('arv_station') SQL = ''' with station_table as ( select station_name, district_name, city_name, station_id from station join district on station.district_id = district.district_id join city on district.city_id = city.city_id ) select distinct i.train_id, train_name from interval i join train on train.train_id = i.train_id where i.train_id in (select train_id from interval where dep_station in (select station_id from station_table where station_name like '%{0}%' or district_name like '%{0}%' or city_name like '%{0}%')) and i.train_id in (select train_id from interval where arv_station in (select station_id from station_table where station_name like '%{1}%' or district_name like '%{1}%' or city_name like '%{1}%')) '''.format(depart_place, arrival_place) session = DBSession() resp = [] sql_result = session.execute(SQL) for result in sql_result: resp.append({result[0]: result[1]}) return jsonify(result=resp)
def get(self): train_name = request.args.get('train_name') session = DBSession() train_id = session.query( Train.train_id).filter(Train.train_name == train_name).first() interval_list = session.query(Interval.interval_id, City.city_name, District.district_name, Station.station_name, Interval.dep_datetime) \ .join(Station, Station.station_id == Interval.dep_station) \ .join(District, Station.district_id == District.district_id) \ .join(City, District.city_id == City.city_id) \ .filter(Interval.train_id == train_id) \ .order_by(Interval.interval_id) \ .all() resp = [] id_num = 1 for interval_id, city_name, district_name, station_name, dep_datetime in interval_list: resp.append({ 'id': id_num, 'district': city_name + ',' + district_name, 'station': station_name, 'time': str(dep_datetime) }) id_num += 1 arv_datetime, last_city_name, last_district_name, last_station_name = \ session.query(Interval.arv_datetime, City.city_name, District.district_name, Station.station_name) \ .join(Station, Station.station_id == Interval.arv_station) \ .join(District, Station.district_id == District.district_id) \ .join(City, District.city_id == City.city_id) \ .filter(Interval.interval_id == interval_list[-1][0]) \ .first() resp.append({ 'id': id_num, 'district': last_city_name + ',' + last_district_name, 'station': last_station_name, 'time': str(arv_datetime) }) return jsonify(result=resp, code=0)
def get(self): """ Geographic position query API **argument**: - `geo_name`: `str` **return**: A JSON dictionary with values: - `code`: `int`, always equals to 0 - `result`: `list` of dictionaries of position information: - `province`: `str` - `city`: `str` - `district`: `str` - `station`: `str` """ session = DBSession() try: geo_name = request.args.get('geo_name') stations = session.query(Province.province_name.label('province'), City.city_name.label('city'), District.district_name.label('district'), Station.station_name.label('station')) \ .join(District, Station.district_id == District.district_id) \ .join(City, District.city_id == City.city_id) \ .join(Province, Province.province_id == City.province_id) \ .filter(or_(Station.station_name.like('%' + geo_name + '%'), District.district_name.like('%' + geo_name + '%'), City.city_name.like('%' + geo_name + '%'), Province.province_name.like('%' + geo_name + '%')), Station.available == True) \ .all() resp = [dict(zip(station.keys(), station)) for station in stations] return jsonify(result=resp, code=0) except: return jsonify(code=10, error='Query error') finally: session.close()
def delete(self): """ Station deletion API, **JWT required** The body should be a JSON dictionary including the following attribute(s): - `station_name`: `str` **return**: A JSON dictionary with values - `code`: `int`, equals to 0 if deletion is successful - `result`: `str` for success message, shown if `code == 0` - `error`: `str`, shown if `code != 0` """ session = DBSession() try: body = request.get_json() station_name = body.get('station_name') # Find if the station exists station: Station = session.query(Station).filter( Station.station_name == station_name, Station.available == True).first() if not station: return jsonify(code=1, error="站点不存在或已删除") # Check if the station has train passing interval = session.query(Interval).filter( or_(Interval.dep_station == station.station_id, Interval.arv_station == station.station_id)).first() if interval: return jsonify(code=2, error="站点仍有火车经过") station.available = False session.commit() return jsonify(code=0, result="删除成功") except: session.rollback() return jsonify(code=10, error='操作失败,请联系运维人员') finally: session.close()
def get(): dep_place = '%镇江%' arv_place = '%丹阳%' dg_only = True session = DBSession() station_table = session.query(Station.station_name, Station.station_id, District.district_name, City.city_name) \ .join(District, Station.district_id == District.district_id) \ .join(City, District.city_id == City.city_id) dep_station_table = station_table.filter(or_(District.district_name.like(dep_place), Station.station_name.like(dep_place), City.city_name.like(dep_place))) \ .subquery() arv_station_table = station_table.filter(or_(District.district_name.like(arv_place), Station.station_name.like(arv_place), City.city_name.like(arv_place))) \ .subquery() dep_train_info = session.query(Interval.train_id, Interval.dep_station) \ .join(dep_station_table, Interval.dep_station == dep_station_table.c.station_id) \ .subquery() arv_train_info = session.query(Interval.train_id, Interval.arv_station) \ .join(arv_station_table, Interval.arv_station == arv_station_table.c.station_id) \ .subquery() print('ding') raw_train_info = session.query(Interval.train_id, Train.train_name, func.min(Interval.interval_id).label('first_interval'), func.max(Interval.interval_id).label('last_interval')) \ .join(Train, Train.train_id == Interval.train_id) \ .join(dep_train_info, Interval.train_id == dep_train_info.c.train_id) \ .join(arv_train_info, Interval.train_id == arv_train_info.c.train_id) \ .filter(dep_train_info.c.dep_station != arv_train_info.c.arv_station, or_(Interval.dep_station == dep_train_info.c.dep_station, Interval.arv_station == arv_train_info.c.arv_station)) \ .group_by(Interval.train_id, Train.train_name) \ .subquery() # raw_train_info = session.query(Interval.train_id, Train.train_name, # func.min(Interval.interval_id).label('first_interval'), # func.max(Interval.interval_id).label('last_interval')) \ # .join(Train, Train.train_id == Interval.train_id) \ # .filter(Interval.train_id.in_(dep_train_info.with_entities(Train.train_id).all()), # Interval.train_id.in_(arv_train_info.with_entities(Train.train_id).all()), # or_(Interval.dep_station.in_(dep_train_info.with_entities(Interval.dep_station).all()), # Interval.arv_station.in_(arv_train_info.with_entities(Interval.arv_station).all()))) \ # .group_by(Interval.train_id, Train.train_name) \ # .subquery() print('ding') dep_i = aliased(Interval, name='dep_i') arv_i = aliased(Interval, name='arv_i') dep_s = aliased(Station, name='dep_s') arv_s = aliased(Station, name='arv_s') train_info_list = session.query(raw_train_info.c.train_name, raw_train_info.c.first_interval, raw_train_info.c.last_interval, dep_s.station_name, func.cast(dep_i.dep_datetime, String), arv_s.station_name, func.cast(arv_i.arv_datetime, String)) \ .join(dep_i, dep_i.interval_id == raw_train_info.c.first_interval) \ .join(arv_i, arv_i.interval_id == raw_train_info.c.last_interval) \ .join(dep_s, dep_s.station_id == dep_i.dep_station) \ .join(arv_s, arv_s.station_id == arv_i.arv_station) \ .order_by(dep_i.dep_datetime) \ .all() train_info_list = list(filter(lambda x: x['train_name'][0] in 'DG' if dg_only else True, map(lambda x: dict(zip( ['train_name', 'first_interval', 'last_interval', 'dep_station', 'dep_time', 'arv_station', 'arv_time'], x)), train_info_list))) return train_info_list
import os import traceback from datetime import time from model.Database import DBSession from model.models import Station, Train, Interval, Price session = DBSession() errors = [] for root, subFolders, files in os.walk('/Users/whexy/Downloads/12307_intervals_G'): for file_i, filename in enumerate(files): try: with open(os.path.join(root, filename), "r") as f: train_name = filename.split(".")[0] print("Handling {}, {}".format(file_i, train_name)) train: Train = session.query(Train).filter(Train.train_name == train_name).first() # Impossible to run if train is None: print("Skip " + train_name) continue intervals = f.read().splitlines()[::-1] # 按照逆序读取 interval_id_list = [] # 存储 id for interval in intervals: interval_info = interval.split(',') dep_s_name = ''.join(interval_info[1].split()) arv_s_name = ''.join(interval_info[3].split())
def post(self): """ Station addition API, **JWT required** The body should be a JSON dictionary including the following attribute(s): - `province_name`: `str` - `city_name`: `str` - `district_name`: `str` - `station_name`: `str` **return**: A JSON dictionary with values - `code`: `int`, equals to 0 if addition is successful - `result`: `str` for success message, shown if `code == 0` - `error`: `str`, shown if `code != 0` """ session = DBSession() try: body = request.get_json() province_name = body.get('province_name') city_name = body.get('city_name') district_name = body.get('district_name') station_name = body.get('station_name') station = session.query(Station) \ .filter(Station.station_name == station_name) \ .first() exist_flag = False if station: if station.available: return jsonify(code=1, error="站点已存在!") else: district = session.query(District) \ .filter(District.district_id == station.district_id) \ .first() if district.district_name == district_name: station.available = True session.commit() return jsonify(code=0, result='站点{}添加成功'.format(station_name)) exist_flag = True province = session.query(Province).filter( Province.province_name == province_name).first() if province is None: new_province = Province(province_name=province_name) session.add(new_province) session.commit() province = session.query(Province).filter( Province.province_name == province_name).first() city = session.query(City).filter( City.city_name == city_name).first() if city is None: new_city = City(city_name=city_name, province_id=province.province_id) session.add(new_city) session.commit() city = session.query(City).filter( City.city_name == city_name).first() district = session.query(District).filter( District.district_name == district_name).first() if district is None: new_district = District(district_name=district_name, city_id=city.city_id) session.add(new_district) session.commit() district = session.query(District).filter( District.district_name == district_name).first() if exist_flag: station.district_id = district.district_id station.available = True else: station = Station(station_name=station_name, district_id=district.district_id) session.add(station) session.commit() return jsonify(code=0, result='站点{}添加成功'.format(station_name)) except: traceback.print_exc() session.rollback() return jsonify(code=1, error='添加失败,站点已存在或地址信息有误。') finally: session.close()
def post(self): """ Train line creation API, **JWT required** The body should be a JSON dictionary including the following attribute(s): - `train_name`: `str` - `line`: `list` of dictionaries containing: - `dep_station`: `str` - `arv_station`: `str` - `dep_time`: `str` - `arv_time`: `str` - `price`: `dict` containing at least one of: - `seat_type_1`, `str` - `seat_type_2`, `str` - `seat_type_3`, `str` - `seat_type_4`, `str` - `seat_type_5`, `str` - `seat_type_6`, `str` - `seat_type_7`, `str` **return**: A JSON dictionary with values: - `code`: `int`, equals to 0 if creation is successful - `error`: `str`, shown if `code != 0` - `result`: `str`, shown if `code == 0` """ session = DBSession() try: body = request.get_json() train_name = body.get('train_name') if not train_name: return jsonify(code=11, error='火车名为空') train: Train = session.query(Train).filter( Train.train_name == train_name).first() if train: return jsonify(code=11, error='火车名已存在!') new_train = Train(train_name=train_name) session.add(new_train) session.commit() session.flush() train_id = new_train.train_id interval_id_list = [] interval_list = body.get('line') seat_type_list = [] for interval_info in interval_list: dep_station = session.query(Station.station_id) \ .filter(Station.station_name == interval_info['dep_station'], Station.available == True) \ .first() \ .station_id arv_station = session.query(Station.station_id) \ .filter(Station.station_name == interval_info['arv_station'], Station.available == True) \ .first() \ .station_id dep_datetime = None arv_datetime = None if 'dep_time' in interval_info.keys( ) and interval_info['dep_time']: dep_datetime = time( *list(map(int, interval_info['dep_time'].split(':')))) if 'arv_time' in interval_info.keys( ) and interval_info['arv_time']: arv_datetime = time( *list(map(int, interval_info['arv_time'].split(':')))) new_interval = Interval(train_id=train_id, dep_station=dep_station, arv_station=arv_station, dep_datetime=dep_datetime, arv_datetime=arv_datetime) session.add(new_interval) session.commit() session.flush() interval_id = new_interval.interval_id interval_id_list.append(interval_id) price_dict = interval_info['price'] for k, v in price_dict.items(): seat_type_id = int(k[-1]) if seat_type_id not in seat_type_list: seat_type_list.append(seat_type_id) if not v: continue seat_price = max(0.01, abs(float(v))) new_price = Price(interval_id=interval_id, seat_type_id=seat_type_id, price=seat_price) session.add(new_price) session.commit() session.execute(func.add_seats(seat_type_list, train_id)) for index, interval_id in enumerate(interval_id_list): interval = session.query(Interval).filter( Interval.interval_id == interval_id).first() interval.next_id = interval_id_list[ index + 1] if index < len(interval_id_list) - 1 else None interval.prev_id = interval_id_list[index - 1] if index > 0 else None session.commit() return jsonify(code=0, result='线路添加成功') except: traceback.print_exc() session.rollback() return jsonify(code=12, error='添加失败,请检查输入是否合法') finally: session.close()