words_db.py

import sqlite3
from loguru import logger

DB_PATH = './database/word_database.db'

class WordsDB(object):
    def __init__(self):
        logger.info('Initialized words database.')
    
    def _connect_db(self):
        conn = sqlite3.connect(
            DB_PATH,
            timeout=10, 
            check_same_thread=False)
        cursor = conn.cursor()
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS words (
            id INTEGER PRIMARY KEY,
            word TEXT NOT NULL,
            definition TEXT NOT NULL
        )
        ''')
        return conn, conn.cursor()


    def add_word(self, word, definition):
        self.conn, self.cursor = self._connect_db()
        self.cursor.execute('INSERT INTO words (word, definition) VALUES (?, ?)', (word, definition))
        self.conn.commit()
        self.cursor.close()
        self.conn.close()
        

    def delete_word(self, word):
        self.conn, self.cursor = self._connect_db()
        self.cursor.execute('DELETE FROM words WHERE word = ?', (word,))
        self.conn.commit()
        self.cursor.close()
        self.conn.close()

    def update_word(self, word, new_definition):
        self.conn, self.cursor = self._connect_db()
        self.cursor.execute('UPDATE words SET definition = ? WHERE word = ?', (new_definition, word))
        self.conn.commit()
        self.cursor.close()
        self.conn.close()

    def query_word(self):
        self.conn, self.cursor = self._connect_db()
        self.cursor.execute('SELECT * FROM words')
        res = self.cursor.fetchall()
        self.cursor.close()
        self.conn.close()
        return res
    

words_db = WordsDB()

if __name__ == '__main__':

    words_db.add_word('apple', '苹果')
    words_db.add_word('banana', '香蕉')
    words_db.update_word('banana', '新的香蕉')
    result = words_db.query_word('banana')
    print(result)