Creating unique id by using Date and string by storing data in Database table

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 date
dt=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 string
id_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_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

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

Query to insert record in Database table

Based on the database one query is to be used. We will get autoincrement-id by using lastrowid

#query = "INSERT INTO  `student3` (`name`)  VALUES(%s)" # MySQL query
query = "INSERT INTO  `student3` (`name`)  VALUES(?)" # SQLite query
my_data = (my_name) # collect the user entered data
id = my_conn.execute(query, my_data)  # insert data
id_auto=str(id.lastrowid) # convert id to string

Here the id.lastrorwid returns the id column value assigned by Database to the just inserted record.( Previous id value is incrmented by 1 and assigned to this record. )

On button click insert record

On click of the button user entred data is inserted to database table.

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

def add_data():
	pass

Storing the unique string-id in database table

The unique string is created by using category string ( AB ) , date string ( YYYY-mm-dd ) and id returned from database. This string is stored in the same row in a column id_str by using UPDATE Query with WHERE condition using unique id.

query="UPDATE  `student3` SET id_str='"+id_str+"' WHERE id="+id_auto
my_conn.execute(query)  # insert data

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 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
from sqlalchemy.exc import SQLAlchemyError
#my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_db")
my_conn = create_engine("sqlite:///G:\\testing\\my_db\\my_new_db5.db")
# 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)  # read name
   
    if len(my_name) < 2 : # Length of name should more than 2
        flag_validation = False
    if flag_validation:
        my_str.set("Adding data...")
        try:
            #query = "INSERT INTO  `student3` (`name`)  VALUES(%s)" # MySQL query
            query = "INSERT INTO  `student3` (`name`)  VALUES(?)" # SQLite query
            my_data = (my_name) # collect the user entered data
            id = my_conn.execute(query, my_data)  # insert data
            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 background color
            my_str.set("ID:" + id_auto) # update the label stringvar
            #lb1.after(3000, lambda: lb1.config(fg="white", text=""))
           
            dt=date.today().strftime('%Y-%m-%d') # Today's date as string
            #print(dt)
            id_str='AB'+dt+id_auto.zfill(5) # AB2023-04-1900003
            #print(str_id)
            lb2.config(bg='yellow',text=id_str)  # update the text option 
            lb2.after(3000, lambda: lb1.config(text="")) # hide after 3 seconds
            #insert into mysql the final id #
            query="UPDATE  `student3` SET id_str='"+id_str+"' WHERE id="+id_auto
            my_conn.execute(query)  # insert data
        except SQLAlchemyError as e:
            error = str(e.__dict__["orig"])
            
            # return error
            lb1.config(fg="red",bg='yellow')  # foreground background color
            print(error)
            my_str.set(error) # display error message
    else:
        lb1.config(fg="red",bg='yellow')  # foreground 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 `student3` (
  `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_db5.db file with student3 table
Use this SQLdump to create student3 table in MySQL database

CREATE TABLE `student3` (
  `id` int(3) NOT NULL,
  `name` varchar(25) NOT NULL,
  `dt` timestamp NOT NULL DEFAULT current_timestamp(),
  `id_str` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `student3`
  ADD PRIMARY KEY (`id`);

Python Tkinter Dictionary Python- Tutorials

Subscribe to our YouTube Channel here

Unique id with Date and string from Database