[Python] How to display copyable records in UI

import tkinter as tk
from tkinter import ttk, messagebox, filedialog
import oracledb
import pandas as pd
import configparser
import os

class OracleDataExplorer:
def __init__(self, root):
self.root = root
self.root.title("Oracle Database Explorer")
self.root.geometry("1400x900")

# Initialize configuration
self.config = configparser.ConfigParser()
self.config_file = "config.ini"
self.load_config()

# Database connection
self.connection = None
self.connected = False

# Table configuration
self.table_config = {
"cart": {
"order_no_col": "ord_no",
"loc_code_col": "loc_cd",
"query_by": "cart_id"
},
"ace_order": {
"order_no_col": "ord_no",
"app_num_col": "card_app_num"
},
"orders": {
"order_no_col": "order_number",
"query_by": "order_number"
}
# Add all tables here
}

# Create UI
self.create_connection_frame()
self.create_search_frame()
self.create_results_frame()
self.create_export_frame()
self.create_config_frame()

# Load saved credentials
self.load_saved_credentials()

def load_config(self):
if not os.path.exists(self.config_file):
self.config['DATABASE'] = {
'username': '',
'password': '',
'dsn': 'localhost:1521/ORCLPDB1',
'save_credentials': 'False'
}
self.save_config()
else:
self.config.read(self.config_file)

def save_config(self):
with open(self.config_file, 'w') as configfile:
self.config.write(configfile)

def load_saved_credentials(self):
if self.config.getboolean('DATABASE', 'save_credentials', fallback=False):
self.username.insert(0, self.config['DATABASE'].get('username', ''))
self.password.insert(0, self.config['DATABASE'].get('password', ''))
self.dsn.delete(0, tk.END)
self.dsn.insert(0, self.config['DATABASE'].get('dsn', 'localhost:1521/ORCLPDB1'))
self.save_creds_var.set(True)

def create_connection_frame(self):
frame = ttk.LabelFrame(self.root, text="Database Connection", padding=10)
frame.grid(row=0, column=0, columnspan=2, padx=10, pady=5, sticky="ew")

ttk.Label(frame, text="Username:").grid(row=0, column=0, sticky="e")
self.username = ttk.Entry(frame)
self.username.grid(row=0, column=1, padx=5, pady=2, sticky="ew")

ttk.Label(frame, text="Password:").grid(row=1, column=0, sticky="e")
self.password = ttk.Entry(frame, show="*")
self.password.grid(row=1, column=1, padx=5, pady=2, sticky="ew")

ttk.Label(frame, text="DSN:").grid(row=2, column=0, sticky="e")
self.dsn = ttk.Entry(frame)
self.dsn.grid(row=2, column=1, padx=5, pady=2, sticky="ew")

self.connect_btn = ttk.Button(frame, text="Connect", command=self.connect_db)
self.connect_btn.grid(row=3, column=0, columnspan=2, pady=5)

def create_config_frame(self):
frame = ttk.LabelFrame(self.root, text="Configuration", padding=10)
frame.grid(row=3, column=0, columnspan=2, padx=10, pady=5, sticky="ew")

self.save_creds_var = tk.BooleanVar(value=False)
ttk.Checkbutton(
frame,
text="Save credentials (not recommended for shared computers)",
variable=self.save_creds_var
).grid(row=0, column=0, sticky="w")

ttk.Button(
frame,
text="Save Configuration",
command=self.save_current_config
).grid(row=0, column=1, sticky="e")

def save_current_config(self):
self.config['DATABASE']['username'] = self.username.get()
self.config['DATABASE']['password'] = self.password.get()
self.config['DATABASE']['dsn'] = self.dsn.get()
self.config['DATABASE']['save_credentials'] = str(self.save_creds_var.get())
self.save_config()
messagebox.showinfo("Success", "Configuration saved to config.ini")

def create_search_frame(self):
frame = ttk.LabelFrame(self.root, text="Search Criteria", padding=10)
frame.grid(row=1, column=0, padx=10, pady=5, sticky="nsew")

