<reponame>Keerthisaraa/Norman-PD-Impact-Database<filename>project0/project0.py
import sqlite3
import tempfile
import urllib
import urllib.request

import PyPDF2


# fetching incident data from the Norman, Oklahoma police department
def fetchincidents(url):
    headers = {}
    headers[
        "User-Agent"
    ] = "Mozilla/5.0 (X11; Linux i686) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.27 Safari/537.17"

    data = urllib.request.urlopen(urllib.request.Request(url, headers=headers)).read()
    return data


# Getting the pages from pdf
def getpages(pdfReader, no_of_pages):
    cleaned_pages = []
    for pg_no in range(no_of_pages):
        page = pdfReader.getPage(pg_no)
        pcontent = page.extractText()
        cleaned_pcontent = pcontent.replace(" \n", " ")
        cleaned_pages.append(cleaned_pcontent)

    return cleaned_pages


# extracting individual columns from the incidents
def extract_page_incident(incident_list):
    extract_incident_dt = []
    extract_incident_no = []
    extract_incident_loc = []
    extract_incident_nature = []
    extract_incident_ori = []
    for idx, val in enumerate(incident_list):
        if idx % 5 == 0:
            extract_incident_dt.append(val)
        if idx % 5 == 1:
            extract_incident_no.append(val)
        if idx % 5 == 2:
            extract_incident_loc.append(val)
        if idx % 5 == 3:
            extract_incident_nature.append(val)
        if idx % 5 == 4:
            extract_incident_ori.append(val)
    return (
        extract_incident_dt,
        extract_incident_no,
        extract_incident_loc,
        extract_incident_nature,
        extract_incident_ori,
    )


# defined this exception to find if all the columns values have the same count
class ListLengthMismatch(Exception):
    pass


def extract_doc_incident(cleaned_pages):
    # defining the incident columns as a list
    doc_incident_dt = []
    doc_incident_no = []
    doc_incident_loc = []
    doc_incident_nature = []
    doc_incident_ori = []
    len_page = len(cleaned_pages)
    for idx, cpage in enumerate(cleaned_pages):
        # getting the incident column values
        incident_list = cpage.split("\n")
        # excluding the empty spaces in the data
        incident_list = [value for value in incident_list if value != ""]
        if idx == 0:
            # excluding the heading and column names from the page. time-stamp from the last page
            incident_list = incident_list[5 : (len(incident_list) - 2)]
        (
            incident_dt,
            incident_no,
            incident_loc,
            incident_nature,
            incident_ori,
        ) = extract_page_incident(incident_list)

        if idx == len_page - 1:
            incident_dt = incident_dt[: (len(incident_dt) - 1)]
        # adding the list of extracted columns to the current list (column by column)
        doc_incident_dt.extend(incident_dt)
        doc_incident_no.extend(incident_no)
        doc_incident_loc.extend(incident_loc)
        doc_incident_nature.extend(incident_nature)
        doc_incident_ori.extend(incident_ori)
    # matching the length of the columns
    all_length_match = (
        len(doc_incident_dt)
        == len(doc_incident_no)
        == len(doc_incident_loc)
        == len(doc_incident_nature)
        == len(doc_incident_ori)
    )
    # if the length of the columns are not matched it will raise an listlengthmismatch exception
    if not all_length_match:
        print(
            len(doc_incident_dt),
            len(doc_incident_no),
            len(doc_incident_loc),
            len(doc_incident_nature),
            len(doc_incident_ori),
        )
        raise ListLengthMismatch

    return [
        doc_incident_dt,
        doc_incident_no,
        doc_incident_loc,
        doc_incident_nature,
        doc_incident_ori,
    ]


# extract incidents
def extractincidents(incident_data):
    fp = tempfile.TemporaryFile()

    # Write the pdf data to a temp file
    fp.write(incident_data)

    # Set the curser of the file back to the begining
    fp.seek(0)

    # Read the PDF
    pdfReader = PyPDF2.pdf.PdfFileReader(fp)

    # Read the PDF
    pdfReader = PyPDF2.pdf.PdfFileReader(fp)
    no_of_pages = pdfReader.getNumPages()
    cleaned_pages = getpages(pdfReader, no_of_pages)
    return extract_doc_incident(cleaned_pages)


