Any way to censor Treeview Data Display?

Question

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?

Answer 1

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()