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 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 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()