Showing non-repeating random records in Tkinter window from MySQL or SQLite database table
Use one of the two lines below based on the database used.
#q="SELECT id,name,class,mark,gender FROM student2 ORDER BY RANDOM() LIMIT 0,1"#SQlite
q="SELECT id,name,class,mark,gender FROM student2 ORDER BY RAND() LIMIT 0,1"#MySQL
Code to display random record on Tkinter
import tkinter as tk
from tkinter import *
my_w = tk.Tk()
my_w.geometry("400x250") # width and height of the window
from sqlalchemy import create_engine
#my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_tutorial")
#q="SELECT id,name,class,mark,gender FROM student2 WHERE status=False ORDER BY RAND() LIMIT 0,1"#MySQL
### SQLite #####
my_conn = create_engine("sqlite:///E:\\testing\\my_db\\my_db.db") #SQLite
q="SELECT id,name,class,mark,gender FROM student2 WHERE status=False ORDER BY RANDOM() LIMIT 0,1"#SQlite
my_cursor=my_conn.execute(q)
data_row=my_cursor.fetchone()
print(data_row)
i=0
for student in data_row:
my_label=tk.Label(my_w,text=str(student),font=20)
my_label.grid(row=1,column=i,padx=10,pady=25)
i=i+1
my_w.mainloop()
On button click show record
On every click of the button one random record is displayed.
import tkinter as tk
from tkinter import *
my_w = tk.Tk()
my_w.geometry("400x150")
from sqlalchemy import create_engine
#my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_tutorial")
#q="SELECT id,name,class,mark,gender FROM student2 WHERE status=False ORDER BY RAND() LIMIT 0,1"#MySQL
### SQLite #####
my_conn = create_engine("sqlite:///E:\\testing\\my_db\\my_db.db") #SQLite
q="SELECT id,name,class,mark,gender FROM student2 WHERE status=False ORDER BY RANDOM() LIMIT 0,1"#SQlite
####### end of connection ####
b1=tk.Button(my_w,text='Show',command=lambda:my_display())
b1.grid(row=0,column=0,padx=5,pady=10,columnspan=2,sticky='w')
def my_display(): # to show random record
my_cursor=my_conn.execute(q)
data_row=my_cursor.fetchone()
#print(data_row)
i=0
for w in my_w.grid_slaves(2): #
w.grid_forget() # remove all elements of 2nd row
for student in data_row:
my_label=tk.Label(my_w,text=str(student),font=20)
my_label.grid(row=2,column=i,padx=5,pady=5)
i=i+1
my_w.mainloop()
Getting random record without repeating
As we are getting records from Database and each time one random row from the full list is collected, there is always chance that record will be repeated from the list.
To make it one time only we will update one more column of the table by True or False to indicate that the record is returned or not. Boolean Data in Tinyint(1) column →
The query is here.
q="SELECT id,name,class,mark,gender FROM student2 WHERE status=False ORDER BY RANDOM() LIMIT 0,1"#SQlite
#q="SELECT id,name,class,mark,gender FROM student2 WHERE status=False ORDER BY RAND() LIMIT 0,1"#MySQL
Once the record is returned, the same record status column will be set to True to prevent its appearance again. Here based on the id column value we are setting the status to 1 ( True ).
'UPDATE student2 set status=1 WHERE id='+ str(data_row[0])
For this script we have used student2 table which has one additional column status to hold the boolean value.
import tkinter as tk
from tkinter import *
my_w = tk.Tk()
my_w.geometry("400x150")
from sqlalchemy import create_engine
#my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_tutorial")
#q="SELECT id,name,class,mark,gender FROM student2 WHERE status=False ORDER BY RAND() LIMIT 0,1"#MySQL
### SQLite #####
my_conn = create_engine("sqlite:///E:\\testing\\my_db\\my_db.db") #SQLite
q="SELECT id,name,class,mark,gender FROM student2 WHERE status=False ORDER BY RANDOM() LIMIT 0,1"#SQlite
####### end of connection ####
b1=tk.Button(my_w,text='Show',command=lambda:my_display(),bg='lightgreen')
b1.grid(row=0,column=0,padx=5,pady=10,columnspan=2,sticky='w')
def my_display(): # to show random record
my_cursor=my_conn.execute(q)
data_row=my_cursor.fetchone()
[w.grid_forget() for w in my_w.grid_slaves(2)] # remove all previous data
if data_row: # Once the data is collected ( not None )
my_conn.execute('UPDATE student2 set status=1 WHERE id='+ str(data_row[0]))
#print(data_row)
i=0
for student in data_row:
my_label=tk.Label(my_w,text=str(student),font=20)
my_label.grid(row=2,column=i,padx=5,pady=5)
i=i+1
else: # No record is available to show
my_label=tk.Label(my_w,text='No more records',font=20)
my_label.grid(row=2,column=0,columnspan=4,padx=5,pady=5)
my_w.mainloop()
Reset records
To reset all rows with status=False
UPDATE student2 SET status=False
We may provide one Reset Button to update the status of all the records to False to make it ready for next round of random record collection.
def my_reset():
my_conn.execute('UPDATE student2 set status=0' )
my_label=tk.Label(my_w,text='Records Resetted',font=20)
my_label.grid(row=2,column=0,columnspan=4,padx=5,pady=5)
Here is the full code with Reset record button
import tkinter as tk
from tkinter import *
my_w = tk.Tk()
my_w.geometry("400x150")
from sqlalchemy import create_engine
#my_conn = create_engine("mysql+mysqldb://root:test@localhost/my_tutorial")
#q="SELECT id,name,class,mark,gender FROM student2 WHERE status=False ORDER BY RAND() LIMIT 0,1"#MySQL
########## SQLite #####
my_conn = create_engine("sqlite:///E:\\testing\\my_db\\my_db.db") #SQLite
q="SELECT id,name,class,mark,gender FROM student2 WHERE status=False ORDER BY RANDOM() LIMIT 0,1"#SQlite
####### end of connection ####
b1=tk.Button(my_w,text='Show',command=lambda:my_display(),bg='lightgreen',width=18)
b1.grid(row=0,column=0,padx=5,pady=10,columnspan=2,sticky='w')
b2=tk.Button(my_w,text='Reset',command=lambda:my_reset(),bg='lightyellow',width=18)
b2.grid(row=0,column=2,padx=5,pady=10,sticky='w')
def my_display(): # to show random record
my_cursor=my_conn.execute(q)
data_row=my_cursor.fetchone()
[w.grid_forget() for w in my_w.grid_slaves(2)] # remove all previous data
if data_row: # Once the data is collected ( not None )
my_conn.execute('UPDATE student2 set status=1 WHERE id='+ str(data_row[0]))
#print(data_row)
i=0 # to increment column value
for student in data_row:
my_label=tk.Label(my_w,text=str(student),font=20)
my_label.grid(row=2,column=i,padx=5,pady=5)
i=i+1 # Next column number
else: # No record is available to show
my_label=tk.Label(my_w,text='No more record',font=20)
my_label.grid(row=2,column=0,columnspan=4,padx=5,pady=5)
def my_reset():
my_conn.execute('UPDATE student2 set status=0' ) # for all records update status
[w.grid_forget() for w in my_w.grid_slaves(2)] # remove all previous data
my_label=tk.Label(my_w,text='Records Resetted',font=20)
my_label.grid(row=2,column=0,columnspan=4,padx=5,pady=5)
my_w.mainloop()
Creating student2 table with data
Structure of the student2 table
CREATE TABLE `student2` (
`id` int(2) NOT NULL DEFAULT 0,
`name` varchar(50) NOT NULL DEFAULT '',
`class` varchar(10) NOT NULL DEFAULT '',
`mark` int(3) NOT NULL DEFAULT 0,
`gender` varchar(6) NOT NULL DEFAULT 'male',
`status` tinyint(1) NOT NULL DEFAULT 0
);