I am able to use SQLite3
as the database (DB) and get Treeview
to display data from the DB. However, I was wondering whether Treeview
has any functionality to censor the first few characters in a certain column for all entries?
Here is the lean code:
############################## | DATABASE CONNECTION FOR PARTICULARS | #################################### connectforce = sqlite3.connect('force_database.db') #Create Cursor - Link to database c = connectforce.cursor() ##create table #c.execute("""create table patient_list ( # nric text, # full_name text, # age integer, # gender text, # ethnic text # )""") ############################## | TREEVIEW CONNECTION | #################################### my_tree = ttk.Treeview(frame4_pl) my_tree['column'] = ("NRIC", "Full Name", "Age", "Gender", "Ethnic") my_tree.column("#0", width=0, minwidth=25) my_tree.column("NRIC", anchor=CENTER, width=90, minwidth=25) my_tree.column("Full Name", anchor=W, width=150, minwidth=25) my_tree.column("Age",anchor=CENTER, width=55, minwidth=25) my_tree.column("Gender", anchor=CENTER, width=60, minwidth=25) my_tree.column("Ethnic", anchor=CENTER, width=90, minwidth=25) my_tree.heading("#0", text="", anchor=CENTER) my_tree.heading("NRIC", text="NRIC", anchor=CENTER) my_tree.heading("Full Name", text="Full Name", anchor=CENTER) my_tree.heading("Age", text="Age", anchor=CENTER) my_tree.heading("Gender", text="Gender", anchor=CENTER) my_tree.heading("Ethnic", text="Ethnicity", anchor=CENTER) my_tree.bind('<Double 1>', display_selection) def submit_database(): connectpatient = sqlite3.connect('patient_database.db') #Create Cursor - Link to database c = connectpatient.cursor() #Insert Into Table c.execute("INSERT INTO patient_list VALUES (:entry_nric, :entry_name, :entry_age, :entry_gender, :entry_Ethnic)", { 'entry_nric': entry_nric.get(), 'entry_name': entry_name.get(), 'entry_age': entry_age.get(), 'entry_gender': entry_gender.get(), 'entry_Ethnic': entry_Ethnic.get() } ) #Save into Latest/Selected Patient Array for 7TH FRAME latestpatient_list(1) #Commit Changes connectpatient.commit() #Close Connection connectpatient.close() def view_database(): connectpatient = sqlite3.connect('patient_database.db') #Create Cursor - Link to database c = connectpatient.cursor() #Query the Database c.execute("SELECT *, oid FROM patient_list") #c.execute("SELECT nric, full_name FROM patient_list") #c.execute("SELECT nric, full_name, age, gender, ethnic FROM patient_list WHERE nric LIKE '%"+keyword_check_passed+"%'") #c.execute("SELECT * FROM patient_list") records = c.fetchall() record = "" for record in records: print(record) # it print all records in the database my_tree.insert("", tk.END, values=record) #Perhaps some amendment here to censor with asterisk for some letters for "nric" column? my_tree.pack() #Commit Changes connectpatient.commit() #Close Connection connectpatient.close()
I can get variables from the Entry Widget (with Tkinter)
to be successfully stored and reflected from the updated DB to the Treeview upon the view_database()
function call. But I was hoping to censor the first 5 characters of nric
column data with *
for all entries when viewing the Treeview
. Would that be possible or an alternative method to work around with?
Advertisement
Answer
You can make the substitution in the SQL query itself, by combining your desired prefix with a substring of the column, taken from the sixth character to the end.
Here’s a pure Sqlite example:
sqlite> CREATE TABLE test (col1 text, col2 text); sqlite> INSERT INTO test (col1, col2) VALUES ('Hello world', 'hello hello'), ('First column', 'Second column'); sqlite> SELECT ('*****' || substr(col1, 6, length(col1))) AS redacted, col2 FROM test; ***** world|hello hello ***** column|Second column
substr is a function that takes a substring of a string value, ||
is the string concatenation operator.
Labelling the new value with AS redacted
will have Sqlite returned the value in a column named “redacted”.
Adopting this approach, the view_database
function might look like this:
def view_database(): connectpatient = sqlite3.connect('patient_database.db') #Create Cursor - Link to database c = connectpatient.cursor() #Query the Database query = """SELECT '*****' || SUBSTR(nric, 6, LENGTH(nric)) AS nric, full_name, age, gender, ethnic, oid FROM patient_list""" c.execute(query) records = c.fetchall() record = "" for record in records: print(record) # it print all records in the database my_tree.insert("", tk.END, values=record) my_tree.pack() #Commit Changes connectpatient.commit() #Close Connection connectpatient.close()