Python Forum

Hi all,
I have made a form in Tkinter to make entries in MySql database. I would like the Sr. No. entry box to display the next available number. i.e if i have 5 entries at 1,2,3,4,5 and I have to add another the sr.no. entry box should automatically show No.6. My present code is

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

mydb= mysql.connector.connect(host="localhost",user="root", password="mudit",database="Clinicmaster",auth_plugin="mysql_native_password")
cursor=mydb.cursor()

def addwork():
    
    srno=ent3.get()
    work=ent4.get()
   
    sql=("INSERT INTO workrequired(srno,work)"  "VALUES(%s,%s)" ) 
    cursor.execute(sql,(srno,work))
    mydb.commit()
    print("DONE")
    return True
   
win=Tk()
win.title("ADD Work")
win.geometry("300x250")
win.configure(background='light blue')
win.resizable(False,False)

frm1=Frame(win,bg="light blue")
frm1.pack(side=tk.LEFT,padx=20)

var3=StringVar()
srno=StringVar()
var4=StringVar()
workrequired=StringVar()

label3=Label(frm1,textvariable=var3,bg="light blue")
var3.set("Sr.No.")
label3.grid(row=2,column=1,padx=10,pady=10)

ent3=Entry(frm1,textvariable=srno,width=10)
srno.set(" ")
ent3.grid(row=2,column=2,sticky=tk.W,padx=10,pady=10)

label4=Label(frm1,textvariable=var4,bg="light blue")
var4.set("Work Required")
label4.grid(row=3,column=1,padx=10,pady=10)

ent4=Entry(frm1,textvariable=workrequired)
workrequired.set(" ")
ent4.grid(row=3,column=2,padx=10,pady=10)

btn=Button(frm1, text="ADD",command=addwork)
btn.grid(row=5,column=2,padx=10,pady=10)

win.mainloop()

Get a row count from the database and add 1to display the next number

(Sep-13-2021, 07:31 AM)menator01 Wrote: [ -> ]Get a row count from the database and add 1to display the next number

I am fairly new to python can you please give a code example.

This is with sqlite3 but queries are the same

#! /usr/bin/env python3
import sqlite3
import tkinter as tk

conn = sqlite3.connect('test.db')
cursor = conn.cursor()
cursor.execute('drop table if exists test_table')
conn.commit()

conn = sqlite3.connect('test.db')
cursor = conn.cursor()
table = '''create table test_table(
            id integer primary key autoincrement not null,
            name varchar(100) not null
        )'''
conn.execute(table)
conn.commit()

names = [(1,'tom'), (2, 'harry'), (3, 'john'),(4, 'mary'), (5, 'pam')]

conn = sqlite3.connect('test.db')
cursor = conn.cursor()
cursor.executemany('insert into test_table (id, name) values (?, ?)', names)
conn.commit()

count = cursor.execute('select count(*) from test_table')
next_num = count.fetchall()[0][0]
conn.close()

root = tk.Tk()
var = tk.IntVar()
var.set(next_num+1)
field1 = tk.Entry(root, text=var, width=5)
field1.grid(column=0, row=0)

field2 = tk.Entry(root, width=20)
field2.grid(column=1, row=0)

label = tk.Label(root, text=f'Current entries: {var.get()-1}')
label.grid(column=0, row=1, sticky='w')

btn = tk.Button(root, text='Submit')
btn.grid(column=1, row=1, sticky='e')
root.mainloop()

(Sep-13-2021, 09:11 AM)menator01 Wrote: [ -> ]This is with sqlite3 but queries are the same

#! /usr/bin/env python3
import sqlite3
import tkinter as tk

conn = sqlite3.connect('test.db')
cursor = conn.cursor()
cursor.execute('drop table if exists test_table')
conn.commit()

conn = sqlite3.connect('test.db')
cursor = conn.cursor()
table = '''create table test_table(
            id integer primary key autoincrement not null,
            name varchar(100) not null
        )'''
conn.execute(table)
conn.commit()

names = [(1,'tom'), (2, 'harry'), (3, 'john'),(4, 'mary'), (5, 'pam')]

conn = sqlite3.connect('test.db')
cursor = conn.cursor()
cursor.executemany('insert into test_table (id, name) values (?, ?)', names)
conn.commit()

count = cursor.execute('select count(*) from test_table')
next_num = count.fetchall()[0][0]
conn.close()

root = tk.Tk()
var = tk.IntVar()
var.set(next_num+1)
field1 = tk.Entry(root, text=var, width=5)
field1.grid(column=0, row=0)

field2 = tk.Entry(root, width=20)
field2.grid(column=1, row=0)

label = tk.Label(root, text=f'Current entries: {var.get()-1}')
label.grid(column=0, row=1, sticky='w')

btn = tk.Button(root, text='Submit')
btn.grid(column=1, row=1, sticky='e')
root.mainloop()

Thanks I tried but obviously I am doing something wrong. next_num is defined inside the function and i am calling it in a variable var3 outside in the frame. am getting an error next-num not defined. here is my code.

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

