Select SQLite database table to export data to CSV file by using Pandas DataFrame

1. Importing Required Libraries

    from sqlalchemy import create_engine, text # For database connection and SQL execution
    from sqlalchemy.exc import SQLAlchemyError # To handle SQL exceptions
    import tkinter as tk # GUI library
    from tkinter import filedialog # File dialog for opening and saving files
    import pandas as pd # Data manipulation library
	

• sqlalchemy: Provides tools for connecting to and executing SQL commands on the database.
• tkinter: Used to create the graphical user interface (GUI).
• pandas: A library for manipulating and exporting data (e.g., to CSV).

2. Defining Global Variables

    my_font = ['Arial', 14, 'normal'] # Font style for radio buttons
    radio_buttons = [] # List to track dynamically created radio buttons
    my_conn = None # Database connection object
	

• my_font: Specifies the font style for dynamically created radio buttons.
• radio_buttons: Keeps track of all dynamically created radio buttons, enabling their removal or update.
• my_conn: Stores the SQLite database connection object for reuse.

3. Function: Connect to SQLite Database and Display Tables

def my_connect(type):
    global my_conn, radio_buttons
    f_types = [
        ('All Files', '*.*'), 
        ('Text Document', '*.txt'),
        ('Database files', '*.db')
    ]
    if type == 'existing':
        file = filedialog.askopenfilename(filetypes=f_types)
    else:
        file = filedialog.asksaveasfilename(filetypes=f_types, defaultextension=".db")
    
    try:
        if file:  # Ensure a file was selected
            l1_path.config(text=f"Connected to: {file}", fg="green")
            file1 = 'sqlite:///' + file 
            my_conn = create_engine(file1).connect()  # Connect to SQLite database

            # Fetch table names
            q = "SELECT name FROM sqlite_master WHERE type = 'table'"
            r_set = my_conn.execute(text(q)) 
            table_names = [row[0] for row in r_set]

            # Remove existing radio buttons
            for button in radio_buttons:
                button.destroy()
            radio_buttons.clear()

            # Create new radio buttons for each table
            for i, t_name in enumerate(table_names):
                rd1 = tk.Radiobutton(
                    my_w, text=t_name, variable=my_var, value=t_name,
                    command=my_selected, fg="blue", bg="light gray", 
                    font=my_font, selectcolor="yellow"
                )
                rd1.grid(row=3 + i, column=0, columnspan=4, sticky="w", padx=10, pady=5)
                radio_buttons.append(rd1)
        else:
            l1_path.config(text="No file selected", fg="red")
    except SQLAlchemyError as e:
        error = str(e.__dict__['orig'])
        l1_path.config(text=f"Error: {error}", fg="red")

• my_connect: Connects to the SQLite database and dynamically creates radio buttons for each table found.
• create_engine: Establishes the database connection.
• my_conn.execute: Executes the SQL query to fetch table names from the database.
• enumerate(): adds a counter to an iterable

4. Function: Handle Table Selection and Export

def my_selected():
    global my_conn
    selected_table = my_var.get()  # Get selected table name from the radio button
    if not selected_table:
        print("No table selected.")
        return

    # Open file dialog to save the CSV file
    file_path = filedialog.asksaveasfilename(
        defaultextension=".csv",
        filetypes=[("CSV files", "*.csv"), ("All files", "*.*")]
    )
    if not file_path:
        print("File save cancelled.")
        return

    try:
        query = f"SELECT * FROM {selected_table}"
        df = pd.read_sql(query, my_conn)  # Load table into a Pandas DataFrame

        df.to_csv(file_path, index=False)  # Save DataFrame as CSV
        print(f"Table '{selected_table}' exported successfully to {file_path}")
    except Exception as e:
        print("An error occurred:", e)

• my_selected(): Triggered when a table is selected ( user clicks a radio button) ; fetches and exports its data to a CSV file.
• pd.read_sql(): Reads the SQL query results into a Pandas DataFrame.
• pd.to_csv(): export a DataFrame to a CSV file.

5. Main Application Window

my_w = tk.Tk()
my_w.geometry("800x500")  # Set window size
my_w.title("plus2net SQLite Connector")  # Title of the window

my_var = tk.StringVar()  # Variable to store the selected table name

# Header
font1 = ('Times', 20, 'bold')
l0 = tk.Label(my_w, text='plus2net.com SQLite Connector', font=font1)
l0.grid(row=0, column=0, columnspan=4, pady=10)

