Skip to content
Advertisement

Any way to censor Treeview Data Display?

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()
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement