Unique id with Date and string from Database

";require "templates/body_start.php";?> Autoincrement unique id from database

We will store the data in MySQL or SQLite database and then display one unique reference number or ticket number to user.

The unique id returned by database will be added with today’s date in YYYY-mm-dd format and one string ( AB in above picture) specifying any group or category.

Tkinter GUI showing unique string using database autoicrement id and date

MySQL Autoincrement Column

Use the appropriate lines for SQLite or MySQL database by commenting and un-commenting them.

Today's date as string

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

zfill()

The incremental unique id returned by the database after inserting the record is added with zeros at the left side. Here if the id is 9 then it became 00009 by using zfill() string method.

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

Connection to Database

Use proper path ( for SQLite database ) or use userid and password for MySQL database.

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

Here my_conn is used inside the script, so based on the database used, one of the lines to be used in above code.
Connection to SQLite database
Connection to MySQL database

1. Importing Required Modules

This section imports the necessary modules and libraries required for the program.

from datetime import dateimport tkinter as tkfrom tkinter import *from sqlalchemy import create_engine, textfrom 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

This section initializes the main window and sets its dimensions, title, and fonts.

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

This section establishes a connection to the MySQL database.

# Change the database to MySQL or SQLite here. #my_conn = create_engine("sqlite:///E:\\testing\\sqlite\\my_new_db7.db") # update Pathmy_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

This section creates the GUI elements like labels, text box, and buttons.

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

The add_data function inserts a record into the database and updates the GUI with the result.

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

This part generates a unique ID and updates the database with it.

dt = date.today().strftime("%Y-%m-%d") # Today's date in YYYY-mm-ddid_str = "AB" + dt + id_auto.zfill(5) # Unique string with char + date + unique idquery = "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

Handles any errors that occur during database operations.

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

Starts the GUI application.

my_w.mainloop()

Displaying data

We have used Labels to display errror messages and unique id to the user. Here we have used config() to update the text option of the Label.

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

We are using after() to update the text option of the Label ( after 3000 milli seconds ) here .

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

from datetime import dateimport tkinter as tkfrom tkinter import *my_w = tk.Tk()my_w.geometry("600x250")my_w.title("www.plus2net.com")from sqlalchemy import create_engine,textfrom 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 Pathmy_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_db")my_conn = my_conn.connect()# add one Labelfont1=['Arial',26,'normal'] # font family size and stylefont2=['Arial',36,'normal'] # font family size and stylelb0 = tk.Label(my_w, text="Add Data",font=font1, width=30, anchor="c")lb0.grid(row=1, column=1, columnspan=4)# add one text boxt1 = 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

Use this in your query to create SQLite table

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

Download SQLite database my_new_db7.db file with student7 table
Use this SQLdump to create student7 table in MySQL database

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;

Auto-Increment ID in Tkinter GUI with Python #Python #Tkinter #PythonGUI #Programming #ticketNumber

Displaying records from student table

Python Tkinter Dictionary Python- Tutorials