Question

in this assignment you will create and use a database to find meanings and synonyms for given phrases. To do so you will use tables of synsets -- sets of one or more synonyms (specific phrases) that share the same meaning.

Your program should:

1. Display a message stating its goal
2. Create a database file using SQLite using your name (i.e. use your name as a file name - for example, my database will be named "nimdvir")
3. In your database, create a table named "synsets" with the columns "SynsetID" and "Definition", and populate it with values from the attached text file "Synsets" (also available to download here - https://drive.google.com/file/d/1qmj518lIjTFn6xifnd_PCl6AadY_JeG8/view?usp=sharing)
4. In your database, create a table named "phrases" with the columns "SynsetID" and "phrase", and populate it with values from the attached text file "Phrases" (also available to download here - https://drive.google.com/file/d/1r-JZrF_Ei7a2_FPhXJqo9b-i8nvpqMbc/view?usp=sharing)
5. Hint 1: make sure the encoding is correct (see previous readings)
6. Hint 2: make sure you parse the text files correctly
7. Hint 3: it is recommended not to import all the data while you are still working on the program. Use just a few values to make sure your program runs correctly, and them change it to import all the values from the files.
8. Ask the user to enter a phrase and check if it's in the database (not case sensitive)
9. If it's not in the database display a message and prompt the user to try again
10. If it is in the database, return as output:
11. How many meanings the phrase has? (in how many synsets it appear)
12. How many unique synonyms the phrase has? (how many other phrases share the same synsets? Make sure to not count the phrase itself or the same phrase twice)
13. Display all the meanings (definitions) the phrase has, including their corresponding Synset ID
14. Below each definition out the correlating synonyms

For example, for the phrase "jail"

there are 2 meanings and 11 unique synonyms

Meaning 1:

lock up or confine, in or as in a jail

Synset ID:

2494356

Synonyms:

gaol

• immure
• jailed
• jug
• lag
• laggin
• remand

Meaning 2:

A correctional institution used to detain persons who are in the lawful custody of the government (either accused persons awaiting trial or convicted persons serving a sentence)

Synset ID:

3592245

Synonyms:

clink

• gaol
• pokey
• poky
• slammer
• Remember:

Only use the material covered in this module -- do not use more advanced functions not covered yet in the course

• Make sure to include comments that explain all your steps (starts with #). Also use a comment to sign your name at the beginning of the program!
• Work individually and only submit original work

Submit a .py file!

Answer 1

import sqlite3
import pandas as pd
conn = sqlite3.connect('nimdvir.db')
c = conn.cursor()
c.execute('''CREATE TABLE synsets([SynsetID] INTEGER,[Definition] text)''')
c.execute('''CREATE TABLE phrases([SynsetID] INTEGER,[phrase] text)''')
conn.commit()
with open('/content/synsets.txt', 'r') as f:
    for line in f:
        data = line.split('\t')
        c.execute('INSERT INTO synsets (SynsetID, Definition) VALUES (?, ?)', (data[0], data[1].strip()))

with open('/content/phrases.txt', 'r') as f:
    for line in f:
        data = line.split('\t')
        c.execute('INSERT INTO phrases (SynsetID, phrase) VALUES (?, ?)', (data[0], data[1].strip()))

word = str(input("Enter the phrase: "))
query = 'SELECT * FROM phrases WHERE phrase=' + "'"+ word.lower() + "'"
df = pd.read_sql_query(query, conn)
if df.empty:
  print("please try again!")
else:
  result_query = 'SELECT DISTINCT s.SynsetID,Definition FROM phrases p INNER JOIN synsets s ON s.SynsetID=p.SynsetID WHERE phrase=' + "'"+ word.lower() + "'"
  result_df = pd.read_sql_query(result_query, conn)
  lst = result_df['SynsetID'].values.tolist()
  query = 'SELECT DISTINCT phrase,SynsetID FROM phrases WHERE SynsetID IN (SELECT DISTINCT s.SynsetID FROM phrases p INNER JOIN synsets s ON s.SynsetID=p.SynsetID WHERE phrase=' + "'"+ word.lower() + "') AND phrase<>"+ "'"+ word.lower() + "'"
  df = pd.read_sql_query(query, conn)
  syn = df['phrase'].values.tolist()
  print("There are "+ str(len(lst)) +" meanings and "+ str(len(syn)) +" unique synonyms")
  j=1
  for i in lst:
    print("Meaning - ",j)
    print(result_df[result_df['SynsetID']==i]['Definition'].values[0])
    print("Synset ID: ")
    print(i)
    print("Synonyms:")
    words = df[df['SynsetID']==i]['phrase'].values.tolist()
    for w in words:
      if w!=word.lower():
        print(w)
    j=j+1

ANy query you can ask in the comment. Thanks.