SQL Manager

×

Source Code

import tkinter as tk
from tkinter import filedialog, messagebox, ttk, simpledialog
import sqlite3, os

class SQLiteGUI:
    def __init__(self, root):
        self.root = root
        self.root.title("Free DB Manager (CheapITSupport)")
        self.conn = None
        self.current_table = None
        self.root.overrideredirect(True)
        self.offset_x = 0
        self.offset_y = 0
        self.resizing = False
        self.resize_dir = None
        self.start_x = 0
        self.start_y = 0
        self.start_width = 0
        self.start_height = 0
        self.create_widgets()

    def create_widgets(self):
        title_bar = tk.Frame(self.root, bg="#2e2e2e", relief='raised', bd=0)
        title_bar.pack(side="top", fill="x")
        title_label = tk.Label(title_bar, text="DB Man (CheapITSupport SQLite3 Manager)", bg="#2e2e2e", fg="white", padx=10)
        title_label.pack(side="left", pady=5)
        close_button = tk.Button(title_bar, text="✕", command=self.close_app, bg="#2e2e2e", fg="white",
                                 bd=0, padx=5, pady=2, activebackground="red", activeforeground="white",
                                 font=("Arial", 12, "bold"))
        close_button.pack(side="right", padx=5, pady=2)
        title_bar.bind("", self.start_move)
        title_bar.bind("", self.stop_move)
        title_bar.bind("", self.do_move)
        title_label.bind("", self.start_move)
        title_label.bind("", self.stop_move)
        title_label.bind("", self.do_move)
        main_frame = tk.Frame(self.root, bg="#f0f0f0")
        main_frame.pack(fill="both", expand=True)
        db_frame = tk.Frame(main_frame, bg="#f0f0f0")
        db_frame.pack(pady=10, padx=10, fill=tk.X)
        tk.Label(db_frame, text="Database Path:", bg="#f0f0f0").pack(side=tk.LEFT, padx=5)
        self.db_path_var = tk.StringVar()
        self.db_entry = tk.Entry(db_frame, textvariable=self.db_path_var, width=50)
        self.db_entry.pack(side=tk.LEFT, padx=5)
        tk.Button(db_frame, text="Browse", command=self.browse_db).pack(side=tk.LEFT, padx=5)
        tk.Button(db_frame, text="Connect", command=self.connect_db).pack(side=tk.LEFT, padx=5)
        tk.Button(db_frame, text="Create New DB", command=self.create_new_db).pack(side=tk.LEFT, padx=5)
        tables_frame = tk.Frame(main_frame, bg="#f0f0f0")
        tables_frame.pack(pady=10, padx=10, fill=tk.BOTH, expand=True)
        tables_list_frame = tk.Frame(tables_frame, bg="#f0f0f0")
        tables_list_frame.pack(side=tk.LEFT, fill=tk.Y)
        tk.Label(tables_list_frame, text="Tables:", bg="#f0f0f0").pack()
        self.tables_listbox = tk.Listbox(tables_list_frame, width=30)
        self.tables_listbox.pack(fill=tk.Y, expand=True)
        self.tables_listbox.bind("<>", self.on_table_select)
        scrollbar = tk.Scrollbar(tables_list_frame, orient="vertical")
        scrollbar.config(command=self.tables_listbox.yview)
        scrollbar.pack(side=tk.RIGHT, fill=tk.Y)
        self.tables_listbox.config(yscrollcommand=scrollbar.set)
        data_frame = tk.Frame(tables_frame, bg="#f0f0f0")
        data_frame.pack(side=tk.LEFT, fill=tk.BOTH, expand=True, padx=10)
        self.tree = ttk.Treeview(data_frame, show='headings')
        self.tree.pack(fill=tk.BOTH, expand=True)
        tree_scroll_y = ttk.Scrollbar(data_frame, orient="vertical", command=self.tree.yview)
        tree_scroll_y.pack(side=tk.RIGHT, fill=tk.Y)
        tree_scroll_x = ttk.Scrollbar(data_frame, orient="horizontal", command=self.tree.xview)
        tree_scroll_x.pack(side=tk.BOTTOM, fill=tk.X)
        self.tree.configure(yscrollcommand=tree_scroll_y.set, xscrollcommand=tree_scroll_x.set)
        crud_frame = tk.Frame(main_frame, bg="#f0f0f0")
        crud_frame.pack(pady=10)
        tk.Button(crud_frame, text="Add Record", command=self.add_record).pack(side=tk.LEFT, padx=5)
        tk.Button(crud_frame, text="Edit Record", command=self.edit_record).pack(side=tk.LEFT, padx=5)
        tk.Button(crud_frame, text="Delete Record", command=self.delete_record).pack(side=tk.LEFT, padx=5)
        tk.Button(crud_frame, text="Add Table", command=self.add_table).pack(side=tk.LEFT, padx=5)
        tk.Button(crud_frame, text="Delete Table", command=self.delete_table).pack(side=tk.LEFT, padx=5)
        tk.Button(crud_frame, text="Add Column", command=self.add_column).pack(side=tk.LEFT, padx=5)
        tk.Button(crud_frame, text="Delete Column", command=self.delete_column).pack(side=tk.LEFT, padx=5)
        tk.Button(crud_frame, text="Refresh", command=self.refresh_table).pack(side=tk.LEFT, padx=5)
        search_frame = tk.Frame(main_frame, bg="#f0f0f0")
        search_frame.pack(pady=10, padx=10, fill=tk.X)
        tk.Label(search_frame, text="Search:", bg="#f0f0f0").pack(side=tk.LEFT, padx=5)
        self.search_var = tk.StringVar()
        self.search_entry = tk.Entry(search_frame, textvariable=self.search_var, width=30)
        self.search_entry.pack(side=tk.LEFT, padx=5)
        tk.Label(search_frame, text=" in ", bg="#f0f0f0").pack(side=tk.LEFT)
        self.search_column_var = tk.StringVar()
        self.search_column_combo = ttk.Combobox(search_frame, textvariable=self.search_column_var, state='readonly')
        self.search_column_combo.pack(side=tk.LEFT, padx=5)
        tk.Button(search_frame, text="Search", command=self.search_records).pack(side=tk.LEFT, padx=5)
        tk.Button(search_frame, text="Clear Search", command=self.clear_search).pack(side=tk.LEFT, padx=5)
        main_frame.bind("", self.start_move)
        main_frame.bind("", self.stop_move)
        main_frame.bind("", self.do_move)
        for widget in main_frame.winfo_children():
            widget.bind("", self.start_move)
            widget.bind("", self.stop_move)
            widget.bind("", self.do_move)
        self.resize_grip = tk.Frame(self.root, cursor="sizing", bg="#2e2e2e")
        self.resize_grip.place(relx=1.0, rely=1.0, anchor="se", width=16, height=16)
        self.resize_grip.bind("", self.start_resize)
        self.resize_grip.bind("", self.stop_resize)
        self.resize_grip.bind("", self.do_resize)
    def close_app(self):
        if self.conn:
            self.conn.close()
        self.root.destroy()
    def start_move(self, event):
        if not self.resizing:
            self.offset_x = event.x
            self.offset_y = event.y
    def stop_move(self, event):
        self.offset_x = 0
        self.offset_y = 0
    def do_move(self, event):
        if not self.resizing:
            x = event.x_root - self.offset_x
            y = event.y_root - self.offset_y
            screen_width = self.root.winfo_screenwidth()
            screen_height = self.root.winfo_screenheight()
            window_width = self.root.winfo_width()
            window_height = self.root.winfo_height()
            if x < 0:
                x = 0
            elif x + window_width > screen_width:
                x = screen_width - window_width
            if y < 0:
                y = 0
            elif y + window_height > screen_height:
                y = screen_height - window_height
            self.root.geometry(f'+{x}+{y}')
    def start_resize(self, event):
        self.resizing = True
        self.start_x = event.x_root
        self.start_y = event.y_root
        self.start_width = self.root.winfo_width()
        self.start_height = self.root.winfo_height()
    def stop_resize(self, event):
        self.resizing = False
    def do_resize(self, event):
        if self.resizing:
            dx = event.x_root - self.start_x
            dy = event.y_root - self.start_y
            new_width = self.start_width + dx
            new_height = self.start_height + dy
            screen_width = self.root.winfo_screenwidth()
            screen_height = self.root.winfo_screenheight()
            min_width = 400
            min_height = 300
            max_width = screen_width - self.root.winfo_x()
            max_height = screen_height - self.root.winfo_y()
            if new_width < min_width:
                new_width = min_width
            elif new_width > max_width:
                new_width = max_width
            if new_height < min_height:
                new_height = min_height
            elif new_height > max_height:
                new_height = max_height
            self.root.geometry(f"{new_width}x{new_height}")
    def browse_db(self):
        file_path = filedialog.askopenfilename(
            title="Select SQLite Database",
            filetypes=[("SQLite DB", "*.sqlite *.db"), ("All Files", "*.*")]
        )
        if file_path:
            self.db_path_var.set(file_path)
    def create_new_db(self):
        file_path = filedialog.asksaveasfilename(
            title="Create New SQLite Database",
            defaultextension=".sqlite",
            filetypes=[("SQLite DB", "*.sqlite *.db"), ("All Files", "*.*")]
        )
        if file_path:
            if os.path.exists(file_path):
                response = messagebox.askyesno("Overwrite", "File exists. Overwrite?")
                if not response:
                    return
            try:
                conn = sqlite3.connect(file_path)
                conn.close()
                self.db_path_var.set(file_path)
                messagebox.showinfo("Success", f"Database created at {file_path}")
            except sqlite3.Error as e:
                messagebox.showerror("Error", f"Failed to create database.\n{e}")
    def connect_db(self):
        path = self.db_path_var.get()
        if not path:
            messagebox.showerror("Error", "Please select a database file.")
            return
        try:
            self.conn = sqlite3.connect(path)
            self.populate_tables()
            messagebox.showinfo("Success", f"Connected to {path}")
        except sqlite3.Error as e:
            messagebox.showerror("Error", f"Failed to connect to database.\n{e}")
    def populate_tables(self):
        if not self.conn:
            return
        cursor = self.conn.cursor()
        try:
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
            tables = cursor.fetchall()
            self.tables_listbox.delete(0, tk.END)
            for table in tables:
                self.tables_listbox.insert(tk.END, table[0])
        except sqlite3.Error as e:
            messagebox.showerror("Error", f"Failed to retrieve tables.\n{e}")
    def on_table_select(self, event):
        selection = self.tables_listbox.curselection()
        if selection:
            index = selection[0]
            table_name = self.tables_listbox.get(index)
            self.current_table = table_name
            self.display_table_data(table_name)
            self.populate_search_columns(table_name)
    def display_table_data(self, table_name):
        if not self.conn:
            return
        cursor = self.conn.cursor()
        try:
            cursor.execute(f"PRAGMA table_info({table_name})")
            columns_info = cursor.fetchall()
            columns = [info[1] for info in columns_info]
            self.tree.delete(*self.tree.get_children())
            self.tree["columns"] = columns
            for col in columns:
                self.tree.heading(col, text=col)
                self.tree.column(col, width=100, anchor=tk.CENTER)
            cursor.execute(f"SELECT * FROM {table_name}")
            rows = cursor.fetchall()
            for row in rows:
                self.tree.insert("", tk.END, values=row)
        except sqlite3.Error as e:
            messagebox.showerror("Error", f"Failed to retrieve data from {table_name}.\n{e}")
    def populate_search_columns(self, table_name):
        cursor = self.conn.cursor()
        try:
            cursor.execute(f"PRAGMA table_info({table_name})")
            columns_info = cursor.fetchall()
            columns = [info[1] for info in columns_info]
            columns.insert(0, "All Columns")
            self.search_column_combo['values'] = columns
            self.search_column_combo.current(0)
        except sqlite3.Error as e:
            messagebox.showerror("Error", f"Failed to retrieve columns for search.\n{e}")
    def add_record(self):
        if not self.current_table:
            messagebox.showerror("Error", "No table selected.")
            return
        cursor = self.conn.cursor()
        try:
            cursor.execute(f"PRAGMA table_info({self.current_table})")
            columns_info = cursor.fetchall()
            columns = [info[1] for info in columns_info]
            types = [info[2] for info in columns_info]
            entry_window = tk.Toplevel(self.root)
            entry_window.title(f"Add Record to {self.current_table}")
            x = self.root.winfo_x() + 50
            y = self.root.winfo_y() + 50
            entry_window.geometry(f"+{x}+{y}")
            entries = {}
            for idx, (col, col_type) in enumerate(zip(columns, types)):
                tk.Label(entry_window, text=f"{col} ({col_type})").grid(row=idx, column=0, padx=5, pady=5, sticky=tk.E)
                entry = tk.Entry(entry_window)
                entry.grid(row=idx, column=1, padx=5, pady=5)
                entries[col] = entry
            def submit():
                values = []
                for col in columns:
                    val = entries[col].get()
                    if val == '':
                        val = None
                    values.append(val)
                placeholders = ', '.join(['?'] * len(values))
                columns_names = ', '.join(columns)
                try:
                    cursor.execute(f"INSERT INTO {self.current_table} ({columns_names}) VALUES ({placeholders})", values)
                    self.conn.commit()
                    entry_window.destroy()
                    self.display_table_data(self.current_table)
                    messagebox.showinfo("Success", "Record added successfully.")
                except sqlite3.Error as e:
                    messagebox.showerror("Error", f"Failed to add record.\n{e}")
            tk.Button(entry_window, text="Submit", command=submit).grid(row=len(columns), column=0, columnspan=2, pady=10)
        except sqlite3.Error as e:
            messagebox.showerror("Error", f"Failed to retrieve table info.\n{e}")
    def edit_record(self):
        selected_item = self.tree.focus()
        if not selected_item:
            messagebox.showerror("Error", "No record selected.")
            return
        record = self.tree.item(selected_item)['values']
        cursor = self.conn.cursor()
        try:
            cursor.execute(f"PRAGMA table_info({self.current_table})")
            columns_info = cursor.fetchall()
            columns = [info[1] for info in columns_info]
            types = [info[2] for info in columns_info]
            edit_window = tk.Toplevel(self.root)
            edit_window.title(f"Edit Record in {self.current_table}")
            x = self.root.winfo_x() + 100
            y = self.root.winfo_y() + 100
            edit_window.geometry(f"+{x}+{y}")
            entries = {}
            for idx, (col, col_type, val) in enumerate(zip(columns, types, record)):
                tk.Label(edit_window, text=f"{col} ({col_type})").grid(row=idx, column=0, padx=5, pady=5, sticky=tk.E)
                entry = tk.Entry(edit_window)
                entry.insert(0, val)
                entry.grid(row=idx, column=1, padx=5, pady=5)
                entries[col] = entry
            def submit():
                new_values = []
                for col in columns:
                    val = entries[col].get()
                    if val == '':
                        val = None
                    new_values.append(val)
                primary_key = columns[0]
                primary_value = record[0]
                set_clause = ', '.join([f"{col}=?" for col in columns])
                try:
                    cursor.execute(f"UPDATE {self.current_table} SET {set_clause} WHERE {primary_key} = ?", (*new_values, primary_value))
                    self.conn.commit()
                    edit_window.destroy()
                    self.display_table_data(self.current_table)
                    messagebox.showinfo("Success", "Record updated successfully.")
                except sqlite3.Error as e:
                    messagebox.showerror("Error", f"Failed to update record.\n{e}")
            tk.Button(edit_window, text="Submit", command=submit).grid(row=len(columns), column=0, columnspan=2, pady=10)
        except sqlite3.Error as e:
            messagebox.showerror("Error", f"Failed to retrieve table info.\n{e}")
    def delete_record(self):
        selected_item = self.tree.focus()
        if not selected_item:
            messagebox.showerror("Error", "No record selected.")
            return
        record = self.tree.item(selected_item)['values']
        cursor = self.conn.cursor()
        try:
            cursor.execute(f"PRAGMA table_info({self.current_table})")
            columns_info = cursor.fetchall()
            columns = [info[1] for info in columns_info]
            primary_key = columns[0]
            primary_value = record[0]
            confirm = messagebox.askyesno("Confirm Delete", "Are you sure you want to delete the selected record?")
            if confirm:
                cursor.execute(f"DELETE FROM {self.current_table} WHERE {primary_key} = ?", (primary_value,))
                self.conn.commit()
                self.display_table_data(self.current_table)
                messagebox.showinfo("Success", "Record deleted successfully.")
        except sqlite3.Error as e:
            messagebox.showerror("Error", f"Failed to delete record.\n{e}")
    def add_table(self):
        if not self.conn:
            messagebox.showerror("Error", "No database connected.")
            return
        table_name = simpledialog.askstring("Input", "Enter new table name:", parent=self.root)
        if not table_name:
            return
        num_columns = simpledialog.askinteger("Input", "Enter number of columns:", parent=self.root, minvalue=1)
        if not num_columns:
            return
        columns = []
        for i in range(num_columns):
            col_name = simpledialog.askstring("Input", f"Enter name for column {i+1}:", parent=self.root)
            if not col_name:
                messagebox.showerror("Error", "Column name cannot be empty.")
                return
            col_type = simpledialog.askstring("Input", f"Enter data type for column '{col_name}':", parent=self.root)
            if not col_type:
                messagebox.showerror("Error", "Column type cannot be empty.")
                return
            columns.append(f"{col_name} {col_type}")
        create_stmt = f"CREATE TABLE {table_name} ({', '.join(columns)});"
        cursor = self.conn.cursor()
        try:
            cursor.execute(create_stmt)
            self.conn.commit()
            self.populate_tables()
            messagebox.showinfo("Success", f"Table '{table_name}' created successfully.")
        except sqlite3.Error as e:
            messagebox.showerror("Error", f"Failed to create table.\n{e}")
    def delete_table(self):
        if not self.current_table:
            messagebox.showerror("Error", "No table selected.")
            return
        confirm = messagebox.askyesno("Confirm Delete", f"Are you sure you want to delete table '{self.current_table}'?")
        if confirm:
            cursor = self.conn.cursor()
            try:
                cursor.execute(f"DROP TABLE {self.current_table};")
                self.conn.commit()
                self.populate_tables()
                self.tree.delete(*self.tree.get_children())
                messagebox.showinfo("Success", f"Table '{self.current_table}' deleted successfully.")
                self.current_table = None
            except sqlite3.Error as e:
                messagebox.showerror("Error", f"Failed to delete table.\n{e}")
    def add_column(self):
        if not self.current_table:
            messagebox.showerror("Error", "No table selected.")
            return
        col_name = simpledialog.askstring("Input", "Enter new column name:", parent=self.root)
        if not col_name:
            return
        col_type = simpledialog.askstring("Input", f"Enter data type for column '{col_name}':", parent=self.root)
        if not col_type:
            return
        alter_stmt = f"ALTER TABLE {self.current_table} ADD COLUMN {col_name} {col_type};"
        cursor = self.conn.cursor()
        try:
            cursor.execute(alter_stmt)
            self.conn.commit()
            self.display_table_data(self.current_table)
            messagebox.showinfo("Success", f"Column '{col_name}' added successfully.")
        except sqlite3.Error as e:
            messagebox.showerror("Error", f"Failed to add column.\n{e}")
    def delete_column(self):
        if not self.current_table:
            messagebox.showerror("Error", "No table selected.")
            return
        cursor = self.conn.cursor()
        try:
            cursor.execute(f"PRAGMA table_info({self.current_table})")
            columns_info = cursor.fetchall()
            columns = [info[1] for info in columns_info]
            delete_column_window = tk.Toplevel(self.root)
            delete_column_window.title(f"Delete Column from {self.current_table}")
            x = self.root.winfo_x() + 150
            y = self.root.winfo_y() + 150
            delete_column_window.geometry(f"+{x}+{y}")
            tk.Label(delete_column_window, text="Select Column to Delete:").pack(padx=10, pady=10)
            selected_column_var = tk.StringVar()
            selected_column_combo = ttk.Combobox(delete_column_window, textvariable=selected_column_var, values=columns, state='readonly')
            selected_column_combo.pack(padx=10, pady=5)
            if columns:
                selected_column_combo.current(0)
            def confirm_delete():
                column_to_delete = selected_column_var.get()
                if not column_to_delete:
                    messagebox.showerror("Error", "No column selected.")
                    return
                if messagebox.askyesno("Confirm Delete", f"Are you sure you want to delete column '{column_to_delete}'?"):
                    try:
                        cursor.execute("SELECT sqlite_version();")
                        version = cursor.fetchone()[0]
                        major, minor, patch = map(int, version.split('.'))
                        if (major, minor, patch) < (3, 35, 0):
                            messagebox.showerror("Error", "SQLite version does not support DROP COLUMN. Please update SQLite.")
                            delete_column_window.destroy()
                            return
                        alter_stmt = f"ALTER TABLE {self.current_table} DROP COLUMN {column_to_delete};"
                        cursor.execute(alter_stmt)
                        self.conn.commit()
                        messagebox.showinfo("Success", f"Column '{column_to_delete}' deleted successfully.")
                        self.display_table_data(self.current_table)
                        delete_column_window.destroy()
                    except sqlite3.Error as e:
                        messagebox.showerror("Error", f"Failed to delete column.\n{e}")
            tk.Button(delete_column_window, text="Delete", command=confirm_delete).pack(pady=10)
        except sqlite3.Error as e:
            messagebox.showerror("Error", f"Failed to retrieve columns.\n{e}")
    def refresh_table(self):
        if self.current_table:
            self.display_table_data(self.current_table)
    def search_records(self):
        if not self.current_table:
            messagebox.showerror("Error", "No table selected.")
            return
        search_term = self.search_var.get().strip()
        if not search_term:
            messagebox.showerror("Error", "Please enter a search term.")
            return
        search_column = self.search_column_var.get()
        cursor = self.conn.cursor()
        try:
            if search_column == "All Columns":
                cursor.execute(f"PRAGMA table_info({self.current_table})")
                columns_info = cursor.fetchall()
                columns = [info[1] for info in columns_info]
                like_clause = " OR ".join([f"{col} LIKE ?" for col in columns])
                query = f"SELECT * FROM {self.current_table} WHERE {like_clause}"
                params = [f"%{search_term}%"] * len(columns)
            else:
                query = f"SELECT * FROM {self.current_table} WHERE {search_column} LIKE ?"
                params = [f"%{search_term}%"]
            cursor.execute(query, params)
            rows = cursor.fetchall()
            self.tree.delete(*self.tree.get_children())
            cursor.execute(f"PRAGMA table_info({self.current_table})")
            columns_info = cursor.fetchall()
            columns = [info[1] for info in columns_info]
            for row in rows:
                self.tree.insert("", tk.END, values=row)
        except sqlite3.Error as e:
            messagebox.showerror("Error", f"Failed to perform search.\n{e}")
    def clear_search(self):
        self.search_var.set("")
        if self.current_table:
            self.display_table_data(self.current_table)
def main():
    root = tk.Tk()
    app = SQLiteGUI(root)
    root.geometry("1200x700")
    root.minsize(600, 400)
    root.mainloop()
if __name__ == "__main__":
    main()
            

Copy