def insert_client_violent_warnings(db_config, target_db):
chunk_size = db_config["chunk_size"]
offset = 0
chunk_no = 1
while True:
try:
sirius_details, warnings_df = get_basic_data_table(
db_config=db_config,
mapping_file_name=mapping_file_name,
table_definition=definition,
chunk_details={"chunk_size": chunk_size, "offset": offset},
)
target_db.insert_data(
table_name=definition["destination_table_name"],
df=warnings_df,
sirius_details=sirius_details,
chunk_no=chunk_no,
)
offset += chunk_size
chunk_no += 1
except EmptyDataFrame as e:
log.debug(e)
break
except Exception as e:
log.error(f"Unexpected error: {e}")
os._exit(1)
def insert_notes(db_config, target_db):
chunk_size = db_config["chunk_size"]
offset = 0
chunk_no = 1
while True:
try:
sirius_details, notes_df = get_basic_data_table(
db_config=db_config,
mapping_file_name=mapping_file_name,
table_definition=definition,
chunk_details={
"chunk_size": chunk_size,
"offset": offset
},
)
if len(notes_df) > 0:
target_db.insert_data(
table_name=definition["destination_table_name"],
df=notes_df,
sirius_details=sirius_details,
chunk_no=chunk_no,
)
offset += chunk_size
chunk_no += 1
except Exception:
break
def insert_phonenumbers_deputies_daytime(db_config, target_db):
chunk_size = db_config["chunk_size"]
offset = 0
chunk_no = 1
persons_query = f"""
select "id", "c_deputy_no" from {db_config["target_schema"]}.persons
where "type" = 'actor_deputy';
"""
persons_df = pd.read_sql_query(persons_query,
db_config["db_connection_string"])
persons_df = persons_df[["id", "c_deputy_no"]]
while True:
try:
sirius_details, phonenos_df = get_basic_data_table(
db_config=db_config,
mapping_file_name=mapping_file_name,
table_definition=definition,
chunk_details={
"chunk_size": chunk_size,
"offset": offset
},
)
phonenos_joined_df = phonenos_df.merge(persons_df,
how="left",
left_on="c_deputy_no",
right_on="c_deputy_no")
phonenos_joined_df["person_id"] = phonenos_joined_df["id_y"]
phonenos_joined_df = phonenos_joined_df.drop(columns=["id_y"])
phonenos_joined_df = phonenos_joined_df.rename(
columns={"id_x": "id"})
phonenos_joined_df["person_id"] = (
phonenos_joined_df["person_id"].fillna(0).astype(int).astype(
object).where(phonenos_joined_df["person_id"].notnull()))
phonenos_joined_df = reapply_datatypes_to_fk_cols(
columns=["person_id"], df=phonenos_joined_df)
target_db.insert_data(
table_name=definition["destination_table_name"],
df=phonenos_joined_df,
sirius_details=sirius_details,
chunk_no=chunk_no,
)
offset += chunk_size
chunk_no += 1
except Exception:
break
def insert_bonds(target_db, db_config):
chunk_size = db_config["chunk_size"]
offset = 0
chunk_no = 1
existing_cases_query = f"""
SELECT c_cop_case, c_bond_no, id from {db_config['target_schema']}.cases;
"""
existing_cases_df = pd.read_sql_query(
existing_cases_query, db_config["db_connection_string"]
)
existing_cases_df = existing_cases_df.loc[existing_cases_df["c_bond_no"].notnull()]
while True:
try:
sirius_details, bonds_df = get_basic_data_table(
db_config=db_config,
mapping_file_name=mapping_file_name,
table_definition=definition,
chunk_details={"chunk_size": chunk_size, "offset": offset},
)
bonds_cases_joined_df = bonds_df.merge(
existing_cases_df,
how="left",
left_on="c_cop_case",
right_on="c_cop_case",
)
bonds_cases_joined_df = bonds_cases_joined_df.rename(
columns={"id_x": "id", "id_y": "order_id"}
)
bonds_cases_joined_df = reapply_datatypes_to_fk_cols(
columns=["order_id"], df=bonds_cases_joined_df
)
bonds_cases_joined_df = bonds_cases_joined_df.loc[
bonds_cases_joined_df["bondreferencenumber"] != ""
]
target_db.insert_data(
table_name=definition["destination_table_name"],
df=bonds_cases_joined_df,
sirius_details=sirius_details,
chunk_no=chunk_no,
)
offset += chunk_size
chunk_no += 1
except Exception:
break
def insert_deputy_death_notifications(db_config, target_db):
chunk_size = db_config["chunk_size"]
offset = 0
chunk_no = 1
persons_query = (
f'select "id", "c_deputy_no" from {db_config["target_schema"]}.persons '
f"where \"type\" = 'actor_deputy';")
persons_df = pd.read_sql_query(persons_query,
db_config["db_connection_string"])
persons_df = persons_df[["id", "c_deputy_no"]]
while True:
try:
sirius_details, deputy_death_df = get_basic_data_table(
db_config=db_config,
mapping_file_name=mapping_file_name,
table_definition=definition,
chunk_details={
"chunk_size": chunk_size,
"offset": offset
},
)
death_joined_df = deputy_death_df.merge(persons_df,
how="left",
left_on="c_deputy_no",
right_on="c_deputy_no")
death_joined_df["person_id"] = death_joined_df["id_y"]
death_joined_df = death_joined_df.drop(columns=["id_y"])
death_joined_df = death_joined_df.rename(columns={"id_x": "id"})
death_joined_df = reapply_datatypes_to_fk_cols(
columns=["person_id"], df=death_joined_df)
target_db.insert_data(
table_name=definition["destination_table_name"],
df=death_joined_df,
sirius_details=sirius_details,
chunk_no=chunk_no,
)
offset += chunk_size
chunk_no += 1
except Exception:
log.debug(f"End of insert_deputy_death_notifications")
break
def insert_addresses_clients(db_config, target_db):
chunk_size = db_config["chunk_size"]
offset = 0
chunk_no = 1
persons_query = (
f'select "id", "caserecnumber" from {db_config["target_schema"]}.persons '
f"where \"type\" = 'actor_client';")
persons_df = pd.read_sql_query(persons_query,
db_config["db_connection_string"])
persons_df = persons_df[["id", "caserecnumber"]]
while True:
try:
sirius_details, addresses_df = get_basic_data_table(
db_config=db_config,
mapping_file_name=mapping_file_name,
table_definition=definition,
chunk_details={
"chunk_size": chunk_size,
"offset": offset
},
)
addresses_joined_df = addresses_df.merge(persons_df,
how="left",
left_on="c_case",
right_on="caserecnumber")
addresses_joined_df["person_id"] = addresses_joined_df["id_y"]
addresses_joined_df = addresses_joined_df.drop(columns=["id_y"])
addresses_joined_df = addresses_joined_df.rename(
columns={"id_x": "id"})
addresses_joined_df = reapply_datatypes_to_fk_cols(
columns=["person_id"], df=addresses_joined_df)
target_db.insert_data(
table_name=definition["destination_table_name"],
df=addresses_joined_df,
sirius_details=sirius_details,
chunk_no=chunk_no,
)
offset += chunk_size
chunk_no += 1
except Exception:
break
def insert_order_deputies(db_config, target_db):
# Get the standard data from casrec 'deputy' table
sirius_details, person_df = get_basic_data_table(
db_config=db_config,
mapping_file_name=mapping_file_name,
table_definition=definition,
)
# Get ids of deputies that have already been transformed
existing_deputies_query = f"""
select c_deputy_no, id from {db_config['target_schema']}.persons
where casrec_mapping_file_name = 'deputy_persons_mapping';
"""
existing_deputies_df = pd.read_sql_query(existing_deputies_query,
db_config["db_connection_string"])
# use the id of the existing deputy
existing_deputies_merged_df = person_df.merge(existing_deputies_df,
how="left",
left_on="c_deputy_no",
right_on="c_deputy_no")
existing_deputies_merged_df = existing_deputies_merged_df.rename(
columns={"id_y": "id"})
deputies_df = existing_deputies_merged_df.drop(columns=["id_x"])
# deputyship
deputyship_query = f"""select "Deputy No", "CoP Case" from {db_config["source_schema"]}.deputyship;"""
deputyship_df = pd.read_sql_query(deputyship_query,
db_config["db_connection_string"])
order_query = (
f"""select "id", "c_cop_case" from {db_config["target_schema"]}.cases;"""
)
order_df = pd.read_sql_query(order_query,
db_config["db_connection_string"])
deputyship_persons_joined_df = deputyship_df.merge(deputies_df,
how="left",
left_on="Deputy No",
right_on="c_deputy_no")
deputyship_persons_joined_df["deputy_id"] = deputyship_persons_joined_df[
"id"]
deputyship_persons_joined_df["deputy_id"] = deputyship_persons_joined_df[
"deputy_id"].astype("Int64")
deputyship_persons_joined_df = deputyship_persons_joined_df.drop(
columns=["id"])
deputyship_persons_order_df = deputyship_persons_joined_df.merge(
order_df, how="left", left_on="CoP Case", right_on="c_cop_case")
deputyship_persons_order_df["order_id"] = deputyship_persons_order_df["id"]
deputyship_persons_order_df = deputyship_persons_order_df.drop(
columns=["id", "Deputy No", "CoP Case"])
deputyship_persons_order_df = deputyship_persons_order_df[
deputyship_persons_order_df["casrec_mapping_file_name"].notna()]
deputyship_persons_order_df = add_unique_id(
db_conn_string=db_config["db_connection_string"],
db_schema=db_config["target_schema"],
table_definition=definition,
source_data_df=deputyship_persons_order_df,
)
deputyship_persons_order_df = reapply_datatypes_to_fk_cols(
columns=["order_id", "deputy_id"], df=deputyship_persons_order_df)
target_db.insert_data(
table_name=definition["destination_table_name"],
df=deputyship_persons_order_df,
sirius_details=sirius_details,
)
def insert_addresses_deputies(db_config, target_db):
deputyship_query = f"""
select "Dep Addr No", "Deputy No"
from {db_config['source_schema']}.deputyship
"""
deputyship_df = pd.read_sql_query(deputyship_query,
db_config["db_connection_string"])
deputyship_df = prep_df_for_merge(df=deputyship_df, column="Dep Addr No")
deputy_persons_query = f"""
select c_deputy_no, id as person_id
from {db_config['target_schema']}.persons
where casrec_mapping_file_name = 'deputy_persons_mapping'
"""
deputy_persons_df = pd.read_sql_query(deputy_persons_query,
db_config["db_connection_string"])
chunk_size = db_config["chunk_size"]
offset = 0
chunk_no = 1
while True:
try:
sirius_details, addresses_df = get_basic_data_table(
db_config=db_config,
mapping_file_name=mapping_file_name,
table_definition=definition,
chunk_details={
"chunk_size": chunk_size,
"offset": offset
},
)
addresses_df = prep_df_for_merge(df=addresses_df,
column="c_dep_addr_no")
address_deputyship_joined_df = addresses_df.merge(
deputyship_df,
how="left",
left_on="c_dep_addr_no",
right_on="Dep Addr No",
)
address_persons_joined_df = address_deputyship_joined_df.merge(
deputy_persons_df,
how="left",
left_on="Deputy No",
right_on="c_deputy_no",
)
address_persons_joined_df = address_persons_joined_df.drop(
columns=["Dep Addr No", "Deputy No"])
address_persons_joined_df["person_id"] = (
address_persons_joined_df["person_id"].fillna(0).astype(
int).astype(object).where(
address_persons_joined_df["person_id"].notnull()))
address_persons_joined_df = address_persons_joined_df.drop_duplicates(
)
address_persons_joined_df = address_persons_joined_df.drop(
columns=["id"])
address_persons_joined_df = process_unique_id.add_unique_id(
db_conn_string=db_config["db_connection_string"],
db_schema=db_config["target_schema"],
table_definition=definition,
source_data_df=address_persons_joined_df,
)
# some addresses don't seem to match up with people...
address_persons_joined_df = address_persons_joined_df[
address_persons_joined_df["person_id"].notna()]
address_persons_joined_df = reapply_datatypes_to_fk_cols(
columns=["person_id"], df=address_persons_joined_df)
target_db.insert_data(
table_name=definition["destination_table_name"],
df=address_persons_joined_df,
sirius_details=sirius_details,
chunk_no=chunk_no,
)
offset += chunk_size
chunk_no += 1
except Exception:
break