def get_last_run_time(self, run_type):
db = MySqlConnector('127.0.0.1', 'fcc_amateur', 'root', 'a')
db.execute_query(f"""SELECT id, run_date, run_type FROM last_run WHERE run_type='{run_type}' ORDER BY run_date desc LIMIT 1""", False)
row = db.fetchone()
if row is None:
return None
return row[1]
def _parse_en_file(self):
db = MySqlConnector('127.0.0.1', 'fcc_amateur', 'root', 'a')
logging.info("Parsing EN.dat")
try:
en_file = open(f"{self._FILE_DIR}/license/EN.dat")
except FileNotFoundError:
logging.error("EN.dat not found")
return
line_count = 0
while True:
line_string = en_file.readline().replace('\n', '')
line_count += 1
if line_count % self._log_line_interval == 0:
logging.info(f"Parsing line {line_count}")
line_string = line_string.replace("\\", "\\\\").replace("'", "\\'")
line = line_string.split('|')
if len(line) <= 1:
db.commit()
break
try:
db.execute_query(f"""INSERT INTO en_entities (
fcc_id,
call_sign,
entity_type,
licensee_id,
entity_name,
first_name,
middle_initial,
last_name,
name_suffix,
phone,
email,
street_address,
city,
state,
zip,
po_box,
address_attn_line,
fcc_reg_number)
VALUES ('{line[1]}', '{line[4]}', '{line[5]}', '{line[6]}', '{line[7]}', '{line[8]}', '{line[9]}',
'{line[10]}', '{line[11]}', '{line[12]}', '{line[14]}', '{line[15]}', '{line[16]}', '{line[17]}',
'{line[18]}', '{line[19]}', '{line[20]}', '{line[22]}')
ON DUPLICATE KEY UPDATE `fcc_id`={line[1]}
""", False)
if line_count % 500 == 0:
logging.debug("Committing!")
db.commit()
except Exception as ex:
logging.error(f"Error on line: ```{line_string}```", ex)
raise ex
en_file.close()
def get_connector(source):
driver = source['driver']
if driver == 'mysql':
from mysql_connector import MySqlConnector
return MySqlConnector(source)
elif driver == 'postgresql':
from postgresql_connector import PostgreSqlConnector
return PostgreSqlConnector(source)
else:
raise LookupError("Driver {0} not supported".format(driver))
def _parse_ad_file(self):
db = MySqlConnector('127.0.0.1', 'fcc_amateur', 'root', 'a')
logging.info("Parsing AD.dat")
try:
ad_file = open(f"{self._FILE_DIR}/application/AD.dat")
except FileNotFoundError:
logging.error("AD.dat not found")
return
line_count = 0
while True:
line_string = ad_file.readline().replace('\n', '')
line_count += 1
if line_count % self._log_line_interval == 0:
logging.info(f"Parsing line {line_count}")
line_string = line_string.replace('\\', '')
line = line_string.split('|')
if len(line) <= 1:
db.commit()
break
if line[2] != '':
line[2] = f"'{line[2]}'"
else:
line[2] = 'NULL'
try:
db.execute_query(f"""INSERT INTO ad_application_detail (
unique_identifier,
uls_file_number,
application_purpose,
application_status,
receipt_date)
VALUES('{line[1]}', {line[2]}, '{line[4]}', '{line[6]}',
{self._mysql_date_str(line[10])})
ON DUPLICATE KEY UPDATE `unique_identifier`={line[1]}
""", False)
if line_count % 500 == 0:
logging.debug("Committing!")
db.commit()
except Exception as ex:
logging.error(f"Error on line: ```{line_string}```", ex)
raise ex
ad_file.close()
def _parse_vc_file(self):
db = MySqlConnector('127.0.0.1', 'fcc_amateur', 'root', 'a')
logging.info("Parsing VC.dat")
try:
ad_file = open(f"{self._FILE_DIR}/application/VC.dat")
except FileNotFoundError:
logging.error("VC.dat not found")
return
line_count = 0
while True:
line_string = ad_file.readline().replace('\n', '')
line_count += 1
if line_count % self._log_line_interval == 0:
logging.info(f"Parsing line {line_count}")
line_string = line_string.replace('\\', '')
line_string = line_string.replace("'", "\\'")
line = line_string.split('|')
if len(line) <= 1:
db.commit()
break
if line[5] != '':
line[5] = f"'{line[5]}'"
else:
line[5] = 'NULL'
try:
db.execute_query(f"""INSERT INTO vc_vanity_call_sign (
unique_identifier,
uls_file_number,
order_preference,
requested_call_sign)
VALUES('{line[1]}', '{line[2]}', '{line[4]}', {line[5]})
ON DUPLICATE KEY UPDATE `unique_identifier`={line[1]}, `order_preference`={line[4]}
""", False)
if line_count % 500 == 0:
logging.debug("Committing!")
db.commit()
except Exception as ex:
logging.error(f"Error on line: ```{line_string}```", ex)
raise ex
ad_file.close()
def _parse_hd_file(self):
db = MySqlConnector('127.0.0.1', 'fcc_amateur', 'root', 'a')
logging.info("Parsing HD.dat")
try:
hd_file = open(f"{self._FILE_DIR}/license/HD.dat")
except FileNotFoundError:
logging.error("HD.dat not found")
return
line_count = 0
while True:
line_string = hd_file.readline().replace('\n', '')
line_count += 1
if line_count % self._log_line_interval == 0:
logging.info(f"Parsing line {line_count}")
line = line_string.split('|')
if len(line) <= 1:
db.commit()
break
try:
db.execute_query(f"""INSERT INTO hd_license_header (
fcc_id,
call_sign,
license_status,
grant_date,
expired_date,
cancellation_date,
reserved)
VALUES('{line[1]}', '{line[4]}', '{line[5]}', {self._mysql_date_str(line[7])},
{self._mysql_date_str(line[8])}, {self._mysql_date_str(line[9])}, '{line[11]}')
ON DUPLICATE KEY UPDATE `fcc_id`={line[1]}
""", False)
if line_count % 500 == 0:
logging.debug("Committing!")
db.commit()
except Exception as ex:
logging.error(f"Error on line: ```{line_string}```", ex)
raise ex
hd_file.close()
def _parse_am_file(self):
db = MySqlConnector('127.0.0.1', 'fcc_amateur', 'root', 'a')
try:
am_file = open(f"{self._FILE_DIR}/license/AM.dat")
except FileNotFoundError:
logging.error("AM.dat not found")
return
line_count = 0
while True:
line_string = am_file.readline().replace('\n', '')
line_count += 1
if line_count % self._log_line_interval == 0:
logging.info(f"Parsing line {line_count}")
line = line_string.split('|')
if len(line) <= 1:
db.commit()
break
try:
db.execute_query(f"""INSERT INTO am_amateur (
fcc_id,
call_sign,
operator_class)
VALUES('{line[1]}', '{line[4]}', '{line[5]}')
ON DUPLICATE KEY UPDATE `fcc_id`={line[1]}
""", False)
if line_count % 500 == 0:
logging.debug("Committing!")
db.commit()
except Exception as ex:
logging.error(f"Error on line: ```{line_string}```", ex)
raise ex
am_file.close()
def set_last_run_time(self, date, run_type):
db = MySqlConnector('127.0.0.1', 'fcc_amateur', 'root', 'a')
mysql_date = self._mysql_date_str(date.strftime('%m/%d/%Y'))
db.execute_query(f"""INSERT INTO last_run (run_date, run_type) VALUES ({mysql_date}, '{run_type}')""")
def __init__(self):
self._mysql_connector = MySqlConnector('127.0.0.1', 'fcc_amateur',
'root', 'a')
class VanitySearch:
_mysql_connector = None
def __init__(self):
self._mysql_connector = MySqlConnector('127.0.0.1', 'fcc_amateur',
'root', 'a')
def generate_possibles(self, *character_rules):
character_rules = list(character_rules)
possibles = set()
if len(character_rules) == 1:
for x in character_rules[0]:
possibles.add(str(x))
return possibles
for x in character_rules[0]:
sub_possibles = self.generate_possibles(*character_rules[1:])
for y in sub_possibles:
possibles.add(str(x) + str(y))
return possibles
def get_banned_handles(self, handles_set):
banned = set()
# 1. KA2AA-KA9ZZ, KC4AAA-KC4AAF, KC4USA-KC4USZ, KG4AA-KG4ZZ,
# KC6AA-KC6ZZ, KL9KAA- KL9KHZ, KX6AA-KX6ZZ;
banned_handles = set()
banned_handles = banned_handles.union(
self.generate_possibles({'KA'}, self.chars_through('2', '9'),
self.chars_through('A', 'Z'),
self.chars_through('A', 'Z')))
banned_handles = banned_handles.union(
self.generate_possibles({'KC4AA'}, self.chars_through('A', 'F')))
banned_handles = banned_handles.union(
self.generate_possibles({'KC4US'}, self.chars_through('A', 'Z')))
banned_handles = banned_handles.union(
self.generate_possibles({'KG4'}, self.chars_through('A', 'Z'),
self.chars_through('A', 'Z')))
banned_handles = banned_handles.union(
self.generate_possibles({'KC6'}, self.chars_through('A', 'Z'),
self.chars_through('A', 'Z')))
banned_handles = banned_handles.union(
self.generate_possibles({'KL9K'}, self.chars_through('A', 'H'),
self.chars_through('A', 'Z')))
banned_handles = banned_handles.union(
self.generate_possibles({'KX6'}, self.chars_through('A', 'Z'),
self.chars_through('A', 'Z')))
banned_handles_contained = handles_set.intersection(banned_handles)
banned = banned.union(banned_handles_contained)
# 2. Any call sign having the letters SOS or QRA-QUZ as the suffix;
banned_suffixes = self.generate_possibles({'Q'},
self.chars_through('R', 'U'),
self.chars_through('A', 'Z'))
banned_suffixes.add('SOS')
for x in handles_set:
if len(x) >= 3:
end = x[-3:]
if end in banned_suffixes:
banned.add(x)
# 3. Any call sign having the letters AM-AZ as the prefix (these prefixes
# are assigned to other countries by the ITU);
banned_prefixes = self.generate_possibles({'A'},
self.chars_through('M', 'Z'))
for x in handles_set:
if len(x) >= 2:
beginning = x[0:2]
if beginning in banned_prefixes:
banned.add(x)
# 4. Any 2-by-3 format call sign having the letter X as the first letter of the suffix;
for x in handles_set:
if len(x) == 6 and x[2] in self.chars_through('0',
'9') and x[3] == 'X':
banned.add(x)
# 5. Any 2-by-3 format call sign having the letters AF, KF, NF, or WF as the prefix
# and the letters EMA as the suffix (U.S Government FEMA stations);
banned_handles = self.generate_possibles({'A', 'K', 'N', 'W'}, 'F',
self.chars_through('0', '9'),
{'EMA'})
banned_handles_contained = banned_handles.intersection(handles_set)
banned = banned.union(banned_handles_contained)
banned_prefixes = set()
# 6. Any 2-by-3 format call sign having the letters AA-AL as the prefix;
# 7. Any 2-by-3 format call sign having the letters NA-NZ as the prefix;
# 8. Any 2-by-3 format call sign having the letters WC, WK, WM, WR, or WT
# as the prefix (Group X call signs);
banned_prefixes = banned_prefixes.union(
self.generate_possibles({'A', 'K', 'N', 'W'},
self.chars_through('A', 'L')))
banned_prefixes = banned_prefixes.union(
self.generate_possibles({'N'}, self.chars_through('A', 'Z')))
banned_prefixes = banned_prefixes.union({'WC', 'WK', 'WM', 'WR', 'WT'})
for x in handles_set:
if len(x) == 6 and x[2] in self.chars_through(
'0', '9') and x[0:2] in banned_prefixes:
banned.add(x)
# 9. Any 2-by-3 format call sign having the letters KP, NP or WP as the prefix
# and the numeral 0, 6, 7, 8 or 9;
# 10. Any 2-by-2 format call sign having the letters KP, NP or WP as the prefix
# and the numeral 0, 6, 7, 8 or 9;
# 11. Any 2-by-1 format call sign having the letters KP, NP or WP as the prefix
# and the numeral 0, 6, 7, 8 or 9;
banned_prefixes = self.generate_possibles({'K', 'N', 'W'}, {'P'},
{'0', '6', '7', '8', '9'})
for x in handles_set:
if len(x) >= 3:
beginning = x[0:3]
if beginning in banned_prefixes:
banned.add(x)
# 12. Call signs having the single letter prefix (K, N or W), a single digit numeral
# 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 and a single letter suffix are reserved for the
# special event call sign system.
short_handles = self.generate_possibles({'K', 'N', 'W'},
self.chars_through('0', '9'))
short_banned = short_handles.intersection(handles_set)
banned = banned.union(short_banned)
# Two letter prefixes that are designed for regions 11-13 are not available in regions
# 1-10.
regionals = self.generate_possibles(
{'AL', 'KL', 'NL', 'WL', 'KP', 'NP', 'WP', 'AH', 'KH', 'NH', 'WH'},
self.chars_through('0', '9'), self.chars_through('A', 'Z'))
regional_banned = regionals.intersection(handles_set)
banned = banned.union(regional_banned)
return banned
# noinspection PyMethodMayBeStatic
def chars_through(self, first, last):
first_ord = ord(first)
last_ord = ord(last)
result = set()
for x in range(first_ord, last_ord):
result.add(chr(x))
return result
def scan_existing_handles(self, possibles_list):
possibles_str = "','".join(possibles_list)
possibles_str = "'" + possibles_str + "'"
query = f"""select hd_license_header.call_sign,expired_date from hd_license_header
where hd_license_header.expired_date >= DATE_SUB(NOW(), INTERVAL 24 MONTH)
AND hd_license_header.call_sign in ({possibles_str});"""
self._mysql_connector.execute_query(query, False)
row = self._mysql_connector.fetchone()
existing = set()
while True:
row = self._mysql_connector.fetchone()
if row is None:
break
existing.add(row[0])
return existing
def scan_license_applications(self, possibles_list):
possibles_str = "','".join(possibles_list)
possibles_str = "'" + possibles_str + "'"
query = f"""select ad_application_detail.unique_identifier, ad_application_detail.uls_file_number,
ad_application_detail.receipt_date, vc_vanity_call_sign.requested_call_sign, vc_vanity_call_sign.
order_preference from ad_application_detail join vc_vanity_call_sign on ad_application_detail.
unique_identifier=vc_vanity_call_sign.unique_identifier where
ad_application_detail.receipt_date >= DATE_SUB(NOW(), INTERVAL 25 DAY) and
vc_vanity_call_sign.requested_call_sign in ({possibles_str});"""
self._mysql_connector.execute_query(query, False)
row = self._mysql_connector.fetchone()
lowest_rank = dict.fromkeys(possibles_list)
for x in lowest_rank:
lowest_rank[x] = dict({'preference': None, 'date': None})
while True:
if row is None:
break
order_pref = lowest_rank[row[3]]
if order_pref['preference'] is None or row[4] <= order_pref[
'preference']:
order_pref['preference'] = row[4]
lowest_rank[row[3]] = order_pref
if order_pref['date'] is None or row[2] <= order_pref['date']:
order_pref['date'] = row[2]
lowest_rank[row[3]] = order_pref
row = self._mysql_connector.fetchone()
return lowest_rank
def generate_vanity_handles(self):
possibles = set()
handles_1x2 = self.generate_possibles({"K", "N", "W"},
self.chars_through('0', '9'),
self.chars_through('A', 'Z'),
self.chars_through('A', 'Z'))
handles_2x1 = self.generate_possibles({"A", "K", "N", "W"},
self.chars_through('A', 'Z'),
self.chars_through('0', '9'),
self.chars_through('A', 'Z'))
handles_2x2 = self.generate_possibles({"A", "K"},
self.chars_through('A', 'Z'),
self.chars_through('0', '9'),
self.chars_through('A', 'Z'),
self.chars_through('A', 'Z'))
handles_1x3 = self.generate_possibles({"K", "N", "W"},
self.chars_through('0', '9'),
self.chars_through('A', 'Z'),
self.chars_through('A', 'Z'),
self.chars_through('A', 'Z'))
possibles = possibles.union(handles_1x2, handles_2x1, handles_2x2,
handles_1x3)
banned_handles = self.get_banned_handles(possibles)
before_len = len(possibles)
possibles.difference_update(banned_handles)
after_len = len(possibles)
logging.info(f"Removed {before_len - after_len} banned.")
before_len = len(possibles)
existing = self.scan_existing_handles(possibles)
possibles.difference_update(existing)
after_len = len(possibles)
logging.info(f"Removed {before_len - after_len} existing.")
license_data = self.scan_license_applications(possibles)
f = open("possibles.txt", "w+")
for x in sorted(license_data):
if license_data[x]['preference'] is not None and license_data[x][
'preference'] == 1:
continue
date_pref = license_data[x]['date']
date_preference = f'\t{date_pref}' if date_pref is not None else ''
order_pref = license_data[x]['preference']
order_preference = f"\t{order_pref}" if order_pref is not None else ''
f.write(f"{x}{date_preference}{order_preference}\n")
f.close()
return
import re
from mysql_connector import MySqlConnector
def remove_html_tags(text):
return re.sub(r"<[^>]*>", " ", text)
def remove_html_encodings(text):
return re.sub(r"&[^\s]*;", " ", text)
def clean_text(text):
text = remove_html_tags(text)
text = remove_html_encodings(text)
text = text.strip()
return text
if __name__ == "__main__":
connector = MySqlConnector('parlai')
docs_cursor = connector.get_all_documents_cursor()
for row in docs_cursor:
row_dict = dict(zip(docs_cursor.column_names, row))
id = row_dict['id']
title = clean_text(row_dict['title'])
introduction = clean_text(row_dict['introduction'])
connector.update_document_set_title_intro((title, introduction, id))