class SnowRouteDataAccess(DataAccess):
def __init__(self, **kwargs):
super().__init__(**kwargs)
self.da_base = DataAccessBase(model.SnowRoute, SnowRouteInfo, **kwargs)
def list(self):
"""
:rtype: list[SnowRouteInfo]
"""
return self.da_base.list()
def get_by_id(self, route_id):
"""
:type route_id: int
:rtype: SnowRouteInfo
"""
return self.da_base.get_data_by_id(route_id)
def get_by_name(self, route_name):
"""
:type route_name: str
:rtype: SnowRouteInfo
"""
return self.da_base.get_data_by_name(route_name)
def insert(self, r, **kwargs):
"""
:type r: SnowRouteInfo
:rtype: model.SnowRoute
"""
return self.da_base.insert(r, **kwargs)
class SnowEventDataAccess(DataAccess):
def __init__(self, **kwargs):
super().__init__(**kwargs)
self.da_base = DataAccessBase(model.SnowEvent, SnowEventInfo, **kwargs)
def list(self):
"""
:rtype: list[SnowEventInfo]
"""
return self.da_base.list()
def list_by_year(self, years):
"""
:type years: list[int]
:rtype: list[SpecialEventInfo]
"""
results = []
for year in years:
res = self.da_base.search_date_range(
('start_time', datetime.datetime(int(year), 1, 1, 0, 0, 0)),
('end_time', datetime.datetime(int(year), 12, 31, 11, 59, 59)))
if res:
results.extend(res)
return results
def years(self):
"""
:rtype: list[int]:
"""
ys = []
for snei in self.da_base.list(as_model=True):
y = snei.start_time.year
if y not in ys:
ys.append(y)
return sorted(ys)
def get_by_id(self, id):
"""
:type id: int
:rtype: SnowEventInfo
"""
return self.da_base.get_data_by_id(id)
def get_by_name(self, route_name):
"""
:type route_name: str
:rtype: SnowEventInfo
"""
return self.da_base.get_data_by_name(route_name)
def insert(self, r, **kwargs):
"""
:type r: SnowEventInfo
:rtype: model.SnowEvent
"""
return self.da_base.insert(r, **kwargs)
class WZGroupDataAccess(DataAccess):
def __init__(self, **kwargs):
super().__init__(**kwargs)
self.da_base = DataAccessBase(model.WorkZoneGroup, WorkZoneGroupInfo,
**kwargs)
def list(self):
"""
:rtype: list[pyticas_tetres.ttypes.WorkZoneGroupInfo]
"""
return self.da_base.list()
def list_by_year(self, years):
"""
:type years: list[int]
:return:
"""
if years:
wheres = [('years', y) for y in years]
return self.da_base.search(wheres, op='or', cond='like')
else:
return self.da_base.search([('years', None), ('years', '')],
op='or',
cond='match')
def years(self):
"""
:rtype: list[int]:
"""
ys = []
for wzi in self.da_base.list():
if not wzi.years:
continue
for y in wzi.years.split(','):
iy = int(y)
if iy not in ys:
ys.append(iy)
return sorted(ys)
def get_by_id(self, wz_id):
"""
:type wz_id: int
:rtype: pyticas_tetres.ttypes.WorkZoneGroupInfo
"""
return self.da_base.get_data_by_id(wz_id)
def get_by_name(self, wz_name):
"""
:type wz_name: str
:rtype: pyticas_tetres.ttypes.WorkZoneGroupInfo
"""
return self.da_base.get_data_by_name(wz_name)
def insert(self, wzi, **kwargs):
"""
:type wzi: pyticas_tetres.ttypes.WorkZoneGroupInfo
:rtype: model.WorkZoneGroup
"""
return self.da_base.insert(wzi, **kwargs)
def update_years(self, id, **kwargs):
"""
:type id: int
:rtype: bool
"""
wzgm = self.da_base.get_model_by_id(id)
years = []
for wzm in wzgm._wzs:
syear = wzm.start_time.year
eyear = wzm.end_time.year
for y in range(syear, eyear + 1):
y = str(y)
if y not in years:
years.append(y)
return self.update(id, {'years': ','.join(years)}, **kwargs)
def search(self, searches, op='and', cond='match', **kwargs):
""" search data
**Example**
- find item that `sevent_id` is 1
>>> search([('sevent_id', 1)]
- find item that `name` == 'test and start_time == 2016-01-01 00:00 and end_time == 2016-01-01 07:00
>>> search([('name', 'test'), ('start_time', datetime(2016, 1, 1, 0, 0)), ('end_time', 2016, 1, 1, 7, 0)], op='and', cond='match')
- find items that `years` contains one of 2014, 2015 and 2016
>>> search([('years', y) for y in [2014, 2015, 2016]], op='or', cond='like')
"""
return self.da_base.search(searches, op, cond, **kwargs)
class WorkZoneDataAccess(DataAccess):
def __init__(self, **kwargs):
super().__init__(**kwargs)
self.da_base = DataAccessBase(model.WorkZone, WorkZoneInfo, **kwargs)
def list(self, **kwargs):
"""
:rtype: list[pyticas_tetres.ttypes.WorkZoneInfo]
"""
return self.da_base.list(**kwargs)
def list_by_year(self, years):
"""
:type years: list[int]
:return:
"""
wheres = [('years', y) for y in years]
return self.da_base.search(wheres, op='or', cond='like')
def years(self):
"""
:rtype: list[int]:
"""
ys = []
for wzi in self.da_base.list():
for y in wzi.years.split(','):
iy = int(y)
if iy not in ys:
ys.append(iy)
return sorted(ys)
def get_model_by_id(self, wz_id):
"""
:type wz_id: int
:rtype: pyticas_tetres.db.model.WorkZone
"""
return self.da_base.get_model_by_id(wz_id)
def get_by_id(self, wz_id):
"""
:type wz_id: int
:rtype: pyticas_tetres.ttypes.WorkZoneInfo
"""
return self.da_base.get_data_by_id(wz_id)
def get_by_name(self, wz_name):
"""
:type wz_name: str
:rtype: pyticas_tetres.ttypes.WorkZoneInfo
"""
return self.da_base.get_data_by_name(wz_name)
def insert(self, wzi, **kwargs):
"""
:type wzi: pyticas_tetres.ttypes.WorkZoneInfo
:rtype: model.WorkZone
"""
return self.da_base.insert(wzi, **kwargs)
def search(self, searches, op='and', cond='match', **kwargs):
""" search data
**Example**
- find item that `sevent_id` is 1
>>> search([('sevent_id', 1)]
- find item that `name` == 'test and start_time == 2016-01-01 00:00 and end_time == 2016-01-01 07:00
>>> search([('name', 'test'), ('start_time', datetime(2016, 1, 1, 0, 0)), ('end_time', 2016, 1, 1, 7, 0)], op='and', cond='match')
- find items that `years` contains one of 2014, 2015 and 2016
>>> search([('years', y) for y in [2014, 2015, 2016]], op='or', cond='like')
"""
return self.da_base.search(searches, op, cond, **kwargs)
def search_date_range(self, sdt, edt, **kwargs):
""" search data overlapped with the given data range
:type sdt: datetime.datetime
:type sdt: datetime.datetime
:rtype: list[pyticas_tetres.ttypes.WorkZoneInfo]
"""
as_model = kwargs.get('as_model', False)
clauses = or_()
clauses = or_(clauses, self.da_base.dbModel.end_time < sdt)
clauses = or_(clauses, self.da_base.dbModel.start_time > edt)
clauses = not_(clauses)
data_list = []
for model_data in self.da_base.session.query(
self.da_base.dbModel).filter(clauses):
if as_model:
data_list.append(model_data)
else:
data_list.append(self.da_base.to_info(model_data))
return data_list
class SnowMgmtDataAccess(DataAccess):
def __init__(self, **kwargs):
super().__init__(**kwargs)
self.da_base = DataAccessBase(model.SnowManagement, SnowManagementInfo,
**kwargs)
def list(self, **kwargs):
"""
:rtype: list[SnowManagementInfo]
"""
return self.da_base.list(**kwargs)
def list_by_period(self, sdt, edt, **kwargs):
"""
:type sdt: datetime.datetime
:type edt: datetime.datetime
:rtype: list[SnowManagementInfo]
"""
sdt = sdt.replace(second=0, microsecond=0)
edt = edt.replace(second=59, microsecond=59)
qry = (self.da_base.session.query(self.da_base.dbModel).join(
model.SnowEvent).filter(
or_(
and_(model.SnowEvent.start_time <= sdt,
model.SnowEvent.end_time >= sdt),
and_(model.SnowEvent.start_time >= sdt,
model.SnowEvent.end_time <= edt),
and_(model.SnowEvent.start_time <= edt,
model.SnowEvent.end_time >= edt),
)))
data_list = []
for model_data in qry:
data_list.append(self.da_base.to_info(model_data, **kwargs))
return data_list
def get_by_id(self, route_id):
"""
:type route_id: int
:rtype: SnowManagementInfo
"""
return self.da_base.get_data_by_id(route_id)
def get_by_name(self, route_name):
"""
:type route_name: str
:rtype: SnowManagementInfo
"""
return self.da_base.get_data_by_name(route_name)
def insert(self, r, **kwargs):
"""
:type r: SnowManagementInfo
:rtype: model.SnowManagement
"""
return self.da_base.insert(r, **kwargs)
def search(self, searches, op='and', cond='match', **kwargs):
"""
:type searches: list
:return:
"""
return self.da_base.search(searches, op=op, cond=cond, **kwargs)
class TTRouteDataAccess(DataAccess):
def __init__(self, **kwargs):
super().__init__(**kwargs)
self.da_base = DataAccessBase(model.TTRoute, TTRouteInfo, **kwargs)
def list(self):
"""
:rtype: list[TTRouteInfo]
"""
return self.da_base.list()
def list_by_corridor(self, corridor_name, **kwargs):
sess = self.da_base.session
dbModel = self.da_base.dbModel
qry = sess.query(dbModel).filter(dbModel.corridor == corridor_name)
as_model = kwargs.get('as_model', False)
limit = kwargs.get('limit', None)
group_by = kwargs.get('group_by', None)
order_by = kwargs.get('order_by', None)
window_size = kwargs.get('window_size', 1000)
# apply 'order by'
if order_by and isinstance(order_by, tuple):
# e.g. order_by = ('id', 'desc')
# e.g. order_by = ('name', 'asc')
qry = qry.order_by(
getattr(getattr(dbModel, order_by[0]), order_by[1])())
if group_by:
if isinstance(group_by, str):
qry = qry.group_by(getattr(dbModel, group_by))
else:
qry = qry.group_by(group_by)
# apply 'limit'
if limit:
qry = qry.limit(limit)
for m in self.da_base.query_generator(qry, window_size):
if as_model:
yield m
else:
yield self.da_base.to_info(m)
def get_by_id(self, route_id):
"""
:type route_id: int
:rtype: TTRouteInfo
"""
return self.da_base.get_data_by_id(route_id)
def get_by_name(self, route_name):
"""
:type route_name: str
:rtype: TTRouteInfo
"""
return self.da_base.get_data_by_name(route_name)
def insert(self, r, **kwargs):
"""
:type r: TTRouteInfo
:rtype: model.TTRoute
"""
return self.da_base.insert(r, **kwargs)
class ConfigDataAccess(DataAccess):
def __init__(self, **kwargs):
super().__init__(**kwargs)
self.da_base = DataAccessBase(model.Config, ConfigInfo, **kwargs)
def list(self, **kwargs):
"""
:rtype: list[pyticas_tetres.ttypes.ConfigInfo]
"""
return self.da_base.list(**kwargs)
def get_by_id(self, pkey):
"""
:type pkey: int
:rtype: ConfigInfo
"""
return self.da_base.get_data_by_id(pkey)
def get_by_name(self, name, **kwargs):
"""
:type name: str
:rtype: pyticas_tetres.ttypes.ConfigInfo
"""
if kwargs.get('as_model', False):
return self.da_base.get_model_by_name(name, **kwargs)
else:
return self.da_base.get_data_by_name(name, **kwargs)
def insert(self, name, content, **kwargs):
"""
:type name: str
:type content: str
:rtype: model.Config
"""
c = ConfigInfo()
c.name = name
c.content = content
return self.da_base.insert(c, **kwargs)
def insert_or_update(self, name, content):
"""
:type name: str
:type content: str
:rtype: Union(model.Config, bool)
"""
item = self.get_by_name(name, as_model=True)
if not item:
return self.insert(name, content)
else:
if self.update(item.id, {'content': content}):
item.content = content
return item
return False
def delete_by_name(self, name, **kwargs):
"""
:type name: str
:rtype: bool
"""
print_exception = kwargs.get('print_exception',
DEFAULT_PRINT_EXCEPTION)
sess = self.get_session()
try:
sess.query(model.Config).filter(model.Config.name == name).delete()
sess.commit()
return True
except Exception as ex:
if print_exception:
tb.traceback(ex)
sess.rollback()
return False