def find_session_by_show(show_id):
conn = Connection()
sess = conn.query(ShowSession.session_id,ShowSession.show_id,ShowSession.start_time,ShowSession.sale_time,ShowSession.end_sale_time,ShowSession.limit)\
.filter_by(show_id=show_id)\
.all()
conn.close()
return sess
def get_session_detail_by_id(show_id, session_id):
conn = Connection()
sess = conn.query(ShowSession.session_id,ShowSession.show_id,ShowSession.start_time,ShowSession.sale_time,ShowSession.end_sale_time,ShowSession.limit)\
.filter_by(show_id=show_id, session_id=session_id)\
.first()
conn.close()
return sess
def find_stadiums_by_city(city):
conn = Connection()
stadiums_in_city = conn.query(Stadium.stadium_id,Stadium.stadium_name,Stadium.address,Stadium.has_seats,Stadium.pic_src)\
.filter_by(city=city)\
.all()
conn.close()
return stadiums_in_city
def get_max_area_id(stadium_id):
conn = Connection()
num = conn.query(func.max(Area.area_id))\
.filter_by(stadium_id=stadium_id)\
.first()
conn.close()
return num
def add_a_session(new_sess):
conn = Connection()
try:
conn.add(new_sess)
conn.commit()
conn.close()
print('[db]:新增场次信息成功')
except:
conn.rollback()
print('[db]:rollback add_a_session')
def find_all_shows():
conn = Connection()
try:
shows = conn.query(Shows.show_id,Shows.show_name,Shows.show_type,Shows.show_pic,Shows.producer,Shows.stadium_id,Shows.can_choose_seats,Shows.description_file)\
.all()
conn.close()
return shows
except:
print('[db]: find_all_shows failed')
return None
def add_an_artist(new_art):
conn = Connection()
try:
conn.add(new_art)
conn.commit()
conn.close()
print('[db]:新增艺人成功')
except:
conn.rollback()
print('[db]:rollback add_artist')
def find_artists_by_show(show_id):
try:
conn = Connection()
artists = conn.query(Artist.artist_id,Artist.artist_name,Artist.artist_pic).join(Includes)\
.filter_by(show_id=show_id).all()
conn.close()
return artists
except:
print('find_artists_by_show failed')
return None
def add_an_area(area):
conn = Connection()
try:
conn.add(area)
conn.commit()
conn.close()
return ('[db]:新增场馆区域成功')
except:
conn.rollback()
return ('[db]:rollback add_an_area')
def add_a_stadium(stadium):
conn = Connection()
try:
conn.add(stadium)
conn.commit()
conn.close()
print('[db]:新增场馆信息成功')
except:
conn.rollback()
print('[db]:rollback add_a_stadium')
def add_a_show(new_show):
conn = Connection()
try:
conn.add(new_show)
conn.commit()
conn.close()
print('[db]:新增演出信息成功')
except:
conn.rollback()
print('[db]:rollback add_a_show')
def find_follow_by_user(user_id):
try:
conn = Connection()
follows = conn.query(Artist.artist_id,Artist.artist_name).join(Follows)\
.filter_by(user_id=user_id)\
.all()
conn.close()
return follows
except:
print('find_follow_by_user failed')
return None
def find_detail_by_show_id(show_id):
conn = Connection()
try:
shows = conn.query(Shows.show_id,Shows.show_name,Shows.show_type,Shows.show_pic,Shows.producer,Shows.can_choose_seats,Shows.description_file)\
.join(Stadium).filter_by(show_id=show_id)\
.all()
conn.close()
return shows
except:
print('[db]: find_shows_by_stadium failed')
return None
def find_areas_by_stadium(stadium_id):
conn = Connection()
try:
areas_in_stadium = conn.query(Area.stadium_id,Area.area_id,Area.area_name,Area.coordinate,Area.row_count,Area.col_count)\
.filter_by(stadium_id=stadium_id)\
.all()
conn.close()
return areas_in_stadium
except:
print('[db]:find_areas_by_stadium failed')
return None
def find_shows_by_artist(artist_id):
try:
conn = Connection()
shows = conn.query(Shows.show_id,Shows.show_name,Shows.show_type,Shows.producer,Shows.stadium_id,Shows.can_choose_seats,Shows.description_file)\
.join(Includes)\
.filter_by(artist=artist_id)\
.all()
conn.close()
return shows
except:
print('find_shows_by_artist failed')
return None
def count_follow_by_artist(artist_id):
conn = Connection()
count = conn.query(func.count(Follows.user_id))\
.filter_by(artist_id=artist_id).first()
conn.close()
return count
# add_follow
# delete_follow
# add_artist
# delete_artist
# add_artist_to_show
def edit_an_artist(art_id, new_art):
conn = Connection()
try:
art = conn.query(Artist).filter_by(artist_id=art_id).first()
art.artist_name = new_art.artist_name
art.artist_pic = new_art.artist_pic
conn.commit()
conn.close()
print('[db]:编辑艺人成功')
except:
conn.rollback()
print('[db]:rollback edit_an_artist')
def edit_an_area(s_id, a_id, new_area):
conn = Connection()
try:
area = conn.query(Area).filter_by(area_id=a_id,
stadium_id=s_id).first()
area.area_name = new_area.area_name
area.coordinate = new_area.coordinate
area.row_count = new_area.row_count
area.col_count = new_area.col_count
conn.commit()
conn.close()
print('[db]:编辑区域成功')
except:
conn.rollback()
print('[db]:rollback edit_an_area')
def edit_a_stadium(stadium_id, new_stadium):
conn = Connection()
try:
stadium = conn.query(Stadium).filter_by(stadium_id=stadium_id).first()
stadium.stadium_name = new_stadium.stadium_name
stadium.city = new_stadium.city
stadium.address = new_stadium.address
stadium.has_seats = new_stadium.has_seats
stadium.pic_src = new_stadium.pic_src
conn.commit()
conn.close()
print('[db]:更新场馆信息成功')
except:
conn.rollback()
print('[db]:rollback edit_stadium')
def edit_a_session(show_id, session_id, new_session):
conn = Connection()
try:
sess = conn.query(ShowSession).filter_by(
show_id=show_id, session_id=session_id).first()
print(sess)
sess.start_time = new_session.start_time
sess.sale_time = new_session.end_sale_time
sess.end_sale_time = new_session.end_sale_time
sess.limit = new_session.limit
conn.commit()
conn.close()
print('[db]:更新场次信息成功')
except:
conn.rollback()
print('[db]:rollback edit_a_session')
def edit_show_info(show_id, new_show):
conn = Connection()
try:
show = conn.query(Shows).filter_by(show_id=show_id).first()
show.show_name = new_show.show_name
show.show_type = new_show.show_type
show.producer = new_show.producer
show.show_pic = new_show.show_pic
show.desc_file_name = new_show.desc_file_name
show.stadium_id = new_show.stadium_id
show.can_choose_seats = new_show.can_choose_seats
conn.commit()
conn.close()
print('[db]:更新演出信息成功')
except:
conn.rollback()
print('[db]:rollback edit_show_info')
def find_follows_by_user(user_id):
conn = Connection()
try:
my_fo=conn.query(Follows.artist_id,Artist.artist_name)\
.filter_by(user_id=user_id)\
.join(Artist)
conn.commit()
conn.close()
print('[db]:获取关注成功')
return my_fo
except:
print('find_follows_by_user failed')
return None
def delete_an_area(stadium_id, area_id):
conn = Connection()
try:
area = conn.query(Area).filter_by(area_id=area_id,
stadium_id=stadium_id).first()
conn.delete(area)
conn.commit()
conn.close()
return '[db]:删除区域成功'
except:
conn.rollback()
return '[db]:rollback delete_an_area'
def find_all_stadiums():
conn = Connection()
all_stadiums = conn.query(Stadium.stadium_id,Stadium.stadium_name,Stadium.city,Stadium.address,Stadium.has_seats,Stadium.pic_src)\
.all()
conn.close()
return all_stadiums
def delete_follow(user_id, art_id):
conn = Connection()
try:
fo = conn.query(Follows).filter_by(user_id=user_id,
artist_id=art_id).first()
conn.delete(fo)
conn.commit()
conn.close()
print('[db]:取消关注成功')
except:
conn.rollback()
print('[db]:rollback delete_follow')
def add_follow(user_id, art_id):
conn = Connection()
try:
fo = Follows(user_id=user_id, artist_id=art_id)
conn = Connection()
conn.add(fo)
conn.commit()
conn.close()
print('[db]:关注成功')
except:
conn.rollback()
print('[db]:rollback add_follow')
def find_all_artists():
conn = Connection()
artists = conn.query(Artist.artist_id, Artist.artist_name,
Artist.artist_pic).all()
conn.close()
return artists
def add_show_art(show_id, art_id):
conn = Connection()
try:
a_s = Includes(show_id=show_id, artist=art_id)
conn = Connection()
conn.add(a_s)
conn.commit()
conn.close()
print('[db]:新增 艺人-演出 成功')
except:
conn.rollback()
print('[db]:rollback add_show_art')
def delete_an_artist(art_id):
conn = Connection()
try:
artist = conn.query(Artist).filter_by(artist_id=art_id).first()
conn.delete(artist)
conn.commit()
conn.close()
return '[db]:删除艺人成功'
except:
conn.rollback()
return '[db]:rollback delete_artist'
def delete_show_art(show_id, art_id):
conn = Connection()
try:
a_s = conn.query(Includes).filter_by(show_id=show_id,
artist=art_id).first()
conn.delete(a_s)
conn.commit()
conn.close()
print('[db]:取消 艺人-演出 成功')
except:
conn.rollback()
print('[db]:rollback delete_show_art')