中級者向け No.057
SQLiteダッシュボード
SQLiteのテーブル一覧・SQLクエリ実行・結果グリッド表示ができるGUIクライアント。
📋 目次
1. アプリ概要
SQLiteのテーブル一覧・SQLクエリ実行・結果グリッド表示ができるGUIクライアント。
このアプリはdbカテゴリの実践的なPythonアプリです。使用ライブラリは tkinter（標準ライブラリ）、難易度は ★★★ です。
Pythonの豊富なライブラリを活用することで、実用的なアプリを短いコードで実装できます。ソースコードをコピーして実行し、仕組みを理解したうえでカスタマイズに挑戦してみてください。
GUIアプリ開発はプログラミングの楽しさを実感できる最も効果的な学習方法のひとつです。変数・関数・クラス・イベント処理などの重要な概念が自然と身につきます。
2. 機能一覧
- SQLiteダッシュボードのメイン機能
- 直感的なGUIインターフェース
- 入力値のバリデーション
- エラーハンドリング
- 結果の見やすい表示
- クリア機能付き
3. 事前準備・環境
動作確認環境
Python 3.10 以上 / Windows・Mac・Linux すべて対応
以下の環境で動作確認しています。
- Python 3.10 以上
- OS: Windows 10/11・macOS 12+・Ubuntu 20.04+
4. 完全なソースコード
コードのコピー方法
右上の「コピー」ボタンをクリックするとコードをクリップボードにコピーできます。
app057.py
import tkinter as tk
from tkinter import ttk, messagebox, filedialog
import sqlite3
import os
class App057:
"""SQLiteダッシュボード"""
def __init__(self, root):
self.root = root
self.root.title("SQLiteダッシュボード")
self.root.geometry("1100x680")
self.root.configure(bg="#1e1e1e")
self._conn = None
self._db_path = None
self._build_ui()
def _build_ui(self):
header = tk.Frame(self.root, bg="#252526", pady=6)
header.pack(fill=tk.X)
tk.Label(header, text="🗄 SQLiteダッシュボード",
font=("Noto Sans JP", 12, "bold"),
bg="#252526", fg="#4fc3f7").pack(side=tk.LEFT, padx=12)
ttk.Button(header, text="📂 DBを開く",
command=self._open_db).pack(side=tk.LEFT, padx=4)
ttk.Button(header, text="🆕 新規DB",
command=self._new_db).pack(side=tk.LEFT, padx=2)
ttk.Button(header, text="▶ 実行 (F5)",
command=self._execute).pack(side=tk.LEFT, padx=4)
ttk.Button(header, text="💾 結果CSV出力",
command=self._export_csv).pack(side=tk.LEFT, padx=2)
self.root.bind("<F5>", lambda e: self._execute())
main = ttk.PanedWindow(self.root, orient=tk.HORIZONTAL)
main.pack(fill=tk.BOTH, expand=True, padx=4, pady=4)
# 左: スキーマツリー
left = tk.Frame(main, bg="#252526", width=220)
main.add(left, weight=0)
tk.Label(left, text="スキーマ", bg="#252526", fg="#888",
font=("Arial", 9)).pack(anchor="w", padx=4, pady=2)
self.schema_tree = ttk.Treeview(left, show="tree", selectmode="browse")
ssb = ttk.Scrollbar(left, command=self.schema_tree.yview)
self.schema_tree.configure(yscrollcommand=ssb.set)
ssb.pack(side=tk.RIGHT, fill=tk.Y)
self.schema_tree.pack(fill=tk.BOTH, expand=True)
self.schema_tree.bind("<Double-1>", self._on_schema_dblclick)
# 右
right = tk.Frame(main, bg="#1e1e1e")
main.add(right, weight=1)
vpaned = ttk.PanedWindow(right, orient=tk.VERTICAL)
vpaned.pack(fill=tk.BOTH, expand=True)
# SQL エディタ
editor_f = tk.Frame(vpaned, bg="#1e1e1e")
vpaned.add(editor_f, weight=1)
tk.Label(editor_f, text="SQL エディタ", bg="#1e1e1e", fg="#888",
font=("Arial", 9)).pack(anchor="w")
self.sql_text = tk.Text(editor_f, bg="#0d1117", fg="#d4d4d4",
font=("Courier New", 10), relief=tk.FLAT,
height=8, insertbackground="#fff",
undo=True, wrap=tk.NONE)
sql_sb = ttk.Scrollbar(editor_f, command=self.sql_text.yview)
self.sql_text.configure(yscrollcommand=sql_sb.set)
sql_sb.pack(side=tk.RIGHT, fill=tk.Y)
self.sql_text.pack(fill=tk.BOTH, expand=True)
self.sql_text.insert("1.0", "SELECT * FROM sqlite_master WHERE type='table';")
# 結果
result_f = tk.Frame(vpaned, bg="#1e1e1e")
vpaned.add(result_f, weight=2)
tk.Label(result_f, text="結果", bg="#1e1e1e", fg="#888",
font=("Arial", 9)).pack(anchor="w")
self.result_tree = ttk.Treeview(result_f, show="headings",
selectmode="extended")
rxsb = ttk.Scrollbar(result_f, orient=tk.HORIZONTAL,
command=self.result_tree.xview)
rysb = ttk.Scrollbar(result_f, command=self.result_tree.yview)
self.result_tree.configure(xscrollcommand=rxsb.set,
yscrollcommand=rysb.set)
rysb.pack(side=tk.RIGHT, fill=tk.Y)
self.result_tree.pack(fill=tk.BOTH, expand=True)
rxsb.pack(fill=tk.X)
# エラー/メッセージ
self.msg_text = tk.Text(right, height=3, bg="#1a1a2e", fg="#f48771",
font=("Courier New", 9), relief=tk.FLAT,
state=tk.DISABLED)
self.msg_text.pack(fill=tk.X, padx=0)
self.status_var = tk.StringVar(value="DBファイルを開いてください")
tk.Label(self.root, textvariable=self.status_var,
bg="#252526", fg="#858585", font=("Arial", 9),
anchor="w", padx=8).pack(fill=tk.X, side=tk.BOTTOM)
def _open_db(self):
path = filedialog.askopenfilename(
filetypes=[("SQLite", "*.db *.sqlite *.sqlite3"), ("すべて", "*.*")])
if path:
self._connect(path)
def _new_db(self):
path = filedialog.asksaveasfilename(
defaultextension=".db",
filetypes=[("SQLite", "*.db")])
if path:
self._connect(path)
def _connect(self, path):
try:
if self._conn:
self._conn.close()
self._conn = sqlite3.connect(path)
self._db_path = path
self.root.title(f"SQLiteダッシュボード — {os.path.basename(path)}")
self._refresh_schema()
self.status_var.set(f"接続: {path}")
except Exception as e:
messagebox.showerror("エラー", str(e))
def _refresh_schema(self):
self.schema_tree.delete(*self.schema_tree.get_children())
if not self._conn:
return
try:
tables = self._conn.execute(
"SELECT name FROM sqlite_master WHERE type='table' "
"ORDER BY name").fetchall()
for (tname,) in tables:
node = self.schema_tree.insert("", tk.END, text=f"📋 {tname}",
values=[tname], open=False)
try:
cols = self._conn.execute(
f"PRAGMA table_info({tname})").fetchall()
for col in cols:
col_text = f" {col[1]} ({col[2]})"
pk = " 🔑" if col[5] else ""
self.schema_tree.insert(node, tk.END,
text=col_text + pk)
except Exception:
pass
except Exception as e:
self._show_msg(str(e))
def _on_schema_dblclick(self, event):
sel = self.schema_tree.selection()
if not sel:
return
item = sel[0]
vals = self.schema_tree.item(item, "values")
if vals:
tname = vals[0]
self.sql_text.delete("1.0", tk.END)
self.sql_text.insert("1.0", f"SELECT * FROM {tname} LIMIT 100;")
self._execute()
def _execute(self):
if not self._conn:
messagebox.showwarning("警告", "DBを開いてください")
return
sql = self.sql_text.get("1.0", tk.END).strip()
if not sql:
return
self._show_msg("")
try:
statements = [s.strip() for s in sql.split(";") if s.strip()]
last_select = None
affected = 0
for stmt in statements:
upper = stmt.upper().lstrip()
if upper.startswith("SELECT") or upper.startswith("PRAGMA") or upper.startswith("WITH"):
last_select = stmt
else:
cur = self._conn.execute(stmt)
self._conn.commit()
affected += cur.rowcount if cur.rowcount > 0 else 0
if last_select:
cur = self._conn.execute(last_select)
rows = cur.fetchall()
cols = [d[0] for d in cur.description] if cur.description else []
self._show_results(cols, rows)
self.status_var.set(f"{len(rows)} 行取得")
else:
self._refresh_schema()
self.status_var.set(f"実行完了 ({affected} 行影響)")
except Exception as e:
self._show_msg(str(e))
self.status_var.set("エラー")
def _show_results(self, cols, rows):
self.result_tree.delete(*self.result_tree.get_children())
if not cols:
return
self.result_tree.configure(columns=cols)
for col in cols:
self.result_tree.heading(col, text=col)
self.result_tree.column(col, width=120, anchor="w")
for row in rows[:2000]:
self.result_tree.insert("", tk.END, values=row)
def _show_msg(self, text):
self.msg_text.configure(state=tk.NORMAL)
self.msg_text.delete("1.0", tk.END)
self.msg_text.insert(tk.END, text)
self.msg_text.configure(state=tk.DISABLED)
def _export_csv(self):
if not self.result_tree.get_children():
messagebox.showinfo("情報", "結果がありません")
return
path = filedialog.asksaveasfilename(
defaultextension=".csv",
filetypes=[("CSV", "*.csv")])
if not path:
return
import csv
cols = self.result_tree["columns"]
try:
with open(path, "w", newline="", encoding="utf-8-sig") as f:
writer = csv.writer(f)
writer.writerow(cols)
for iid in self.result_tree.get_children():
writer.writerow(self.result_tree.item(iid, "values"))
messagebox.showinfo("完了", f"CSVを保存しました:\n{path}")
except Exception as e:
messagebox.showerror("エラー", str(e))
if __name__ == "__main__":
root = tk.Tk()
app = App057(root)
root.mainloop()
5. コード解説
SQLiteダッシュボードのコードを詳しく解説します。クラスベースの設計で各機能を整理して実装しています。
クラス設計とコンストラクタ
App057クラスにアプリの全機能をまとめています。__init__でウィンドウ設定、_build_ui()でUI構築、process()でメイン処理を担当します。責任の分離により、コードが読みやすくなります。
import tkinter as tk
from tkinter import ttk, messagebox, filedialog
import sqlite3
import os
class App057:
"""SQLiteダッシュボード"""
def __init__(self, root):
self.root = root
self.root.title("SQLiteダッシュボード")
self.root.geometry("1100x680")
self.root.configure(bg="#1e1e1e")
self._conn = None
self._db_path = None
self._build_ui()
def _build_ui(self):
header = tk.Frame(self.root, bg="#252526", pady=6)
header.pack(fill=tk.X)
tk.Label(header, text="🗄 SQLiteダッシュボード",
font=("Noto Sans JP", 12, "bold"),
bg="#252526", fg="#4fc3f7").pack(side=tk.LEFT, padx=12)
ttk.Button(header, text="📂 DBを開く",
command=self._open_db).pack(side=tk.LEFT, padx=4)
ttk.Button(header, text="🆕 新規DB",
command=self._new_db).pack(side=tk.LEFT, padx=2)
ttk.Button(header, text="▶ 実行 (F5)",
command=self._execute).pack(side=tk.LEFT, padx=4)
ttk.Button(header, text="💾 結果CSV出力",
command=self._export_csv).pack(side=tk.LEFT, padx=2)
self.root.bind("<F5>", lambda e: self._execute())
main = ttk.PanedWindow(self.root, orient=tk.HORIZONTAL)
main.pack(fill=tk.BOTH, expand=True, padx=4, pady=4)
# 左: スキーマツリー
left = tk.Frame(main, bg="#252526", width=220)
main.add(left, weight=0)
tk.Label(left, text="スキーマ", bg="#252526", fg="#888",
font=("Arial", 9)).pack(anchor="w", padx=4, pady=2)
self.schema_tree = ttk.Treeview(left, show="tree", selectmode="browse")
ssb = ttk.Scrollbar(left, command=self.schema_tree.yview)
self.schema_tree.configure(yscrollcommand=ssb.set)
ssb.pack(side=tk.RIGHT, fill=tk.Y)
self.schema_tree.pack(fill=tk.BOTH, expand=True)
self.schema_tree.bind("<Double-1>", self._on_schema_dblclick)
# 右
right = tk.Frame(main, bg="#1e1e1e")
main.add(right, weight=1)
vpaned = ttk.PanedWindow(right, orient=tk.VERTICAL)
vpaned.pack(fill=tk.BOTH, expand=True)
# SQL エディタ
editor_f = tk.Frame(vpaned, bg="#1e1e1e")
vpaned.add(editor_f, weight=1)
tk.Label(editor_f, text="SQL エディタ", bg="#1e1e1e", fg="#888",
font=("Arial", 9)).pack(anchor="w")
self.sql_text = tk.Text(editor_f, bg="#0d1117", fg="#d4d4d4",
font=("Courier New", 10), relief=tk.FLAT,
height=8, insertbackground="#fff",
undo=True, wrap=tk.NONE)
sql_sb = ttk.Scrollbar(editor_f, command=self.sql_text.yview)
self.sql_text.configure(yscrollcommand=sql_sb.set)
sql_sb.pack(side=tk.RIGHT, fill=tk.Y)
self.sql_text.pack(fill=tk.BOTH, expand=True)
self.sql_text.insert("1.0", "SELECT * FROM sqlite_master WHERE type='table';")
# 結果
result_f = tk.Frame(vpaned, bg="#1e1e1e")
vpaned.add(result_f, weight=2)
tk.Label(result_f, text="結果", bg="#1e1e1e", fg="#888",
font=("Arial", 9)).pack(anchor="w")
self.result_tree = ttk.Treeview(result_f, show="headings",
selectmode="extended")
rxsb = ttk.Scrollbar(result_f, orient=tk.HORIZONTAL,
command=self.result_tree.xview)
rysb = ttk.Scrollbar(result_f, command=self.result_tree.yview)
self.result_tree.configure(xscrollcommand=rxsb.set,
yscrollcommand=rysb.set)
rysb.pack(side=tk.RIGHT, fill=tk.Y)
self.result_tree.pack(fill=tk.BOTH, expand=True)
rxsb.pack(fill=tk.X)
# エラー/メッセージ
self.msg_text = tk.Text(right, height=3, bg="#1a1a2e", fg="#f48771",
font=("Courier New", 9), relief=tk.FLAT,
state=tk.DISABLED)
self.msg_text.pack(fill=tk.X, padx=0)
self.status_var = tk.StringVar(value="DBファイルを開いてください")
tk.Label(self.root, textvariable=self.status_var,
bg="#252526", fg="#858585", font=("Arial", 9),
anchor="w", padx=8).pack(fill=tk.X, side=tk.BOTTOM)
def _open_db(self):
path = filedialog.askopenfilename(
filetypes=[("SQLite", "*.db *.sqlite *.sqlite3"), ("すべて", "*.*")])
if path:
self._connect(path)
def _new_db(self):
path = filedialog.asksaveasfilename(
defaultextension=".db",
filetypes=[("SQLite", "*.db")])
if path:
self._connect(path)
def _connect(self, path):
try:
if self._conn:
self._conn.close()
self._conn = sqlite3.connect(path)
self._db_path = path
self.root.title(f"SQLiteダッシュボード — {os.path.basename(path)}")
self._refresh_schema()
self.status_var.set(f"接続: {path}")
except Exception as e:
messagebox.showerror("エラー", str(e))
def _refresh_schema(self):
self.schema_tree.delete(*self.schema_tree.get_children())
if not self._conn:
return
try:
tables = self._conn.execute(
"SELECT name FROM sqlite_master WHERE type='table' "
"ORDER BY name").fetchall()
for (tname,) in tables:
node = self.schema_tree.insert("", tk.END, text=f"📋 {tname}",
values=[tname], open=False)
try:
cols = self._conn.execute(
f"PRAGMA table_info({tname})").fetchall()
for col in cols:
col_text = f" {col[1]} ({col[2]})"
pk = " 🔑" if col[5] else ""
self.schema_tree.insert(node, tk.END,
text=col_text + pk)
except Exception:
pass
except Exception as e:
self._show_msg(str(e))
def _on_schema_dblclick(self, event):
sel = self.schema_tree.selection()
if not sel:
return
item = sel[0]
vals = self.schema_tree.item(item, "values")
if vals:
tname = vals[0]
self.sql_text.delete("1.0", tk.END)
self.sql_text.insert("1.0", f"SELECT * FROM {tname} LIMIT 100;")
self._execute()
def _execute(self):
if not self._conn:
messagebox.showwarning("警告", "DBを開いてください")
return
sql = self.sql_text.get("1.0", tk.END).strip()
if not sql:
return
self._show_msg("")
try:
statements = [s.strip() for s in sql.split(";") if s.strip()]
last_select = None
affected = 0
for stmt in statements:
upper = stmt.upper().lstrip()
if upper.startswith("SELECT") or upper.startswith("PRAGMA") or upper.startswith("WITH"):
last_select = stmt
else:
cur = self._conn.execute(stmt)
self._conn.commit()
affected += cur.rowcount if cur.rowcount > 0 else 0
if last_select:
cur = self._conn.execute(last_select)
rows = cur.fetchall()
cols = [d[0] for d in cur.description] if cur.description else []
self._show_results(cols, rows)
self.status_var.set(f"{len(rows)} 行取得")
else:
self._refresh_schema()
self.status_var.set(f"実行完了 ({affected} 行影響)")
except Exception as e:
self._show_msg(str(e))
self.status_var.set("エラー")
def _show_results(self, cols, rows):
self.result_tree.delete(*self.result_tree.get_children())
if not cols:
return
self.result_tree.configure(columns=cols)
for col in cols:
self.result_tree.heading(col, text=col)
self.result_tree.column(col, width=120, anchor="w")
for row in rows[:2000]:
self.result_tree.insert("", tk.END, values=row)
def _show_msg(self, text):
self.msg_text.configure(state=tk.NORMAL)
self.msg_text.delete("1.0", tk.END)
self.msg_text.insert(tk.END, text)
self.msg_text.configure(state=tk.DISABLED)
def _export_csv(self):
if not self.result_tree.get_children():
messagebox.showinfo("情報", "結果がありません")
return
path = filedialog.asksaveasfilename(
defaultextension=".csv",
filetypes=[("CSV", "*.csv")])
if not path:
return
import csv
cols = self.result_tree["columns"]
try:
with open(path, "w", newline="", encoding="utf-8-sig") as f:
writer = csv.writer(f)
writer.writerow(cols)
for iid in self.result_tree.get_children():
writer.writerow(self.result_tree.item(iid, "values"))
messagebox.showinfo("完了", f"CSVを保存しました:\n{path}")
except Exception as e:
messagebox.showerror("エラー", str(e))
if __name__ == "__main__":
root = tk.Tk()
app = App057(root)
root.mainloop()
UIレイアウトの構築
LabelFrameで入力エリアと結果エリアを視覚的に分けています。pack()で縦に並べ、expand=Trueで結果エリアが画面いっぱいに広がるよう設定しています。
import tkinter as tk
from tkinter import ttk, messagebox, filedialog
import sqlite3
import os
class App057:
"""SQLiteダッシュボード"""
def __init__(self, root):
self.root = root
self.root.title("SQLiteダッシュボード")
self.root.geometry("1100x680")
self.root.configure(bg="#1e1e1e")
self._conn = None
self._db_path = None
self._build_ui()
def _build_ui(self):
header = tk.Frame(self.root, bg="#252526", pady=6)
header.pack(fill=tk.X)
tk.Label(header, text="🗄 SQLiteダッシュボード",
font=("Noto Sans JP", 12, "bold"),
bg="#252526", fg="#4fc3f7").pack(side=tk.LEFT, padx=12)
ttk.Button(header, text="📂 DBを開く",
command=self._open_db).pack(side=tk.LEFT, padx=4)
ttk.Button(header, text="🆕 新規DB",
command=self._new_db).pack(side=tk.LEFT, padx=2)
ttk.Button(header, text="▶ 実行 (F5)",
command=self._execute).pack(side=tk.LEFT, padx=4)
ttk.Button(header, text="💾 結果CSV出力",
command=self._export_csv).pack(side=tk.LEFT, padx=2)
self.root.bind("<F5>", lambda e: self._execute())
main = ttk.PanedWindow(self.root, orient=tk.HORIZONTAL)
main.pack(fill=tk.BOTH, expand=True, padx=4, pady=4)
# 左: スキーマツリー
left = tk.Frame(main, bg="#252526", width=220)
main.add(left, weight=0)
tk.Label(left, text="スキーマ", bg="#252526", fg="#888",
font=("Arial", 9)).pack(anchor="w", padx=4, pady=2)
self.schema_tree = ttk.Treeview(left, show="tree", selectmode="browse")
ssb = ttk.Scrollbar(left, command=self.schema_tree.yview)
self.schema_tree.configure(yscrollcommand=ssb.set)
ssb.pack(side=tk.RIGHT, fill=tk.Y)
self.schema_tree.pack(fill=tk.BOTH, expand=True)
self.schema_tree.bind("<Double-1>", self._on_schema_dblclick)
# 右
right = tk.Frame(main, bg="#1e1e1e")
main.add(right, weight=1)
vpaned = ttk.PanedWindow(right, orient=tk.VERTICAL)
vpaned.pack(fill=tk.BOTH, expand=True)
# SQL エディタ
editor_f = tk.Frame(vpaned, bg="#1e1e1e")
vpaned.add(editor_f, weight=1)
tk.Label(editor_f, text="SQL エディタ", bg="#1e1e1e", fg="#888",
font=("Arial", 9)).pack(anchor="w")
self.sql_text = tk.Text(editor_f, bg="#0d1117", fg="#d4d4d4",
font=("Courier New", 10), relief=tk.FLAT,
height=8, insertbackground="#fff",
undo=True, wrap=tk.NONE)
sql_sb = ttk.Scrollbar(editor_f, command=self.sql_text.yview)
self.sql_text.configure(yscrollcommand=sql_sb.set)
sql_sb.pack(side=tk.RIGHT, fill=tk.Y)
self.sql_text.pack(fill=tk.BOTH, expand=True)
self.sql_text.insert("1.0", "SELECT * FROM sqlite_master WHERE type='table';")
# 結果
result_f = tk.Frame(vpaned, bg="#1e1e1e")
vpaned.add(result_f, weight=2)
tk.Label(result_f, text="結果", bg="#1e1e1e", fg="#888",
font=("Arial", 9)).pack(anchor="w")
self.result_tree = ttk.Treeview(result_f, show="headings",
selectmode="extended")
rxsb = ttk.Scrollbar(result_f, orient=tk.HORIZONTAL,
command=self.result_tree.xview)
rysb = ttk.Scrollbar(result_f, command=self.result_tree.yview)
self.result_tree.configure(xscrollcommand=rxsb.set,
yscrollcommand=rysb.set)
rysb.pack(side=tk.RIGHT, fill=tk.Y)
self.result_tree.pack(fill=tk.BOTH, expand=True)
rxsb.pack(fill=tk.X)
# エラー/メッセージ
self.msg_text = tk.Text(right, height=3, bg="#1a1a2e", fg="#f48771",
font=("Courier New", 9), relief=tk.FLAT,
state=tk.DISABLED)
self.msg_text.pack(fill=tk.X, padx=0)
self.status_var = tk.StringVar(value="DBファイルを開いてください")
tk.Label(self.root, textvariable=self.status_var,
bg="#252526", fg="#858585", font=("Arial", 9),
anchor="w", padx=8).pack(fill=tk.X, side=tk.BOTTOM)
def _open_db(self):
path = filedialog.askopenfilename(
filetypes=[("SQLite", "*.db *.sqlite *.sqlite3"), ("すべて", "*.*")])
if path:
self._connect(path)
def _new_db(self):
path = filedialog.asksaveasfilename(
defaultextension=".db",
filetypes=[("SQLite", "*.db")])
if path:
self._connect(path)
def _connect(self, path):
try:
if self._conn:
self._conn.close()
self._conn = sqlite3.connect(path)
self._db_path = path
self.root.title(f"SQLiteダッシュボード — {os.path.basename(path)}")
self._refresh_schema()
self.status_var.set(f"接続: {path}")
except Exception as e:
messagebox.showerror("エラー", str(e))
def _refresh_schema(self):
self.schema_tree.delete(*self.schema_tree.get_children())
if not self._conn:
return
try:
tables = self._conn.execute(
"SELECT name FROM sqlite_master WHERE type='table' "
"ORDER BY name").fetchall()
for (tname,) in tables:
node = self.schema_tree.insert("", tk.END, text=f"📋 {tname}",
values=[tname], open=False)
try:
cols = self._conn.execute(
f"PRAGMA table_info({tname})").fetchall()
for col in cols:
col_text = f" {col[1]} ({col[2]})"
pk = " 🔑" if col[5] else ""
self.schema_tree.insert(node, tk.END,
text=col_text + pk)
except Exception:
pass
except Exception as e:
self._show_msg(str(e))
def _on_schema_dblclick(self, event):
sel = self.schema_tree.selection()
if not sel:
return
item = sel[0]
vals = self.schema_tree.item(item, "values")
if vals:
tname = vals[0]
self.sql_text.delete("1.0", tk.END)
self.sql_text.insert("1.0", f"SELECT * FROM {tname} LIMIT 100;")
self._execute()
def _execute(self):
if not self._conn:
messagebox.showwarning("警告", "DBを開いてください")
return
sql = self.sql_text.get("1.0", tk.END).strip()
if not sql:
return
self._show_msg("")
try:
statements = [s.strip() for s in sql.split(";") if s.strip()]
last_select = None
affected = 0
for stmt in statements:
upper = stmt.upper().lstrip()
if upper.startswith("SELECT") or upper.startswith("PRAGMA") or upper.startswith("WITH"):
last_select = stmt
else:
cur = self._conn.execute(stmt)
self._conn.commit()
affected += cur.rowcount if cur.rowcount > 0 else 0
if last_select:
cur = self._conn.execute(last_select)
rows = cur.fetchall()
cols = [d[0] for d in cur.description] if cur.description else []
self._show_results(cols, rows)
self.status_var.set(f"{len(rows)} 行取得")
else:
self._refresh_schema()
self.status_var.set(f"実行完了 ({affected} 行影響)")
except Exception as e:
self._show_msg(str(e))
self.status_var.set("エラー")
def _show_results(self, cols, rows):
self.result_tree.delete(*self.result_tree.get_children())
if not cols:
return
self.result_tree.configure(columns=cols)
for col in cols:
self.result_tree.heading(col, text=col)
self.result_tree.column(col, width=120, anchor="w")
for row in rows[:2000]:
self.result_tree.insert("", tk.END, values=row)
def _show_msg(self, text):
self.msg_text.configure(state=tk.NORMAL)
self.msg_text.delete("1.0", tk.END)
self.msg_text.insert(tk.END, text)
self.msg_text.configure(state=tk.DISABLED)
def _export_csv(self):
if not self.result_tree.get_children():
messagebox.showinfo("情報", "結果がありません")
return
path = filedialog.asksaveasfilename(
defaultextension=".csv",
filetypes=[("CSV", "*.csv")])
if not path:
return
import csv
cols = self.result_tree["columns"]
try:
with open(path, "w", newline="", encoding="utf-8-sig") as f:
writer = csv.writer(f)
writer.writerow(cols)
for iid in self.result_tree.get_children():
writer.writerow(self.result_tree.item(iid, "values"))
messagebox.showinfo("完了", f"CSVを保存しました:\n{path}")
except Exception as e:
messagebox.showerror("エラー", str(e))
if __name__ == "__main__":
root = tk.Tk()
app = App057(root)
root.mainloop()
イベント処理
ボタンのcommand引数でクリックイベントを、bind('
import tkinter as tk
from tkinter import ttk, messagebox, filedialog
import sqlite3
import os
class App057:
"""SQLiteダッシュボード"""
def __init__(self, root):
self.root = root
self.root.title("SQLiteダッシュボード")
self.root.geometry("1100x680")
self.root.configure(bg="#1e1e1e")
self._conn = None
self._db_path = None
self._build_ui()
def _build_ui(self):
header = tk.Frame(self.root, bg="#252526", pady=6)
header.pack(fill=tk.X)
tk.Label(header, text="🗄 SQLiteダッシュボード",
font=("Noto Sans JP", 12, "bold"),
bg="#252526", fg="#4fc3f7").pack(side=tk.LEFT, padx=12)
ttk.Button(header, text="📂 DBを開く",
command=self._open_db).pack(side=tk.LEFT, padx=4)
ttk.Button(header, text="🆕 新規DB",
command=self._new_db).pack(side=tk.LEFT, padx=2)
ttk.Button(header, text="▶ 実行 (F5)",
command=self._execute).pack(side=tk.LEFT, padx=4)
ttk.Button(header, text="💾 結果CSV出力",
command=self._export_csv).pack(side=tk.LEFT, padx=2)
self.root.bind("<F5>", lambda e: self._execute())
main = ttk.PanedWindow(self.root, orient=tk.HORIZONTAL)
main.pack(fill=tk.BOTH, expand=True, padx=4, pady=4)
# 左: スキーマツリー
left = tk.Frame(main, bg="#252526", width=220)
main.add(left, weight=0)
tk.Label(left, text="スキーマ", bg="#252526", fg="#888",
font=("Arial", 9)).pack(anchor="w", padx=4, pady=2)
self.schema_tree = ttk.Treeview(left, show="tree", selectmode="browse")
ssb = ttk.Scrollbar(left, command=self.schema_tree.yview)
self.schema_tree.configure(yscrollcommand=ssb.set)
ssb.pack(side=tk.RIGHT, fill=tk.Y)
self.schema_tree.pack(fill=tk.BOTH, expand=True)
self.schema_tree.bind("<Double-1>", self._on_schema_dblclick)
# 右
right = tk.Frame(main, bg="#1e1e1e")
main.add(right, weight=1)
vpaned = ttk.PanedWindow(right, orient=tk.VERTICAL)
vpaned.pack(fill=tk.BOTH, expand=True)
# SQL エディタ
editor_f = tk.Frame(vpaned, bg="#1e1e1e")
vpaned.add(editor_f, weight=1)
tk.Label(editor_f, text="SQL エディタ", bg="#1e1e1e", fg="#888",
font=("Arial", 9)).pack(anchor="w")
self.sql_text = tk.Text(editor_f, bg="#0d1117", fg="#d4d4d4",
font=("Courier New", 10), relief=tk.FLAT,
height=8, insertbackground="#fff",
undo=True, wrap=tk.NONE)
sql_sb = ttk.Scrollbar(editor_f, command=self.sql_text.yview)
self.sql_text.configure(yscrollcommand=sql_sb.set)
sql_sb.pack(side=tk.RIGHT, fill=tk.Y)
self.sql_text.pack(fill=tk.BOTH, expand=True)
self.sql_text.insert("1.0", "SELECT * FROM sqlite_master WHERE type='table';")
# 結果
result_f = tk.Frame(vpaned, bg="#1e1e1e")
vpaned.add(result_f, weight=2)
tk.Label(result_f, text="結果", bg="#1e1e1e", fg="#888",
font=("Arial", 9)).pack(anchor="w")
self.result_tree = ttk.Treeview(result_f, show="headings",
selectmode="extended")
rxsb = ttk.Scrollbar(result_f, orient=tk.HORIZONTAL,
command=self.result_tree.xview)
rysb = ttk.Scrollbar(result_f, command=self.result_tree.yview)
self.result_tree.configure(xscrollcommand=rxsb.set,
yscrollcommand=rysb.set)
rysb.pack(side=tk.RIGHT, fill=tk.Y)
self.result_tree.pack(fill=tk.BOTH, expand=True)
rxsb.pack(fill=tk.X)
# エラー/メッセージ
self.msg_text = tk.Text(right, height=3, bg="#1a1a2e", fg="#f48771",
font=("Courier New", 9), relief=tk.FLAT,
state=tk.DISABLED)
self.msg_text.pack(fill=tk.X, padx=0)
self.status_var = tk.StringVar(value="DBファイルを開いてください")
tk.Label(self.root, textvariable=self.status_var,
bg="#252526", fg="#858585", font=("Arial", 9),
anchor="w", padx=8).pack(fill=tk.X, side=tk.BOTTOM)
def _open_db(self):
path = filedialog.askopenfilename(
filetypes=[("SQLite", "*.db *.sqlite *.sqlite3"), ("すべて", "*.*")])
if path:
self._connect(path)
def _new_db(self):
path = filedialog.asksaveasfilename(
defaultextension=".db",
filetypes=[("SQLite", "*.db")])
if path:
self._connect(path)
def _connect(self, path):
try:
if self._conn:
self._conn.close()
self._conn = sqlite3.connect(path)
self._db_path = path
self.root.title(f"SQLiteダッシュボード — {os.path.basename(path)}")
self._refresh_schema()
self.status_var.set(f"接続: {path}")
except Exception as e:
messagebox.showerror("エラー", str(e))
def _refresh_schema(self):
self.schema_tree.delete(*self.schema_tree.get_children())
if not self._conn:
return
try:
tables = self._conn.execute(
"SELECT name FROM sqlite_master WHERE type='table' "
"ORDER BY name").fetchall()
for (tname,) in tables:
node = self.schema_tree.insert("", tk.END, text=f"📋 {tname}",
values=[tname], open=False)
try:
cols = self._conn.execute(
f"PRAGMA table_info({tname})").fetchall()
for col in cols:
col_text = f" {col[1]} ({col[2]})"
pk = " 🔑" if col[5] else ""
self.schema_tree.insert(node, tk.END,
text=col_text + pk)
except Exception:
pass
except Exception as e:
self._show_msg(str(e))
def _on_schema_dblclick(self, event):
sel = self.schema_tree.selection()
if not sel:
return
item = sel[0]
vals = self.schema_tree.item(item, "values")
if vals:
tname = vals[0]
self.sql_text.delete("1.0", tk.END)
self.sql_text.insert("1.0", f"SELECT * FROM {tname} LIMIT 100;")
self._execute()
def _execute(self):
if not self._conn:
messagebox.showwarning("警告", "DBを開いてください")
return
sql = self.sql_text.get("1.0", tk.END).strip()
if not sql:
return
self._show_msg("")
try:
statements = [s.strip() for s in sql.split(";") if s.strip()]
last_select = None
affected = 0
for stmt in statements:
upper = stmt.upper().lstrip()
if upper.startswith("SELECT") or upper.startswith("PRAGMA") or upper.startswith("WITH"):
last_select = stmt
else:
cur = self._conn.execute(stmt)
self._conn.commit()
affected += cur.rowcount if cur.rowcount > 0 else 0
if last_select:
cur = self._conn.execute(last_select)
rows = cur.fetchall()
cols = [d[0] for d in cur.description] if cur.description else []
self._show_results(cols, rows)
self.status_var.set(f"{len(rows)} 行取得")
else:
self._refresh_schema()
self.status_var.set(f"実行完了 ({affected} 行影響)")
except Exception as e:
self._show_msg(str(e))
self.status_var.set("エラー")
def _show_results(self, cols, rows):
self.result_tree.delete(*self.result_tree.get_children())
if not cols:
return
self.result_tree.configure(columns=cols)
for col in cols:
self.result_tree.heading(col, text=col)
self.result_tree.column(col, width=120, anchor="w")
for row in rows[:2000]:
self.result_tree.insert("", tk.END, values=row)
def _show_msg(self, text):
self.msg_text.configure(state=tk.NORMAL)
self.msg_text.delete("1.0", tk.END)
self.msg_text.insert(tk.END, text)
self.msg_text.configure(state=tk.DISABLED)
def _export_csv(self):
if not self.result_tree.get_children():
messagebox.showinfo("情報", "結果がありません")
return
path = filedialog.asksaveasfilename(
defaultextension=".csv",
filetypes=[("CSV", "*.csv")])
if not path:
return
import csv
cols = self.result_tree["columns"]
try:
with open(path, "w", newline="", encoding="utf-8-sig") as f:
writer = csv.writer(f)
writer.writerow(cols)
for iid in self.result_tree.get_children():
writer.writerow(self.result_tree.item(iid, "values"))
messagebox.showinfo("完了", f"CSVを保存しました:\n{path}")
except Exception as e:
messagebox.showerror("エラー", str(e))
if __name__ == "__main__":
root = tk.Tk()
app = App057(root)
root.mainloop()
Textウィジェットでの結果表示
tk.Textウィジェットをstate=DISABLED（読み取り専用）で作成し、更新時はNORMALに変更してinsert()で内容を書き込み、再びDISABLEDに戻します。
import tkinter as tk
from tkinter import ttk, messagebox, filedialog
import sqlite3
import os
class App057:
"""SQLiteダッシュボード"""
def __init__(self, root):
self.root = root
self.root.title("SQLiteダッシュボード")
self.root.geometry("1100x680")
self.root.configure(bg="#1e1e1e")
self._conn = None
self._db_path = None
self._build_ui()
def _build_ui(self):
header = tk.Frame(self.root, bg="#252526", pady=6)
header.pack(fill=tk.X)
tk.Label(header, text="🗄 SQLiteダッシュボード",
font=("Noto Sans JP", 12, "bold"),
bg="#252526", fg="#4fc3f7").pack(side=tk.LEFT, padx=12)
ttk.Button(header, text="📂 DBを開く",
command=self._open_db).pack(side=tk.LEFT, padx=4)
ttk.Button(header, text="🆕 新規DB",
command=self._new_db).pack(side=tk.LEFT, padx=2)
ttk.Button(header, text="▶ 実行 (F5)",
command=self._execute).pack(side=tk.LEFT, padx=4)
ttk.Button(header, text="💾 結果CSV出力",
command=self._export_csv).pack(side=tk.LEFT, padx=2)
self.root.bind("<F5>", lambda e: self._execute())
main = ttk.PanedWindow(self.root, orient=tk.HORIZONTAL)
main.pack(fill=tk.BOTH, expand=True, padx=4, pady=4)
# 左: スキーマツリー
left = tk.Frame(main, bg="#252526", width=220)
main.add(left, weight=0)
tk.Label(left, text="スキーマ", bg="#252526", fg="#888",
font=("Arial", 9)).pack(anchor="w", padx=4, pady=2)
self.schema_tree = ttk.Treeview(left, show="tree", selectmode="browse")
ssb = ttk.Scrollbar(left, command=self.schema_tree.yview)
self.schema_tree.configure(yscrollcommand=ssb.set)
ssb.pack(side=tk.RIGHT, fill=tk.Y)
self.schema_tree.pack(fill=tk.BOTH, expand=True)
self.schema_tree.bind("<Double-1>", self._on_schema_dblclick)
# 右
right = tk.Frame(main, bg="#1e1e1e")
main.add(right, weight=1)
vpaned = ttk.PanedWindow(right, orient=tk.VERTICAL)
vpaned.pack(fill=tk.BOTH, expand=True)
# SQL エディタ
editor_f = tk.Frame(vpaned, bg="#1e1e1e")
vpaned.add(editor_f, weight=1)
tk.Label(editor_f, text="SQL エディタ", bg="#1e1e1e", fg="#888",
font=("Arial", 9)).pack(anchor="w")
self.sql_text = tk.Text(editor_f, bg="#0d1117", fg="#d4d4d4",
font=("Courier New", 10), relief=tk.FLAT,
height=8, insertbackground="#fff",
undo=True, wrap=tk.NONE)
sql_sb = ttk.Scrollbar(editor_f, command=self.sql_text.yview)
self.sql_text.configure(yscrollcommand=sql_sb.set)
sql_sb.pack(side=tk.RIGHT, fill=tk.Y)
self.sql_text.pack(fill=tk.BOTH, expand=True)
self.sql_text.insert("1.0", "SELECT * FROM sqlite_master WHERE type='table';")
# 結果
result_f = tk.Frame(vpaned, bg="#1e1e1e")
vpaned.add(result_f, weight=2)
tk.Label(result_f, text="結果", bg="#1e1e1e", fg="#888",
font=("Arial", 9)).pack(anchor="w")
self.result_tree = ttk.Treeview(result_f, show="headings",
selectmode="extended")
rxsb = ttk.Scrollbar(result_f, orient=tk.HORIZONTAL,
command=self.result_tree.xview)
rysb = ttk.Scrollbar(result_f, command=self.result_tree.yview)
self.result_tree.configure(xscrollcommand=rxsb.set,
yscrollcommand=rysb.set)
rysb.pack(side=tk.RIGHT, fill=tk.Y)
self.result_tree.pack(fill=tk.BOTH, expand=True)
rxsb.pack(fill=tk.X)
# エラー/メッセージ
self.msg_text = tk.Text(right, height=3, bg="#1a1a2e", fg="#f48771",
font=("Courier New", 9), relief=tk.FLAT,
state=tk.DISABLED)
self.msg_text.pack(fill=tk.X, padx=0)
self.status_var = tk.StringVar(value="DBファイルを開いてください")
tk.Label(self.root, textvariable=self.status_var,
bg="#252526", fg="#858585", font=("Arial", 9),
anchor="w", padx=8).pack(fill=tk.X, side=tk.BOTTOM)
def _open_db(self):
path = filedialog.askopenfilename(
filetypes=[("SQLite", "*.db *.sqlite *.sqlite3"), ("すべて", "*.*")])
if path:
self._connect(path)
def _new_db(self):
path = filedialog.asksaveasfilename(
defaultextension=".db",
filetypes=[("SQLite", "*.db")])
if path:
self._connect(path)
def _connect(self, path):
try:
if self._conn:
self._conn.close()
self._conn = sqlite3.connect(path)
self._db_path = path
self.root.title(f"SQLiteダッシュボード — {os.path.basename(path)}")
self._refresh_schema()
self.status_var.set(f"接続: {path}")
except Exception as e:
messagebox.showerror("エラー", str(e))
def _refresh_schema(self):
self.schema_tree.delete(*self.schema_tree.get_children())
if not self._conn:
return
try:
tables = self._conn.execute(
"SELECT name FROM sqlite_master WHERE type='table' "
"ORDER BY name").fetchall()
for (tname,) in tables:
node = self.schema_tree.insert("", tk.END, text=f"📋 {tname}",
values=[tname], open=False)
try:
cols = self._conn.execute(
f"PRAGMA table_info({tname})").fetchall()
for col in cols:
col_text = f" {col[1]} ({col[2]})"
pk = " 🔑" if col[5] else ""
self.schema_tree.insert(node, tk.END,
text=col_text + pk)
except Exception:
pass
except Exception as e:
self._show_msg(str(e))
def _on_schema_dblclick(self, event):
sel = self.schema_tree.selection()
if not sel:
return
item = sel[0]
vals = self.schema_tree.item(item, "values")
if vals:
tname = vals[0]
self.sql_text.delete("1.0", tk.END)
self.sql_text.insert("1.0", f"SELECT * FROM {tname} LIMIT 100;")
self._execute()
def _execute(self):
if not self._conn:
messagebox.showwarning("警告", "DBを開いてください")
return
sql = self.sql_text.get("1.0", tk.END).strip()
if not sql:
return
self._show_msg("")
try:
statements = [s.strip() for s in sql.split(";") if s.strip()]
last_select = None
affected = 0
for stmt in statements:
upper = stmt.upper().lstrip()
if upper.startswith("SELECT") or upper.startswith("PRAGMA") or upper.startswith("WITH"):
last_select = stmt
else:
cur = self._conn.execute(stmt)
self._conn.commit()
affected += cur.rowcount if cur.rowcount > 0 else 0
if last_select:
cur = self._conn.execute(last_select)
rows = cur.fetchall()
cols = [d[0] for d in cur.description] if cur.description else []
self._show_results(cols, rows)
self.status_var.set(f"{len(rows)} 行取得")
else:
self._refresh_schema()
self.status_var.set(f"実行完了 ({affected} 行影響)")
except Exception as e:
self._show_msg(str(e))
self.status_var.set("エラー")
def _show_results(self, cols, rows):
self.result_tree.delete(*self.result_tree.get_children())
if not cols:
return
self.result_tree.configure(columns=cols)
for col in cols:
self.result_tree.heading(col, text=col)
self.result_tree.column(col, width=120, anchor="w")
for row in rows[:2000]:
self.result_tree.insert("", tk.END, values=row)
def _show_msg(self, text):
self.msg_text.configure(state=tk.NORMAL)
self.msg_text.delete("1.0", tk.END)
self.msg_text.insert(tk.END, text)
self.msg_text.configure(state=tk.DISABLED)
def _export_csv(self):
if not self.result_tree.get_children():
messagebox.showinfo("情報", "結果がありません")
return
path = filedialog.asksaveasfilename(
defaultextension=".csv",
filetypes=[("CSV", "*.csv")])
if not path:
return
import csv
cols = self.result_tree["columns"]
try:
with open(path, "w", newline="", encoding="utf-8-sig") as f:
writer = csv.writer(f)
writer.writerow(cols)
for iid in self.result_tree.get_children():
writer.writerow(self.result_tree.item(iid, "values"))
messagebox.showinfo("完了", f"CSVを保存しました:\n{path}")
except Exception as e:
messagebox.showerror("エラー", str(e))
if __name__ == "__main__":
root = tk.Tk()
app = App057(root)
root.mainloop()
例外処理とエラーハンドリング
try-exceptでValueErrorとExceptionを捕捉し、messagebox.showerror()でエラーメッセージを表示します。予期しないエラーも処理することで、アプリの堅牢性が向上します。
import tkinter as tk
from tkinter import ttk, messagebox, filedialog
import sqlite3
import os
class App057:
"""SQLiteダッシュボード"""
def __init__(self, root):
self.root = root
self.root.title("SQLiteダッシュボード")
self.root.geometry("1100x680")
self.root.configure(bg="#1e1e1e")
self._conn = None
self._db_path = None
self._build_ui()
def _build_ui(self):
header = tk.Frame(self.root, bg="#252526", pady=6)
header.pack(fill=tk.X)
tk.Label(header, text="🗄 SQLiteダッシュボード",
font=("Noto Sans JP", 12, "bold"),
bg="#252526", fg="#4fc3f7").pack(side=tk.LEFT, padx=12)
ttk.Button(header, text="📂 DBを開く",
command=self._open_db).pack(side=tk.LEFT, padx=4)
ttk.Button(header, text="🆕 新規DB",
command=self._new_db).pack(side=tk.LEFT, padx=2)
ttk.Button(header, text="▶ 実行 (F5)",
command=self._execute).pack(side=tk.LEFT, padx=4)
ttk.Button(header, text="💾 結果CSV出力",
command=self._export_csv).pack(side=tk.LEFT, padx=2)
self.root.bind("<F5>", lambda e: self._execute())
main = ttk.PanedWindow(self.root, orient=tk.HORIZONTAL)
main.pack(fill=tk.BOTH, expand=True, padx=4, pady=4)
# 左: スキーマツリー
left = tk.Frame(main, bg="#252526", width=220)
main.add(left, weight=0)
tk.Label(left, text="スキーマ", bg="#252526", fg="#888",
font=("Arial", 9)).pack(anchor="w", padx=4, pady=2)
self.schema_tree = ttk.Treeview(left, show="tree", selectmode="browse")
ssb = ttk.Scrollbar(left, command=self.schema_tree.yview)
self.schema_tree.configure(yscrollcommand=ssb.set)
ssb.pack(side=tk.RIGHT, fill=tk.Y)
self.schema_tree.pack(fill=tk.BOTH, expand=True)
self.schema_tree.bind("<Double-1>", self._on_schema_dblclick)
# 右
right = tk.Frame(main, bg="#1e1e1e")
main.add(right, weight=1)
vpaned = ttk.PanedWindow(right, orient=tk.VERTICAL)
vpaned.pack(fill=tk.BOTH, expand=True)
# SQL エディタ
editor_f = tk.Frame(vpaned, bg="#1e1e1e")
vpaned.add(editor_f, weight=1)
tk.Label(editor_f, text="SQL エディタ", bg="#1e1e1e", fg="#888",
font=("Arial", 9)).pack(anchor="w")
self.sql_text = tk.Text(editor_f, bg="#0d1117", fg="#d4d4d4",
font=("Courier New", 10), relief=tk.FLAT,
height=8, insertbackground="#fff",
undo=True, wrap=tk.NONE)
sql_sb = ttk.Scrollbar(editor_f, command=self.sql_text.yview)
self.sql_text.configure(yscrollcommand=sql_sb.set)
sql_sb.pack(side=tk.RIGHT, fill=tk.Y)
self.sql_text.pack(fill=tk.BOTH, expand=True)
self.sql_text.insert("1.0", "SELECT * FROM sqlite_master WHERE type='table';")
# 結果
result_f = tk.Frame(vpaned, bg="#1e1e1e")
vpaned.add(result_f, weight=2)
tk.Label(result_f, text="結果", bg="#1e1e1e", fg="#888",
font=("Arial", 9)).pack(anchor="w")
self.result_tree = ttk.Treeview(result_f, show="headings",
selectmode="extended")
rxsb = ttk.Scrollbar(result_f, orient=tk.HORIZONTAL,
command=self.result_tree.xview)
rysb = ttk.Scrollbar(result_f, command=self.result_tree.yview)
self.result_tree.configure(xscrollcommand=rxsb.set,
yscrollcommand=rysb.set)
rysb.pack(side=tk.RIGHT, fill=tk.Y)
self.result_tree.pack(fill=tk.BOTH, expand=True)
rxsb.pack(fill=tk.X)
# エラー/メッセージ
self.msg_text = tk.Text(right, height=3, bg="#1a1a2e", fg="#f48771",
font=("Courier New", 9), relief=tk.FLAT,
state=tk.DISABLED)
self.msg_text.pack(fill=tk.X, padx=0)
self.status_var = tk.StringVar(value="DBファイルを開いてください")
tk.Label(self.root, textvariable=self.status_var,
bg="#252526", fg="#858585", font=("Arial", 9),
anchor="w", padx=8).pack(fill=tk.X, side=tk.BOTTOM)
def _open_db(self):
path = filedialog.askopenfilename(
filetypes=[("SQLite", "*.db *.sqlite *.sqlite3"), ("すべて", "*.*")])
if path:
self._connect(path)
def _new_db(self):
path = filedialog.asksaveasfilename(
defaultextension=".db",
filetypes=[("SQLite", "*.db")])
if path:
self._connect(path)
def _connect(self, path):
try:
if self._conn:
self._conn.close()
self._conn = sqlite3.connect(path)
self._db_path = path
self.root.title(f"SQLiteダッシュボード — {os.path.basename(path)}")
self._refresh_schema()
self.status_var.set(f"接続: {path}")
except Exception as e:
messagebox.showerror("エラー", str(e))
def _refresh_schema(self):
self.schema_tree.delete(*self.schema_tree.get_children())
if not self._conn:
return
try:
tables = self._conn.execute(
"SELECT name FROM sqlite_master WHERE type='table' "
"ORDER BY name").fetchall()
for (tname,) in tables:
node = self.schema_tree.insert("", tk.END, text=f"📋 {tname}",
values=[tname], open=False)
try:
cols = self._conn.execute(
f"PRAGMA table_info({tname})").fetchall()
for col in cols:
col_text = f" {col[1]} ({col[2]})"
pk = " 🔑" if col[5] else ""
self.schema_tree.insert(node, tk.END,
text=col_text + pk)
except Exception:
pass
except Exception as e:
self._show_msg(str(e))
def _on_schema_dblclick(self, event):
sel = self.schema_tree.selection()
if not sel:
return
item = sel[0]
vals = self.schema_tree.item(item, "values")
if vals:
tname = vals[0]
self.sql_text.delete("1.0", tk.END)
self.sql_text.insert("1.0", f"SELECT * FROM {tname} LIMIT 100;")
self._execute()
def _execute(self):
if not self._conn:
messagebox.showwarning("警告", "DBを開いてください")
return
sql = self.sql_text.get("1.0", tk.END).strip()
if not sql:
return
self._show_msg("")
try:
statements = [s.strip() for s in sql.split(";") if s.strip()]
last_select = None
affected = 0
for stmt in statements:
upper = stmt.upper().lstrip()
if upper.startswith("SELECT") or upper.startswith("PRAGMA") or upper.startswith("WITH"):
last_select = stmt
else:
cur = self._conn.execute(stmt)
self._conn.commit()
affected += cur.rowcount if cur.rowcount > 0 else 0
if last_select:
cur = self._conn.execute(last_select)
rows = cur.fetchall()
cols = [d[0] for d in cur.description] if cur.description else []
self._show_results(cols, rows)
self.status_var.set(f"{len(rows)} 行取得")
else:
self._refresh_schema()
self.status_var.set(f"実行完了 ({affected} 行影響)")
except Exception as e:
self._show_msg(str(e))
self.status_var.set("エラー")
def _show_results(self, cols, rows):
self.result_tree.delete(*self.result_tree.get_children())
if not cols:
return
self.result_tree.configure(columns=cols)
for col in cols:
self.result_tree.heading(col, text=col)
self.result_tree.column(col, width=120, anchor="w")
for row in rows[:2000]:
self.result_tree.insert("", tk.END, values=row)
def _show_msg(self, text):
self.msg_text.configure(state=tk.NORMAL)
self.msg_text.delete("1.0", tk.END)
self.msg_text.insert(tk.END, text)
self.msg_text.configure(state=tk.DISABLED)
def _export_csv(self):
if not self.result_tree.get_children():
messagebox.showinfo("情報", "結果がありません")
return
path = filedialog.asksaveasfilename(
defaultextension=".csv",
filetypes=[("CSV", "*.csv")])
if not path:
return
import csv
cols = self.result_tree["columns"]
try:
with open(path, "w", newline="", encoding="utf-8-sig") as f:
writer = csv.writer(f)
writer.writerow(cols)
for iid in self.result_tree.get_children():
writer.writerow(self.result_tree.item(iid, "values"))
messagebox.showinfo("完了", f"CSVを保存しました:\n{path}")
except Exception as e:
messagebox.showerror("エラー", str(e))
if __name__ == "__main__":
root = tk.Tk()
app = App057(root)
root.mainloop()
6. ステップバイステップガイド
このアプリをゼロから自分で作る手順を解説します。コードをコピーするだけでなく、実際に手順を追って自分で書いてみましょう。
-
1ファイルを作成する
新しいファイルを作成して app057.py と保存します。
-
2クラスの骨格を作る
App057クラスを定義し、__init__とmainloop()の最小構成を作ります。
-
3タイトルバーを作る
Frameを使ってカラーバー付きのタイトルエリアを作ります。
-
4入力フォームを実装する
LabelFrameとEntryウィジェットで入力エリアを作ります。
-
5処理ロジックを実装する
_execute()メソッドにメインロジックを実装します。
-
6結果表示を実装する
TextウィジェットかLabelに結果を表示する_show_result()を実装します。
-
7エラー処理を追加する
try-exceptとmessageboxでエラーハンドリングを追加します。
7. カスタマイズアイデア
基本機能を習得したら、以下のカスタマイズに挑戦してみましょう。
💡 ダークモードを追加する
bg色・fg色を辞書で管理し、ボタン1つでダークモード・ライトモードを切り替えられるようにしましょう。
💡 データの保存機能
処理結果をCSV・TXTファイルに保存する機能を追加しましょう。filedialog.asksaveasfilename()でファイル保存ダイアログが使えます。
💡 設定ダイアログ
フォントサイズや色などの設定をユーザーが変更できるオプションダイアログを追加しましょう。
8. よくある問題と解決法
❌ 日本語フォントが表示されない
原因：システムに日本語フォントが見つからない場合があります。
解決法：font引数を省略するかシステムに合ったフォントを指定してください。
❌ ライブラリのインポートエラー
原因：必要なライブラリがインストールされていません。
解決法：pip install コマンドで必要なライブラリをインストールしてください。
❌ ウィンドウサイズが合わない
原因：画面解像度や表示スケールによって異なる場合があります。
解決法：root.geometry()で適切なサイズに調整してください。
9. 練習問題
アプリの理解を深めるための練習問題です。
-
課題1：機能拡張
SQLiteダッシュボードに新しい機能を1つ追加してみましょう。
-
課題2：UIの改善
色・フォント・レイアウトを変更して、より使いやすいUIにカスタマイズしましょう。
-
課題3：保存機能の追加
処理結果をファイルに保存する機能を追加しましょう。