# Path display label
l1_path = tk.Label(my_w, text='', font=('Arial', 12), wraplength=700, anchor="w", justify="left")
l1_path.grid(row=1, column=0, columnspan=4, sticky="w", pady=5, padx=10)

# Connect button
b1 = tk.Button(my_w, text='Browse & Connect', width=20, command=lambda: my_connect('existing'))
b1.grid(row=2, column=0, columnspan=4, pady=10)

# Start Tkinter main loop
my_w.mainloop()

• tk.Tk: Initializes the main application window.
• tk.Label: Adds labels for headers and displaying the database path.
• tk.Button: Creates a button for initiating database connection.
• my_w.mainloop: Starts the Tkinter event loop to display the GUI.

from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError
import tkinter as tk
from tkinter import filedialog
import pandas as pd

# Global variables
my_font = ['Arial', 14, 'normal']
radio_buttons = []  # List to track radio buttons
my_conn = None
# Function to connect to SQLite database
def my_connect(type):
    global my_conn, radio_buttons
    f_types = [('All Files', '*.*'), 
              ('Text Document', '*.txt'),
              ('Database files', "*.db")]
    if type == 'existing':
        file = filedialog.askopenfilename(filetypes=f_types)
    else:
        file = filedialog.asksaveasfilename(filetypes=f_types, defaultextension=".db",)
    
    try:
        if file:  # Ensure a file was selected
            # Update the label with the selected file path
            l1_path.config(text=f"Connected to: {file}", fg="green")
            
            # Connect to the SQLite database
            file1 = 'sqlite:///' + file 
            my_conn = create_engine(file1).connect()

            # Fetch table names SQL query 
            q="SELECT name FROM sqlite_master WHERE type = 'table'"
            r_set = my_conn.execute(text(q)) 
            table_names = [row[0] for row in r_set]

            # Remove any previously created radio buttons
            for button in radio_buttons:
                button.destroy()
            radio_buttons.clear()

            # Create new radio buttons based on table names 
            for i, t_name in enumerate(table_names):
                rd1 = tk.Radiobutton(my_w, text=t_name, variable=my_var, value=t_name,
                       command=my_selected, fg="blue", bg="light gray", font=my_font,
                       selectcolor="yellow", activebackground="light blue",
                       activeforeground="yellow", indicatoron=0, width=15,
                       compound="left")
                rd1.grid(row=3 + i, column=0, columnspan=4, sticky="w", padx=10, pady=5)
                radio_buttons.append(rd1)
        else:
            l1_path.config(text="No file selected", fg="red")
    except SQLAlchemyError as e:
        error = str(e.__dict__['orig'])
        l1_path.config(text=f"Error: {error}", fg="red")
        print("Error:", error)

# Function to handle table selection
def my_selected():
    global my_conn
    selected_table = my_var.get() # from Radio button selection
    if not selected_table:
        print("No table selected.")
        return

    # Open file dialog to save the CSV file
    file_path = filedialog.asksaveasfilename(
        defaultextension=".csv",
        filetypes=[("CSV files", "*.csv"), ("All files", "*.*")]
    )
    if not file_path:
        print("File save cancelled.")
        return

    try:
        # Fetch data from the selected table and create a DataFrame
        query = f"SELECT * FROM {selected_table}"
        df = pd.read_sql(query, my_conn) # Pandas DataFrame created 

        # Export DataFrame to CSV
        df.to_csv(file_path, index=False) # from dataframe to CSV 
        print(f"Table '{selected_table}' exported successfully to {file_path}")
    except Exception as e:
        print("An error occurred:", e)
# Main application window
my_w = tk.Tk()
my_w.geometry("800x500")  # Width & height of window
my_w.title("plus2net SQLite Connector") # title of window 

my_var = tk.StringVar() # to store table name

# Header
font1 = ('Times', 20, 'bold') # higher font for heading
l0 = tk.Label(my_w, text='plus2net.com SQLite Connector', font=font1)
l0.grid(row=0, column=0, columnspan=4, pady=10)

# Path display label
l1_path = tk.Label(my_w, text='', font=('Arial', 12), wraplength=700, anchor="w", justify="left")
l1_path.grid(row=1, column=0, columnspan=4, sticky="w", pady=5, padx=10)

# Connect button
b1 = tk.Button(my_w, text='Browse & Connect', width=20, command=lambda: my_connect('existing'))
b1.grid(row=2, column=0, columnspan=4, pady=10)

# Start Tkinter main loop
my_w.mainloop()