callcheck.py

import sqlite3
import pandas
from sqlite3 import Error
from file_read_backwards import FileReadBackwards

def sql_connection():

    try:

        con = sqlite3.connect('calldata.db')

        return con

    except Error:

        print(Error)

def sql_table(con):

    cursorObj = con.cursor()

    cursorObj.execute("CREATE TABLE IF NOT EXISTS hams(callsign text PRIMARY KEY, lastname text, firstname text, staddress text, city text, state text, zip text, granted text, expires text)")

    con.commit()

def insert_ham(con):

    cursorObj = con.cursor()

    df = pandas.read_csv("calldata.txt", delimiter=",", dtype="object")
    df.to_sql("hams", con, if_exists='append',index=False )

    con.commit()

def importFCCdata(con):
    '''
    line_list[4] = callsign
    line_list[7] = lastname, firstname mi
    line_list[8] = firstname
    line_list[9] = middle initial
    line_list[10] = lastname
    line_list[15] = street address
    line_list[16] = city
    line_list[17] = state
    line_list[18] = zip
    '''
    with FileReadBackwards("EN.dat",encoding="utf-8") as in_file, open("calldata.txt", 'w') as out_file:
        callsign_set = set()
        out_file.write('callsign,lastname,firstname,staddress,city,state,zip\n')
        for line in in_file:
            line_list = line.split('|')
            if line_list[4] in callsign_set:
                continue
            else:
                callsign_set.add(line_list[4])
                if line_list[10] == "":
                    if line_list[8] == "":
                        continue
                    else:
                        line_list[10] = line_list[7]
                out_file.write(line_list[4].replace(",","") + "," + line_list[10].replace(",","") + "," + line_list[8].replace(",","") + "," + line_list[15].replace(",","") + "," + line_list[16].replace(",","") + "," + line_list[17].replace(",","") + "," + line_list[18].replace(",","") + "\n")

    insert_ham(con)

def addFCCdata(con):

    con.row_factory = sqlite3.Row
    cursorObj = con.cursor()
    '''
    line_list[4] = callsign
    line_list[7] = date granted
    line_list[8] = date expires
    line_list[30] = first name
    line_list[31] = middle initial
    line_list[32] = last name
    '''
    with FileReadBackwards("HD.dat",encoding="utf-8") as in_file, open("datedata.txt", 'w') as out_file:
        callsign_set = set()
        for line in in_file:
            line_list = line.split('|')
            if line_list[4] in callsign_set:
                continue
            else:
                callsign_set.add(line_list[4])
                cursorObj.execute("UPDATE hams SET granted = ?, expires = ? WHERE callsign = ?",(line_list[7],line_list[8],line_list[4]))
                '''
                cursorObj.execute("SELECT lastname, firstname FROM hams WHERE callsign = ?",(line_list[4],))
                result = cursorObj.fetchone()

                if result is not None:
                    if result["lastname"] is not None and result["firstname"] is not None:
                        #if result["lastname"].lower() == line_list[32].lower() and result["firstname"].lower() == line_list[30].lower():
                            cursorObj.execute("UPDATE hams SET granted = ?, expires = ? WHERE callsign = ?",(line_list[7],line_list[8],line_list[4]))
                        else:
                            out_file.write("callsign: {} firstname: {}  first from DB: {}  lastname: {} last from DB: {}\n".format(line_list[4],line_list[30],result["firstname"],line_list[32],result["lastname"]))
                    else:
                        continue
                        Silly me. I forgot we're dealing with the government. I tried this check to make sure first name and last name matched, only to catch 11,529 mispellings.
    '''
    con.commit()



con = sql_connection()

sql_table(con)

importFCCdata(con)

addFCCdata(con)