# creating the database
def createdb(drop_if_exits):
    conn = sqlite3.connect("Norman_police.db")
    cur = conn.cursor()
    # dropping the table if already exists
    if drop_if_exits:
        cur.execute("""DROP TABLE IF EXISTS incidents""")
        conn.commit()
    # creating a table named incidents
    cur.execute(
        """
    CREATE TABLE incidents (
        incident_time TEXT,
        incident_number TEXT,
        incident_location TEXT,
        incident_nature TEXT,
        incident_ori TEXT)
    """
    )
    conn.commit()
    return conn


# populating the incident table with values
def populatedb(conn, incidents):
    inc_dt, inc_no, inc_loc, inc_nat, inc_ori = incidents
    cur = conn.cursor()

    for i in range(len(inc_dt)):
        cur.execute(
            """ 
        INSERT INTO incidents (
            incident_time,
            incident_number,
            incident_location,
            incident_nature,
            incident_ori) values 
            (?,?,?,?,?)
        """,
            (
                inc_dt[i],
                inc_no[i],
                inc_loc[i],
                inc_nat[i],
                inc_ori[i],
            ),
        )
    conn.commit()
    return conn


# Printing the incident_nature values with its number of occurrences in alphabetical order
def status(conn):
    cur = conn.cursor()
    cur.execute(
        """
        select incident_nature, count(incident_nature) as count from incidents group by incident_nature order by incident_nature;"""
    )
    result = cur.fetchall()
    output = result
    return "\n".join([itype + " | " + str(nocc) for itype, nocc in output])

import sqlite3
import tempfile
import urllib
import urllib.request

import PyPDF2


# fetching incident data from the Norman, Oklahoma police department
def fetchincidents(url):
    headers = {}
    headers[
        "User-Agent"
    ] = "Mozilla/5.0 (X11; Linux i686) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.27 Safari/537.17"

    data = urllib.request.urlopen(urllib.request.Request(url, headers=headers)).read()
    return data


# Getting the pages from pdf
def getpages(pdfReader, no_of_pages):
    cleaned_pages = []
    for pg_no in range(no_of_pages):
        page = pdfReader.getPage(pg_no)
        pcontent = page.extractText()
        cleaned_pcontent = pcontent.replace(" \n", " ")
        cleaned_pages.append(cleaned_pcontent)

    return cleaned_pages


# extracting individual columns from the incidents
def extract_page_incident(incident_list):
    extract_incident_dt = []
    extract_incident_no = []
    extract_incident_loc = []
    extract_incident_nature = []
    extract_incident_ori = []
    for idx, val in enumerate(incident_list):
        if idx % 5 == 0:
            extract_incident_dt.append(val)
        if idx % 5 == 1:
            extract_incident_no.append(val)
        if idx % 5 == 2:
            extract_incident_loc.append(val)
        if idx % 5 == 3:
            extract_incident_nature.append(val)
        if idx % 5 == 4:
            extract_incident_ori.append(val)
    return (
        extract_incident_dt,
        extract_incident_no,
        extract_incident_loc,
        extract_incident_nature,
        extract_incident_ori,
    )


# defined this exception to find if all the columns values have the same count
class ListLengthMismatch(Exception):
    pass


def extract_doc_incident(cleaned_pages):
    # defining the incident columns as a list
    doc_incident_dt = []
    doc_incident_no = []
    doc_incident_loc = []
    doc_incident_nature = []
    doc_incident_ori = []
    len_page = len(cleaned_pages)
    for idx, cpage in enumerate(cleaned_pages):
        # getting the incident column values
        incident_list = cpage.split("\n")
        # excluding the empty spaces in the data
        incident_list = [value for value in incident_list if value != ""]
        if idx == 0:
            # excluding the heading and column names from the page. time-stamp from the last page
            incident_list = incident_list[5 : (len(incident_list) - 2)]
        (
            incident_dt,
            incident_no,
            incident_loc,
            incident_nature,
            incident_ori,
        ) = extract_page_incident(incident_list)

        if idx == len_page - 1:
            incident_dt = incident_dt[: (len(incident_dt) - 1)]
        # adding the list of extracted columns to the current list (column by column)
        doc_incident_dt.extend(incident_dt)
        doc_incident_no.extend(incident_no)
        doc_incident_loc.extend(incident_loc)
        doc_incident_nature.extend(incident_nature)
        doc_incident_ori.extend(incident_ori)
    # matching the length of the columns
    all_length_match = (
        len(doc_incident_dt)
        == len(doc_incident_no)
        == len(doc_incident_loc)
        == len(doc_incident_nature)
        == len(doc_incident_ori)
    )
    # if the length of the columns are not matched it will raise an listlengthmismatch exception
    if not all_length_match:
        print(
            len(doc_incident_dt),
            len(doc_incident_no),
            len(doc_incident_loc),
            len(doc_incident_nature),
            len(doc_incident_ori),
        )
        raise ListLengthMismatch

    return [
        doc_incident_dt,
        doc_incident_no,
        doc_incident_loc,
        doc_incident_nature,
        doc_incident_ori,
    ]


