tausciam

callcheck.py

Nov 23rd, 2019
100
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. import sqlite3
  2. import pandas
  3. from sqlite3 import Error
  4. from file_read_backwards import FileReadBackwards
  5.  
  6. def sql_connection():
  7.  
  8.     try:
  9.  
  10.         con = sqlite3.connect('calldata.db')
  11.  
  12.         return con
  13.  
  14.     except Error:
  15.  
  16.         print(Error)
  17.  
  18. def sql_table(con):
  19.  
  20.     cursorObj = con.cursor()
  21.  
  22.     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)")
  23.  
  24.     con.commit()
  25.    
  26. def insert_ham(con):
  27.    
  28.     cursorObj = con.cursor()
  29.    
  30.     df = pandas.read_csv("calldata.txt", delimiter=",", dtype="object")
  31.     df.to_sql("hams", con, if_exists='append',index=False )
  32.    
  33.     con.commit()
  34.    
  35. def importFCCdata(con):
  36.     '''
  37.    line_list[4] = callsign
  38.    line_list[7] = lastname, firstname mi
  39.    line_list[8] = firstname
  40.    line_list[9] = middle initial
  41.    line_list[10] = lastname
  42.    line_list[15] = street address
  43.    line_list[16] = city
  44.    line_list[17] = state
  45.    line_list[18] = zip
  46.    '''
  47.     with FileReadBackwards("EN.dat",encoding="utf-8") as in_file, open("calldata.txt", 'w') as out_file:
  48.         callsign_set = set()
  49.         out_file.write('callsign,lastname,firstname,staddress,city,state,zip\n')
  50.         for line in in_file:
  51.             line_list = line.split('|')
  52.             if line_list[4] in callsign_set:
  53.                 continue
  54.             else:
  55.                 callsign_set.add(line_list[4])
  56.                 if line_list[10] == "":
  57.                     if line_list[8] == "":
  58.                         continue
  59.                     else:
  60.                         line_list[10] = line_list[7]
  61.                 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")
  62.                
  63.     insert_ham(con)
  64.    
  65. def addFCCdata(con):
  66.    
  67.     con.row_factory = sqlite3.Row
  68.     cursorObj = con.cursor()
  69.     '''
  70.    line_list[4] = callsign
  71.    line_list[7] = date granted
  72.    line_list[8] = date expires
  73.    line_list[30] = first name
  74.    line_list[31] = middle initial
  75.    line_list[32] = last name
  76.    '''
  77.     with FileReadBackwards("HD.dat",encoding="utf-8") as in_file, open("datedata.txt", 'w') as out_file:
  78.         callsign_set = set()
  79.         for line in in_file:
  80.             line_list = line.split('|')
  81.             if line_list[4] in callsign_set:
  82.                 continue
  83.             else:
  84.                 callsign_set.add(line_list[4])
  85.                 cursorObj.execute("UPDATE hams SET granted = ?, expires = ? WHERE callsign = ?",(line_list[7],line_list[8],line_list[4]))
  86.                 '''
  87.                cursorObj.execute("SELECT lastname, firstname FROM hams WHERE callsign = ?",(line_list[4],))
  88.                result = cursorObj.fetchone()
  89.                
  90.                if result is not None:
  91.                    if result["lastname"] is not None and result["firstname"] is not None:
  92.                        #if result["lastname"].lower() == line_list[32].lower() and result["firstname"].lower() == line_list[30].lower():
  93.                            cursorObj.execute("UPDATE hams SET granted = ?, expires = ? WHERE callsign = ?",(line_list[7],line_list[8],line_list[4]))
  94.                        else:
  95.                            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"]))
  96.                    else:
  97.                        continue
  98.                        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.
  99.    '''
  100.     con.commit()
  101.    
  102.  
  103.  
  104. con = sql_connection()
  105.  
  106. sql_table(con)
  107.  
  108. importFCCdata(con)
  109.  
  110. addFCCdata(con)
RAW Paste Data