def create_partition(area_type, release):
area_type = area_type.lower()
partition_id = get_partition_id(area_type, release)
if area_type in ["nhstrust", "utla", "ltla", "msoa"]:
area_partition = f"{release:%Y_%-m_%-d}_{area_type}"
else:
area_partition = f"{release:%Y_%-m_%-d}_other"
# session = Session(autocommit=True)
session = Session()
try:
# session.begin()
session.execute(
f"""
CREATE TABLE IF NOT EXISTS covid19.time_series_p{area_partition}
PARTITION OF covid19.time_series ( partition_id )
FOR VALUES IN ('{partition_id}');
"""
)
session.flush()
except ProgrammingError as e:
session.rollback()
except Exception as err:
session.rollback()
raise err
finally:
session.close()
return partition_id
def get_release(timestamp):
insert_stmt = (
insert(ReleaseReference.__table__)
.values(timestamp=timestamp)
)
stmt = (
insert_stmt
.on_conflict_do_update(
index_elements=[ReleaseReference.timestamp],
set_={ReleaseReference.timestamp.name: insert_stmt.excluded.timestamp}
)
.returning(ReleaseReference.id)
)
# session = Session(autocommit=True)
session = Session()
try:
response = session.execute(stmt)
result = response.fetchone()[0]
except Exception as err:
session.rollback()
raise err
finally:
session.close()
return result
def get_email_recipients():
session = Session()
query = session.execute(
select([ReportRecipient.recipient]).where(
and_(ReportRecipient.deactivated == False,
not_(ReportRecipient.approved_by == None))))
return [item[0] for item in query]
def create_partition(area_type: str, release: datetime):
"""
Creates new database partition - if one doesn't already exist - for
the `time_series` table based on `area_type` and `release` datestamp.
Parameters
----------
area_type : str
Area type, as defined in the `area_reference` table.
release: datetime
Release timestamp of the data.
Returns
-------
NoReturn
"""
partition_id = get_partition_id(area_type, release)
if area_type in ["nhsTrust", "utla", "ltla", "msoa"]:
area_partition = f"{release:%Y_%-m_%-d}_{area_type.lower()}"
else:
area_partition = f"{release:%Y_%-m_%-d}_other"
session = Session()
try:
session.execute(
f"""
CREATE TABLE IF NOT EXISTS covid19.time_series_p{area_partition}
PARTITION OF covid19.time_series ( partition_id )
FOR VALUES IN ('{partition_id}');
"""
)
session.flush()
except ProgrammingError as e:
session.rollback()
except Exception as err:
session.rollback()
raise err
finally:
session.close()
def get_release_id(datestamp: datetime, process_name: str) -> Tuple[int, datetime]:
query = (
select([
ReleaseReference.id,
ReleaseReference.timestamp
])
.select_from(
join(
ReleaseReference, ReleaseCategory,
ReleaseReference.id == ReleaseCategory.release_id
)
)
.where(
and_(
func.DATE(ReleaseReference.timestamp) == func.DATE(datestamp.isoformat()),
ReleaseCategory.process_name == process_name
)
)
)
session = Session()
try:
response = session.execute(query)
result = response.fetchone()
if result is not None:
return result
except Exception as err:
session.rollback()
raise err
finally:
session.close()
try:
release = ReleaseReference(timestamp=datestamp)
session.begin()
session.add(release)
session.commit()
category = ReleaseCategory(release_id=release.id, process_name=process_name)
session.begin()
session.add(category)
session.commit()
except Exception as err:
session.rollback()
raise err
finally:
session.close()
return get_release_id(datestamp, process_name)
def register_file(filepath: str,
timestamp: datetime,
instance_id: str,
release_id=None) -> True:
parsed_filepath = parse_filepath(filepath)
processed_file = ProcessedFile(file_path=filepath,
type=category_label(parsed_filepath),
timestamp=timestamp,
release_id=release_id,
process_id=instance_id)
session = Session()
try:
session.add(processed_file)
session.flush()
except IntegrityError as err:
session.rollback()
query = session.execute(
select([
ProcessedFile.id,
]).where(
and_(ProcessedFile.file_path == filepath,
ProcessedFile.process_id == instance_id)))
result = query.fetchone()
if result is not None:
return True
logging.info("Record already exists.")
raise err
except Exception as err:
session.rollback()
raise err
finally:
session.close()
return True
def get_metric_id(metric: str):
stmt = (
select([
MetricReference.id
])
.where(
MetricReference.metric == metric
)
)
session = Session()
try:
result = session.execute(stmt)
return result.fetchone()[0]
except Exception as err:
session.rollback()
raise err
finally:
session.close()
def get_release_id(datestamp: datetime, process_name: str) -> Tuple[int, datetime]:
"""
Generates or retrieves the `release_id` for the process.
Parameters
----------
datestamp : datetime
Datestamp for the data.
process_name : str
Name of the process - must match the ENUM defined in the database.
Returns
-------
Tuple[int, datetime]
Tuple of `release_id` and the timestamp associated with the release.
"""
query = (
select([
ReleaseReference.id,
ReleaseReference.timestamp
])
.select_from(
join(
ReleaseReference, ReleaseCategory,
ReleaseReference.id == ReleaseCategory.release_id
)
)
.where(
and_(
func.DATE(ReleaseReference.timestamp) == datestamp.date(),
ReleaseCategory.process_name == process_name
)
)
)
session = Session()
try:
response = session.execute(query)
result = response.fetchone()
if result is not None:
return result
except Exception as err:
session.rollback()
raise err
finally:
session.close()
session = Session(autocommit=True)
try:
release = ReleaseReference(timestamp=datestamp)
session.add(release)
session.flush()
category = ReleaseCategory(
release_id=release.id,
process_name=process_name
)
session.add(category)
session.flush()
except Exception as err:
session.rollback()
raise err
finally:
session.close()
return get_release_id(datestamp, process_name)