# extract incidents
def extractincidents(incident_data):
    fp = tempfile.TemporaryFile()

    # Write the pdf data to a temp file
    fp.write(incident_data)

    # Set the curser of the file back to the begining
    fp.seek(0)

    # Read the PDF
    pdfReader = PyPDF2.pdf.PdfFileReader(fp)

    # Read the PDF
    pdfReader = PyPDF2.pdf.PdfFileReader(fp)
    no_of_pages = pdfReader.getNumPages()
    cleaned_pages = getpages(pdfReader, no_of_pages)
    return extract_doc_incident(cleaned_pages)


# creating the database
def createdb(drop_if_exits):
    conn = sqlite3.connect("Norman_police.db")
    cur = conn.cursor()
    # dropping the table if already exists
    if drop_if_exits:
        cur.execute("""DROP TABLE IF EXISTS incidents""")
        conn.commit()
    # creating a table named incidents
    cur.execute(
        """
    CREATE TABLE incidents (
        incident_time TEXT,
        incident_number TEXT,
        incident_location TEXT,
        incident_nature TEXT,
        incident_ori TEXT)
    """
    )
    conn.commit()
    return conn


# populating the incident table with values
def populatedb(conn, incidents):
    inc_dt, inc_no, inc_loc, inc_nat, inc_ori = incidents
    cur = conn.cursor()

    for i in range(len(inc_dt)):
        cur.execute(
            """ 
        INSERT INTO incidents (
            incident_time,
            incident_number,
            incident_location,
            incident_nature,
            incident_ori) values 
            (?,?,?,?,?)
        """,
            (
                inc_dt[i],
                inc_no[i],
                inc_loc[i],
                inc_nat[i],
                inc_ori[i],
            ),
        )
    conn.commit()
    return conn


# Printing the incident_nature values with its number of occurrences in alphabetical order
def status(conn):
    cur = conn.cursor()
    cur.execute(
        """
        select incident_nature, count(incident_nature) as count from incidents group by incident_nature order by incident_nature;"""
    )
    result = cur.fetchall()
    output = result
    return "\n".join([itype + " | " + str(nocc) for itype, nocc in output])
# fetching incident data from the Norman, Oklahoma police department # Getting the pages from pdf # extracting individual columns from the incidents # defined this exception to find if all the columns values have the same count # defining the incident columns as a list # getting the incident column values # excluding the empty spaces in the data # excluding the heading and column names from the page. time-stamp from the last page # adding the list of extracted columns to the current list (column by column) # matching the length of the columns # if the length of the columns are not matched it will raise an listlengthmismatch exception # extract incidents # Write the pdf data to a temp file # Set the curser of the file back to the begining # Read the PDF # Read the PDF # creating the database # dropping the table if already exists DROP TABLE IF EXISTS incidents # creating a table named incidents CREATE TABLE incidents (         incident_time TEXT,         incident_number TEXT,         incident_location TEXT,         incident_nature TEXT,         incident_ori TEXT) # populating the incident table with values INSERT INTO incidents (             incident_time,             incident_number,             incident_location,             incident_nature,             incident_ori) values              (?,?,?,?,?) # Printing the incident_nature values with its number of occurrences in alphabetical order select incident_nature, count(incident_nature) as count from incidents group by incident_nature order by incident_nature;