self.search_mode = tk.StringVar(value="cart_id")
ttk.Radiobutton(frame, text="Search by Cart ID", variable=self.search_mode, value="cart_id").grid(row=0, column=0, sticky="w")
ttk.Radiobutton(frame, text="Search by Order No + Loc Code", variable=self.search_mode, value="order_loc").grid(row=1, column=0, sticky="w")

ttk.Label(frame, text="Cart ID:").grid(row=2, column=0, sticky="e")
self.cart_id_entry = ttk.Entry(frame)
self.cart_id_entry.grid(row=2, column=1, padx=5, pady=2, sticky="ew")

ttk.Label(frame, text="Order No:").grid(row=3, column=0, sticky="e")
self.order_no_entry = ttk.Entry(frame)
self.order_no_entry.grid(row=3, column=1, padx=5, pady=2, sticky="ew")

ttk.Label(frame, text="Loc Code:").grid(row=4, column=0, sticky="e")
self.loc_code_entry = ttk.Entry(frame)
self.loc_code_entry.grid(row=4, column=1, padx=5, pady=2, sticky="ew")

self.search_btn = ttk.Button(frame, text="Search", command=self.search_data, state=tk.DISABLED)
self.search_btn.grid(row=5, column=0, columnspan=2, pady=5)

self.search_mode.trace("w", self.update_search_ui)
self.update_search_ui()

def create_results_frame(self):
frame = ttk.LabelFrame(self.root, text="Results", padding=10)
frame.grid(row=1, column=1, rowspan=2, padx=10, pady=5, sticky="nsew")

# Create notebook for multiple tables
self.results_notebook = ttk.Notebook(frame)
self.results_notebook.pack(fill=tk.BOTH, expand=True)

# Search within results
search_frame = ttk.Frame(frame)
search_frame.pack(fill=tk.X, pady=5)

ttk.Label(search_frame, text="Search in results:").pack(side=tk.LEFT)
self.search_entry = ttk.Entry(search_frame)
self.search_entry.pack(side=tk.LEFT, fill=tk.X, expand=True, padx=5)
ttk.Button(search_frame, text="Find", command=self.find_in_results).pack(side=tk.LEFT)

self.root.columnconfigure(1, weight=1)
self.root.rowconfigure(1, weight=1)

def create_export_frame(self):
frame = ttk.Frame(self.root)
frame.grid(row=2, column=0, padx=10, pady=5, sticky="ew")

self.export_btn = ttk.Button(frame, text="Export to Excel", command=self.export_to_excel, state=tk.DISABLED)
self.export_btn.pack(pady=5)

def update_search_ui(self, *args):
mode = self.search_mode.get()
if mode == "cart_id":
self.cart_id_entry.config(state=tk.NORMAL)
self.order_no_entry.config(state=tk.DISABLED)
self.loc_code_entry.config(state=tk.DISABLED)
else:
self.cart_id_entry.config(state=tk.DISABLED)
self.order_no_entry.config(state=tk.NORMAL)
self.loc_code_entry.config(state=tk.NORMAL)

def connect_db(self):
try:
if self.save_creds_var.get():
self.save_current_config()

self.connection = oracledb.connect(
user=self.username.get(),
password=self.password.get(),
dsn=self.dsn.get()
)
self.connected = True
self.connect_btn.config(text="Connected", style="Success.TButton")
self.search_btn.config(state=tk.NORMAL)
messagebox.showinfo("Success", "Database connection established")
except Exception as e:
messagebox.showerror("Connection Error", f"Failed to connect: {str(e)}")
self.connected = False
self.connect_btn.config(text="Connect")

def get_join_condition(self, table):
config = self.table_config.get(table, {})

if "query_by" not in config:
return None

if config["query_by"] == "cart_id":
if "cart_id" not in self.base_params:
return None
return {
"join_on": f"{config.get('order_no_col', 'ord_no')} = rd_no",
"params": {"ord_no": self.base_params["ord_no"]}
}
elif isinstance(config["query_by"], list):
conditions = []
params = {}
for col in config["query_by"]:
if col+"_col" in config and col in self.base_params:
db_col = config[col+"_col"]
conditions.append(f"{db_col} = :{col}")
params[col] = self.base_params[col]
if not conditions:
return None
return {"join_on": " AND ".join(conditions), "params": params}
else:
if config["query_by"] not in self.base_params:
return None
db_col = config.get(config["query_by"]+"_col", config["query_by"])
return {
"join_on": f"{db_col} = :{config['query_by']}",
"params": {config["query_by"]: self.base_params[config["query_by"]]}
}

