def get_last_n_saved_since(self, energy_device_id, since_ts, n=-1):
db_session = DbSession()
edmm = None
try:
if n == -1:
edmm = db_session.query(EnergyDeviceMeasureModel) \
.filter(EnergyDeviceMeasureModel.energy_device_id == energy_device_id) \
.filter(EnergyDeviceMeasureModel.created_at >= self.date_str_to_datetime(since_ts)) \
.order_by(desc(EnergyDeviceMeasureModel.created_at)) \
.all()
else:
edmm = db_session.query(EnergyDeviceMeasureModel) \
.filter(EnergyDeviceMeasureModel.energy_device_id == energy_device_id) \
.filter(EnergyDeviceMeasureModel.created_at >= self.date_str_to_datetime(since_ts)) \
.order_by(desc(EnergyDeviceMeasureModel.created_at)) \
.limit(n) \
.all()
except InvalidRequestError as e:
self.__cleanupDbSession(db_session, self.__class__.__name__)
except Exception as e:
# Nothing to roll back
self.__logger.error(
"Could not query from {} table in database".format(
self.__tablename__),
exc_info=True)
raise DbException(
"Could not query from {} table in database".format(
self.__tablename__))
return edmm
def get_usage_since(self, energy_device_id, since_ts):
self.__logger.debug(
"get_usage_since() energy_device_id {} since_ts {}".format(
energy_device_id, str(since_ts)))
db_session = DbSession()
energy_at_ts = 0
try:
energy_at_ts = db_session.query(EnergyDeviceMeasureModel) \
.filter(EnergyDeviceMeasureModel.energy_device_id == energy_device_id) \
.filter(EnergyDeviceMeasureModel.created_at <= since_ts) \
.order_by(desc(EnergyDeviceMeasureModel.created_at)) \
.first()
except InvalidRequestError as e:
self.__cleanupDbSession(db_session, self.__class__.__name__)
except Exception as e:
# Nothing to roll back
self.__logger.error(
"Could not query from {} table in database".format(
self.__tablename__),
exc_info=True)
raise DbException(
"Could not query from {} table in database".format(
self.__tablename__))
energy_now = self.get_last_saved(energy_device_id)
if energy_now is None or energy_at_ts is None:
self.__logger.warn(
'get_usage_since() - could not get data from database')
return 0
energy_used = round(
(energy_now.kw_total - energy_at_ts.kw_total) * 10) / 10
self.__logger.debug('get_usage_since() - since {} usage {}kWh'.format(
since_ts.strftime("%d/%m/%Y, %H:%M:%S"), energy_used))
return energy_used
def get_open_charge_session_for_device(
device) -> ChargeSessionModel | None:
db_session = DbSession()
open_charge_session_for_device = None
try:
# Build query to get id of latest chargesession for this device.
open_charge_session_for_device = db_session.query(ChargeSessionModel) \
.filter(ChargeSessionModel.energy_device_id == device) \
.filter(ChargeSessionModel.end_time == None) \
.order_by(desc(ChargeSessionModel.start_time)) \
.first() # Call first to return an object instead of an array
except InvalidRequestError as e:
ChargeSessionModel.__cleanupDbSession(db_session,
ChargeSessionModel.__class__)
except Exception as e:
# Nothing to roll back
ChargeSessionModel.logger.error(
"Could not query from {} table in database".format(
ChargeSessionModel.__tablename__),
exc_info=True)
raise DbException(
"Could not query from {} table in database".format(
ChargeSessionModel.__tablename__))
return open_charge_session_for_device
def get_specific_charge_session(energy_device_id, rfid, km, end_value,
tariff) -> ChargeSessionModel | None:
db_session = DbSession()
csm = None
try:
csm = db_session.query(ChargeSessionModel) \
.filter(ChargeSessionModel.energy_device_id == energy_device_id) \
.filter(ChargeSessionModel.rfid == rfid) \
.filter(ChargeSessionModel.km == km) \
.filter(ChargeSessionModel.end_value == end_value) \
.filter(ChargeSessionModel.end_time != None) \
.filter(ChargeSessionModel.tariff == tariff) \
.first()
except InvalidRequestError as e:
ChargeSessionModel.__cleanupDbSession(db_session,
ChargeSessionModel.__class__)
except:
# Nothing to roll back
ChargeSessionModel.logger.error(
"Could not query from {} table in database".format(
ChargeSessionModel.__tablename__),
exc_info=True)
raise DbException(
"Could not query from {} table in database".format(
ChargeSessionModel.__tablename__))
return csm
def get_latest_charge_session(device,
rfid=None) -> ChargeSessionModel | None:
db_session = DbSession()
# Build query to get id of latest chargesession for this device.
qry_latest_id = db_session.query(func.max(ChargeSessionModel.id)) \
.filter(ChargeSessionModel.energy_device_id == device)
# If rfid is specified, expand the query with a filter on rfid.
if rfid is not None:
qry_latest_id = qry_latest_id.filter(
ChargeSessionModel.rfid == str(rfid))
# Now query the ChargeSession that we're interested in (latest for device or latest for device AND rfid).
latest_charge_session = None
try:
latest_charge_session = db_session.query(ChargeSessionModel) \
.filter(ChargeSessionModel.id == qry_latest_id) \
.first()
except InvalidRequestError as e:
ChargeSessionModel.__cleanupDbSession(db_session,
ChargeSessionModel.__class__)
except Exception as e:
# Nothing to roll back
ChargeSessionModel.logger.error(
"Could not query from {} table in database".format(
ChargeSessionModel.__tablename__),
exc_info=True)
raise DbException(
"Could not query from {} table in database".format(
ChargeSessionModel.__tablename__))
return latest_charge_session
def get_time_of_kwh(energy_device_id, kw_total):
db_session = DbSession()
edmm = None
try:
edmm = db_session.query(EnergyDeviceMeasureModel) \
.filter(EnergyDeviceMeasureModel.energy_device_id == energy_device_id) \
.filter(EnergyDeviceMeasureModel.kw_total == kw_total) \
.filter(EnergyDeviceMeasureModel.a_l1 == 0) \
.filter(EnergyDeviceMeasureModel.a_l2 == 0) \
.filter(EnergyDeviceMeasureModel.a_l3 == 0) \
.order_by(EnergyDeviceMeasureModel.created_at.asc()) \
.first()
except InvalidRequestError as e:
EnergyDeviceMeasureModel.__cleanupDbSession(
db_session, self.__class__.__name__)
except Exception as e:
# Nothing to roll back
EnergyDeviceMeasureModel.__logger.error(
"Could not query from {} table in database".format(
EnergyDeviceMeasureModel.__tablename__),
exc_info=True)
raise DbException(
"Could not query from {} table in database".format(
EnergyDeviceMeasureModel.__tablename__))
return edmm.created_at if edmm is not None else None
def save(self):
self.logger.debug(".save()")
db_session = DbSession()
# Prevent expiration after the session is closed or object is made transient or disconnected
db_session.expire_on_commit = False
try:
# No need to 'add', committing this class
db_session.add(self)
db_session.commit()
# Keep it detached
make_transient(self)
make_transient_to_detached(self)
except InvalidRequestError as e:
self.logger.error(
".save() - Could not commit to {} table in database".format(
self.__tablename__),
exc_info=True)
self.__cleanupDbSession(db_session, self.__class__.__name__)
except Exception as e:
db_session.rollback()
self.logger.error(
".save() - Could not commit to {} table in database".format(
self.__tablename__),
exc_info=True)
raise DbException(
"Could not commit to {} table in database".format(
self.__tablename__))
def get_config():
db_session = DbSession()
# Prevent expiration after the session is closed or object is made transient or disconnected
db_session.expire_on_commit = False
ccm = None
try:
# Should be only one, return last modified
ccm = db_session.query(ChargerConfigModel) \
.order_by(desc(ChargerConfigModel.modified_at)) \
.first()
# Detach (not transient) from database, allows saving in other Threads
# https://docs.sqlalchemy.org/en/14/orm/session_api.html#sqlalchemy.orm.make_transient_to_detached
make_transient(ccm)
make_transient_to_detached(ccm)
except InvalidRequestError as e:
ChargerConfigModel.__cleanupDbSession(db_session,
ChargerConfigModel.__class__)
except Exception as e:
# Nothing to roll back
ChargerConfigModel.logger.error(
"Could not query from {} table in database".format(
ChargerConfigModel.__tablename__),
exc_info=True)
raise DbException(
"Could not query from {} table in database".format(
ChargerConfigModel.__tablename__))
return ccm
def save(self):
db_session = DbSession()
try:
db_session.add(self)
db_session.commit()
except InvalidRequestError as e:
self.__cleanupDbSession(db_session, self.__class__.__name__)
except Exception as e:
db_session.rollback()
self.logger.error("Could not save to {} table in database".format(
self.__tablename__),
exc_info=True)
raise DbException("Could not save to {} table in database".format(
self.__tablename__))
def get_count(self, q):
count = 0
try:
count_q = q.statement.with_only_columns([func.count()
]).order_by(None)
count = q.session.execute(count_q).scalar()
except Exception as e:
self.logger.error(
"Could not query from {} table in database".format(
self.__tablename__),
exc_info=True)
raise DbException(
"Could not query from {} table in database".format(
self.__tablename__))
return count
def get(username):
db_session = DbSession()
user = None
try:
# Should be only one, return last modified
user = db_session.query(User) \
.filter(User.username == username) \
.first()
except InvalidRequestError as e:
User.__cleanupDbSession(db_session, User.__class__)
except Exception as e:
# Nothing to roll back
User.__logger.error("Could not query {} table in database".format(
User.__tablename__),
exc_info=True)
raise DbException("Could not query {} table in database".format(
User.__tablename__))
return user
def deleteId(id):
db_session = DbSession()
try:
db_session.query(OffPeakHoursModel) \
.filter(OffPeakHoursModel.id == id) \
.delete()
db_session.commit()
except InvalidRequestError as e:
self.__cleanupDbSession(db_session, self.__class__.__name__)
except Exception as e:
db_session.rollback()
OffPeakHoursModel.logger.error(
"Could not delete {} from {} table in database".format(
id, OffPeakHoursModel.__tablename__),
exc_info=True)
raise DbException(
"Could not delete {} from {} table in database".format(
id, OffPeakHoursModel.__tablename__))
def delete_all():
db_session = DbSession()
try:
# Should be only one
num_rows_deleted = db_session.query(User) \
.delete()
db_session.commit()
except InvalidRequestError as e:
User.__cleanupDbSession(db_session, User.__class__.__name__)
except Exception as e:
db_session.rollback()
User.__logger.error(
"Could not commit to {} table in database".format(
User.__tablename__),
exc_info=True)
raise DbException(
"Could not commit to {} table in database".format(
User.__tablename__))
def get_all_sessions() -> typing.List[ChargeSessionModel] | None:
db_session = DbSession()
csm = None
try:
csm = db_session.query(ChargeSessionModel) \
.all()
except InvalidRequestError as e:
ChargeSessionModel.__cleanupDbSession(db_session,
ChargeSessionModel.__class__)
except Exception as e:
# Nothing to roll back
ChargeSessionModel.logger.error(
"Could not query from {} table in database".format(
ChargeSessionModel.__tablename__),
exc_info=True)
raise DbException(
"Could not query from {} table in database".format(
ChargeSessionModel.__tablename__))
return csm
def get_all():
db_session = DbSession()
rfidm = None
try:
rfidm = db_session.query(RfidModel) \
.all()
except InvalidRequestError as e:
RfidModel.__cleanupDbSession(db_session,
RfidModel.__class__.__name__)
except Exception as e:
# Nothing to roll back
RfidModel.__logger.error(
"Could not query from {} table in database".format(
RfidModel.__tablename__),
exc_info=True)
raise DbException(
"Could not query from {} table in database".format(
RfidModel.__tablename__))
return rfidm
def update(self, data) -> None:
for key, item in data.items():
setattr(self, key, item)
self.modified_at = datetime.now()
db_session = DbSession()
try:
db_session.add(self)
db_session.commit()
except InvalidRequestError as e:
self.__cleanupDbSession(db_session, self.__class__.__name__)
except Exception as e:
db_session.rollback()
self.logger.error(
"Could not update to {} table in database".format(
self.__tablename__),
exc_info=True)
raise DbException(
"Could not update to {} table in database".format(
self.__tablename__))
def init_db():
logger = logging.getLogger('nl.oppleo.models.Base init_db()')
# import all modules here that might define models so that
# they will be registered properly on the metadata. Otherwise
# you will have to import them first before calling init_db()
import nl.oppleo.models.ChargerConfigModel
import nl.oppleo.models.ChargeSessionModel
import nl.oppleo.models.EnergyDeviceMeasureModel
import nl.oppleo.models.EnergyDeviceModel
import nl.oppleo.models.OffPeakHoursModel
import nl.oppleo.models.RfidModel
import nl.oppleo.models.User
try:
Base.metadata.create_all(bind=engine)
oppleoSystemConfig.dbAvailable = True
except:
logger.error('COULD NOT CONNECT TO DATABASE!!!')
print('COULD NOT CONNECT TO DATABASE!!!')
raise DbException('Database connection failed')
def delete(self):
db_session = DbSession()
db_session.expire_on_commit = True
try:
db_session.delete(self)
db_session.commit()
# Keep it detached
make_transient(self)
make_transient_to_detached(self)
except InvalidRequestError as e:
self.__cleanupDbSession(db_session, self.__class__.__name__)
except Exception as e:
db_session.rollback()
self.logger.error(
"Could not delete from {} table in database".format(
self.__tablename__),
exc_info=True)
raise DbException(
"Could not delete from {} table in database".format(
self.__tablename__))
def get():
db_session = DbSession()
edm = None
try:
edm = db_session.query(EnergyDeviceModel) \
.order_by(desc(EnergyDeviceModel.energy_device_id)) \
.first()
except InvalidRequestError as e:
EnergyDeviceModel.__cleanupDbSession(db_session,
EnergyDeviceModel.__class__)
except Exception as e:
# Nothing to roll back
EnergyDeviceModel.logger.error(
"Could not get energy device from table {} in database ({})".
format(EnergyDeviceModel.__tablename__, str(e)),
exc_info=True)
raise DbException(
"Could not get energy device from table {} in database ({})".
format(EnergyDeviceModel.__tablename__, str(e)))
return edm
def get_one_charge_session(id) -> ChargeSessionModel | None:
db_session = DbSession()
csm = None
try:
csm = db_session.query(ChargeSessionModel) \
.filter(ChargeSessionModel.id == id) \
.limit(1) \
.all()[0]
except InvalidRequestError as e:
ChargeSessionModel.__cleanupDbSession(db_session,
ChargeSessionModel.__class__)
except:
# Nothing to roll back
ChargeSessionModel.logger.error(
"Could not query from {} table in database".format(
ChargeSessionModel.__tablename__),
exc_info=True)
raise DbException(
"Could not query from {} table in database".format(
ChargeSessionModel.__tablename__))
return csm
def get_weekday(weekday):
db_session = DbSession()
r = None
try:
r = db_session.query(OffPeakHoursModel) \
.filter(OffPeakHoursModel.weekday == OffPeakHoursModel.weekdayToEnStr(weekday)) \
.order_by(OffPeakHoursModel.off_peak_start.asc()) \
.all()
except InvalidRequestError as e:
OffPeakHoursModel.__cleanupDbSession(
db_session, OffPeakHoursModel.__class__.__name__)
except Exception as e:
# Nothing to roll back
OffPeakHoursModel.logger.error(
"Could not query from {} table in database".format(
OffPeakHoursModel.__tablename__),
exc_info=True)
raise DbException(
"Could not query from {} table in database".format(
OffPeakHoursModel.__tablename__))
return r
def get_charge_session_count_for_rfid(rfid) -> int:
db_session = DbSession()
charge_session_count = 0
try:
# Build query to get id of latest chargesession for this device.
charge_session_count = db_session.query(ChargeSessionModel) \
.filter(ChargeSessionModel.rfid == rfid) \
.count() # Count the number of sessions
except InvalidRequestError as e:
ChargeSessionModel.__cleanupDbSession(db_session,
ChargeSessionModel.__class__)
except Exception as e:
# Nothing to roll back
ChargeSessionModel.logger.error(
"Could not query from {} table in database".format(
ChargeSessionModel.__tablename__),
exc_info=True)
raise DbException(
"Could not query from {} table in database".format(
ChargeSessionModel.__tablename__))
return charge_session_count
def get_history(
energy_device_id=None) -> typing.List[ChargeSessionModel] | None:
db_session = DbSession()
from sqlalchemy import func, extract #, distinct
csm = None
try:
if (energy_device_id == None):
csm = db_session.query( func.sum(ChargeSessionModel.total_energy).label('TotalEnergy'), \
func.sum(ChargeSessionModel.total_price).label('TotalPrice'), \
extract('year', ChargeSessionModel.end_time).label('Year'), \
extract('month', ChargeSessionModel.end_time).label('Month')) \
.group_by( extract('year', ChargeSessionModel.end_time), \
extract('month', ChargeSessionModel.end_time)) \
.all()
else: # filter energy_device_id
csm = db_session.query( func.sum(ChargeSessionModel.total_energy).label('TotalEnergy'), \
func.sum(ChargeSessionModel.total_price).label('TotalPrice'), \
extract('year', ChargeSessionModel.end_time).label('Year'), \
extract('month', ChargeSessionModel.end_time).label('Month')) \
.filter(ChargeSessionModel.energy_device_id == energy_device_id) \
.group_by( extract('year', ChargeSessionModel.end_time), \
extract('month', ChargeSessionModel.end_time)) \
.all()
except InvalidRequestError as e:
ChargeSessionModel.__cleanupDbSession(db_session,
ChargeSessionModel.__class__)
except Exception as e:
# Nothing to roll back
ChargeSessionModel.logger.error(
"Could not query from {} table in database".format(
ChargeSessionModel.__tablename__),
exc_info=True)
raise DbException(
"Could not query from {} table in database".format(
ChargeSessionModel.__tablename__))
return csm
def getOpenChargeSession(device=None) -> ChargeSessionModel | None:
db_session = DbSession()
if device is None:
return None
# Now query the ChargeSession that we're interested in (latest for device).
try:
latest = db_session.query(ChargeSessionModel) \
.filter(ChargeSessionModel.energy_device_id == device) \
.order_by(ChargeSessionModel.id.desc()) \
.first()
except InvalidRequestError as e:
ChargeSessionModel.logger.error(
"Could not query from {} table in database".format(
ChargeSessionModel.__tablename__),
exc_info=True)
ChargeSessionModel.__cleanupDbSession(db_session,
ChargeSessionModel.__class__)
return None
except Exception as e:
# Nothing to roll back
ChargeSessionModel.logger.error(
"Could not query from {} table in database".format(
ChargeSessionModel.__tablename__),
exc_info=True)
raise DbException(
"Could not query from {} table in database".format(
ChargeSessionModel.__tablename__))
if (latest is None or latest.end_time != None):
# No recent open charge session
return None
return latest
def get_last_n_sessions_since(
self,
energy_device_id=None,
since_ts=None,
n=-1) -> typing.List[ChargeSessionModel] | None:
db_session = DbSession()
csm = None
try:
if (n == -1):
if (since_ts == None):
if (energy_device_id == None):
csm = db_session.query(ChargeSessionModel) \
.order_by(desc(ChargeSessionModel.start_time)) \
.all()
else: # filter energy_device_id
csm = db_session.query(ChargeSessionModel) \
.filter(ChargeSessionModel.energy_device_id == energy_device_id) \
.order_by(desc(ChargeSessionModel.start_time)) \
.all()
else: # filter since_ts
if (energy_device_id == None):
csm = db_session.query(ChargeSessionModel) \
.filter(ChargeSessionModel.start_time >= self.date_str_to_datetime(since_ts)) \
.order_by(desc(ChargeSessionModel.start_time)) \
.all()
else: # filter energy_device_id
csm = db_session.query(ChargeSessionModel) \
.filter(ChargeSessionModel.energy_device_id == energy_device_id) \
.filter(ChargeSessionModel.start_time >= self.date_str_to_datetime(since_ts)) \
.order_by(desc(ChargeSessionModel.start_time)) \
.all()
else: # limit n
if (since_ts == None):
if (energy_device_id == None):
csm = db_session.query(ChargeSessionModel) \
.order_by(desc(ChargeSessionModel.start_time)) \
.limit(n) \
.all()
else: # filter energy_device_id
csm = db_session.query(ChargeSessionModel) \
.filter(ChargeSessionModel.energy_device_id == energy_device_id) \
.order_by(desc(ChargeSessionModel.start_time)) \
.limit(n) \
.all()
else: # filter since_ts
if (energy_device_id == None):
csm = db_session.query(ChargeSessionModel) \
.filter(ChargeSessionModel.start_time >= self.date_str_to_datetime(since_ts)) \
.order_by(desc(ChargeSessionModel.start_time)) \
.limit(n) \
.all()
else: # filter energy_device_id
csm = db_session.query(ChargeSessionModel) \
.filter(ChargeSessionModel.energy_device_id == energy_device_id) \
.filter(ChargeSessionModel.start_time >= self.date_str_to_datetime(since_ts)) \
.order_by(desc(ChargeSessionModel.start_time)) \
.limit(n) \
.all()
except InvalidRequestError as e:
self.__cleanupDbSession(db_session, self.__class__.__name__)
except Exception as e:
# Nothing to roll back
self.logger.error(
"Could not query from {} table in database".format(
self.__tablename__),
exc_info=True)
raise DbException(
"Could not query from {} table in database".format(
self.__tablename__))
return csm
def is_off_peak(self, timestamp) -> bool:
self.logger.debug('is_off_peak()')
if not isinstance(timestamp, datetime):
self.logger.debug(
'is_off_peak() - timestamp is not of type datetime')
return False
db_session = DbSession()
# Weekday?
r = None
try:
r = db_session.query(OffPeakHoursModel) \
.filter(OffPeakHoursModel.weekday == OffPeakHoursModel.weekdayToEnStr(timestamp.weekday())) \
.filter(OffPeakHoursModel.off_peak_start <= cast(timestamp, Time)) \
.filter(OffPeakHoursModel.off_peak_end >= cast(timestamp, Time))
except InvalidRequestError as e:
self.__cleanupDbSession(db_session, self.__class__.__name__)
except Exception as e:
# Nothing to roll back
self.logger.error(
"Could not query from {} table in database".format(
self.__tablename__),
exc_info=True)
raise DbException(
"Could not query from {} table in database".format(
self.__tablename__))
if r is not None and self.get_count(r) > 0:
self.logger.debug(
'is_off_peak(): DayOfWeek {} within off-peak'.format(
str(timestamp.strftime("%d/%m/%Y, %H:%M:%S"))))
return True
# Is this a public holiday?
r = None
try:
r = db_session.query(OffPeakHoursModel) \
.filter(
or_(
and_( # Specific holiday
OffPeakHoursModel.holiday_day == int(timestamp.date().day),
OffPeakHoursModel.holiday_month == int(timestamp.date().month),
OffPeakHoursModel.holiday_year == int(timestamp.date().year)
),
and_( # Recurring holiday
OffPeakHoursModel.holiday_day == int(timestamp.date().day),
OffPeakHoursModel.holiday_month == int(timestamp.date().month),
OffPeakHoursModel.recurring == True
)
)
) \
.filter(OffPeakHoursModel.off_peak_start <= cast(timestamp, Time)) \
.filter(OffPeakHoursModel.off_peak_end >= cast(timestamp, Time))
except InvalidRequestError as e:
self.__cleanupDbSession(db_session, self.__class__.__name__)
except Exception as e:
# Nothing to roll back
self.logger.error(
"Could not query from {} table in database".format(
self.__tablename__),
exc_info=True)
raise DbException(
"Could not query from {} table in database".format(
self.__tablename__))
if r is not None and self.get_count(r) > 0:
self.logger.debug(
'is_off_peak(): Holiday {} within off-peak'.format(
str(timestamp.strftime("%d/%m/%Y, %H:%M:%S"))))
return True
self.logger.debug('is_off_peak(): {} not within off-peak'.format(
str(timestamp.strftime("%d/%m/%Y, %H:%M:%S"))))
return False
