Project1: English dictionary Json+difflib+SQL

命令行交互：通过给定的单词，搜索加载的json字典库，给出相应的解释

json 和 difflib

（1）json.load可加载json软件到变量中，保存为字典格式；
（2）difflib可进行字符串匹配，get_close_matches可得到匹配度最高的字符串列表list，n为个数，cutoff为百分比；

import json
from difflib import get_close_matches

data=json.load(open("data.json"))

def translate(word):
    word=word.lower()
    if word in data:
        return data[word]
    elif word.title() in data:
        return data[word.title()]
    elif word.upper() in data:
        return data[word.upper()]
    tmp=get_close_matches(word,data.keys(),n=1)[0]
    flag=input("Do you mean to search for {}? Press Y to confirm and N to reject: ".format(tmp))
    if(flag=="N"):
        return "The word doesn't existed, please double check it."
    if tmp in data:
        return data[tmp]
    else:
        return "The word doesn't existed, please double check it."

word=input("Enter words: ")

res=translate(word)
cnt=0;
for i in res:
    cnt+=1
    print("Explaination {}: {}".format(cnt,i))

SQL

为减少load的空间占用，调用SQL完成单词搜索；

mysql.connector作为连接sql的工具
（1）mysql.connector.connect()访问sql，赋值给con

con=mysql.connector.connect(
        user = "ardit700_student",
        password = "ardit700_student",
        host="108.167.140.122",
        database = "ardit700_pm1database"
        )

（2）con.cursor()赋值给cursor，进行后续操作
（3）query=cursor.execute("SELECT * FROM Dictionary WHERE Expression = ‘{}’".format(word))
为SQL语句，得到Expression列下的所需单词
（4）Expression LIKE 'r%‘ 可得到r开头的所有单词的
（5）cursor.fetchall()，得到元组列表，元组的第一个为key，第二个值为value；

import mysql.connector

con=mysql.connector.connect(
        user = "ardit700_student",
        password = "ardit700_student",
        host="108.167.140.122",
        database = "ardit700_pm1database"
        )

cursor = con.cursor()


word= input("Enter a word: ")

query = cursor.execute("SELECT * FROM Dictionary WHERE Expression = '{}%'".format(word))
results=cursor.fetchall()

if not results:
    query = cursor.execute("SELECT * FROM Dictionary WHERE Expression LIKE '{}%' AND length(Expression)<{}".format(word[:-1],len(word)+1))
    results=cursor.fetchall()

if results:
    for result in results:
        print(result[0],":",result[1])
else:
    print("No word found")

补充的SQL查询代码

Get all rows where the value of the column Expression starts with r:

"SELECT * FROM Dictionary WHERE Expression  LIKE 'r%'"

Get all rows where the value of the column Expression starts with rain:

"SELECT * FROM Dictionary WHERE Expression  LIKE 'rain%'"

All rows where the length of the value of the column Expression is less than four characters:

"SELECT * FROM Dictionary WHERE length(Expression) < 4"

All rows where the length of the value of the column Expression is four characters:

"SELECT * FROM Dictionary WHERE length(Expression) = 4"

All rows where the length of the value of the column Expression is greater than 1 but less than 4 characters:

"SELECT * FROM Dictionary WHERE length(Expression) > 1 AND length(Expression) < 4"

All rows of column Definition where the value of the column Expression starts with r:

"SELECT Definition FROM Dictionary WHERE Expression  LIKE 'r%'"