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