def search_data(self):
if not self.connected:
messagebox.showerror("Error", "Not connected to database")
return

# Clear previous results
for child in self.results_notebook.winfo_children():
child.destroy()

# Get base parameters
search_mode = self.search_mode.get()
try:
with self.connection.cursor() as cursor:
if search_mode == "cart_id":
cart_id = self.cart_id_entry.get()
if not cart_id:
messagebox.showerror("Error", "Please enter Cart ID")
return

# Get cart details
cursor.execute(f"""
SELECT {self.table_config['cart']['order_no_col']},
{self.table_config['cart']['loc_code_col']}
FROM cart
WHERE cart_id = :cart_id
""", {"cart_id": cart_id})

cart_result = cursor.fetchone()
if not cart_result:
messagebox.showerror("Error", f"No cart found with ID: {cart_id}")
return

ord_no, loc_cd = cart_result
self.base_params = {
"cart_id": cart_id,
"ord_no": ord_no,
"loc_cd": loc_cd
}
else:
ord_no = self.order_no_entry.get()
loc_cd = self.loc_code_entry.get()
if not ord_no or not loc_cd:
messagebox.showerror("Error", "Please enter both Order No and Loc Code")
return

self.base_params = {
"ord_no": ord_no,
"loc_cd": loc_cd
}

# Get card_app_num from ace_order if table exists
if "ace_order" in self.table_config:
cursor.execute(f"""
SELECT {self.table_config['ace_order']['app_num_col']}
FROM ace_order
WHERE {self.table_config['ace_order']['order_no_col']} = rd_no
""", {"ord_no": self.base_params["ord_no"]})

ace_result = cursor.fetchone()
if ace_result:
self.base_params["card_app_num"] = ace_result[0]

# Query all configured tables
self.all_data = {}
tables_queried = 0

for table in self.table_config.keys():
join_condition = self.get_join_condition(table)
if not join_condition:
continue

try:
query = f"SELECT * FROM {table} WHERE {join_condition['join_on']}"
cursor.execute(query, join_condition["params"])

columns = [col[0] for col in cursor.description]
data = cursor.fetchall()

if data:
self.all_data[table] = {
"columns": columns,
"data": data,
"query": query,
"params": join_condition["params"]
}
self.display_table_data(table, columns, data)
tables_queried += 1

except oracledb.DatabaseError as e:
print(f"Error querying {table}: {e}")
continue

if tables_queried == 0:
messagebox.showinfo("No Results", "No matching records found in any table")
self.export_btn.config(state=tk.DISABLED)
else:
self.export_btn.config(state=tk.NORMAL)
messagebox.showinfo("Search Complete",
f"Found results in {tables_queried} tables")

except Exception as e:
messagebox.showerror("Search Error", f"An error occurred: {str(e)}")

def display_table_data(self, table_name, columns, data):
# Create a frame for this table
frame = ttk.Frame(self.results_notebook)
self.results_notebook.add(frame, text=table_name)

# Create a canvas for scrolling
canvas = tk.Canvas(frame, borderwidth=0)
vsb = ttk.Scrollbar(frame, orient="vertical", command=canvas.yview)
hsb = ttk.Scrollbar(frame, orient="horizontal", command=canvas.xview)
canvas.configure(yscrollcommand=vsb.set, xscrollcommand=hsb.set)

vsb.pack(side="right", fill="y")
hsb.pack(side="bottom", fill="x")
canvas.pack(side="left", fill="both", expand=True)

# Create the table inside canvas
table_frame = ttk.Frame(canvas)
canvas.create_window((0,0), window=table_frame, anchor="nw")

# Configure scrolling
def on_frame_configure(event):
canvas.configure(scrollregion=canvas.bbox("all"))

