nngo/DBClient
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
DBClient/app/ui/import_dialog.py
T
nngoandClaude Sonnet 4.6 aae47125cd feat: add SQL dump import support to ImportDialog 
Extends ImportDialog to handle .sql dump files alongside CSV/JSON.
Adds _SqlImportWorker that splits SQL with sqlparse (semicolon fallback),
executes each statement via driver.execute_query(), and collects non-fatal
errors. UI shows a text preview of the dump, statement count, a
stop-on-error checkbox, and a final summary with any per-statement errors.

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
2026-05-22 09:20:13 -04:00

483 lines
20 KiB
Python
Raw Permalink Blame History

"""
Import CSV / JSON / SQL dump into a database.
CSV / JSON flow:
1. Pick file → parse → table preview → insert rows via driver.insert_row()
SQL dump flow:
1. Pick .sql file → split into statements (sqlparse) → text preview
2. Execute each statement via driver.execute_query()
3. Report progress; optional "stop on first error" mode
"""
import csv
import json
import os
from PyQt6.QtWidgets import (
QDialog, QVBoxLayout, QHBoxLayout, QFormLayout,
QLabel, QLineEdit, QPushButton, QComboBox,
QTableWidget, QTableWidgetItem, QHeaderView,
QDialogButtonBox, QFileDialog, QProgressBar,
QMessageBox, QCheckBox, QStackedWidget, QPlainTextEdit,
QWidget,
)
from PyQt6.QtCore import Qt, QThread, pyqtSignal
from PyQt6.QtGui import QFont
_PREVIEW_ROWS = 50
_PREVIEW_SQL_LINES = 200 # lines of SQL text shown in preview
# ── CSV / JSON row-insert worker ──────────────────────────────────────────────
class _RowImportWorker(QThread):
progress = pyqtSignal(int) # rows inserted so far
finished = pyqtSignal(int) # total inserted
error = pyqtSignal(str)
def __init__(self, driver, database: str, table: str,
rows: list, parent=None):
super().__init__(parent)
self._driver = driver
self._database = database
self._table = table
self._rows = rows
def run(self):
inserted = 0
try:
for row in self._rows:
self._driver.insert_row(self._database, self._table, row)
inserted += 1
if inserted % 50 == 0:
self.progress.emit(inserted)
self.finished.emit(inserted)
except Exception as e:
self.error.emit(f"Row {inserted + 1}: {e}")
# ── SQL dump execution worker ─────────────────────────────────────────────────
class _SqlImportWorker(QThread):
progress = pyqtSignal(int) # statements executed so far
stmt_error = pyqtSignal(int, str) # (stmt_index, message) — non-fatal
finished = pyqtSignal(int, int) # (executed, total)
fatal = pyqtSignal(str) # stopped on error
def __init__(self, driver, database: str, sql_text: str,
stop_on_error: bool = False, parent=None):
super().__init__(parent)
self._driver = driver
self._database = database
self._sql_text = sql_text
self._stop_on_error = stop_on_error
def run(self):
try:
import sqlparse
stmts = [s.strip() for s in sqlparse.split(self._sql_text) if s.strip()]
except ImportError:
# Fallback: naive semicolon split
stmts = [s.strip() for s in self._sql_text.split(";") if s.strip()]
total = len(stmts)
executed = 0
for i, stmt in enumerate(stmts):
try:
self._driver.execute_query(stmt)
executed += 1
except Exception as e:
self.stmt_error.emit(i, str(e))
if self._stop_on_error:
self.fatal.emit(
f"Stopped at statement {i + 1}/{total}:\n{e}"
)
return
if (i + 1) % 20 == 0:
self.progress.emit(i + 1)
self.progress.emit(total)
self.finished.emit(executed, total)
# ── Dialog ────────────────────────────────────────────────────────────────────
class ImportDialog(QDialog):
"""Import CSV, JSON, or SQL dump into the database."""
# Internal mode constants
_MODE_TABLE = 0 # CSV / JSON
_MODE_SQL = 1 # SQL dump
def __init__(self, driver, database: str, table: str = "", parent=None):
super().__init__(parent)
self._driver = driver
self._database = database
self._table = table
self._rows: list = [] # parsed rows (CSV/JSON mode)
self._sql_text = "" # raw SQL text (SQL mode)
self._sql_stmts = 0 # statement count (SQL mode)
self._worker = None
self._mode = self._MODE_TABLE
self._errors: list[str] = [] # non-fatal SQL errors collected
self.setWindowTitle("Import Data")
self.setModal(True)
self.setMinimumSize(680, 520)
self._build_ui()
# ── UI ────────────────────────────────────────────────────────────────────
def _build_ui(self):
root = QVBoxLayout(self)
root.setSpacing(8)
# ── File picker ───────────────────────────────────────────────────────
file_row = QHBoxLayout()
self._path_edit = QLineEdit()
self._path_edit.setReadOnly(True)
self._path_edit.setPlaceholderText(
"Select a .csv, .json, or .sql file…"
)
browse_btn = QPushButton("Browse…")
browse_btn.setFixedWidth(80)
browse_btn.clicked.connect(self._browse)
file_row.addWidget(self._path_edit)
file_row.addWidget(browse_btn)
root.addLayout(file_row)
# ── CSV-only options ──────────────────────────────────────────────────
self._csv_opts_widget = self._build_csv_opts()
self._csv_opts_widget.setVisible(False)
root.addWidget(self._csv_opts_widget)
self._header_cb = QCheckBox("First row is a header (CSV only)")
self._header_cb.setChecked(True)
self._header_cb.toggled.connect(self._reload_preview)
self._header_cb.setVisible(False)
root.addWidget(self._header_cb)
# ── SQL-only options ──────────────────────────────────────────────────
self._sql_opts_widget = self._build_sql_opts()
self._sql_opts_widget.setVisible(False)
root.addWidget(self._sql_opts_widget)
# ── Preview stack: page 0 = table, page 1 = SQL text ─────────────────
self._preview_label = QLabel("Preview:")
root.addWidget(self._preview_label)
self._preview_stack = QStackedWidget()
self._preview_table = QTableWidget(0, 0)
self._preview_table.setAlternatingRowColors(True)
self._preview_table.setEditTriggers(
QTableWidget.EditTrigger.NoEditTriggers)
self._preview_table.horizontalHeader().setSectionResizeMode(
QHeaderView.ResizeMode.ResizeToContents)
self._preview_table.verticalHeader().setDefaultSectionSize(22)
self._preview_table.setSelectionMode(
QTableWidget.SelectionMode.NoSelection)
self._preview_stack.addWidget(self._preview_table) # idx 0
self._preview_sql = QPlainTextEdit()
self._preview_sql.setReadOnly(True)
self._preview_sql.setFont(QFont("Consolas", 10))
self._preview_sql.setLineWrapMode(QPlainTextEdit.LineWrapMode.NoWrap)
self._preview_stack.addWidget(self._preview_sql) # idx 1
root.addWidget(self._preview_stack, 1)
# ── Status / progress ─────────────────────────────────────────────────
self._status_lbl = QLabel("")
root.addWidget(self._status_lbl)
self._progress = QProgressBar()
self._progress.setVisible(False)
root.addWidget(self._progress)
# ── Buttons ───────────────────────────────────────────────────────────
self._btns = QDialogButtonBox(
QDialogButtonBox.StandardButton.Ok |
QDialogButtonBox.StandardButton.Cancel
)
self._ok_btn = self._btns.button(QDialogButtonBox.StandardButton.Ok)
self._ok_btn.setText("Import")
self._ok_btn.setEnabled(False)
self._btns.accepted.connect(self._start_import)
self._btns.rejected.connect(self.reject)
root.addWidget(self._btns)
def _build_csv_opts(self) -> QWidget:
w = QWidget()
lay = QHBoxLayout(w)
lay.setContentsMargins(0, 0, 0, 0)
lay.addWidget(QLabel("Delimiter:"))
self._delim_combo = QComboBox()
self._delim_combo.addItems([", (comma)", "; (semicolon)",
"\\t (tab)", "| (pipe)"])
self._delim_combo.setFixedWidth(140)
self._delim_combo.currentIndexChanged.connect(self._reload_preview)
lay.addWidget(self._delim_combo)
lay.addStretch()
return w
def _build_sql_opts(self) -> QWidget:
w = QWidget()
lay = QHBoxLayout(w)
lay.setContentsMargins(0, 0, 0, 0)
self._stop_on_error_cb = QCheckBox("Stop on first error")
self._stop_on_error_cb.setChecked(False)
self._stop_on_error_cb.setToolTip(
"When unchecked, errors are logged and execution continues.\n"
"When checked, the import halts at the first failing statement."
)
lay.addWidget(self._stop_on_error_cb)
lay.addStretch()
return w
# ── File loading ──────────────────────────────────────────────────────────
def _browse(self):
path, _ = QFileDialog.getOpenFileName(
self, "Open file", "",
"Supported files (*.csv *.json *.sql);;"
"CSV files (*.csv);;"
"JSON files (*.json);;"
"SQL dump files (*.sql);;"
"All files (*)"
)
if not path:
return
self._path_edit.setText(path)
ext = os.path.splitext(path)[1].lower()
is_csv = ext == ".csv"
is_sql = ext == ".sql"
self._csv_opts_widget.setVisible(is_csv)
self._header_cb.setVisible(is_csv)
self._sql_opts_widget.setVisible(is_sql)
self._mode = self._MODE_SQL if is_sql else self._MODE_TABLE
self._reload_preview()
def _delimiter(self) -> str:
return {0: ",", 1: ";", 2: "\t", 3: "|"}.get(
self._delim_combo.currentIndex(), ","
)
def _reload_preview(self):
path = self._path_edit.text()
if not path:
return
ext = os.path.splitext(path)[1].lower()
if ext == ".sql":
self._load_sql_preview(path)
elif ext == ".csv":
self._load_csv_preview(path)
elif ext == ".json":
self._load_json_preview(path)
else:
self._status_lbl.setText("Unsupported file type.")
# ── CSV / JSON loading ────────────────────────────────────────────────────
def _load_csv_preview(self, path: str):
try:
self._rows = self._parse_csv(path)
except Exception as e:
self._status_lbl.setText(f"Parse error: {e}")
self._rows = []
self._ok_btn.setEnabled(False)
return
self._preview_stack.setCurrentIndex(0)
self._preview_label.setText(f"Preview (first {_PREVIEW_ROWS} rows):")
self._populate_table_preview(self._rows[:_PREVIEW_ROWS])
target = f"'{self._database}'.'{self._table}'" if self._table else f"'{self._database}'"
self._status_lbl.setText(
f"{len(self._rows):,} row(s) ready to import into {target}"
)
self._ok_btn.setEnabled(bool(self._rows))
def _load_json_preview(self, path: str):
try:
self._rows = self._parse_json(path)
except Exception as e:
self._status_lbl.setText(f"Parse error: {e}")
self._rows = []
self._ok_btn.setEnabled(False)
return
self._preview_stack.setCurrentIndex(0)
self._preview_label.setText(f"Preview (first {_PREVIEW_ROWS} rows):")
self._populate_table_preview(self._rows[:_PREVIEW_ROWS])
target = f"'{self._database}'.'{self._table}'" if self._table else f"'{self._database}'"
self._status_lbl.setText(
f"{len(self._rows):,} row(s) ready to import into {target}"
)
self._ok_btn.setEnabled(bool(self._rows))
def _parse_csv(self, path: str) -> list:
with open(path, newline="", encoding="utf-8-sig") as f:
reader = csv.reader(f, delimiter=self._delimiter())
all_rows = list(reader)
if not all_rows:
return []
if self._header_cb.isChecked():
headers, data_rows = all_rows[0], all_rows[1:]
else:
headers = [f"col{i+1}" for i in range(len(all_rows[0]))]
data_rows = all_rows
return [
dict(zip(headers, (row + [""] * len(headers))[: len(headers)]))
for row in data_rows
]
def _parse_json(self, path: str) -> list:
with open(path, encoding="utf-8") as f:
data = json.load(f)
if isinstance(data, list):
return [r for r in data if isinstance(r, dict)]
if isinstance(data, dict):
for key in ("rows", "data", "records", "items"):
if isinstance(data.get(key), list):
return data[key]
raise ValueError("JSON must be an array of objects or {rows: [...]}")
# ── SQL loading ───────────────────────────────────────────────────────────
def _load_sql_preview(self, path: str):
try:
with open(path, encoding="utf-8", errors="replace") as f:
self._sql_text = f.read()
except Exception as e:
self._status_lbl.setText(f"Read error: {e}")
self._sql_text = ""
self._sql_stmts = 0
self._ok_btn.setEnabled(False)
return
# Count statements
try:
import sqlparse
stmts = [s for s in sqlparse.split(self._sql_text) if s.strip()]
except ImportError:
stmts = [s for s in self._sql_text.split(";") if s.strip()]
self._sql_stmts = len(stmts)
# Show first N lines in text preview
lines = self._sql_text.splitlines()
preview_text = "\n".join(lines[:_PREVIEW_SQL_LINES])
if len(lines) > _PREVIEW_SQL_LINES:
preview_text += f"\n\n… ({len(lines) - _PREVIEW_SQL_LINES:,} more lines)"
self._preview_sql.setPlainText(preview_text)
self._preview_stack.setCurrentIndex(1)
self._preview_label.setText(
f"Preview (first {_PREVIEW_SQL_LINES} lines):"
)
self._status_lbl.setText(
f"{self._sql_stmts:,} statement(s) ready to execute "
f"on database '{self._database}'"
)
self._ok_btn.setEnabled(self._sql_stmts > 0)
# ── Table preview (CSV/JSON) ───────────────────────────────────────────────
def _populate_table_preview(self, rows: list):
if not rows:
self._preview_table.setRowCount(0)
self._preview_table.setColumnCount(0)
return
headers = list(rows[0].keys())
self._preview_table.setColumnCount(len(headers))
self._preview_table.setHorizontalHeaderLabels(headers)
self._preview_table.setRowCount(len(rows))
for r, row in enumerate(rows):
for c, key in enumerate(headers):
val = row.get(key, "")
self._preview_table.setItem(
r, c, QTableWidgetItem("" if val is None else str(val))
)
# ── Import dispatch ───────────────────────────────────────────────────────
def _start_import(self):
self._ok_btn.setEnabled(False)
self._errors.clear()
if self._mode == self._MODE_SQL:
self._start_sql_import()
else:
self._start_row_import()
def _start_row_import(self):
if not self._rows:
return
self._progress.setMaximum(len(self._rows))
self._progress.setValue(0)
self._progress.setVisible(True)
self._worker = _RowImportWorker(
self._driver, self._database, self._table, self._rows, parent=self
)
self._worker.progress.connect(self._progress.setValue)
self._worker.finished.connect(self._on_row_done)
self._worker.error.connect(self._on_fatal_error)
self._worker.start()
def _start_sql_import(self):
if not self._sql_text.strip():
return
self._progress.setMaximum(self._sql_stmts)
self._progress.setValue(0)
self._progress.setVisible(True)
self._status_lbl.setText("Executing SQL statements…")
self._worker = _SqlImportWorker(
self._driver, self._database, self._sql_text,
stop_on_error=self._stop_on_error_cb.isChecked(),
parent=self,
)
self._worker.progress.connect(self._progress.setValue)
self._worker.stmt_error.connect(self._on_stmt_error)
self._worker.finished.connect(self._on_sql_done)
self._worker.fatal.connect(self._on_fatal_error)
self._worker.start()
# ── Worker callbacks ──────────────────────────────────────────────────────
def _on_row_done(self, count: int):
self._progress.setValue(count)
QMessageBox.information(
self, "Import Complete",
f"Successfully imported {count:,} row(s) into '{self._table}'."
)
self.accept()
def _on_sql_done(self, executed: int, total: int):
self._progress.setValue(total)
skipped = total - executed
msg = (
f"Executed {executed:,} of {total:,} statement(s) "
f"on database '{self._database}'."
)
if self._errors:
msg += f"\n\n⚠️ {len(self._errors)} statement(s) produced errors:"
msg += "\n" + "\n".join(self._errors[:10])
if len(self._errors) > 10:
msg += f"\n… and {len(self._errors) - 10} more"
QMessageBox.warning(self, "Import Finished with Errors", msg)
else:
QMessageBox.information(self, "Import Complete", msg)
self.accept()
def _on_stmt_error(self, idx: int, msg: str):
self._errors.append(f"Statement {idx + 1}: {msg}")
self._status_lbl.setText(
f"Running… ({len(self._errors)} error(s) so far)"
)
def _on_fatal_error(self, msg: str):
self._progress.setVisible(False)
self._ok_btn.setEnabled(True)
QMessageBox.critical(self, "Import Error", f"Import failed:\n{msg}")
Reference in New Issue View Git Blame Copy Permalink