K
K
kossav2019-05-18 05:35:57
Python
kossav, 2019-05-18 05:35:57

How to update an empty string in sqlite3 in Python?

Good day.
Help to understand Sql. I'm trying to update a single row in the database, but a simple UPDATE fails to update an empty record.
I try to add a line if the base is empty, if not, then replace it. But for some reason .fetchall() and .fetchone() don't want to be empty.
What's wrong here?

import tkinter as tk
from tkinter import ttk
import sqlite3



class Main(tk.Frame):
    def __init__(self, root):
        super().__init__(root)
        self.init_main()
        self.db = db
        self.view_records()

    def init_main(self):
        toolbar = tk.Frame(bg='#d7d8e0', bd=2)
        toolbar.pack(side=tk.TOP, fill=tk.X)

        self.add_img = tk.PhotoImage(file='savee.gif')
        btn_open_dialog = tk.Button(toolbar, text='Настройки', command=self.open_dialog, bg='#d7d8e0', bd=0,
                                    compound=tk.TOP, image=self.add_img)
        btn_open_dialog.pack(side=tk.RIGHT)

        self.add_img2 = tk.PhotoImage(file='savee.gif')
        btn_open_dialog2 = tk.Button(toolbar, text='Пуск', command=self.open_dialog2, bg='#d7d8e0', bd=0,
                                    compound=tk.TOP, image=self.add_img)
        btn_open_dialog2.pack(side=tk.LEFT)

        self.tree = ttk.Treeview(self, columns=('url', 'rep', 'obem', 'nick'), height=15, show='headings')

        self.tree.column('url', width=200, anchor=tk.CENTER)
        self.tree.column('rep', width=200, anchor=tk.CENTER)
        self.tree.column('obem', width=200, anchor=tk.CENTER)
        self.tree.column('nick', width=200, anchor=tk.CENTER)

        self.tree.heading('url', text='url')
        self.tree.heading('rep', text='rep')
        self.tree.heading('obem', text='obem')
        self.tree.heading('nick', text='nick')

        self.tree.pack()

    def records(self, url, rep, obem, nick):
        self.db.insert_data(url, rep, obem, nick)
        self.view_records()

    def view_records(self):
        self.db.c.execute('''SELECT * FROM finance''')
        [self.tree.delete(i) for i in self.tree.get_children()]
        [self.tree.insert('', 'end', values=row) for row in self.db.c.fetchall()]
        print(self.db.c.fetchone())
        print(self.tree.get_children)


    def open_dialog(self):
        Child()
    def open_dialog2(self):
        Child()

class Child(tk.Toplevel):
    def __init__(self):
        super().__init__(root)
        self.init_child()
        self.view = app

    def init_child(self):                  
        self.title('Настройки')
        self.geometry('500x250+400+300')
        self.resizable(False, False)

        label_url = tk.Label(self, text='URL:')
        label_url.place(x=20, y=20)
        label_rep = tk.Label(self, text='реп')
        label_rep.place(x=20, y=50)
        label_obem = tk.Label(self, text='обьем <')
        label_obem.place(x=20, y=80)
        label_nick = tk.Label(self, text='Nickname')
        label_nick.place(x=20, y=110)

        self.entry_url = ttk.Entry(self)             
        self.entry_url.place(x=50, y=20)
        self.entry_rep = ttk.Entry(self)             
        self.entry_rep.place(x=220, y=50)
        self.entry_obem = ttk.Entry(self)              
        self.entry_obem.place(x=220, y=80)
        self.entry_nick = ttk.Entry(self)             
        self.entry_nick.place(x=220, y=110)

        btn_cancel = ttk.Button(self, text='Закрыть', command=self.destroy)
        btn_cancel.place(x=300, y=170)

        btn_ok = ttk.Button(self, text='Сохранить')                         
        btn_ok.place(x=220, y=170)
        btn_ok.bind('<Button-1>', lambda event: self.view.records(self.entry_url.get(),
                                                                  self.entry_rep.get(),
                                                                  self.entry_obem.get(),
                                                                  self.entry_nick.get()))


        self.grab_set()
        self.focus_set()

class DB:
    def __init__(self):
        self.conn = sqlite3.connect('finance.db')
        self.c = self.conn.cursor()
        self.c.execute(
            '''CREATE TABLE IF NOT EXISTS finance (url text, rep integer, obem integer, nick text)''')
        self.conn.commit()

    def insert_data(self, url, rep, obem, nick):
        self.c.execute('''SELECT * FROM finance WHERE url''')

        if self.c.fetchone() == False:
            self.c.execute('''INSERT INTO finance(url, rep, obem, nick) VALUES (?, ?, ?, ?)''')
        else:
            self.c.execute('''UPDATE finance set url=?, rep=?, obem=?, nick=?''', (url, rep, obem, nick))
        self.conn.commit()

if __name__ == "__main__":
    root = tk.Tk()
    db = DB()
    app = Main(root)
    app.pack()
    root.title("тестовая")
    root.geometry("850x550+300+200")
    root.resizable(False, False)
    root.mainloop()

Answer the question

In order to leave comments, you need to log in

2 answer(s)
D
Dimonchik, 2019-05-18
@dimonchik2013

https://sqlitebrowser.org/ here you train queries, then insert them into the program

K
kova1ev, 2019-05-18
@kova1ev

fetchone()
Fetches the next row of a query result set, returning a single sequence, or None when no more data is available.
fetchall()
Fetches all (remaining) rows of a query result, returning a list. Note that the cursor's arraysize attribute can affect the performance of this operation. An empty list is returned when no rows are available
from the documentation try replacing False with None

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question