table_frame.bind("<Configure>", on_frame_configure)

# Bind mouse wheel for scrolling
def on_mousewheel(event):
canvas.yview_scroll(int(-1*(event.delta/120)), "units")

canvas.bind_all("<MouseWheel>", on_mousewheel)

# Display records based on count
if len(data) == 1:
# Single record - display vertically
for i, (col, val) in enumerate(zip(columns, data[0])):
row_frame = ttk.Frame(table_frame)
row_frame.pack(fill=tk.X, padx=5, pady=2)

ttk.Label(row_frame, text=f"{col}:", width=20, anchor="e").pack(side=tk.LEFT)
ttk.Label(row_frame, text=str(val), anchor="w").pack(side=tk.LEFT, fill=tk.X, expand=True)
else:
# Multiple records - display horizontally in a table
tree = ttk.Treeview(table_frame, columns=columns, show="headings")

for col in columns:
tree.heading(col, text=col)
tree.column(col, width=120, anchor="w", stretch=tk.YES)

for row in data:
tree.insert("", tk.END, values=row)

tree.pack(fill=tk.BOTH, expand=True)

# Enable copying
def copy_selection():
selected = tree.selection()
if selected:
items = [tree.item(item)['values'] for item in selected]
self.root.clipboard_clear()
self.root.clipboard_append("\n".join("\t".join(map(str, item)) for item in items))

# Context menu
menu = tk.Menu(self.root, tearoff=0)
menu.add_command(label="Copy Selected", command=copy_selection)
menu.add_command(label="Copy All", command=lambda: self.copy_all_data(table_name))

def show_menu(event):
menu.tk_popup(event.x_root, event.y_root)

tree.bind("<Button-3>", show_menu)

def copy_all_data(self, table_name):
if table_name in self.all_data:
data = self.all_data[table_name]
df = pd.DataFrame(data["data"], columns=data["columns"])
self.root.clipboard_clear()
self.root.clipboard_append(df.to_string(index=False))

def find_in_results(self):
search_term = self.search_entry.get().lower()
if not search_term:
return

current_tab = self.results_notebook.tab(self.results_notebook.select(), "text")
if current_tab not in self.all_data:
return

tab_frame = self.results_notebook.nametowidget(self.results_notebook.select())
canvas = tab_frame.winfo_children()[0]
table_frame = canvas.winfo_children()[0]

# Handle both single record and table views
if len(self.all_data[current_tab]["data"]) == 1:
# Single record view - search in labels
for widget in table_frame.winfo_children():
if isinstance(widget, ttk.Frame):
for label in widget.winfo_children():
if isinstance(label, ttk.Label) and search_term in label.cget("text").lower():
widget.config(style="Highlight.TFrame")
canvas.yview_moveto(widget.winfo_y()/table_frame.winfo_height())
else:
# Table view - search in treeview
tree = table_frame.winfo_children()[0]
for item in tree.get_children():
tree.item(item, tags=())

found = False
for item in tree.get_children():
values = tree.item(item)['values']
for value in values:
if search_term in str(value).lower():
tree.item(item, tags=("found",))
found = True
tree.see(item)
break

if found:
tree.tag_configure("found", background="yellow")

def export_to_excel(self):
if not hasattr(self, 'all_data') or not self.all_data:
messagebox.showerror("Error", "No data to export")
return

file_path = filedialog.asksaveasfilename(
defaultextension=".xlsx",
filetypes=[("Excel files", "*.xlsx"), ("All files", "*.*")]
)

if not file_path:
return

try:
with pd.ExcelWriter(file_path) as writer:
for table_name, table_data in self.all_data.items():
df = pd.DataFrame(table_data["data"], columns=table_data["columns"])
df.to_excel(writer, sheet_name=table_name[:31], index=False)

messagebox.showinfo("Success", f"Data exported to {file_path}")
except Exception as e:
messagebox.showerror("Export Error", f"Failed to export: {str(e)}")

if __name__ == "__main__":
root = tk.Tk()
try:
oracledb.init_oracle_client()
except:
pass # Thin mode will be used

app = OracleDataExplorer(root)
root.mainloop()

Continue reading...