def import_words_from_file(self, import_dict):
sql_import_word = '''INSERT INTO VOCABULARY ({},{},{},{},{},{},{},{})
VALUES (?,?,?,?,?,?,?,?)'''.format(
*self.word_attribute_headings[1:])
conn = sqlite3.connect(self.db_file)
c = conn.cursor()
word_values = []
for imp_word in import_dict:
temp_list = []
for heading in self.word_attribute_headings:
if heading != "word_id":
try:
temp_list.append(imp_word[heading])
except:
temp_list.append("-")
word_values.append(tuple(temp_list))
try:
c.executemany(sql_import_word, word_values)
conn.commit()
log.debug("MODEL: Imported Word from File")
except:
log.error("MODEL: Importing Word from File failed")
self.load_db()
def populate_database_from_web(self, words_dict, import_method,
import_translation):
temp_words = []
for word in words_dict:
if import_method == "translation":
temp_words.append(
("-", "-", "-", word["translation"], "-", "-", "-", "-"))
elif import_method == "transliteration" and not import_translation:
temp_words.append(
(word["translation"], "-", "-", "-", "-", "-", "-", "-"))
elif import_method == "transliteration" and import_translation:
temp_words.append((word["translation"], "-", "-",
word["english"], "-", "-", "-", "-"))
sql_populate_db = '''INSERT INTO VOCABULARY ({},{},{},{},{},{},{},{})
VALUES (?,?,?,?,?,?,?,?)'''.format(
*self.word_attribute_headings[1:])
conn = sqlite3.connect(self.db_file)
c = conn.cursor()
try:
c.executemany(sql_populate_db, temp_words)
conn.commit()
log.debug("MODEL: Successfully populated Database from Web.")
except:
log.error("MODEL: Failed populating Database from Web.")
self.load_db()
def get_words_from_web(self, url, start_count=0, end_count=999):
headers = requests.utils.default_headers()
headers.update({
'User-Agent':
'Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:52.0) Gecko/20100101 Firefox/52.0'
})
try:
req = requests.get(url, headers)
except:
log.error("DATA: Requesting {} failed.".format(url))
soup = BeautifulSoup(req.content, 'html.parser')
output = soup.find_all("tr")
if "1000mostcommonwords" in url:
words_list = []
for row in output:
words_dict = {}
cell = row.find_all("td")
words_dict["translation"] = cell[1].text
words_dict["english"] = cell[2].text
words_list.append(words_dict)
log.debug("DATA: Importing {} words.".format(
len(words_list[int(start_count):int(end_count)])))
return (words_list[int(start_count):int(end_count)])
def get_language_from_web(self,
url="https://www.1000mostcommonwords.com/"):
headers = requests.utils.default_headers()
headers.update({
'User-Agent':
'Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:52.0) Gecko/20100101 Firefox/52.0'
})
try:
req = requests.get(url, headers)
except:
log.error("DATA: Requesting {} failed.".format(url))
soup = BeautifulSoup(req.content, 'html.parser')
links = {}
log.debug("DATA: Scraping data from {}".format(url))
if url == "https://www.1000mostcommonwords.com/":
output = soup.find_all("a", attrs={"style": "color: #0000ff;"})
for link in output:
links[link.find(text=True)] = link.get("href")
return (links)
def update_vocabulary_metadata(self, form_contents):
conn = sqlite3.connect(self.db_file)
try:
log.debug("MODEL: Updated Vocabulary Metadata.")
for key, value in form_contents.items():
sql_update_word = '''UPDATE METADATA SET {} = ?'''.format(key)
c = conn.cursor()
c.execute(sql_update_word, (value, ))
conn.commit()
except:
log.error("MODEL: Updating Vocabulary Metadata failed")
self.load_db()
def update_word(self, form_contents, word_id):
conn = sqlite3.connect(self.db_file)
try:
log.debug("MODEL: Updated Word ID {}".format(word_id))
for key, value in form_contents.items():
sql_update_word = '''UPDATE VOCABULARY SET {} = ? WHERE word_id == ?'''.format(
key)
c = conn.cursor()
c.execute(sql_update_word, (value, word_id))
conn.commit()
except:
log.error("MODEL: Updating Word ID {} failed".format(word_id))
self.load_db()
def delete_word(self, word_ids):
# print(word_id)
conn = sqlite3.connect(self.db_file)
c = conn.cursor()
sql_del_word = '''DELETE FROM VOCABULARY WHERE [word_id] = ?'''
try:
c.executemany(sql_del_word, word_ids)
conn.commit()
log.debug("MODEL: Deleted Word ID {word_id} from DB.")
except:
log.error("MODEL: Deleting Word ID {word_id} failed")
self.load_db()
def convertToBinaryData(image_name):
#Convert digital data to binary format
if image_name == "" or image_name == "-":
img = Image.new('RGB', (200, 200), random_rgb())
with io.BytesIO() as output:
img.save(output, format="JPEG")
blobData = output.getvalue()
return blobData
else:
try:
with open(image_name, 'rb') as file:
blobData = file.read()
log.debug("UTILS: Successfully reading Image")
return blobData
except:
log.error("UTILS: Reading Image failed")
return ""
def populate_database_from_text(self, population_words):
temp_words = []
for word in population_words:
temp_words.append(("-", "-", "-", word, "-", "-", "-", "-"))
sql_populate_db = '''INSERT INTO VOCABULARY ({},{},{},{},{},{},{},{})
VALUES (?,?,?,?,?,?,?,?)'''.format(
*self.word_attribute_headings[1:])
conn = sqlite3.connect(self.db_file)
c = conn.cursor()
try:
c.executemany(sql_populate_db, temp_words)
conn.commit()
log.debug("MODEL: Successfully populated Database from text.")
except:
log.error("MODEL: Failed populating Database from text.")
self.load_db()
def _get_config_entry(self, yaml_key, optional = True):
if optional:
try:
if self.conf[yaml_key] == '':
value = ''
log.error("config.yaml entry {} is empty.".format(yaml_key))
else:
value = self.conf[yaml_key]
log.info("config.yaml entry {} is set.".format(yaml_key))
except KeyError:
value = ''
log.info("config.yaml entry {} is missing.".format(yaml_key))
return value
else:
try: # essential settings entries should error and exit
value = self.conf[yaml_key]
except KeyError as ke:
log.error("Missing essential entry in config.yaml: {}".format(ke))
raise SystemExit(3)
return value
def save_word(self, form_contents):
sql_insert_word_values = []
for heading in self.word_attribute_headings[1:]:
try:
sql_insert_word_values.append(form_contents[heading])
except:
sql_insert_word_values.append("-")
sql_insert_new_word = '''INSERT INTO VOCABULARY ({},{},{},{},{},{},{},{})
VALUES (?,?,?,?,?,?,?,?)'''.format(
*self.word_attribute_headings[1:])
conn = sqlite3.connect(self.db_file)
c = conn.cursor()
try:
c.execute(sql_insert_new_word, tuple(sql_insert_word_values))
conn.commit()
log.debug("MODEL: Inserted New Word in DB.")
except:
log.error("MODEL: Inserting Word failed")
self.load_db()
def export_batch(self, generated_word_list, filename):
log.debug("DATA: Exporting {}".format(filename))
if filename[-3:] == "csv":
try:
with open(filename, mode='w', newline='') as csv_file:
csv_writer = csv.writer(csv_file, delimiter=";")
for word_ in generated_word_list:
csv_writer.writerow([word_])
except:
log.error("DATA: Exporting {} failed.".format(filename))
elif filename[-4:] == "xlsx":
wb = oxl.Workbook()
sheet = wb.active
for word_ in generated_word_list:
sheet.append([word_])
try:
wb.save(filename)
except:
log.error("DATA: Exporting {} failed.".format(filename))
elif filename[-4:] == "docx":
document = docx.Document()
document.add_heading('Vocabulary', 0)
for word_ in generated_word_list:
document.add_paragraph(word_)
try:
document.save(filename)
except:
log.error("DATA: Exporting {} failed.".format(filename))
elif filename[-3:] == "txt":
try:
txt_file = open(filename, "w")
for word_ in generated_word_list:
txt_file.write(word_ + "\n")
txt_file.close()
except:
log.error("DATA: Exporting {} failed.".format(filename))
def read_yaml(yamlfile):
"""expects path/file"""
try:
with open(str(yamlfile), "r", encoding = "utf-8") as fyamlfile:
return yaml.safe_load(fyamlfile)
except IOError as errio:
log.error("Can't find %s.", yamlfile)
#raise errio
raise SystemExit(3)
except yaml.parser.ParserError as errparse:
log.error("ParserError in %s.", yamlfile)
#raise errparse
raise SystemExit(3)
except yaml.scanner.ScannerError as errscan:
log.error("ScannerError in %s.", yamlfile)
#raise errscan
raise SystemExit(3)
except Exception as err:
log.error(" trying to load %s.", yamlfile)
raise err
raise SystemExit(3)
def load_excel(self, excel_file="", csv_file=""):
import_dict = []
headings = []
if excel_file != "":
_file = excel_file
wb = oxl.load_workbook(_file)
ws = wb.active
log.debug("Importing {}".format(_file))
for i, row in enumerate(ws.rows):
if i == 0:
for heading in row:
if heading.value != None:
headings.append(heading.value)
else:
temp_word = {}
for j, heading in enumerate(headings):
if row[j].value != None or heading == "related_image":
if heading != "related_image":
temp_word[heading] = row[j].value
else:
image_dir = os.path.dirname(
_file) + "/related_images/"
image_path = image_dir + str(i) + ".jpg"
if os.path.isfile(image_path):
temp_word[
heading] = utils.convertToBinaryData(
image_path)
else:
temp_word[heading] = "-"
log.debug("Importing headings: {}".format(headings))
import_dict.append(temp_word)
elif csv_file != "":
_file = csv_file
log.debug("Importing {}".format(_file))
with open(csv_file, "r", encoding="utf-8-sig") as csvfile:
rows = csv.reader(csvfile, delimiter=';')
for i, row in enumerate(rows):
if i == 0:
for heading in row:
if heading != "":
headings.append(heading)
else:
temp_word = {}
for j, heading in enumerate(headings):
if row[j] != "" or heading == "related_image":
if heading != "related_image":
temp_word[heading] = row[j]
else:
image_dir = os.path.dirname(
csv_file) + "/related_images/"
image_path = image_dir + str(i) + ".jpg"
if os.path.isfile(image_path):
temp_word[
heading] = utils.convertToBinaryData(
image_path)
else:
temp_word[heading] = "-"
import_dict.append(temp_word)
match_count = []
for word in import_dict:
for heading, value in word.items():
if heading == "transliteration" or heading == "translation":
duplicate_check = self.check_for_duplicates(
value, heading_list=["transliteration", "translation"])
if duplicate_check != True:
match_count.append(word)
else:
pass
if match_count != []:
log.error("XLS/CSV Import: Found {} duplicates.".format(
len(match_count)))
message_ = '''Found {} words, which are already in vocabulary. Import anyway?
Press YES to import all.
Press NO to import all without duplicates.
Press CANCEL to abort.
'''.format(str(len(match_count)))
MsgBox = tk.messagebox.askyesnocancel("Found Duplicates", message_)
if MsgBox == True:
log.debug("Importing {} Words.".format(str(len(import_dict))))
self.vocab.import_words_from_file(import_dict)
return
elif MsgBox == False:
for match in match_count:
import_dict.remove(match)
log.debug("Importing {} Words.".format(str(len(import_dict))))
self.vocab.import_words_from_file(import_dict)
return
else:
log.debug("Aborted Importing")
return
else:
log.debug("No Duplicates Found")
self.vocab.import_words_from_file(import_dict)