import sys
import os
import glob
import re
import sqlite3
import numpy as np
import pandas as pd
from datetime import datetime, timedelta
from PyQt6.QtWidgets import (QApplication, QMainWindow, QWidget, QVBoxLayout, QHBoxLayout,
QPushButton, QLabel, QTableWidget, QTableWidgetItem, QTabWidget,
QHeaderView, QFileDialog, QMessageBox, QGroupBox, QComboBox,
QProgressBar, QLineEdit, QMenu, QDialog)
from PyQt6.QtCore import Qt, QThread, pyqtSignal
from PyQt6.QtGui import QAction
DEFAULT_DB_PATH = os.path.join(os.getcwd(), "sapporo_ml_ready_archive.db")
# ==========================================
# カスタムダイアログ: エクセル風「▽」フィルター
# ==========================================
class FilterDialog(QDialog):
def __init__(self, col_name, parent=None):
super().__init__(parent)
self.setWindowTitle(f"🔍 '{col_name}' フィルター設定")
self.setFixedSize(300, 150)
layout = QVBoxLayout(self)
layout.addWidget(QLabel(f"条件を指定してください:"))
h_layout = QHBoxLayout()
self.op_combo = QComboBox()
self.op_combo.addItems(["=", ">", ">=", "<", "<=", "LIKE", "!="])
h_layout.addWidget(self.op_combo)
self.val_input = QLineEdit()
self.val_input.setPlaceholderText("値 (例: GSM_GPV または 10.5)")
h_layout.addWidget(self.val_input)
layout.addLayout(h_layout)
btn = QPushButton("✅ 適用")
btn.setStyleSheet("background-color: #27ae60; font-weight: bold; padding: 5px;")
btn.clicked.connect(self.accept)
layout.addWidget(btn)
def get_condition(self):
op = self.op_combo.currentText()
val = self.val_input.text().strip()
if not val: return None
try:
float(val)
return f"{op} {val}"
except:
return f"{op} '{val}'"
# ==========================================
# バックグラウンド処理: 領域指定型ワイドDB生成
# ==========================================
class DBGeneratorThread(QThread):
progress = pyqtSignal(int, str)
finished = pyqtSignal(str)
error = pyqtSignal(str)
def __init__(self, npz_dir, db_path):
super().__init__()
self.npz_dir = npz_dir
self.db_path = db_path
def run(self):
conn = None
# --- 完璧な湿数と相当温位を再計算する安全な関数 ---
def calc_tddep_and_ept(t_arr, rh_arr, lvl, num_pts, mask):
if t_arr is None or rh_arr is None: return [None]*num_pts, [None]*num_pts
t = t_arr.flatten()[mask]
rh = rh_arr.flatten()[mask]
# ケルビンの場合は摂氏に補正
t_c = np.where(t > 150, t - 273.15, t)
# RHが1.5以下（割合）の場合は100倍して%に補正
rh_percent = np.where(rh <= 1.5, rh * 100.0, rh)
rh_c = np.clip(rh_percent, 0.1, 100.0)
# テテンスの式による水蒸気圧と露点温度の算出
e = 6.112 * np.exp((17.67 * t_c) / (t_c + 243.5)) * (rh_c / 100.0)
td = (243.5 * np.log(e / 6.112)) / (17.67 - np.log(e / 6.112))
tddep = t_c - td # 湿数
# 相当温位の算出
tk = t_c + 273.15
theta = tk * ((1000.0 / lvl) ** 0.2854)
w_mix = 0.622 * e / (lvl - e)
ept = theta * np.exp((2.5e6 * w_mix) / (1004.0 * tk))
def format_arr(arr, min_v, max_v):
res = arr.astype(object)
invalid = np.isnan(arr) | (arr > max_v) | (arr < min_v)
res[invalid] = None
res[~invalid] = np.round(arr[~invalid].astype(float), 3)
return res.tolist()
return format_arr(tddep, -50, 100), format_arr(ept, 200, 500)
# --- 気温減率（下層 - 上層）を計算する関数 ---
def calc_lapse(t_lower, t_upper, num_pts, mask):
if t_lower is None or t_upper is None: return [None]*num_pts
# 下層の温度 － 上層の温度 (正の値が大きいほど不安定)
diff = t_lower.flatten()[mask] - t_upper.flatten()[mask]
res = diff.astype(object)
invalid = np.isnan(diff)
res[invalid] = None
res[~invalid] = np.round(diff[~invalid].astype(float), 3)
return res.tolist()
try:
self.progress.emit(5, "ML Readyデータベースの初期化中...")
conn = sqlite3.connect(self.db_path, timeout=15)
cursor = conn.cursor()
# ssi を lapse (気温減率) に完全置換したスキーマ
cursor.execute('''
CREATE TABLE IF NOT EXISTS weather_master (
id INTEGER PRIMARY KEY AUTOINCREMENT,
model_type TEXT, init_time TEXT, valid_time TEXT, ft INTEGER,
lat REAL, lon REAL,
surf_temp REAL, surf_u REAL, surf_v REAL, surf_rain REAL, surf_snow REAL,
cloud_tot REAL, cloud_low REAL, cloud_mid REAL, cloud_high REAL,
gh_975 REAL, gh_950 REAL, gh_925 REAL, gh_850 REAL, gh_700 REAL, gh_500 REAL,
u_975 REAL, u_950 REAL, u_925 REAL, u_850 REAL, u_700 REAL, u_500 REAL,
v_975 REAL, v_950 REAL, v_925 REAL, v_850 REAL, v_700 REAL, v_500 REAL,
tddep_950 REAL, tddep_925 REAL, tddep_850 REAL, tddep_700 REAL,
ept_950 REAL, ept_925 REAL, ept_850 REAL,
w_700 REAL, vort_500 REAL,
lapse_850_500 REAL, lapse_850_700 REAL, lapse_925_700 REAL,
UNIQUE(model_type, lat, lon, valid_time, ft)
)
''')
cursor.execute('CREATE INDEX IF NOT EXISTS idx_time_loc ON weather_master (valid_time, lat, lon)')
npz_files = glob.glob(os.path.join(self.npz_dir, "*.npz"))
if not npz_files:
self.error.emit(f"エラー: {self.npz_dir} に .npz ファイルが存在しません。")
return
target_files = [
f for f in npz_files
if ("GSM_JP_" in os.path.basename(f) or "MSM_" in os.path.basename(f))
and "GUID" not in os.path.basename(f)
]
total_files = len(target_files)
if total_files == 0:
self.error.emit("抽出対象のGPVデータ(GSM/MSM)が見つかりません。")
return
total_records_inserted = 0
self.progress.emit(10, f"領域限定(N40-46, E138-146)の特徴量抽出を開始... 対象: {total_files}ファイル")
for i, fpath in enumerate(target_files):
fname = os.path.basename(fpath)
m_model = "MSM" if "MSM" in fname else "GSM"
model_type = f"{m_model}_GPV"
time_match = re.search(r'_(\d{14})_', fname)
ft_match = re.search(r'_FT(\d+)', fname)
if not time_match or not ft_match: continue
init_time_str = time_match.group(1)
ft_val = int(ft_match.group(1))
dt_init = datetime.strptime(init_time_str, "%Y%m%d%H%M%S")
dt_valid = dt_init + timedelta(hours=ft_val)
str_init = dt_init.strftime("%Y-%m-%d %H:%M:%S")
str_valid = dt_valid.strftime("%Y-%m-%d %H:%M:%S")
try:
data = np.load(fpath)
files = data.files
lon_key = next((k for k in files if 'lon' in k.lower()), None)
lat_key = next((k for k in files if 'lat' in k.lower()), None)
if not lon_key or not lat_key: continue
lon_arr, lat_arr = data[lon_key], data[lat_key]
if lon_arr.ndim == 1: lon_2d, lat_2d = np.meshgrid(lon_arr, lat_arr)
else: lon_2d, lat_2d = lon_arr, lat_arr
lats_flat = np.round(lat_2d.flatten(), 4)
lons_flat = np.round(lon_2d.flatten(), 4)
mask = (lats_flat >= 40.0) & (lats_flat <= 46.0) & (lons_flat >= 138.0) & (lons_flat <= 146.0)
lats = lats_flat[mask]
lons = lons_flat[mask]
num_pts = len(lats)
if num_pts == 0:
data.close()
continue
def get_v_flat(key_opts):
k = next((x for x in key_opts if x in files), None)
if not k: return [None] * num_pts
arr_flat = data[k].flatten()
masked_arr = arr_flat[mask]
res = masked_arr.astype(object)
invalid = np.isnan(masked_arr) | (masked_arr > 200000) | (masked_arr < -10000)
res[invalid] = None
res[~invalid] = np.round(masked_arr[~invalid].astype(float), 3)
return res.tolist()
def get_raw_arr(key_opts):
k = next((x for x in key_opts if x in files), None)
if k: return data[k]
return None
s_t = get_v_flat(['t2m', 'tmp', 'temp'])
s_u = get_v_flat(['u10', 'u', 'ugrd'])
s_v = get_v_flat(['v10', 'v', 'vgrd'])
s_r = get_v_flat(['precip', 'tp', 'apcp'])
s_s = get_v_flat(['snow', 'weasd', 'snod', 'asnow'])
c_t = get_v_flat(['tcc', 'tcdc', 'TCC', 'TCDC', 'var_0_6_1', 'var_0_6_192'])
c_l = get_v_flat(['lcc', 'LCC', 'var_0_6_3'])
c_m = get_v_flat(['mcc', 'MCC', 'var_0_6_4'])
c_h = get_v_flat(['hcc', 'HCC', 'var_0_6_5'])
gh_975 = get_v_flat(['gh975', 'z975']); u_975 = get_v_flat(['u975']); v_975 = get_v_flat(['v975'])
gh_950 = get_v_flat(['gh950', 'z950']); u_950 = get_v_flat(['u950']); v_950 = get_v_flat(['v950'])
gh_925 = get_v_flat(['gh925', 'z925']); u_925 = get_v_flat(['u925']); v_925 = get_v_flat(['v925'])
gh_850 = get_v_flat(['gh850', 'z850']); u_850 = get_v_flat(['u850']); v_850 = get_v_flat(['v850'])
gh_700 = get_v_flat(['gh700', 'z700']); u_700 = get_v_flat(['u700']); v_700 = get_v_flat(['v700'])
gh_500 = get_v_flat(['gh500', 'z500']); u_500 = get_v_flat(['u500']); v_500 = get_v_flat(['v500'])
w_700 = get_v_flat(['w700']); vort_500 = get_v_flat(['vort500'])
# 湿数・相当温位の動的再計算
t950_arr = get_raw_arr(['t950', 'tmp950']); r950_arr = get_raw_arr(['r950', 'rh950'])
td_950, ep_950 = calc_tddep_and_ept(t950_arr, r950_arr, 950, num_pts, mask)
t925_arr = get_raw_arr(['t925', 'tmp925']); r925_arr = get_raw_arr(['r925', 'rh925'])
td_925, ep_925 = calc_tddep_and_ept(t925_arr, r925_arr, 925, num_pts, mask)
t850_arr = get_raw_arr(['t850', 'tmp850']); r850_arr = get_raw_arr(['r850', 'rh850'])
td_850, ep_850 = calc_tddep_and_ept(t850_arr, r850_arr, 850, num_pts, mask)
t700_arr = get_raw_arr(['t700', 'tmp700']); r700_arr = get_raw_arr(['r700', 'rh700'])
td_700, _ = calc_tddep_and_ept(t700_arr, r700_arr, 700, num_pts, mask)
# 気温減率（大気不安定度）の算出
t500_arr = get_raw_arr(['t500', 'tmp500'])
lapse_850_500 = calc_lapse(t850_arr, t500_arr, num_pts, mask)
lapse_850_700 = calc_lapse(t850_arr, t700_arr, num_pts, mask)
lapse_925_700 = calc_lapse(t925_arr, t700_arr, num_pts, mask)
model_list = [model_type] * num_pts
init_list = [str_init] * num_pts
valid_list = [str_valid] * num_pts
ft_list = [ft_val] * num_pts
rows = zip(
model_list, init_list, valid_list, ft_list, lats, lons,
s_t, s_u, s_v, s_r, s_s, c_t, c_l, c_m, c_h,
gh_975, gh_950, gh_925, gh_850, gh_700, gh_500,
u_975, u_950, u_925, u_850, u_700, u_500,
v_975, v_950, v_925, v_850, v_700, v_500,
td_950, td_925, td_850, td_700,
ep_950, ep_925, ep_850,
w_700, vort_500,
lapse_850_500, lapse_850_700, lapse_925_700
)
cursor.executemany('''
INSERT OR REPLACE INTO weather_master (
model_type, init_time, valid_time, ft, lat, lon,
surf_temp, surf_u, surf_v, surf_rain, surf_snow,
cloud_tot, cloud_low, cloud_mid, cloud_high,
gh_975, gh_950, gh_925, gh_850, gh_700, gh_500,
u_975, u_950, u_925, u_850, u_700, u_500,
v_975, v_950, v_925, v_850, v_700, v_500,
tddep_950, tddep_925, tddep_850, tddep_700,
ept_950, ept_925, ept_850,
w_700, vort_500,
lapse_850_500, lapse_850_700, lapse_925_700
) VALUES (?,?,?,?,?,?, ?,?,?,?,?, ?,?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?, ?,?,?,?,?,?, ?,?,?,?, ?,?,?, ?,?, ?,?,?)
''', rows)
conn.commit()
total_records_inserted += num_pts
data.close()
except Exception as e:
print(f"Error {fname}: {e}")
if i % max(1, total_files // 20) == 0:
self.progress.emit(10 + int((i / total_files) * 85), f"抽出中... {i+1}/{total_files} ファイル")
self.progress.emit(100, "処理完了")
self.finished.emit(f"✓ DB更新完了: 指定領域内の {total_records_inserted:,} レコードを処理しました。")
except Exception as e:
self.error.emit(f"DB生成エラー: {str(e)}")
finally:
if conn:
conn.close()
# ==========================================
# メインアプリケーション UIとロジック
# ==========================================
class IntegratedWeatherSystem(QMainWindow):
def __init__(self):
super().__init__()
self.setWindowTitle("MLデータパイプライン & SQLite エクセル風ブラウザ (Ver 10.3 湿数・大気安定度自動再計算版)")
self.resize(1400, 900)
self.setStyleSheet("background-color: #2b2b2b; color: #e0e0e0; font-family: 'MS Gothic';")
self.current_db_path = DEFAULT_DB_PATH
self.current_npz_dir = os.path.join(os.getcwd(), "gpv_cache_npz")
self.current_page = 1
self.records_per_page = 500
self.total_records = 0
self.is_loading_table = False
self.active_filters = {}
self.init_ui()
if os.path.exists(self.current_db_path):
self.update_browser_tables()
def init_ui(self):
central_widget = QWidget()
self.setCentralWidget(central_widget)
main_layout = QVBoxLayout(central_widget)
db_group = QGroupBox("🗄 接続データベース")
db_group.setStyleSheet("QGroupBox { border: 1px solid #555; font-weight: bold; }")
db_layout = QHBoxLayout(db_group)
self.btn_sel_db = QPushButton("📁 DBファイルを選択 / 新規作成")
self.btn_sel_db.setStyleSheet("background-color: #34495e; padding: 6px;")
self.btn_sel_db.clicked.connect(self.select_db_file)
self.lbl_db_path = QLabel(f"現在のDB: {self.current_db_path}")
self.lbl_db_path.setStyleSheet("color: #00fa9a; font-weight: bold;")
db_layout.addWidget(self.btn_sel_db)
db_layout.addWidget(self.lbl_db_path, 1)
main_layout.addWidget(db_group)
self.tabs = QTabWidget()
self.tabs.setStyleSheet("""
QTabBar::tab { background-color: #3c3f41; padding: 10px; font-weight: bold; font-size: 10pt; }
QTabBar::tab:selected { background-color: #007acc; color: white; }
QTabWidget::pane { border: 1px solid #555; }
""")
self.tab1_npz = QWidget()
self.tab2_obs = QWidget()
self.tab3_browser = QWidget()
self.tabs.addTab(self.tab1_npz, "📥 [Tab 1] GPV(GSM/MSM) 領域限定DB化")
self.tabs.addTab(self.tab2_obs, "📝 [Tab 2] 観測点CSVデータインポート")
self.tabs.addTab(self.tab3_browser, "🔍 [Tab 3] エクセル風 SQLite ブラウザ")
main_layout.addWidget(self.tabs)
self.setup_tab1_npz()
self.setup_tab2_obs()
self.setup_tab3_browser()
def setup_tab1_npz(self):
layout = QVBoxLayout(self.tab1_npz)
npz_layout = QHBoxLayout()
self.btn_sel_npz = QPushButton("📁 NPZフォルダを選択")
self.btn_sel_npz.setStyleSheet("background-color: #2980b9; padding: 6px;")
self.btn_sel_npz.clicked.connect(self.select_npz_dir)
self.lbl_npz_dir = QLabel(f"NPZ: {self.current_npz_dir}")
npz_layout.addWidget(self.btn_sel_npz)
npz_layout.addWidget(self.lbl_npz_dir, 1)
layout.addLayout(npz_layout)
self.btn_generate_db = QPushButton("⚡ 本番: 【GSM/MSM】を【N40-46, E138-146】の範囲でDB抽出")
self.btn_generate_db.setStyleSheet("background-color: #c0392b; font-size: 12pt; font-weight: bold; padding: 15px;")
self.btn_generate_db.clicked.connect(self.start_db_generation)
layout.addWidget(self.btn_generate_db)
self.progress_bar = QProgressBar()
self.progress_bar.setVisible(False)
layout.addWidget(self.progress_bar)
self.lbl_status = QLabel("ステータス: 待機中")
layout.addWidget(self.lbl_status)
layout.addStretch()
def setup_tab2_obs(self):
layout = QVBoxLayout(self.tab2_obs)
ctrl_layout = QHBoxLayout()
self.btn_load_csv = QPushButton("📁 観測データ(CSV)を読込")
self.btn_load_csv.setStyleSheet("background-color: #27ae60; font-weight: bold; padding: 8px;")
self.btn_load_csv.clicked.connect(self.preview_csv)
ctrl_layout.addWidget(self.btn_load_csv)
ctrl_layout.addWidget(QLabel("挿入先テーブル名:"))
self.txt_table_name = QLineEdit("obs_master")
self.txt_table_name.setStyleSheet("background-color: #1e1e1e; padding: 5px;")
ctrl_layout.addWidget(self.txt_table_name)
self.btn_import_csv = QPushButton("💾 表示中のデータをDBにインポート")
self.btn_import_csv.setStyleSheet("background-color: #8e44ad; font-weight: bold; padding: 8px;")
self.btn_import_csv.clicked.connect(self.import_csv_to_db)
self.btn_import_csv.setEnabled(False)
ctrl_layout.addWidget(self.btn_import_csv)
layout.addLayout(ctrl_layout)
self.table_csv_preview = QTableWidget()
self.table_csv_preview.setStyleSheet("background-color: #1e1e1e;")
layout.addWidget(self.table_csv_preview)
def setup_tab3_browser(self):
layout = QVBoxLayout(self.tab3_browser)
filter_layout = QHBoxLayout()
filter_layout.addWidget(QLabel("テーブル:"))
self.combo_tables = QComboBox()
self.combo_tables.currentTextChanged.connect(self.on_table_changed)
filter_layout.addWidget(self.combo_tables)
self.btn_refresh_data = QPushButton("🔄 更新")
self.btn_refresh_data.clicked.connect(self.reload_browser_data)
filter_layout.addWidget(self.btn_refresh_data)
filter_layout.addStretch()
self.btn_prev_page = QPushButton("◀ 前の500件")
self.btn_prev_page.clicked.connect(self.prev_page)
self.lbl_page_info = QLabel("ページ: 1 / 1")
self.btn_next_page = QPushButton("次の500件 ▶")
self.btn_next_page.clicked.connect(self.next_page)
filter_layout.addWidget(self.btn_prev_page)
filter_layout.addWidget(self.lbl_page_info)
filter_layout.addWidget(self.btn_next_page)
layout.addLayout(filter_layout)
lbl_hint = QLabel("💡 Hint: 列の見出し（ヘッダー）を右クリックすると、エクセルの「▽」のようなフィルター絞り込みができます。")
lbl_hint.setStyleSheet("color: #f39c12; font-weight: bold;")
layout.addWidget(lbl_hint)
self.table_browser = QTableWidget()
self.table_browser.setStyleSheet("background-color: #1e1e1e; gridline-color: #444; color: #e0e0e0;")
self.table_browser.cellChanged.connect(self.on_cell_edited)
header = self.table_browser.horizontalHeader()
header.setContextMenuPolicy(Qt.ContextMenuPolicy.CustomContextMenu)
header.customContextMenuRequested.connect(self.show_header_menu)
layout.addWidget(self.table_browser)
def show_header_menu(self, pos):
col = self.table_browser.horizontalHeader().logicalIndexAt(pos)
if col < 0: return
col_name = self.table_browser.horizontalHeaderItem(col).text()
menu = QMenu(self)
menu.setStyleSheet("QMenu { background-color: #2b2b2b; color: white; border: 1px solid #555; } QMenu::item:selected { background-color: #2980b9; }")
action_filter = QAction(f"🔍 '{col_name}' で絞り込み", self)
action_filter.triggered.connect(lambda: self.open_filter_dialog(col_name))
menu.addAction(action_filter)
if col_name in self.active_filters:
action_clear = QAction(f"❌ '{col_name}' のフィルターを解除", self)
action_clear.triggered.connect(lambda: self.clear_filter(col_name))
menu.addAction(action_clear)
if self.active_filters:
action_clear_all = QAction("🧹 全てのフィルターを解除", self)
action_clear_all.triggered.connect(self.clear_all_filters)
menu.addAction(action_clear_all)
menu.exec(self.table_browser.horizontalHeader().mapToGlobal(pos))
def open_filter_dialog(self, col_name):
dialog = FilterDialog(col_name, self)
if dialog.exec():
cond = dialog.get_condition()
if cond:
self.active_filters[col_name] = cond
self.current_page = 1
self.reload_browser_data()
def clear_filter(self, col_name):
if col_name in self.active_filters:
del self.active_filters[col_name]
self.current_page = 1
self.reload_browser_data()
def clear_all_filters(self):
self.active_filters.clear()
self.current_page = 1
self.reload_browser_data()
def select_db_file(self):
path, _ = QFileDialog.getSaveFileName(self, "DBファイルを選択/作成", self.current_db_path, "DB (*.db *.sqlite)")
if path:
self.current_db_path = path
self.lbl_db_path.setText(f"現在のDB: {self.current_db_path}")
self.update_browser_tables()
def select_npz_dir(self):
d = QFileDialog.getExistingDirectory(self, "NPZフォルダを選択", self.current_npz_dir)
if d:
self.current_npz_dir = d
self.lbl_npz_dir.setText(f"NPZ: {self.current_npz_dir}")
def start_db_generation(self):
self.btn_generate_db.setEnabled(False)
self.progress_bar.setVisible(True)
self.progress_bar.setValue(0)
self.db_thread = DBGeneratorThread(self.current_npz_dir, self.current_db_path)
self.db_thread.progress.connect(self.update_progress)
self.db_thread.finished.connect(self.on_db_generated)
self.db_thread.error.connect(self.on_db_error)
self.db_thread.start()
def update_progress(self, val, text):
self.progress_bar.setValue(val)
self.lbl_status.setText(text)
def on_db_generated(self, msg):
self.btn_generate_db.setEnabled(True)
self.progress_bar.setVisible(False)
self.lbl_status.setText(msg)
QMessageBox.information(self, "生成完了", msg)
self.update_browser_tables()
def on_db_error(self, msg):
self.btn_generate_db.setEnabled(True)
self.progress_bar.setVisible(False)
QMessageBox.critical(self, "エラー", msg)
def preview_csv(self):
path, _ = QFileDialog.getOpenFileName(self, "観測データCSVを開く", "", "CSV (*.csv)")
if not path: return
try:
self.current_csv_data = pd.read_csv(path)
df = self.current_csv_data
self.table_csv_preview.setColumnCount(len(df.columns))
self.table_csv_preview.setHorizontalHeaderLabels(df.columns)
self.table_csv_preview.setRowCount(min(len(df), 100))
for r in range(min(len(df), 100)):
for c in range(len(df.columns)):
self.table_csv_preview.setItem(r, c, QTableWidgetItem(str(df.iat[r, c])))
self.btn_import_csv.setEnabled(True)
except Exception as e:
QMessageBox.critical(self, "読込エラー", str(e))
def import_csv_to_db(self):
if self.current_csv_data is None: return
t_name = self.txt_table_name.text().strip()
if not t_name: return
conn = None
try:
conn = sqlite3.connect(self.current_db_path, timeout=15)
self.current_csv_data.to_sql(t_name, conn, if_exists='append', index=False)
QMessageBox.information(self, "完了", f"'{t_name}' に {len(self.current_csv_data)} 件インポートしました。")
self.update_browser_tables()
except Exception as e:
QMessageBox.critical(self, "DB書込エラー", str(e))
finally:
if conn:
conn.close()
def update_browser_tables(self):
self.combo_tables.blockSignals(True)
self.combo_tables.clear()
conn = None
try:
conn = sqlite3.connect(self.current_db_path, timeout=15)
cursor = conn.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
tables = [r[0] for r in cursor.fetchall() if r[0] != "sqlite_sequence"]
self.combo_tables.addItems(tables)
except: pass
finally:
if conn: conn.close()
self.combo_tables.blockSignals(False)
if hasattr(self, 'combo_tables') and self.combo_tables.count() > 0:
self.current_page = 1
self.active_filters.clear()
self.reload_browser_data()
def on_table_changed(self, text):
self.current_page = 1
self.active_filters.clear()
self.reload_browser_data()
def prev_page(self):
if self.current_page > 1: self.current_page -= 1; self.reload_browser_data()
def next_page(self):
max_p = max(1, (self.total_records + self.records_per_page - 1) // self.records_per_page)
if self.current_page < max_p: self.current_page += 1; self.reload_browser_data()
def reload_browser_data(self):
table_name = self.combo_tables.currentText()
if not table_name or not os.path.exists(self.current_db_path): return
self.is_loading_table = True
self.table_browser.clear()
conn = None
try:
conn = sqlite3.connect(self.current_db_path, timeout=15)
cursor = conn.cursor()
where_clauses = [f"{col} {cond}" for col, cond in self.active_filters.items()]
where_sql = " WHERE " + " AND ".join(where_clauses) if where_clauses else ""
cursor.execute(f"SELECT COUNT(*) FROM {table_name}{where_sql}")
self.total_records = cursor.fetchone()[0]
max_pages = max(1, (self.total_records + self.records_per_page - 1) // self.records_per_page)
if self.current_page > max_pages: self.current_page = max_pages
offset = (self.current_page - 1) * self.records_per_page
cursor.execute(f"SELECT rowid, * FROM {table_name}{where_sql} LIMIT {self.records_per_page} OFFSET {offset}")
rows = cursor.fetchall()
col_names = [desc[0] for desc in cursor.description]
self.table_browser.setColumnCount(len(col_names))
header_labels = []
for c in col_names:
header_labels.append(f"🔍 {c}" if c in self.active_filters else c)
self.table_browser.setHorizontalHeaderLabels(header_labels)
self.table_browser.setRowCount(len(rows))
for r_idx, row in enumerate(rows):
for c_idx, val in enumerate(row):
item = QTableWidgetItem("" if val is None else str(val))
if c_idx == 0: item.setFlags(item.flags() & ~Qt.ItemFlag.ItemIsEditable)
self.table_browser.setItem(r_idx, c_idx, item)
filter_msg = f" (フィルター適用中)" if self.active_filters else ""
self.lbl_page_info.setText(f"ページ: {self.current_page} / {max_pages} {filter_msg}")
self.btn_prev_page.setEnabled(self.current_page > 1)
self.btn_next_page.setEnabled(self.current_page < max_pages)
except Exception as e:
print("Browser Load Error:", e)
finally:
if conn: conn.close()
self.is_loading_table = False
def on_cell_edited(self, row, col):
if self.is_loading_table: return
table_name = self.combo_tables.currentText()
col_name = self.table_browser.horizontalHeaderItem(col).text().replace("🔍 ", "")
rowid_item = self.table_browser.item(row, 0)
new_val_item = self.table_browser.item(row, col)
if not rowid_item or not new_val_item: return
row_id = rowid_item.text()
new_val = new_val_item.text()
conn = None
try:
conn = sqlite3.connect(self.current_db_path, timeout=15)
cursor = conn.cursor()
cursor.execute(f"UPDATE {table_name} SET {col_name} = ? WHERE rowid = ?", (new_val, row_id))
conn.commit()
except Exception as e:
QMessageBox.critical(self, "UPDATE エラー", str(e))
self.reload_browser_data()
finally:
if conn: conn.close()
if __name__ == '__main__':
app = QApplication(sys.argv)
window = IntegratedWeatherSystem()
window.show()
sys.exit(app.exec())
未分類
コメント