When we collect data from the source ( database table or Excel or CSV file ) we are not sure how many columns will be available. Here are some SQL which collects different number of columns from the table
First and second query will collect two and three columns respectively from our student table. The third query will return all the columns of the student table.
This way we will not have any fixed number of columns and based on the source or data, we have to create the columns.
For our easy understanding let us create one list and use the elements as column headers. We can replace our list with data from Database table.
l1 = ['id', 'Name', 'Class', 'Mark', 'Gender']
Using this list we will add our columns and headers ( list elements ) to our Treeview.
We will replace the list l1 in above code with our data column headers and for that we have used our MySQL table. Note that souce can be SQLite or Excel or Pandas DataFrame or any other source.
We need to create two variables ( l1 and r_set ) , one is list of headers and other one is list of row data. Let us work with different data sources. Based on the requirment any part of the code can be un-commented to include as source.
MySQL database table as data source
from sqlalchemy import create_engine, text
my_conn = create_engine("mysql+mysqldb://userid:pw@localhost/my_db")
my_conn = my_conn.connect() # Connection objectr_set = my_conn.execute(text('SELECT * FROM student'))
l1 = [rforrinr_set.keys()] # List of column headersr_set = [list(r) forrinr_set]
# l1=['id','Name','Class','Mark','Gender']
import pandas as pd
df = pd.read_excel('D:\\student.xlsx') # Create dataframe using xlsx filel1 = list(df) # List of column names as headersr_set = df.to_numpy().tolist() # Create list of lists using rows
from openpyxl import load_workbook
wb = load_workbook(filename='D:\\student.xlsx', read_only=True)
ws = wb['Sheet1']
r_set = [rowforrowinws.iter_rows(values_only=True)]
l1 = r_set.pop(0) # Collect the first row as column headerswb.close() # Close the workbook after reading
This step is after collecting the data from sources ( l1 , r_set ).
dv.build_table_data(l1, r_set) # adding column and row data
dv.load_table_data() # refresh the table view with data
dv.autofit_columns() # Adjust with available space
dv.autoalign_columns() # String left and Numbers to right
import ttkbootstrap as ttk
from ttkbootstrap.tableview import Tableview
from ttkbootstrap.constants import *
my_w = ttk.Window()
my_w.geometry("400x300") # width and height
colors = my_w.style.colors # for using alternate colours
dv = ttk.tableview.Tableview(
master=my_w,
paginated=True,
searchable=True,
bootstyle=SUCCESS,
pagesize=10,
height=10,
stripecolor=(colors.light, None),
)
dv.grid(row=0, column=0, padx=5, pady=5) # Tableview is placed
# MySQL as data source
from sqlalchemy import create_engine,text # for MySql and SQLite database
"""
my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_tutorial") # update details
my_conn = my_conn.connect()
r_set = my_conn.execute(text("SELECT * FROM student ")) # Change the query
l1 = [r for r in r_set.keys()] # List of Column headers
r_set = [list(r) for r in r_set] # List of rows of data
"""
# SQlite database as datasource
"""
my_conn = create_engine("sqlite:///D:\\testing\\sqlite\\my_db.db") # Update your path
my_conn = my_conn.connect()
r_set = my_conn.execute(text("SELECT * FROM student"))
l1 = [r for r in r_set.keys()] # List of column headers
r_set = [list(r) for r in r_set]
"""
# Pandas dataframe as source
"""
import pandas as pd
df = pd.read_excel("I:\data\student.xlsx") # create dataframe using xlsx file
l1 = list(df) # List of column names as headers
r_set = df.to_numpy().tolist() # Create list of list using rows
"""
# Excel file as source
"""
from openpyxl import load_workbook
wb = load_workbook(filename="I:\data\student.xlsx", read_only=True)
ws = wb["student"]
r_set = [row for row in ws.iter_rows(values_only=True)]
l1 = r_set.pop(0) # Collect the first row as column headers
wb.close() # Close the workbook after reading
"""
# CSV file as source
import csv
file = open("I:\data\student.csv") # file object for student.csv file
csvreader = csv.reader(file)
l1 = []
l1 = next(csvreader) # column headers as first row
r_set = [row for row in csvreader]
# Integrate the data source to Tableview
dv.build_table_data(l1, r_set) # adding column and row data
dv.load_table_data() # refresh the table view with data
dv.autofit_columns() # Adjust with available space
dv.autoalign_columns() # String left and Numbers to right
my_w.mainloop()
Keeping a separate Data Source file
We can keep all the data sources in a different file and use only two variables l1 and r_set. From our main file we can call these two variables like this.
from ttk_table_sources import r_set, l1
This will help in better maintenance of the code and same source can be used in different modules.
'''
r_set = [
[1, "John Deo", "Four", 75, "female"],
[2, "Max Ruin", "Three", 85, "male"],
[3, "Arnold", "Three", 55, "male"],
[4, "Krish Star", "Four", 60, "female"],
[5, "John Mike", "Four", 60, "female"],
[6, "Alex John", "Four", 55, "male"],
[7, "My John Rob", "Five", 78, "male"],
[8, "Asruid", "Five", 85, "male"],
[9, "Tes Qry", "Six", 78, "male"],
[10, "Big John", "Four", 55, "female"],
]
l1 = ["id", "name", "class", "mark", "gender"]
'''
# MySQL as data source
from sqlalchemy import create_engine ,text # for MySql and SQLite database
my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_tutorial")
my_conn = my_conn.connect()
r_set = my_conn.execute(text("SELECT * FROM student")) # Change the query
l1 = [r for r in r_set.keys()] # List of Column headers
r_set = [list(r) for r in r_set] # List of rows of data
# SQlite database as datasource
"""
my_conn = create_engine("sqlite:///D:\\testing\\sqlite\\my_db.db")
my_conn = my_conn.connect()
r_set = my_conn.execute(text("SELECT * FROM student") )
l1 = [r for r in r_set.keys()] # List of column headers
r_set = [list(r) for r in r_set]
"""
# Pandas dataframe as source
"""
import pandas as pd
df = pd.read_excel("I:\data\student.xlsx") # create dataframe using xlsx file
l1 = list(df) # List of column names as headers
r_set = df.to_numpy().tolist() # Create list of list using rows
"""
# Excel file as source
"""
from openpyxl import load_workbook
wb = load_workbook(filename="I:\data\student.xlsx", read_only=True)
ws = wb["student"]
r_set = [row for row in ws.iter_rows(values_only=True)]
l1 = r_set.pop(0) # Collect the first row as column headers
wb.close() # Close the workbook after reading
"""
# CSV file as source
"""
import csv
file = open("F:\data\student.csv") # file object for student.csv file
csvreader = csv.reader(file)
l1 = []
l1 = next(csvreader) # column headers as first row
r_set = [row for row in csvreader]
"""
import ttkbootstrap as ttk
from ttkbootstrap.tableview import Tableview
from ttkbootstrap.constants import *
from ttk_table_sources import r_set, l1
my_w = ttk.Window()
my_w.geometry("400x300") # width and height
colors = my_w.style.colors # for using alternate colours
dv = ttk.tableview.Tableview(
master=my_w,
paginated=True,
searchable=False,
bootstyle=SUCCESS,
pagesize=10,
height=10,
stripecolor=(colors.light, None),
)
dv.grid(row=0, column=0, padx=5, pady=5) # Tableview is placed
# Integrate the data source to Tableview
marks = [int(r[3]) for r in r_set] # List of all marks column
dv.build_table_data(l1, r_set) # adding column and row data
dv.insert_row("end", values=["-", "-SUM-", "All", sum(marks), "All"])
dv.insert_row("end", values=["-", "-MAX-", "All", max(marks), "All"])
dv.load_table_data() # refresh the table view with data
dv.autofit_columns() # Adjust with available space
dv.autoalign_columns() # String left and Numbers to right
my_w.mainloop()