Inserting rows in a MySQL database using Python (Tkinter)

Question

I'm trying to insert rows in a MySQL database using the treeview widget in Tkinter.

The entire data set is supposed to be entered into the table when I execute this code. But I'm only getting an empty string in the places where I gave StringVar.get() and zeroes in the place where I gave IntVar.get(). What's wrong with this code? I tried my best to debug, but I could not.

from tkinter import *
import tkinter as tk
import mysql.connector
from tkinter import messagebox

r = tk.Tk()
r.title("Book Details")
r.geometry("500x500")

connect = mysql.connector.connect(host="localhost", user="root", passwd="1234", database="bookdat")
conn = connect.cursor()
conn.execute("select * from bookinfo")

tree = ttk.Treeview(r)
tree["show"] = "headings"

tree["columns"] = ("Name", "Author", "Edition", "Category", "Cost", "Owner_Address", "Owner_phoneno")

tree.column("Name", width=50, minwidth=50, anchor=tk.CENTER)
tree.column("Author", width=100, minwidth=100, anchor=tk.CENTER)
tree.column("Edition", width=50, minwidth=50, anchor=tk.CENTER)
tree.column("Category", width=150, minwidth=150, anchor=tk.CENTER)
tree.column("Cost", width=150, minwidth=150, anchor=tk.CENTER)
tree.column("Owner_Address", width=150, minwidth=150, anchor=tk.CENTER)
tree.column("Owner_phoneno", width=150, minwidth=150, anchor=tk.CENTER)

tree.heading("Name", text="Name", anchor=tk.CENTER)
tree.heading("Author", text="Author", anchor=tk.CENTER)
tree.heading("Edition", text="Edition", anchor=tk.CENTER)
tree.heading("Category", text="Category", anchor=tk.CENTER)
tree.heading("Cost", text="Cost", anchor=tk.CENTER)
tree.heading("Owner_Address", text="Owner's Address", anchor=tk.CENTER)
tree.heading("Owner_phoneno", text="Owner's contact no", anchor=tk.CENTER)

i = 0
for ro in conn:
    tree.insert('', i, text="", values=(ro[0], ro[1], ro[2], ro[3], ro[4], ro[5], ro[6]))
    i += 1

hsb = ttk.Scrollbar(r, orient="horizontal")

hsb.configure(command=tree.xview)
tree.configure(xscrollcommand=hsb.set)
hsb.pack(fill=X, side=BOTTOM)

vsb = ttk.Scrollbar(r, orient="vertical")
vsb.configure(command=tree.yview)
tree.configure(yscrollcommand=vsb.set)
vsb.pack(fill=Y, side=RIGHT)

tree.pack()

name = tk.StringVar()
author = tk.StringVar()
edition = tk.IntVar()
category = tk.StringVar()
cost = tk.IntVar()
owner_address = tk.StringVar()
owner_phoneno = tk.IntVar()

