class IncidentTypeDataAccess(object):
def __init__(self, **kwargs):
kwargs['session'] = conn.get_session()
kwargs['primary_key'] = 'pkey'
self.da_base = DataAccessBase(model.CADIncidentType, CADIncidentTypeInfo, **kwargs)
def list(self):
"""
:rtype: list[CADIncidentTypeInfo]
"""
return self.da_base.list()
def get_by_id(self, id):
"""
:type id: int
:rtype: CADIncidentTypeInfo
"""
return self.da_base.get_data_by_id(id)
def insert(self, r, **kwargs):
"""
:type r: CADIncidentTypeInfo
:rtype: model.CADIncidentType
"""
return self.da_base.insert(r, **kwargs)
def close_session(self):
return self.da_base.close()
class IncidentTypeDataAccess(DataAccess):
def __init__(self, **kwargs):
super().__init__(**kwargs)
self.da_base = DataAccessBase(model.IncidentType, IncidentTypeInfo,
**kwargs)
def list(self):
"""
:rtype: list[IncidentTypeInfo]
"""
return self.da_base.list()
def get_by_id(self, id):
"""
:type id: int
:rtype: IncidentTypeInfo
"""
return self.da_base.get_data_by_id(id)
def insert(self, r, **kwargs):
"""
:type r: IncidentTypeInfo
:rtype: model.IncidentType
"""
return self.da_base.insert(r, **kwargs)
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 TODReliabilityDataAccess(DataAccess):
def __init__(self, **kwargs):
super().__init__(**kwargs)
self.da_base = DataAccessBase(model.TODReliability, TODReliabilityInfo,
**kwargs)
def list(self):
"""
:rtype: list[TODReliabilityInfo]
"""
return self.da_base.list()
def list_by_route(self, ttr_id, regime_type, **kwargs):
"""
:type ttr_id: int
:rtype: list[TODReliabilityInfo]
"""
as_model = kwargs.get('as_model', False)
qry = (self.da_base.session.query(self.da_base.dbModel).filter(
model.TODReliability.route_id == ttr_id).filter(
model.TODReliability.regime_type == regime_type))
data_list = []
for model_data in qry:
if as_model:
data_list.append(model_data)
else:
data_list.append(self.da_base.to_info(model_data))
return data_list
def get_by_id(self, id):
"""
:type id: int
:rtype: TODReliabilityInfo
"""
return self.da_base.get_data_by_id(id)
def delete_all_for_a_route(self, ttri_id, **kwargs):
"""
:type ttri_id: int
"""
print_exception = kwargs.get('print_exception', False)
try:
qry = (self.da_base.session.query(self.da_base.dbModel).filter(
self.da_base.dbModel.route_id == ttri_id))
qry.delete(synchronize_session=False)
return True
except Exception as ex:
if print_exception:
tb.traceback(ex)
return False
def insert(self, r, **kwargs):
"""
:type r: TODReliabilityInfo
:rtype: model.TTRoute
"""
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 WZFeatureDataAccess(DataAccess):
def __init__(self, **kwargs):
super().__init__(**kwargs)
self.da_base = DataAccessBase(model.WorkZoneFeature,
WorkZoneFeatureInfo, **kwargs)
def list(self, wz_id, **kwargs):
"""
:type wz_id: int
:rtype: list[WorkZoneFeatureInfo] or list[model.WorkZoneFeature]
"""
return self.da_base.search([('wz_id', wz_id)], cond='match', **kwargs)
def list_by_wz_ids(self, wz_ids, **kwargs):
"""
:type wz_ids: list[int]
:rtype: Union(list[WorkZoneFeatureInfo], list[model.WorkZoneFeature])
"""
if not wz_ids:
return []
as_model = kwargs.get('as_model', False)
window_size = kwargs.get('window_size', 1000)
sess = self.da_base.session
dbModel = self.da_base.dbModel
qry = sess.query(dbModel).filter(dbModel.wz_id.in_(wz_ids))
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, wzf_id):
"""
:type wzf_id: int
:rtype: pyticas_tetres.ttypes.WorkZoneFeatureInfo
"""
return self.da_base.get_data_by_id(wzf_id)
def get_by_wzid(self, wz_id):
"""
:type wz_id: int
:rtype: list[pyticas_tetres.ttypes.WorkZoneFeatureInfo]
"""
return self.da_base.search([('wz_id', wz_id)])
def insert(self, wzfi, **kwargs):
"""
:type wzfi: pyticas_tetres.ttypes.WorkZoneFeatureInfo
:rtype: model.WorkZoneFeature
"""
return self.da_base.insert(wzfi, **kwargs)
class RouteWiseMOEParametersDataAccess(DataAccess):
def __init__(self, **kwargs):
super().__init__(**kwargs)
self.da_base = DataAccessBase(model.RouteWiseMOEParameters,
RouteWiseMOEParametersInfo, **kwargs)
def list(self):
"""
:rtype: list[pyticas_tetres.ttypes.WorkZoneGroupInfo]
"""
data_list = self.da_base.list(
order_by=[('reference_tt_route_id',
'asc'), ('start_time', 'asc'), ('end_time',
'asc'), ('status',
'asc')])
for data in data_list:
data.start_time = str(data.start_time) if data.start_time else ""
data.end_time = str(data.end_time) if data.end_time else ""
data.update_time = str(
data.update_time) if data.update_time else ""
return data_list
def get_by_id(self, moe_param_id):
"""
:type wz_id: int
:rtype: pyticas_tetres.ttypes.WorkZoneGroupInfo
"""
return self.da_base.get_data_by_id(moe_param_id)
def insert(self, route_wise_moe_param_info, **kwargs):
"""
:type wzi: pyticas_tetres.ttypes.WorkZoneGroupInfo
:rtype: model.WorkZoneGroup
"""
return self.da_base.insert(route_wise_moe_param_info, **kwargs)
def search_by_route_id(self, route_id, *args, **kwargs):
return self.da_base.search([('reference_tt_route_id', route_id)])
def get_latest_moe_param_for_a_route(self, route_id, *args, **kwargs):
latest_object = None
data_list = self.search_by_route_id(route_id, *args, **kwargs)
if not data_list:
return latest_object
latest_update_time = data_list[0].update_time
latest_object = data_list[0]
for data in data_list:
if data.update_time > latest_update_time:
latest_update_time = data.update_time
latest_object = data
return latest_object
class WeatherDataAccess(DataAccess):
def __init__(self, year, **kwargs):
super().__init__(**kwargs)
self.da_base = DataAccessBase(model_yearly.get_weather_table(year),
WeatherInfo, **kwargs)
def list(self):
"""
:rtype: list[WeatherInfo]
"""
return self.da_base.list()
def list_by_period(self, site_id, prd, sen_id=0):
"""
:type site_id: int
:type prd: pyticas.ttypes.Period
:type sen_id: int
:rtype: list[WeatherInfo]
"""
sfield, sdt = 'dtime', prd.start_date
efield, edt = 'dtime', prd.end_date
qry = (self.da_base.session.query(self.da_base.dbModel).filter(
getattr(self.da_base.dbModel, 'site_id') == site_id).filter(
getattr(self.da_base.dbModel, 'sen_id') == sen_id).filter(
getattr(self.da_base.dbModel, efield) <= edt).filter(
getattr(self.da_base.dbModel, sfield) >= sdt))
data_list = []
for model_data in qry:
data_list.append(self.da_base.to_info(model_data))
return data_list
def get_by_id(self, id):
"""
:type id: int
:rtype: WeatherInfo
"""
return self.da_base.get_data_by_id(id)
def insert(self, r, **kwargs):
"""
:type r: WeatherInfo
:rtype: model.Weather
"""
return self.da_base.insert(r, **kwargs)
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 ActionLogDataAccess(DataAccess):
INSERT = 'insert'
UPDATE = 'update'
DELETE = 'delete'
DT_TTROUTE = 'tt_route'
DT_WEATHER = 'weather'
DT_INCIDENT = 'incident'
DT_WORKZONE = 'workzone'
DT_WORKZONE_GROUP = 'workzone_group'
DT_SPECIALEVENT = 'special_event'
DT_SNOWEVENT = 'snow_event'
DT_SNOWROUTE = 'snow_route'
DT_SNOWMGMT = 'snow_management'
DT_SYSTEMCONFIG = 'sysconfig'
DT_ROUTE_WISE_MOE_PARAMETERS = 'route_wise_moe_parameters'
STATUS_WAIT = 'wait'
STATUS_RUNNING = 'running'
STATUS_FAIL = 'fail'
STATUS_STOPPED = 'stopped'
STATUS_DONE = 'done'
def __init__(self, **kwargs):
super().__init__(**kwargs)
self.da_base = DataAccessBase(model.ActionLog, ActionLogInfo, **kwargs)
def list(self,
start_time=None,
action_types=None,
handled=None,
target_datatypes=None,
data_desc=None,
status=None,
**kwargs):
"""
:param start_time: e.g. 2013-12-04 12:00:00
:type start_time: Union(str, datetime.datetime)
:type action_types: list[str]
:type handled: bool
:type target_datatypes: list[str]
:rtype: list[pyticas_tetres.ttypes.ActionLogInfo]
"""
if isinstance(start_time, str):
start_time = datetime.datetime.strptime(start_time,
'%Y-%m-%d %H:%M:%S')
as_model = kwargs.get('as_model', False)
limit = kwargs.get('limit', None)
order_by = kwargs.get('order_by', None)
dbModel = model.ActionLog
session = self.da_base.session
qry = session.query(dbModel)
if start_time:
qry = qry.filter(dbModel.reg_date >= start_time)
if action_types:
cond = or_(*[
dbModel.action_type == action_type
for action_type in action_types
])
qry = qry.filter(cond)
if handled is not None:
qry = qry.filter(dbModel.handled == handled)
if target_datatypes:
cond = or_(*[
dbModel.target_datatype == target_datatype
for target_datatype in target_datatypes
])
qry = qry.filter(cond)
if data_desc:
qry = qry.filter(dbModel.data_desc.like(data_desc))
if status:
qry = qry.filter(dbModel.status == status)
# 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])())
else:
qry = qry.order_by(dbModel.reg_date.asc())
# apply 'limit'
if limit:
qry = qry.limit(limit)
qry = qry.all()
data_list = []
for m in qry:
if as_model:
data_list.append(m)
else:
data_list.append(self.da_base.to_info(m))
return data_list
def get_by_id(self, pkey):
"""
:type pkey: int
:rtype: Union(list[ActionLogInfo], list[model.ActionLog])
"""
return self.da_base.get_data_by_id(pkey)
def delete_range(self,
start_time=None,
end_time=None,
action_types=None,
handled=None,
target_datatypes=None,
**kwargs):
"""
:param start_time: e.g. 2013-12-04 12:00:00
:type start_time: Union(str, datetime.datetime)
:param end_time: e.g. 2013-12-04 12:00:00
:type end_time: Union(str, datetime.datetime)
:type action_types: list[str]
:type handled: bool
:type target_datatypes: list[str]
:rtype: Union(list[ActionLogInfo], list[model.ActionLog])
"""
print_exception = kwargs.get('print_exception', False)
if isinstance(start_time, str):
start_time = datetime.datetime.strptime(start_time,
'%Y-%m-%d %H:%M:%S')
if isinstance(end_time, str):
end_time = datetime.datetime.strptime(end_time,
'%Y-%m-%d %H:%M:%S')
dbModel = model.ActionLog
session = self.da_base.session
qry = session.query(dbModel)
if start_time:
qry = qry.filter(dbModel.reg_date >= start_time)
if end_time:
qry = qry.filter(dbModel.reg_date <= end_time)
if action_types:
cond = or_(*[
dbModel.action_type == action_type
for action_type in action_types
])
qry = qry.filter(cond)
if handled:
qry = qry.filter(dbModel.handled == handled)
if target_datatypes:
cond = or_(*[
dbModel.target_datatype == target_datatype
for target_datatype in target_datatypes
])
qry = qry.filter(cond)
try:
qry.delete(synchronize_session=False)
return True
except Exception as ex:
if print_exception:
tb.traceback(ex)
return False
def insert(self, r, **kwargs):
"""
:type r: ActionLogInfo
:rtype: model.ActionLog
"""
return self.da_base.insert(r, **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')
:param searches: search condition list (see the above example)
:param op: operand ('and', 'or')
:param cond: condition ('match', 'like')
:param kwargs: if `as_model` is True, returns data as `dbModel` type
:rtype: list[pyticas_tetres.ttypes.ActionLogInfo]
"""
return self.da_base.search(searches, op, cond, **kwargs)
@classmethod
def data_description(cls, datatype, data):
"""
:type datatype: str
:type data: object
:rtype: str
"""
def _(d, a):
attrs = a.split('.')
obj = d
for attr in attrs:
obj = getattr(d, attr, None)
if obj == None:
break
return obj
if datatype == ActionLogDataAccess.DT_TTROUTE:
return 'Travel Time Route (id=%s, name=%s)' % (_(
data, 'id'), _(data, 'name'))
if datatype == ActionLogDataAccess.DT_WEATHER:
return 'Weather (id=%s, usaf=%s, wban=%s, time=%s)' % (_(
data, 'id'), _(data, 'usaf'), _(data, 'wban'), _(
data, 'dtime'))
if datatype == ActionLogDataAccess.DT_INCIDENT:
return (
'Incident (id=%s, type=%s, time=%s, lat=%s, lon=%s area=%s)' %
(_(data, 'id'), _(data, '_incident_type.eventtype'),
_(data, 'cdts'), _(data, 'lat'), _(data,
'lon'), _(data, 'earea')))
if datatype == ActionLogDataAccess.DT_WORKZONE_GROUP:
return 'Workzone Group (id=%s, name=%s)' % (_(
data, 'id'), _(data, 'name'))
if datatype == ActionLogDataAccess.DT_WORKZONE:
return (
'Workzone (id=%s, wz_group=%s, start_time=%s, end_time=%s)' %
(_(data, 'id'), data._wz_group.name
if hasattr(data, '_wz_group') and data._wz_group else None,
_(data, 'start_time'), _(data, 'end_time')))
if datatype == ActionLogDataAccess.DT_SPECIALEVENT:
return (
'Special Event (id=%s, name=%s, start_time=%s, end_time=%s)' %
(_(data, 'id'), _(data, 'name'), _(
data, 'start_time'), _(data, 'end_time')))
if datatype == ActionLogDataAccess.DT_SNOWMGMT:
return (
'Road Condition during Snow Event (id=%s, truck_route=[%s, %s], snow_event=[%s, %s ~ %s], lost=%s, regain=%s)'
% (_(data, 'id'), _(data, 'sroute_id'), data._snowroute.name if
hasattr(data, '_snowroute') and data._snowroute else None,
_(data, 'sevent_id'), data._snowevent.start_time if
hasattr(data, '_snowevent') and data._snowevent else None,
data._snowevent.end_time if hasattr(data, '_snowevent')
and data._snowevent else None, _(
data, 'lane_lost_time'), _(data, 'lane_regain_time')))
if datatype == ActionLogDataAccess.DT_SNOWROUTE:
return ('Truck Route (id=%s, name=%s, prj_id=%s)' %
(_(data, 'id'), _(data, 'name'), _(data, 'prj_id')))
if datatype == ActionLogDataAccess.DT_SNOWEVENT:
return (
'Snow Event (id=%s, start_time=%s, end_time=%s)' %
(_(data, 'id'), _(data, 'start_time'), _(data, 'end_time')))
return 'Unknown'
class NoaaWeatherDataAccess(DataAccess):
def __init__(self, year, **kwargs):
super().__init__(**kwargs)
self.da_base = DataAccessBase(model_yearly.get_noaa_table(year), NoaaWeatherInfo, **kwargs)
def list(self, **kwargs):
"""
:rtype: list[NoaaWeatherInfo]
"""
return self.da_base.list(**kwargs)
def list_by_period(self, usaf, wban, prd):
"""
:type usaf: str or int
:type wban: wban or int
:type prd: pyticas.ttypes.Period
:rtype: list[NoaaWeatherInfo]
"""
sfield, sdt = 'dtime', prd.start_date
efield, edt = 'dtime', prd.end_date
qry = (self.da_base.session.query(self.da_base.dbModel)
.filter(getattr(self.da_base.dbModel, 'usaf') == usaf)
.filter(getattr(self.da_base.dbModel, 'wban') == wban)
.filter(getattr(self.da_base.dbModel, efield) <= edt)
.filter(getattr(self.da_base.dbModel, sfield) >= sdt))
data_list = []
for model_data in qry:
data_list.append(self.da_base.to_info(model_data))
return data_list
def get_by_id(self, id):
"""
:type id: int
:rtype: NoaaWeatherInfo
"""
return self.da_base.get_data_by_id(id)
def delete_range(self, usaf, wban, start_time, end_time, **kwargs):
"""
:type usaf: str
:type wban: str
:type start_time: datetime.datetime
:type end_time: datetime.datetime
:rtype: bool
"""
print_exception = kwargs.get('print_exception', False)
dbModel = self.da_base.dbModel
try:
qry = self.da_base.session.query(dbModel)
if usaf is not None:
qry = qry.filter(dbModel.usaf == usaf)
if wban is not None:
qry = qry.filter(dbModel.wban == wban)
if start_time is not None and end_time is not None:
qry = qry.filter(and_(dbModel.dtime >= start_time, dbModel.dtime <= end_time))
qry.delete(synchronize_session=False)
return True
except Exception as ex:
if print_exception:
tb.traceback(ex)
return False
def insert(self, r, **kwargs):
"""
:type r: NoaaWeatherInfo
:rtype: model.Weather
"""
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
class TravelTimeDataAccess(DataAccess):
def __init__(self, year, **kwargs):
super().__init__(**kwargs)
self.da_base = DataAccessBase(model_yearly.get_tt_table(year), TravelTimeInfo,
**kwargs)
def list(self, **kwargs):
"""
:rtype: list[TravelTimeInfo]
"""
return self.da_base.list(**kwargs)
def list_by_generator(self, **kwargs):
"""
:rtype: list[TravelTimeInfo]
"""
return self.da_base.list_as_generator(**kwargs)
def list_with_deleted_route(self, **kwargs):
"""
:rtype: list[TravelTimeInfo]
"""
res = []
as_model = kwargs.get('as_model', False)
sess = self.da_base.session
dbModel = self.da_base.dbModel
ex = sess.query(model.TTRoute).filter(dbModel.route_id == model.TTRoute.id)
if ex.count():
qry = sess.query(dbModel).filter(~ex.exists())
for m in qry:
if as_model:
res.append(m)
else:
res.append(self.da_base.to_info(m))
return res
def delete_if_route_is_deleted(self, **kwargs):
"""
:rtype: bool
"""
print_exception = kwargs.get('print_exception', False)
sess = self.get_session()
dbModel = self.da_base.dbModel
try:
ex = sess.query(model.TTRoute).filter(dbModel.route_id == model.TTRoute.id)
if ex.count():
sess.query(dbModel).filter(~ex.exists()).delete(synchronize_session=False)
return True
except Exception as ex:
if print_exception:
tb.traceback(ex)
return False
def generator_by_period(self, ttr_id, prd, **kwargs):
"""
:type ttr_id: int
:type prd: pyticas.ttypes.Period
:rtype: list[TravelTimeInfo]
"""
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)
sfield, sdt = 'time', prd.start_date
efield, edt = 'time', prd.end_date
qry = (self.da_base.session.query(self.da_base.dbModel)
.filter(getattr(self.da_base.dbModel, 'route_id') == ttr_id)
.filter(getattr(self.da_base.dbModel, sfield) <= edt)
.filter(getattr(self.da_base.dbModel, efield) > sdt))
# 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(self.da_base.dbModel, order_by[0]), order_by[1])())
if group_by:
if isinstance(group_by, str):
qry = qry.group_by(getattr(self.da_base.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 list_by_period(self, ttr_id, prd, **kwargs):
"""
:type ttr_id: int
:type prd: pyticas.ttypes.Period
:rtype: list[pyticas_tetres.ttypes.TravelTimeInfo]
"""
weekdays = kwargs.get('weekdays', None)
window_size = kwargs.get('window_size', 1000)
start_time = kwargs.get('start_time', None)
end_time = kwargs.get('end_time', None)
as_model = kwargs.get('as_model', False)
order_by = kwargs.get('order_by', None)
limit = kwargs.get('limit', None)
sfield, sdt = 'time', prd.start_date
efield, edt = 'time', prd.end_date
qry = self.da_base.session.query(self.da_base.dbModel)
if ttr_id:
qry = qry.filter(self.da_base.dbModel.route_id == ttr_id)
if sdt:
qry = qry.filter(self.da_base.dbModel.time <= edt)
if edt:
qry = qry.filter(self.da_base.dbModel.time >= sdt)
if weekdays:
qry = qry.filter(extract('dow', self.da_base.dbModel.time).in_(weekdays))
if start_time and end_time:
qry = qry.filter(cast(self.da_base.dbModel.time, Time) >= start_time).filter(
cast(self.da_base.dbModel.time, Time) <= end_time)
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(self.da_base.dbModel, order_by[0]), order_by[1])())
else:
qry = qry.order_by(asc(self.da_base.dbModel.time))
if limit:
qry = qry.limit(limit)
data_list = []
for model_data in qry:
if as_model:
data_list.append(model_data)
else:
data_list.append(self.da_base.to_info(model_data))
return data_list
def get_by_id(self, id):
"""
:type id: int
:rtype: TravelTimeInfo
"""
return self.da_base.get_data_by_id(id)
def delete_range(self, route_id, start_time, end_time, **kwargs):
"""
:type route_id: int
:type start_time: datetime.datetime
:type end_time: datetime.datetime
:rtype: bool
"""
print_exception = kwargs.get('print_exception', False)
try:
dbModel = self.da_base.dbModel
qry = (self.da_base.session.query(dbModel)
.filter(dbModel.route_id == route_id)
.filter(dbModel.time >= start_time)
.filter(dbModel.time <= end_time))
qry.delete(synchronize_session=False)
return True
except Exception as ex:
if print_exception:
tb.traceback(ex)
return False
def delete_all_for_a_route(self, ttr_id, **kwargs):
"""
:type ttr_id: int
"""
print_exception = kwargs.get('print_exception', False)
try:
dbModel = self.da_base.dbModel
stmt = dbModel.__table__.delete(synchronize_session=False).where(dbModel.route_id == ttr_id)
self.execute(stmt)
return True
except Exception as ex:
if print_exception:
tb.traceback(ex)
return False
def insert(self, tti, **kwargs):
"""
:type tti: TravelTimeInfo
:rtype: model.TravelTime
"""
return self.da_base.insert(tti, **kwargs)
def search_date_range(self, sdt, edt):
""" search data overlapped with the given data range
:type sdt: datetime.datetime
:type sdt: datetime.datetime
:rtype: list[pyticas_tetres.ttypes.TravelTimeInfo]
"""
return self.da_base.search_date_range(('time', sdt), ('time', edt))
def get_count(self, route_id, sdt, edt):
"""
:type route_id: int
:type sdt: datetime.datetime
:type sdt: datetime.datetime
:rtype: int
"""
dbModel = self.get_model()
qry = self.get_session().query(func.count(dbModel.id))
qry = qry.filter(dbModel.route_id == route_id)
qry = qry.filter(dbModel.time >= sdt)
qry = qry.filter(dbModel.time <= edt)
cnt = qry.scalar()
return cnt
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 IncidentDataAccess(DataAccess):
def __init__(self, **kwargs):
super().__init__(**kwargs)
self.da_base = DataAccessBase(model.Incident, IncidentInfo, **kwargs)
def list(self, sdate, edate, corridor, direction, **kwargs):
"""
:param sdate: e.g. 2013-12-04 12:00:00
:type sdate: str or datetime.datetime
:param edate: e.g. 2013-12-04 13:00:00
:type edate: str or datetime.datetime
:param corridor: only number part of corridor name e.g. 35W, 94, 494, 100, 169
:type corridor: str
:param direction: e.g. NB, SB, EB, WB
:type direction: str
:rtype: list[IncidentInfo]
"""
if isinstance(sdate, str):
sdate = datetime.datetime.strptime(sdate, '%Y-%m-%d %H:%M:%S')
if isinstance(edate, str):
edate = datetime.datetime.strptime(edate, '%Y-%m-%d %H:%M:%S')
sdate = sdate.replace(second=0, microsecond=0)
edate = edate.replace(second=59, microsecond=59)
as_model = kwargs.get('as_model', False)
eventtype = kwargs.get('eventtype', None)
limit = kwargs.get('limit', None)
order_by = kwargs.get('order_by', None)
dbModel = model.Incident
session = self.da_base.session
qry = (session.query(dbModel).filter(
or_(and_(dbModel.cdts >= sdate, dbModel.cdts <= edate),
and_(dbModel.udts >= sdate, dbModel.udts <= edate),
and_(dbModel.xdts >= sdate, dbModel.xdts <= edate))).filter(
or_(
and_(dbModel.efeanme == corridor,
dbModel.edirpre == direction),
dbModel.ecompl.like('%s %s%%' %
(direction, corridor)))))
# add 'eventtype' filter
if eventtype:
qry = qry.filter(dbModel._incident_type.eventtype == eventtype)
# 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])())
else:
qry = qry.order_by(dbModel.cdts.asc())
# apply 'limit'
if limit:
qry = qry.limit(limit)
qry = qry.all()
data_list = []
for m in qry:
if as_model:
data_list.append(m)
else:
data_list.append(self.da_base.to_info(m))
return data_list
def get_by_id(self, pkey):
"""
:type pkey: int
:rtype: IncidentInfo
"""
return self.da_base.get_data_by_id(pkey)
def delete_range(self, corridor_route, corridor_dir, start_time, end_time,
**kwargs):
"""
:type corridor_route: str
:type corridor_dir: str
:type start_time: datetime.datetime
:type end_time: datetime.datetime
:rtype: bool
"""
print_exception = kwargs.get('print_exception', False)
dbModel = self.da_base.dbModel
qry = self.da_base.session.query(dbModel)
try:
qry = qry.filter(dbModel.road == corridor_route)
qry = qry.filter(dbModel.direction == corridor_dir)
qry = qry.filter(
or_(and_(dbModel.cdts >= start_time, dbModel.cdts <= end_time),
and_(dbModel.udts >= start_time, dbModel.udts <= end_time),
and_(dbModel.xdts >= start_time,
dbModel.xdts <= end_time)))
qry = qry.delete(synchronize_session=False)
return True
except Exception as ex:
if print_exception:
tb.traceback(ex)
return False
def delete_range_all(self, start_time, end_time, **kwargs):
"""
:type corridor_route: str
:type corridor_dir: str
:type start_time: datetime.datetime
:type end_time: datetime.datetime
:rtype: bool
"""
print_exception = kwargs.get('print_exception', False)
dbModel = self.da_base.dbModel
qry = self.da_base.session.query(dbModel)
qry = qry.filter(
or_(and_(dbModel.cdts >= start_time, dbModel.cdts <= end_time),
and_(dbModel.udts >= start_time, dbModel.udts <= end_time),
and_(dbModel.xdts >= start_time, dbModel.xdts <= end_time)))
try:
qry.delete(synchronize_session=False)
return True
except Exception as ex:
if print_exception:
tb.traceback(ex)
return False
def insert(self, r, **kwargs):
"""
:type r: IncidentInfo
:rtype: model.Incident
"""
return self.da_base.insert(r, **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 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 SpecialEventDataAccess(DataAccess):
def __init__(self, **kwargs):
super().__init__(**kwargs)
self.da_base = DataAccessBase(model.Specialevent, SpecialEventInfo,
**kwargs)
def exist(self, name, start_time, end_time):
"""
:type name: str
:type start_time: str
:type end_time: str
:rtype: pyticas_tetres.db.model.SpecialEvent
"""
if isinstance(start_time, str):
start_time = datetime.datetime.strptime(start_time,
'%Y-%m-%d %H:%M:%S')
if isinstance(end_time, str):
end_time = datetime.datetime.strptime(end_time,
'%Y-%m-%d %H:%M:%S')
exs = self.da_base.search([('name', name), ('start_time', start_time),
('end_time', end_time)],
op='and',
cond='match')
return exs
def list(self):
"""
:rtype: list[SpecialEventInfo]
"""
return self.da_base.list()
def list_by_year(self, years):
"""
:type years: list[int]
:rtype: list[SpecialEventInfo]
"""
wheres = [('years', y) for y in years]
return self.da_base.search(wheres, op='or', cond='like')
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.SpecialEventInfo]
"""
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
def years(self):
"""
:rtype: list[int]:
"""
ys = []
for sei in self.da_base.list():
for y in sei.years.split(','):
iy = int(y)
if iy not in ys:
ys.append(iy)
return sorted(ys)
def get_by_id(self, se_id):
"""
:type se_id: int
:rtype: SpecialEventInfo
"""
return self.da_base.get_data_by_id(se_id)
def insert(self, sei, **kwargs):
"""
:type sei: SpecialEventInfo
"""
return self.da_base.insert(sei, **kwargs)