[Python] [Mysql] 离线背单词

Requirement：

Pymysql （Python）

mysql 

 

UI.py:

# coding:utf-8
from tkinter import *
import tkinter as tk
from words import word
import pymysql
from functools import partial


# 根窗口
class enwords_panel(object):

    def __init__(self):
        self.root = Tk()
        self.root.title('Improve Your English')  # 主窗口标题
        self.root.geometry('800x600')  # 主窗口大小，中间的为英文字母x
        self.canvas = tk.Canvas(self.root, width=800, height=600, relief='raised')
        self.canvas.pack()

        #connect to database
        conn = pymysql.connect(host='localhost',
                               user=username,
                               password=password,
                               database='en_words')

        cursor = conn.cursor()

        #data ini
        self.word_queue = []
        self.mastered_list = []
        self.en_word = StringVar()
        self.translate = StringVar()
        self.current_cur = 0
        self.show_word = ''
        self.scan_mode = 0

        self.ini_mastered(cursor)

        opreation_button(self.root,'Last', partial(self.last_word,cursor),70,500)
        opreation_button(self.root, 'Next', partial(self.next_word,cursor), 170, 500)
        opreation_button(self.root, 'Nomral Switch', partial(self.normal_switch,cursor,conn), 270, 500)
        opreation_button(self.root, 'Mastered Switch',  partial(self.mastered_switch,cursor,conn), 430, 500)
        opreation_button(self.root, 'Search Words',  partial(self.search_word,cursor), 590, 500)
        opreation_button(self.root, 'Show Words', self.show_word_func, 590, 50)
        opreation_button(self.root, 'Scan Mode', self.scan_mode_func, 70, 50)
        #opreation_button(self.root, 'set Words', self.test, 300, 500)

        self.en_word_box = tk.Entry(self.root, textvariable=self.en_word, font="Helvetica 40 bold")
        self.en_word_box.place(x=100, y=100)
        opreation_label(self.root, self.translate, 100, 200)

        self.root.mainloop()  # 事件循环

    def show_word_func(self):
        self.translate.set(self.show_word)

    def scan_mode_func(self):
        if self.scan_mode == 0:
            self.scan_mode = 1
            self.show_word_func()
        else:
            self.scan_mode = 0
            self.translate.set('')

    def next_word(self,cursor):
        # fetch a new word
        fetch_word = word.fetch_word(cursor, self.mastered_list, self.word_queue,self.current_cur)
        #don't know why curret_cur cannot be updated in function, so write here
        self.current_cur +=1
        self.en_word.set(fetch_word[0])
        self.show_word = fetch_word[1]
        if self.scan_mode:
            self.translate.set(fetch_word[1])
        else:
            self.translate.set('')
        #print(self.word_queue, self.current_cur)

    def last_word(self,cursor):
        # don't know why curret_cur cannot be updated in function, so write here
        if self.current_cur > 1:
            self.current_cur -= 1
        fetch_word = word.pure_fetch(cursor,self.word_queue,self.current_cur)
        self.en_word.set(fetch_word[0])
        self.show_word = fetch_word[1]
        if self.scan_mode:
            self.translate.set(fetch_word[1])
        else:
            self.translate.set('')
        #print(self.word_queue, self.current_cur)

    def search_word(self,cursor):
        search_word_res = word.search_word(cursor,self.en_word.get())
        self.en_word.set(search_word_res[0])
        self.show_word = search_word_res[1]
        if self.scan_mode:
            self.translate.set(search_word_res[1])
        else:
            self.translate.set('')

    def normal_switch(self,cursor,conn):
        word.change_normal_data(cursor,self.en_word.get(),conn)


    def mastered_switch(self,cursor,conn):
        id = word.change_mastered_data(cursor, self.en_word.get(),conn)
        self.mastered_list.append(id)

    def disconnet(self,conn,cursor):
        # 关闭数据库连接
        cursor.close()
        conn.close()

    def fetch_word(self):
        #103976 words
        pass

    def ini_mastered(self,cursor):

        #get mastered list
        cursor.execute("SELECT id FROM enwords WHERE mastered=1")
        res = cursor.fetchall()
        for i in res:
            self.mastered_list.append(i[0])

        print("Recent Mastered List:" ,self.mastered_list)

        #fetch a new word
        fetch_word = word.fetch_word(cursor,self.mastered_list,self.word_queue,self.current_cur)
        self.current_cur += 1
        self.en_word.set(fetch_word[0])
        self.show_word = fetch_word[1]
        if self.scan_mode:
            self.translate.set(fetch_word[1])

class opreation_button:
    """
    you should pass, the root window, text, command, and pos
    """

    def __init__(self,root,text,command,pos_x,pos_y):
        self.root = root
        self.text = text
        self.command = command
        self.pos_x = pos_x
        self.pos_y = pos_y

        tk.Button(self.root, text=self.text, command=self.command).place(x=pos_x, y=pos_y)

class opreation_label:
    def __init__(self,root,text,pos_x,pos_y,font=("Times New Roman", 35),fg='black'):
        self.root = root
        self.text = tk.StringVar()
        self.text = text
        self.font = font
        self.pos_x = pos_x
        self.pos_y = pos_y
        self.fg = fg

        tk.Label(self.root, textvariable=self.text, font=self.font, fg=fg,justify = 'left',wraplength = 600).place(x=pos_x, y=pos_y)


enwords_panel()

　

word.py

import random


def change_mastered_data(cursor,word,conn):

    cursor.execute("SELECT id,mastered FROM enwords WHERE word=\'"+word+"\'")
    result = cursor.fetchone()
    print(result)

    if result[1]:
        print("TO Zero")
        cursor.execute("UPDATE en_words.enwords SET mastered=0 WHERE word=\'"+word+"\'")
    else:
        print("TO non-Zero")
        cursor.execute("UPDATE en_words.enwords SET mastered=1 WHERE word=\'" + word + "\'")
    conn.commit()
    return result[0]

def change_normal_data(cursor,word,conn):

    cursor.execute("SELECT normal FROM enwords WHERE word=\'"+word+"\'")
    result = cursor.fetchone()[0]
    if result:
        print("TO Zero")
        cursor.execute("UPDATE en_words.enwords SET normal=0 WHERE word=\'"+word+"\'")
    else:
        print("TO non-Zero")
        cursor.execute("UPDATE en_words.enwords SET normal=1 WHERE word=\'" + word + "\'")

    conn.commit()

def fetch_word(cursor,list,queue,current_cur):

    if current_cur == len(queue):
        id = random.randint(1, 103976)
        while id  in list:
            id = random.randint(1, 103976)
        else:
            cursor.execute("SELECT word,translation FROM enwords WHERE id=" + str(id))
            queue.append(id)
            res = cursor.fetchall()
            return res[0]

    else:
        id = queue[current_cur]
        #print(id)
        cursor.execute("SELECT word,translation FROM enwords WHERE id=" + str(id))
        res = cursor.fetchall()
        return res[0]

def pure_fetch(cursor,queue,current_cur):

    if current_cur >= 1:
        id = queue[current_cur-1]
    else:
        id = queue[0]
    cursor.execute("SELECT word,translation FROM enwords WHERE id=" + str(id))
    res = cursor.fetchall()

    return res[0]

def search_word(cursor,word):

    try:
        cursor.execute("SELECT word,translation FROM enwords WHERE word=\'" + word + "\'")
        res = cursor.fetchall()
        return res[0]
    except:
        return ('no result','没有结果')

enwords.sql下载链接：

https://github.com/1eez/103976/blob/master/EnWords.sql