mydb= mysql.connector.connect(host="localhost",user="root", password="mudit",database="Clinicmaster",auth_plugin="mysql_native_password")
cursor=mydb.cursor()

def addwork():
    
    srno=ent3.get()
    work=ent4.get()
   
    sql=("INSERT INTO workrequired(srno,work)"  "VALUES(%s,%s)" ) 
    cursor.execute(sql,(srno,work))
    mydb.commit()

    count = cursor.execute('select count(*) from workrequired')
    next_num = count.fetchall()[0][0]
    mydb.close()
    print("DONE")
    return True
   
win=Tk()
win.title("ADD Work")
win.geometry("300x250")
win.configure(background='light blue')
win.resizable(False,False)

frm1=Frame(win,bg="light blue")
frm1.pack(side=tk.LEFT,padx=20)

var3=IntVar()
var3.set(next_num+1)
srno=IntVar()
var4=StringVar()
workrequired=StringVar()

label3=Label(frm1,intvariable=var3,bg="light blue")
var3.set("Sr.No.")
label3.grid(row=2,column=1,padx=10,pady=10)

ent3=Entry(frm1,textvariable=srno,width=10)
srno.set(" ")
ent3.grid(row=2,column=2,sticky=tk.W,padx=10,pady=10)

label4=Label(frm1,textvariable=var4,bg="light blue")
var4.set("Work Required")
label4.grid(row=3,column=1,padx=10,pady=10)

ent4=Entry(frm1,textvariable=workrequired)
workrequired.set(" ")
ent4.grid(row=3,column=2,padx=10,pady=10)

btn=Button(frm1, text="ADD",command=addwork)
btn.grid(row=5,column=2,padx=10,pady=10)

win.mainloop()

[output]Traceback (most recent call last):
  File "C:\Users\INDIAN\Desktop\python exercises\order form\Add Work - Copy - Copy.py", line 33, in <module>
    var3.set(next_num+1)
NameError: name 'next_num' is not defined
>>> 
[/output]

I would put it in its own function. As it stands now you are calling it when you insert the form data.

I done a version of your code

#! /usr/bin/env python3

# Do the imports
import sqlite3
import tkinter as tk
from functools import partial


# Create/Connect to the sqlite3 database
conn = sqlite3.connect('test.db')
cursor = conn.cursor()
cursor.execute('drop table if exists test_table')
conn.commit()

# Create the table
conn = sqlite3.connect('test.db')
cursor = conn.cursor()
table = '''create table test_table(
            id integer primary key autoincrement not null,
            name varchar(100) not null
        )'''
conn.execute(table)
conn.commit()

# A list of entries for the database
names = [(1,'tom'), (2, 'harry'), (3, 'john'),(4, 'mary'), (5, 'pam')]

# Insert the data into the test database
conn = sqlite3.connect('test.db')
cursor = conn.cursor()
cursor.executemany('insert into test_table (id, name) values (?, ?)', names)
conn.commit()
conn.close()

# A couple of global variables
database = 'test.db'
table = 'test_table'

