insert_criminal_records.py

import sqlite3
import xml.etree.ElementTree as ET
import os
from update_db import get_primary_sql_key

def insert_criminal_records(DB_name):
    print("Inserting the criminal register information")
    conn = sqlite3.connect(DB_name)
    c = conn.cursor()
    file_address = os.path.join(str(os.getcwd()), "data", "criminal_records.xml")
    tree = ET.parse(file_address)
    root = tree.getroot()
    for child in root:
        process_individual_extracts(child, c)
    conn.commit()
    conn.close()
    return
    
def process_individual_extracts(extract, c):
    ICO = get_ICO(extract)
    if ICO == -1:
        return
    else:
        court_records = find_court_records(extract)
        relevant_paragraphs = find_relevant_paragraphs(extract)
        penalties = find_penalties(extract)
        primary_sql_key = get_primary_sql_key(c, ICO)
        if primary_sql_key != 0:
            insert_crimnal_data_to_DB(c, primary_sql_key, court_records, relevant_paragraphs, penalties)
    return

def get_ICO(extract):
    person_entry = extract[0][0][2]
    temp_ico = person_entry.text
    if temp_ico.isnumeric() == True:
        return int(temp_ico)
    else:
        return -1

def find_court_records(extract):
    # TODO --- Make sure that the records are extracted correctly based on the tags
    court_records = []
    court_records_rider = extract[1][0][0]
    for child in court_records_rider:
        if "spisZnacka" in child.tag:
            court_records.append(child.text)
        if "organizace" in child.tag:
            court_records.append(child.text)
        if "odvolaci" in child.tag:
            for sub_child in child:
                if "spisZnacka" in sub_child.tag:
                    court_records.append(sub_child.text)
                if "organizace" in sub_child.tag:
                    court_records.append(sub_child.text)
    return court_records

def find_relevant_paragraphs(extract):
    paragraphs = []
    paragraphs_rider = extract[1][0][1]
    for individual_paragraph in paragraphs_rider:
        paragraphs.append(extract_paragraph_info(individual_paragraph))
    return paragraphs

def extract_paragraph_info(individual_paragraph):
    text_description = "§"
    for child in individual_paragraph[0]:
        if "Cislo" in child.tag:
            text_description += child.text
        if "Pismeno" in child.tag:
            text_description += ", odst. "
            text_description += child.text
        if "zakon" in child.tag:
            temp_law_description = child.text
            # temp_law_description[0].tolower()
            text_description += ", "
            text_description += temp_law_description
    return text_description

def find_penalties(extract):
    paragraphs_rider = extract[1][0]
    for child in paragraphs_rider:
        if "tresty" in child.tag:
            return extract_penalties_info(child)

def extract_penalties_info(extract):
    penalties = []
    for child in extract:
        if "druh" in child[0].tag:
            penalties.append(child[0].text)
    return penalties

def insert_crimnal_data_to_DB(c, primary_sql_key, court_records, relevant_paragraphs, penalties):
    first_instance = court_records[1] + ", sp. zn. " + court_records[0]
    if len(court_records) > 2:
        second_instance = court_records[3] + ", sp. zn. " + court_records[2]
    else:
        second_instance = None
    text_paragraphs = ""
    if relevant_paragraphs != None:
        for elem in relevant_paragraphs:
            text_paragraphs += elem
            text_paragraphs += ", "
    text_penalties = ""
    if penalties != None:
        for elem in penalties:
            text_penalties += elem
            text_penalties += ", "
    c.execute("INSERT INTO criminal_records (company_id, first_instance, second_instance, paragraphs, penalties) VALUES (?, ?, ?, ?, ?)", (primary_sql_key, first_instance, second_instance, text_paragraphs[:-2], text_penalties[:-2],))
    return 0