def add_data(tree):
    f = Frame(r, width=400, height=320, background="grey")
    f.place(x=100, y=250)
    l1 = Label(f, text="name", width=8, font=("Times", 11, "bold"))
    e1 = Entry(f, textvariable=name, width=25)
    l1.place(x=50, y=30)
    e1.place(x=170, y=30)

    l2 = Label(f, text="author", width=8, font=("Times", 11, "bold"))
    e2 = Entry(f, textvariable=author, width=25)
    l2.place(x=50, y=70)
    e2.place(x=170, y=70)

    l3 = Label(f, text="edition", width=8, font=("Times", 11, "bold"))
    e3 = Entry(f, textvariable=edition, width=25)
    l3.place(x=50, y=110)
    e3.place(x=170, y=110)
    e3.delete(0, END)

    l4 = Label(f, text="category", width=8, font=("Times", 11, "bold"))
    e4 = Entry(f, textvariable=category, width=25)
    l4.place(x=50, y=150)
    e4.place(x=170, y=150)

    l5 = Label(f, text="cost", width=8, font=("Times", 11, "bold"))
    e5 = Entry(f, textvariable=cost, width=25)
    l5.place(x=50, y=190)
    e5.place(x=170, y=190)
    e5.delete(0, END)

    l6 = Label(f, text="address", width=8, font=("Times", 11, "bold"))
    e6 = Entry(f, textvariable=owner_address, width=25)
    l6.place(x=50, y=230)
    e6.place(x=170, y=230)

    l7 = Label(f, text="phoneno", width=8, font=("Times", 11, "bold"))
    e7 = Entry(f, textvariable=owner_phoneno, width = l7.place(x=50, y=270)
    e7.place(x=170, y=270)
    e7.delete(0, END)

    def insert_data():
        nonlocal e1, e2, e3, e4, e5, e6, e7
        s = name.get()
        a = author.get()
        e = edition.get()
        cat = category.get()
        cst = cost.get()
        addr = owner_address.get()
        ph = owner_phoneno.get()
        conn.execute("INSERT INTO bookinfo(name,author,edition,category,cost,owner_address,owner_phoneno) VALUES(%s,%s,%s,%s,%s,%s,%s)",(s,a,e,cat,cst,addr,ph))
        connect.commit()
        tree.insert('', "end", text="", values=(s, a, e, cat, cst, addr, ph))
        messagebox.showinfo("Success", "Data registered")

        e1.delete(0, END)
        e2.delete(0, END)
        e3.delete(0, END)
        e4.delete(0, END)
        e5.delete(0, END)
        e6.delete(0, END)
        e7.delete(0, END)
        f.destroy()

    submitbutton = tk.Button(f, text="Submit", command=insert_data)
    submitbutton.configure(font=("Times 11 bold"), bg="PaleTurquoise3", fg="white")
    submitbutton.place(x=100, y=280)

    cancelbutton = tk.Button(f, text="Cancel", command=f.destroy)
    cancelbutton.configure(font=("Times 11 bold"), bg="PaleTurquoise3", fg="white")
    cancelbutton.place(x=240, y=280)


insertbutton = tk.Button(r, text="Insert", command=lambda:add_data(tree))
insertbutton.configure(font=("calibri 14 bold"), bg="PaleTurquoise3", fg="white")
insertbutton.place(x=600, y=260)

r.mainloop()
'''

Your code works fine after fixing some typos and adding `from tkinter import ttk`. Did you call this file from another file which has already created an instance of `Tk()`? — acw1668, Apr 27 '23 at 08:32
It is not recommended to have more than one instance of `Tk()`, so use `Toplevel()` in the posted code instead. — acw1668, Apr 27 '23 at 09:47
Along with the rest of the import statements... The code which I've posted is only a part of the whole program — Varsha Kumaraguru, Apr 27 '23 at 10:02

Ovski · Answer 1 · 2023-04-27T21:53:11.657

You should introduce some value checking because this will crash if you enter alpha characters into the number fields.

There are syntax errors, but other then that it seems to work. I had pycharm check the code, imported whats needed and tested in on a local XAMP database.

If you still get the problem, I need to know what data you enter.

Changes:

Import ttk: from tkinter import messagebox, ttk

Missing bracket: e7 = Entry(f, textvariable=owner_phoneno, width=l7.place(x=50, y=270))

Refactored code:

conn.execute(
    "INSERT INTO bookinfo("
    "name,author,edition,category,cost,owner_address,owner_phoneno"
    ") VALUES(%s,%s,%s,%s,%s,%s,%s)",
    (s, a, e, cat, cst, addr, ph))

Removed brackets from font parameters: submitbutton.configure(font="Times 11 bold", bg="PaleTurquoise3", fg="white")

This would be the correct MRE

from tkinter import *
import tkinter as tk
import mysql.connector
from tkinter import messagebox, ttk

r = tk.Tk()
r.title("Book Details")
r.geometry("500x500")

connect = mysql.connector.connect(host="localhost", user="root", passwd="", database="bookdat")
conn = connect.cursor()
conn.execute("select * from bookinfo")

tree = ttk.Treeview(r)
tree["show"] = "headings"

tree["columns"] = ("Name", "Author", "Edition", "Category", "Cost", "Owner_Address", "Owner_phoneno")

tree.column("Name", width=50, minwidth=50, anchor=tk.CENTER)
tree.column("Author", width=100, minwidth=100, anchor=tk.CENTER)
tree.column("Edition", width=50, minwidth=50, anchor=tk.CENTER)
tree.column("Category", width=150, minwidth=150, anchor=tk.CENTER)
tree.column("Cost", width=150, minwidth=150, anchor=tk.CENTER)
tree.column("Owner_Address", width=150, minwidth=150, anchor=tk.CENTER)
tree.column("Owner_phoneno", width=150, minwidth=150, anchor=tk.CENTER)

tree.heading("Name", text="Name", anchor=tk.CENTER)
tree.heading("Author", text="Author", anchor=tk.CENTER)
tree.heading("Edition", text="Edition", anchor=tk.CENTER)
tree.heading("Category", text="Category", anchor=tk.CENTER)
tree.heading("Cost", text="Cost", anchor=tk.CENTER)
tree.heading("Owner_Address", text="Owner's Address", anchor=tk.CENTER)
tree.heading("Owner_phoneno", text="Owner's contact no", anchor=tk.CENTER)

i = 0
for ro in conn:
    tree.insert('', i, text="", values=(ro[0], ro[1], ro[2], ro[3], ro[4], ro[5], ro[6]))
    i += 1

hsb = ttk.Scrollbar(r, orient="horizontal")

hsb.configure(command=tree.xview)
tree.configure(xscrollcommand=hsb.set)
hsb.pack(fill=X, side=BOTTOM)

vsb = ttk.Scrollbar(r, orient="vertical")
vsb.configure(command=tree.yview)
tree.configure(yscrollcommand=vsb.set)
vsb.pack(fill=Y, side=RIGHT)

tree.pack()

name = tk.StringVar()
author = tk.StringVar()
edition = tk.IntVar()
category = tk.StringVar()
cost = tk.IntVar()
owner_address = tk.StringVar()
owner_phoneno = tk.IntVar()


def add_data(tree):
    f = Frame(r, width=400, height=320, background="grey")
    f.place(x=100, y=250)
    l1 = Label(f, text="name", width=8, font=("Times", 11, "bold"))
    e1 = Entry(f, textvariable=name, width=25)
    l1.place(x=50, y=30)
    e1.place(x=170, y=30)

    l2 = Label(f, text="author", width=8, font=("Times", 11, "bold"))
    e2 = Entry(f, textvariable=author, width=25)
    l2.place(x=50, y=70)
    e2.place(x=170, y=70)

    l3 = Label(f, text="edition", width=8, font=("Times", 11, "bold"))
    e3 = Entry(f, textvariable=edition, width=25)
    l3.place(x=50, y=110)
    e3.place(x=170, y=110)
    e3.delete(0, END)

    l4 = Label(f, text="category", width=8, font=("Times", 11, "bold"))
    e4 = Entry(f, textvariable=category, width=25)
    l4.place(x=50, y=150)
    e4.place(x=170, y=150)

    l5 = Label(f, text="cost", width=8, font=("Times", 11, "bold"))
    e5 = Entry(f, textvariable=cost, width=25)
    l5.place(x=50, y=190)
    e5.place(x=170, y=190)
    e5.delete(0, END)

    l6 = Label(f, text="address", width=8, font=("Times", 11, "bold"))
    e6 = Entry(f, textvariable=owner_address, width=25)
    l6.place(x=50, y=230)
    e6.place(x=170, y=230)

    l7 = Label(f, text="phoneno", width=8, font=("Times", 11, "bold"))
    e7 = Entry(f, textvariable=owner_phoneno, width=25)
    l7.place(x=50, y=270)
    e7.place(x=170, y=270)
    e7.delete(0, END)

    def insert_data():
        nonlocal e1, e2, e3, e4, e5, e6, e7
        s = name.get()
        a = author.get()
        e = edition.get()
        cat = category.get()
        cst = cost.get()
        addr = owner_address.get()
        ph = owner_phoneno.get()
        conn.execute(
            "INSERT INTO bookinfo("
            "name,author,edition,category,cost,owner_address,owner_phoneno"
            ") VALUES(%s,%s,%s,%s,%s,%s,%s)",
            (s, a, e, cat, cst, addr, ph))
        connect.commit()
        tree.insert('', "end", text="", values=(s, a, e, cat, cst, addr, ph))
        messagebox.showinfo("Success", "Data registered")

        e1.delete(0, END)
        e2.delete(0, END)
        e3.delete(0, END)
        e4.delete(0, END)
        e5.delete(0, END)
        e6.delete(0, END)
        e7.delete(0, END)
        f.destroy()

    submitbutton = tk.Button(f, text="Submit", command=insert_data)
    submitbutton.configure(font="Times 11 bold", bg="PaleTurquoise3", fg="white")
    submitbutton.place(x=100, y=280)

    cancelbutton = tk.Button(f, text="Cancel", command=f.destroy)
    cancelbutton.configure(font="Times 11 bold", bg="PaleTurquoise3", fg="white")
    cancelbutton.place(x=240, y=280)


insertbutton = tk.Button(r, text="Insert", command=lambda: add_data(tree))
insertbutton.configure(font="calibri 14 bold", bg="PaleTurquoise3", fg="white")
insertbutton.place(x=600, y=260)

r.mainloop()

Screenshot

Your code still does not fix the line: `e7 = Entry(f, textvariable=owner_phoneno, width=l7.place(x=50, y=270))`. — acw1668, Apr 27 '23 at 12:06
I'm still not getting it... is it because I've already used an instance of Tk()? Because it works perfectly individually, but not with the entire program — Varsha Kumaraguru, Apr 27 '23 at 14:14
@VarshaKumaraguru Do you have problems creating a single instance of Tk()? What do you need help with? Maybe update your question or create a new one? — Ovski, Apr 27 '23 at 21:57
@Ovski I have used root=Tk() in my main program in the beginning... and then r=tk.Tk() within the main program. — Varsha Kumaraguru, Apr 28 '23 at 01:12
Is that the reason why it's not working the way I want it to? — Varsha Kumaraguru, Apr 28 '23 at 01:12
I don't know if it is the reason because I havent read your code, but it probably is. Maybe this explains it: [Why are multiple instances of Tk discouraged?](https://stackoverflow.com/questions/48045401/why-are-multiple-instances-of-tk-discouraged) — Ovski, Apr 28 '23 at 06:27

Inserting rows in a MySQL database using Python (Tkinter)

1 Answers1