[FIXED] how to fetch data stored in sqlite3 database and assign it to the variables in tkinter python

Issue

In the code below I saved value1 and value2 to the sqlite3 database and txt_ in folder named data. What I am trying to achieve here is that when I rerun the programme and open the file, txt_ file should be open in the text area with the lines I added when I saved it. And when I click add button, value1 and value2 should be updated and newly created line should be in the next line. Let me know if my method is correct, if not then please tell me the better one.

CODE:

from tkinter import *
from tkinter import messagebox
import sqlite3
import os

root = Tk()
root.geometry('400x400')

var_e = StringVar(None)
def create_my_db():
    conn = sqlite3.connect(database=r'my db.db')
    cur = conn.cursor()
    cur.execute("""CREATE TABLE IF NOT EXISTS "myLogs"
                (
                    "int_value" INTEGER,
                    "float_value" REAL
                )
                """)
    conn.commit()
create_my_db()

def add_lbl():
    global value1, value2
    value1 += 1
    value2 += 1
    sample = f'This is line {value1} which has value of {value2}\n'
    txt_.insert(END, sample)

def save():
    conn = sqlite3.connect(database=r'my db.db')
    cur = conn.cursor()
    cur.execute("""INSERT INTO myLogs VALUES (?,?)""",
                (
                    value1,
                    value2
                )
                )
    conn.commit()

    # labels to check if the values are stored in the database
    values_lbl.config(text=f'value 1 is [ {value1} ] & value 2 is [ {value2} ]')

def save_txt():
    file_txt = open(f'data/{value1}.txt', 'w')
    file_txt.write(txt_.get(1.0, END))
    file_txt.close()
    messagebox.showinfo('SAVED', 'Data saved to the database.')

def open_():
    for txt_file in os.listdir("data/"):
        if txt_file.split('.')[0] == f'{var_e.get()}':
            file_ = open(f"data/{txt_file}", "r")
            for i in file_:
                txt_.insert(END, i)
            file_.close()

value1 = 0
value2 = 0.9

values_lbl = Label(root, text=f'value 1 is [ {value1} ] & value 2 is [ {value2} ]')
values_lbl.pack()

btn_frame = Frame(root)
btn_frame.pack()

btn_add = Button(btn_frame, text='Add', command=add_lbl)
btn_add.pack(side=LEFT)

e = Entry(btn_frame, textvariable=var_e)
e.pack(side=LEFT)

btn_open = Button(btn_frame, text='Open', command=open_)
btn_save = Button(btn_frame, text='Save', command=lambda:[save(), save_txt()])
btn_open.pack(side=LEFT)
btn_save.pack(side=LEFT)

txt_ = Text(root)
txt_.pack(fill=BOTH, expand=True)

root.mainloop()

Solution

When I posted this question I didn't know how to run the query to update value1 and value2, that's why I didn't mention the query in open_() function. Now I came to know how that query should have been done. So in the below code I added the query to open_() function. Now the complete programme runs fine.

def open_():
    global value1, value2
    txt_.delete(1.0, END)
    for txt_file in os.listdir("data/"):
        if txt_file.split('.')[0] == f'{var_e.get()}':
            file_ = open(f"data/{txt_file}", "r")
            for i in file_:
                txt_.insert(END, i)
            file_.close()

    conn = sqlite3.connect(database=r'my db.db')
    cur = conn.cursor()

    cur.execute("""SELECT * FROM myLogs WHERE int_value=?""", (var_e.get(),))
    row = cur.fetchone()
    if row is None:
        messagebox.showerror("ERROR", 'Invalid input.')
    else:
        value1 = row[0]
        value2 = row[1]
    conn.commit()

Answered By - Milind Khobragade

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)