def add_university_info(**kwargs):
engine = DbEngine.get_instance()
session = engine.get_session(autocommit=False, expire_on_commit=True)
try:
university_count = session.query(Universities).\
filter(Universities.university_name == kwargs["university_name"]).count()
if university_count != 0:
raise DBOperateException(
"Duplicated information input, please check")
university_obj = Universities(
province_id=kwargs["province_id"],
university_id=kwargs["university_id"],
university_name=kwargs["university_name"],
enable=True)
session.add(university_obj)
session.commit()
return university_obj.id
except DBOperateException:
session.rollback()
raise
except Exception:
session.rollback()
LOG.error("Call add_university_info error:%s" %
traceback.format_exc())
raise DBOperateException("Add university info error")
def add_major_info(**kwargs):
engine = DbEngine.get_instance()
session = engine.get_session(autocommit=False, expire_on_commit=True)
try:
major_count = session.query(Majors).\
filter(and_(Majors.college_id == kwargs["college_id"],
Majors.major_id == kwargs["major_id"])).count()
if major_count != 0:
raise DBOperateException("Duplicated information input, please check")
major_obj = Majors(major_id=kwargs["major_id"],
college_id=kwargs["college_id"],
major_name=kwargs["major_name"],
year=kwargs['year'],
enrollment=kwargs['enrollment'],
exempt=kwargs['exempt'],
enable=True)
session.add(major_obj)
session.commit()
return major_obj.id
except DBOperateException:
session.rollback()
raise
except Exception:
session.rollback()
LOG.error("Call add_major_info error:%s" % traceback.format_exc())
raise DBOperateException("Add major info error")
def get_university_info_by_province_id(province_id=None):
engine = DbEngine.get_instance()
session = engine.get_session(autocommit=False, expire_on_commit=True)
try:
if province_id:
university_info = session.query(Universities).filter(
Universities.province_id == province_id).all()
else:
university_info = session.query(Universities).all()
university_info_list = list()
for university in university_info:
university_dict = dict()
university_dict['id'] = university.id
university_dict['university_id'] = university.university_id
university_dict['province_id'] = university.province_id
university_dict['university_name'] = university.university_name
university_dict['enable'] = university.enable
university_info_list.append(university_dict)
LOG.info(
"Call get_university_info_by_province_id university_list:%s " %
university_info_list)
return university_info_list
except Exception:
LOG.error("Call get_university_info_by_id error:%s" %
traceback.format_exc())
raise DBOperateException("Get university info error")
def get_major_id_by_college_id(college_id, major_id):
engine = DbEngine.get_instance()
session = engine.get_session(autocommit=False, expire_on_commit=True)
try:
m_id = session.query(Majors.id).\
filter(and_(Majors.college_id == college_id, Majors.major_id == major_id)).scalar()
return m_id
except Exception:
LOG.error("Call get_major_id_by_college_id error:%s" % traceback.format_exc())
raise DBOperateException("Get major info error")
def delete_major_by_id(major_id):
engine = DbEngine.get_instance()
session = engine.get_session(autocommit=False, expire_on_commit=True)
try:
session.query(Majors).filter(Majors.id == major_id).delete()
session.commit()
except Exception:
session.rollback()
LOG.error("Call delete_major_by_id error:%s" % traceback.format_exc())
raise DBOperateException("Delete major info error")
def get_university_id_by_name(university_name):
engine = DbEngine.get_instance()
session = engine.get_session(autocommit=False, expire_on_commit=True)
try:
university_id = session.query(Universities.id).\
filter(Universities.university_name == university_name).scalar()
return university_id
except Exception:
LOG.error("Call get_university_id_by_name error:%s" %
traceback.format_exc())
raise DBOperateException("Get university info error")
def delete_province_by_id(province_id):
engine = DbEngine.get_instance()
session = engine.get_session(autocommit=False, expire_on_commit=True)
try:
session.query(Provinces).filter(
Provinces.id == province_id).delete()
session.commit()
except Exception:
session.rollback()
LOG.error("Call get_province_info_by_id error:%s" %
traceback.format_exc())
raise DBOperateException("Delete province info error")
def add_province_info(**kwargs):
engine = DbEngine.get_instance()
session = engine.get_session(autocommit=False, expire_on_commit=True)
try:
province_count = session.query(Provinces).\
filter(Provinces.province_name == kwargs['province_name']).count()
if province_count != 0:
raise DBOperateException(
"Duplicated information input, please check")
province_obj = Provinces(province_name=kwargs["province_name"],
enable=True)
session.add(province_obj)
session.commit()
return province_obj.id
except DBOperateException:
session.rollback()
raise
except Exception:
session.rollback()
LOG.error("Call get_province_info_by_id error:%s" %
traceback.format_exc())
raise DBOperateException("Add province info error")
def update_province_by_id(**kwargs):
engine = DbEngine.get_instance()
session = engine.get_session(autocommit=False, expire_on_commit=True)
try:
province_info = session.query(Provinces).filter(
Provinces.id == kwargs['id']).one()
province_info.province_name = kwargs['province_name']
province_info.enable = kwargs['enable']
session.commit()
except Exception:
session.rollback()
LOG.error("Call get_province_info_by_id error:%s" %
traceback.format_exc())
raise DBOperateException("Update province info error")
def get_province_id_by_name(province_name):
engine = DbEngine.get_instance()
session = engine.get_session(autocommit=False, expire_on_commit=True)
try:
province_id = session.query(Provinces.id).filter(
Provinces.province_name == province_name).scalar()
LOG.info("Call get_province_id_by_name province_id:%s" %
province_id)
return province_id
except Exception:
LOG.error("Call get_province_id_by_name error:%s" %
traceback.format_exc())
raise DBOperateException("Get province info error")
def update_university_by_id(**kwargs):
engine = DbEngine.get_instance()
session = engine.get_session(autocommit=False, expire_on_commit=True)
try:
university_info = session.query(Universities).filter(
Universities.id == kwargs['id']).one()
university_info.university_id = kwargs['university_id']
university_info.university_name = kwargs['university_name']
university_info.enable = kwargs['enable']
session.commit()
except Exception:
session.rollback()
LOG.error("Call update_university_by_id error:%s" %
traceback.format_exc())
raise DBOperateException("Update university info error")
def delete_university_by_id(university_id):
engine = DbEngine.get_instance()
session = engine.get_session(autocommit=False, expire_on_commit=True)
try:
"""
删除学校之前先删除学校内所有学院
"""
session.query(Universities).filter(
Universities.id == university_id).delete()
session.commit()
except Exception:
session.rollback()
LOG.error("Call delete_university_by_id error:%s" %
traceback.format_exc())
raise DBOperateException("Delete university info error")
def update_major_by_id(**kwargs):
engine = DbEngine.get_instance()
session = engine.get_session(autocommit=False, expire_on_commit=True)
try:
major_info = session.query(Majors).filter(Majors.id == kwargs['id']).one()
major_info.major_id = kwargs['major_id']
major_info.college_id = kwargs['college_id']
major_info.major_name = kwargs['major_name']
major_info.year = kwargs['year']
major_info.enrollment = kwargs['enrollment']
major_info.exempt = kwargs['exempt']
major_info.enable = kwargs['enable']
session.commit()
except Exception:
session.rollback()
LOG.error("Call update_major_by_id error:%s" % traceback.format_exc())
raise DBOperateException("Update major info error")
def get_major_info_by_college_id(college_id=None):
engine = DbEngine.get_instance()
session = engine.get_session(autocommit=False, expire_on_commit=True)
try:
if college_id:
major_info = session.query(Majors).filter(Majors.college_id == college_id).all()
else:
major_info = session.query(Majors).all()
major_info_list = list()
for major in major_info:
major_dict = dict()
major_dict['id'] = major.id
major_dict['major_name'] = major.major_name
major_dict['college_id'] = major.college_id
major_dict['major_id'] = major.major_id
major_dict['year'] = major.year
major_dict['enrollment'] = major.enrollment
major_dict['exempt'] = major.exempt
major_dict['enable'] = major.enable
major_info_list.append(major_dict)
return major_info_list
except Exception:
LOG.error("Call get_major_info_by_college_id error:%s" % traceback.format_exc())
raise DBOperateException("Get major info error")
def get_province_info_by_id(province_id=None):
engine = DbEngine.get_instance()
session = engine.get_session(autocommit=False, expire_on_commit=True)
try:
if province_id:
province_info = session.query(Provinces).filter(
Provinces.id == province_id).all()
else:
province_info = session.query(Provinces).all()
province_info_list = list()
for province in province_info:
province_dict = dict()
province_dict['province_name'] = province.province_name
province_dict['enable'] = province.enable
province_dict['id'] = province.id
province_info_list.append(province_dict)
LOG.info(
"Call get_province_info_by_id province_list:%s, province_id:%s"
% (province_info_list, province_id))
return province_info_list
except Exception:
LOG.error("Call get_province_info_by_id error:%s" %
traceback.format_exc())
raise DBOperateException("Get province info error")