Unique id with Date and string from Database

Today's date as string

from datetime import date
dt=date.today().strftime('%Y-%m-%d') # Today's date as string

zfill()

id_auto=str(id.lastrowid) # convert id to string
id_str='AB'+dt+id_auto.zfill(5) # AB2023-04-1900009

Connection to Database

from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_tutorial")
#my_conn = create_engine("sqlite:///G:\\testing\\my_db\\my_db.db")#SQLite 

1. Importing Required Modules

from datetime import date
import tkinter as tk
from tkinter import *
from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError

• datetime.date: Used to fetch the current date.
• tkinter: The library for GUI development.
• sqlalchemy: To manage database connections and execute SQL queries.

2. Setting Up the GUI Window

my_w = tk.Tk()
my_w.geometry("600x250")
my_w.title("www.plus2net.com")

font1=['Arial',26,'normal']
font2=['Arial',36,'normal']

• my_w: The main window object.
• geometry: Sets the window size to 600x250 pixels.
• title: Sets the title of the window.

3. Database Connection

# Change the database to MySQL or SQLite here. 
#my_conn = create_engine("sqlite:///E:\\testing\\sqlite\\my_new_db7.db") # update Path
my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_db")
my_conn = my_conn.connect()

• Connects to the MySQL database named my_db using the user id and password pw.
• The connection object is stored in my_conn.

4. GUI Elements

lb0 = tk.Label(my_w, text="Add Data", font=font1, width=30, anchor="c")
lb0.grid(row=1, column=1, columnspan=4)

t1 = tk.Text(my_w, height=1, width=10, font=font1, bg='yellow')
t1.grid(row=3, column=2)

b1 = tk.Button(my_w, text="Add Record", font=font1, bg='lightgreen', command=lambda: add_data())
b1.grid(row=3, column=3, padx=2)

• Label: Displays static text ("Add Data").
• Text: A text input box to enter the name.
• Button: Triggers the add_data function when clicked.

5. Adding Data to the Database

def add_data():
    flag_validation = True
    my_name = t1.get("1.0", END).strip()

    if len(my_name) < 2:
        flag_validation = False

    if flag_validation:
        my_str.set("Adding data...")
        try:
            query = "INSERT INTO `student7` (`name`) VALUES (:my_name)"
            my_data = {'my_name': my_name}
            id = my_conn.execute(text(query), my_data)
            id_auto = str(id.lastrowid)
            ...
        except SQLAlchemyError as e:
            error = str(e.__dict__["orig"])
            ...

• flag_validation: Ensures the input name is valid.
• SQL Query: Inserts a new record into the student7 table.
• lastrowid: Fetches the ID of the newly inserted record.

6. Generating and Updating a Unique ID

dt = date.today().strftime("%Y-%m-%d") # Today's date in YYYY-mm-dd
id_str = "AB" + dt + id_auto.zfill(5) # Unique string with char + date + unique id
query = "UPDATE `student7` SET id_str = :id_str WHERE id = :id_auto"
update_data = {'id_str': id_str, 'id_auto': id_auto}
my_conn.execute(text(query), update_data)

• Generates a string ID in the format ABYYYY-MM-DDXXXXX.
• Updates the table with the new unique ID.

7. Exception Handling

except SQLAlchemyError as e:
    error = str(e.__dict__["orig"])
    my_str.set(error)

• Displays the error message in case of a database operation failure.

8. Running the Application

my_w.mainloop()

Displaying data

lb2.config(bg='yellow',text=id_str)  # update the text option 

lb2.after(3000, lambda: lb1.config(text="")) # Hide after 3 seconds

from datetime import date
import tkinter as tk
from tkinter import *

my_w = tk.Tk()
my_w.geometry("600x250")
my_w.title("www.plus2net.com")

from sqlalchemy import create_engine,text
from sqlalchemy.exc import SQLAlchemyError

# Change the database to MySQL or SQLite here. 
#my_conn = create_engine("sqlite:///E:\\testing\\sqlite\\my_new_db7.db") # update Path
my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_db")
my_conn = my_conn.connect()
# add one Label
font1=['Arial',26,'normal'] # font family size and style
font2=['Arial',36,'normal'] # font family size and style
lb0 = tk.Label(my_w, text="Add Data",font=font1, width=30, anchor="c")
lb0.grid(row=1, column=1, columnspan=4)
# add one text box
t1 = tk.Text(my_w, height=1, width=10, font=font1,bg='yellow')
t1.grid(row=3, column=2)

b1 = tk.Button(my_w, text="Add Record",font=font1, bg='lightgreen' ,
                command=lambda: add_data())
b1.grid(row=3, column=3,padx=2)

my_str=tk.StringVar()
lb1 = tk.Label(my_w, textvariable=my_str, width=10)
lb1.grid(row=3, column=4,padx=2)

lb2 = tk.Label(my_w,  width=20,font=font2,text='')
lb2.grid(row=4, column=1,padx=2,pady=10,columnspan=4)
def add_data():
    flag_validation = True  # Set the flag
    my_name = t1.get("1.0", END).strip()  # Read name and strip whitespace
   
    if len(my_name) < 2:  # Length of name should be more than 2
        flag_validation = False
    if flag_validation:
        my_str.set("Adding data...")
        try:
            # Insert the data into the table
            query = "INSERT INTO `student7` (`name`) VALUES (:my_name)"
            my_data = {'my_name': my_name}  # Collect the user-entered data
            id=my_conn.execute(text(query), my_data)  # Execute the insert query
            id_auto=str(id.lastrowid) # convert id to string
            t1.delete("1.0", END)  # Reset the text entry box
            lb1.config(fg="green", bg="white")  # Foreground and background color
            my_str.set("ID: " + id_auto)  # Update the label stringvar
            # Generate a unique ID string
            dt = date.today().strftime("%Y-%m-%d")  # Today's date as string
            id_str = "AB" + dt + id_auto.zfill(5)  # AB2023-04-1900003
            print(id_str)
            lb2.config(bg="yellow", text=id_str)  # Update the text option
            lb2.after(3000, lambda: lb1.config(text=""))  # Hide after 3 seconds

            # Update the table with the final ID string
            query = "UPDATE `student7` SET id_str = :id_str WHERE id = :id_auto"
            update_data = {'id_str': id_str, 'id_auto': id_auto}
            my_conn.execute(text(query), update_data)  # Execute the update query
            my_conn.commit() 
        except SQLAlchemyError as e:
            error = str(e.__dict__["orig"])
            print(error)
            lb1.config(fg="red", bg="yellow")  # Foreground and background color
            my_str.set(error)  # Display error message
    else:
        lb1.config(fg="red", bg="yellow")  # Foreground and background color
        my_str.set("Check inputs.")
        lb1.after(3000, lambda: lb1.config(fg="white", bg="white", text=""))

my_w.mainloop()

Database table structure

CREATE TABLE `student7` (
  `id` INTEGER  PRIMARY KEY AUTOINCREMENT,
  `name` varchar(25) NOT NULL,
  `dt` timestamp NOT NULL DEFAULT current_timestamp,
  `id_str` varchar(20)
)

CREATE TABLE `student7` (
  `id` int NOT NULL,
  `name` varchar(25) NOT NULL,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `id_str` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `student7`
--
ALTER TABLE `student7`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `student7`
--
ALTER TABLE `student7`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;
COMMIT;