# Create the class for the widget
class Window:
    def __init__(self, parent):
        self.parent = parent
        self.parent.columnconfigure(0, weight=1)
        self.parent.rowconfigure(0, weight=1)


        # Create the id columns and rows for the entry fields
        id_label = tk.Label(self.parent, text='Next ID:', anchor='w')
        id_label['bg'] = 'light blue'
        id_label['relief'] = 'ridge'
        id_label.grid(column=0, row=0, sticky='new')
        self.count = tk.IntVar()
        self.count.set(self.get_count())
        id_number = tk.Entry(self.parent, text=self.count)
        id_number.grid(column=1, row=0, sticky='new')

        work_label = tk.Label(self.parent, text='Work Required:', anchor='w')
        work_label['bg'] = 'light blue'
        work_label['relief'] = 'ridge'
        work_label.grid(column=0,row=1, sticky='new', pady=5)

        string = tk.StringVar()

        self.entry = tk.Entry(self.parent, textvariable=string)
        self.entry.focus()
        self.entry.grid(column=1, row=1, sticky='new', pady=5)

        # Create the submit button
        btn = tk.Button(self.parent, text='Submit', command=partial(self.addwork, id_number, self.entry))
        btn.grid(column=1, row=2, sticky='new')
        self.parent.bind('<Return>', partial(self.addwork, id_number, self.entry))
        self.parent.bind('<KP_Enter>', partial(self.addwork, id_number, self.entry))

        # Create a container frame for the data
        self.dataframe = tk.Frame(self.parent)
        self.dataframe['relief'] = 'flat'
        self.dataframe['borderwidth'] = 1
        self.dataframe['highlightbackground'] = 'black'
        self.dataframe['highlightcolor'] = 'black'
        self.dataframe['highlightthickness'] = 1
        self.dataframe.grid(column=0, row=3, columnspan=2, sticky='new', pady=8)
        self.dataframe.grid_columnconfigure(0, weight=1)
        self.dataframe.grid_columnconfigure(1, weight=3)

        # Create a header row
        label = tk.Label(self.dataframe, text='ID', anchor='w', relief='raised', bg='lightblue')
        label.grid(column=0, row=0, sticky='new')
        label = tk.Label(self.dataframe, text='Name', anchor='w', relief='raised', bg='lightblue')
        label.grid(column=1, row=0, sticky='new')

        # Get the data and display
        row = 1
        for data in self.get_info():
            label = tk.Label(self.dataframe, text=data[0], anchor='w', bg='lightblue')
            label['relief'] = 'ridge'
            label['padx'] = 8
            label.grid(column=0, row=row, sticky='new')
            row += 1

        row = 1
        for data in self.get_info():
            label = tk.Label(self.dataframe, text=data[1].capitalize(), anchor='w', bg='lightblue')
            label['relief'] = 'ridge'
            label['padx'] = 8
            label.grid(column=1, row=row, sticky='new')
            row += 1


    # Create the method for inserting the submitted data into the database
    def addwork(self, id_number, entry, event=None):
        info = [(id_number.get(), entry.get())]
        conn = sqlite3.connect(database)
        conn.executemany(f'insert into {table}(id, name) values (?,?)', info)
        conn.commit()
        conn.close()

        # Update the count display and clear the entry field
        update = self.get_count()
        self.count.set(update)
        self.entry.delete(0, tk.END)

        # Update the displayed data entries
        row = 1
        for data in self.get_info():
            label = tk.Label(self.dataframe, text=data[0], anchor='w', bg='lightblue')
            label['relief'] = 'ridge'
            label['padx'] = 8
            label.grid(column=0, row=row, sticky='new')
            row += 1

        row = 1
        for data in self.get_info():
            label = tk.Label(self.dataframe, text=data[1].capitalize(), anchor='w', bg='lightblue')
            label['relief'] = 'ridge'
            label['padx'] = 8
            label.grid(column=1, row=row, sticky='new')
            row += 1


    # Method for getting entry count
    def get_count(self):
        conn = sqlite3.connect(database)
        cursor = conn.cursor()
        count = conn.execute(f'select count(*) from {table}')
        next_num = count.fetchall()[0][0]+1
        conn.close()
        return next_num

    # Method for getting the data from the database
    def get_info(self):
        conn = sqlite3.connect(database)
        cursor = conn.cursor()
        data = conn.execute(f'select * from {table}')
        info = data.fetchall()
        conn.close()
        return info

# Create the root window and init the Window class
def main():
    root = tk.Tk()
    root.title('ADD Work')
    root.geometry('+250+250')
    root.resizable(0, 0)
    root['bg'] = 'light blue'
    root['padx'] = 10
    root['pady'] = 5
    Window(root)
    root.mainloop()

if __name__ == '__main__':
    main()

(Sep-16-2021, 12:19 AM)menator01 Wrote: [ -> ]I done a version of your code

class Window:
    def __init__(self, parent):
        self.parent = parent
        self.parent.columnconfigure(0, weight=1)
        self.parent.rowconfigure(0, weight=1)

        container = tk.Frame(self.parent)
        container.grid(column=0, row=0, sticky='new')
        container.grid_columnconfigure(0, weight=3)
        container.grid_columnconfigure(1, weight=3)

        id_label = tk.Label(self.parent, text='Next ID:', anchor='w')
        id_label['bg'] = 'light blue'
        id_label['relief'] = 'ridge'
        id_label.grid(column=0, row=0, sticky='new')
        self.count = tk.IntVar()
        self.count.set(self.get_count())
        id_number = tk.Entry(self.parent, text=self.count)
        id_number.grid(column=1, row=0, sticky='new')

        work_label = tk.Label(self.parent, text='Work Required:', anchor='w')
        work_label['bg'] = 'light blue'
        work_label['relief'] = 'ridge'
        work_label.grid(column=0,row=1, sticky='new', pady=5)

        string = tk.StringVar()

        self.entry = tk.Entry(self.parent, textvariable=string)
        self.entry.focus()
        self.entry.grid(column=1, row=1, sticky='new', pady=5)

        btn = tk.Button(self.parent, text='Submit', command=partial(self.addwork, id_number, self.entry))
        btn.grid(column=1, row=2, sticky='new')



    def addwork(self, id_number, entry, event=None):
        info = [(id_number.get(), entry.get())]
        conn = sqlite3.connect('test.db')
        conn.executemany(f'insert into test_table (id, name) values (?,?)', info)
        conn.commit()
        conn.close()

        update = self.get_count()
        self.count.set(update)
        self.entry.delete(0, tk.END)


    def get_count(self):
        conn = sqlite3.connect('test.db')
        cursor = conn.cursor()
        count = conn.execute('select count(*) from test_table')
        next_num = count.fetchall()[0][0]+1
        conn.close()
        return next_num

def main():
    root = tk.Tk()
    root.title('ADD Work')
    root.geometry('+250+250')
    root.resizable(0, 0)
    root['bg'] = 'light blue'
    root['padx'] = 10
    root['pady'] = 5
    Window(root)
    root.mainloop()

if __name__ == '__main__':
    main()

Thanks a lot.