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):
"""
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):
"""
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 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():
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):
"""
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 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(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 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 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):
"""
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):
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):
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):
"""
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 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):
"""
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 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())
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()
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 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
from model.Database import DBSession
from model.models import *
session = DBSession()
with open('../Resources/station_city.csv', "r") as f:
content = f.read().splitlines()[1:]
for line in content:
station_name, province_name, city_name, district_name = line.split(",")
if city_name == "[]":
city_name = province_name
if district_name == "[]":
district_name = city_name
# is province in the table
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()
# is city in the table
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()
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 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()
