| import streamlit as st |
| import pandas as pd |
| import numpy as np |
| import chromadb |
| import faiss |
| import os |
| import io |
| import shutil |
| import zipfile |
| import uuid |
| from chromadb.utils import embedding_functions |
| from streamlit_javascript import st_javascript |
|
|
| st.set_page_config(page_title="VectorDB Converter", layout="wide") |
|
|
| st.markdown(''' |
| <style> |
| /* Pull the main container up to eliminate the huge blank space */ |
| div[data-testid="stAppViewBlockContainer"] { |
| padding-top: 2rem !important; |
| } |
| .block-container { |
| padding-top: 2rem !important; |
| } |
| |
| /* Prevent the 'Deploy' header from covering our content */ |
| header[data-testid="stHeader"] { |
| background-color: transparent; |
| z-index: 998; |
| } |
| |
| /* Make the Tabs sticky at the top */ |
| div[data-testid="stTabs"] > div[data-baseweb="tab-list"] { |
| position: sticky; |
| top: 2rem; |
| background-color: white; |
| z-index: 999; |
| padding-top: 15px; |
| padding-bottom: 10px; |
| border-bottom: 1px solid #f0f2f6; |
| } |
| |
| /* Dark mode background fix for sticky tabs */ |
| @media (prefers-color-scheme: dark) { |
| div[data-testid="stTabs"] > div[data-baseweb="tab-list"] { |
| background-color: #0e1117; |
| border-bottom: 1px solid #333; |
| } |
| } |
| </style> |
| ''', unsafe_allow_html=True) |
|
|
| TRANSLATIONS = { |
| "zh-TW": { |
| "page_title": "向量資料庫轉換器", |
| "title": "向量資料庫轉換器：表格 ↔ 向量庫", |
| "tab_table_to_db": "表格轉向量庫", |
| "tab_db_to_table": "向量庫轉表格", |
| "header_tab1": "將 CSV/Excel 轉換為向量資料庫", |
| "upload_file": "上傳 CSV 或 Excel 檔案", |
| "select_sheet": "選擇 Excel 工作表：", |
| "data_preview": "資料預覽", |
| "total_rows": "總行數", |
| "total_cols": "總欄數", |
| "scroll_down": "在表格內向下捲動以檢視所有資料。", |
| "select_columns": "選擇一個或多個要轉換為向量的文字欄位：", |
| "select_db": "選擇目標向量資料庫：", |
| "btn_convert": "轉換為向量資料庫", |
| "warn_select_col": "請至少選擇一個要轉換的欄位。", |
| "generating": "正在產生向量並儲存至", |
| "initializing": "初始化中...", |
| "saving_chroma": "正在儲存至 ChromaDB...", |
| "saving_faiss": "正在儲存索引與元數據至 FAISS...", |
| "zipping": "正在壓縮輸出檔案...", |
| "download_chroma": "下載 ChromaDB (ZIP)", |
| "download_faiss": "下載 FAISS DB (ZIP)", |
| "success_chroma": "成功轉換為 ChromaDB！", |
| "success_faiss": "成功轉換為 FAISS！", |
| "header_tab2": "將向量資料庫轉換為 CSV/Excel", |
| "source_db": "選擇來源向量資料庫：", |
| "upload_zip_info": "上傳包含您的 {} 檔案的 ZIP 壓縮包。", |
| "upload_zip_btn": "上傳資料庫 ZIP 檔案", |
| "no_collections": "在此 ChromaDB 中找不到任何集合。", |
| "select_collection": "選擇集合：", |
| "extract_data": "提取資料", |
| "fail_read": "無法讀取 ChromaDB：", |
| "no_index": "在上傳的 ZIP 中找不到 .index 檔案。", |
| "select_index": "選擇 FAISS 索引：", |
| "extracted_preview": "提取資料預覽", |
| "download_csv": "下載為 CSV", |
| "download_excel": "下載為 Excel", |
| "error_processing": "處理向量資料庫時發生錯誤：", |
| "generating_progress": "正在產生向量：已處理 {} / {} 行", |
| "error_read_convert": "讀取檔案或轉換時發生錯誤：", |
| "data_source": "資料來源：", |
| "file_upload": "檔案上傳 (CSV/Excel)", |
| "sql_db": "SQL 資料庫", |
| "db_uri": "資料庫連線字串 (URI)", |
| "db_uri_help": "範例：sqlite:///my_database.db 或 mysql+pymysql://user:pass@localhost/dbname", |
| "connect_db": "連線", |
| "select_table": "選擇資料表：", |
| "export_to_sql": "匯出至 SQL 資料庫", |
| "target_table": "目標資料表名稱", |
| "save_to_db": "儲存至資料庫", |
| "db_success": "成功儲存至資料庫！" |
| }, |
| "zh-CN": { |
| "page_title": "向量数据库转换器", |
| "title": "向量数据库转换器：表格 ↔ 向量库", |
| "tab_table_to_db": "表格转向量库", |
| "tab_db_to_table": "向量库转表格", |
| "header_tab1": "将 CSV/Excel 转换为向量数据库", |
| "upload_file": "上传 CSV 或 Excel 文件", |
| "select_sheet": "选择 Excel 工作表：", |
| "data_preview": "数据预览", |
| "total_rows": "总行数", |
| "total_cols": "总列数", |
| "scroll_down": "在表格内向下滚动以查看所有数据。", |
| "select_columns": "选择一个或多个要转换为向量的文本字段：", |
| "select_db": "选择目标向量数据库：", |
| "btn_convert": "转换为向量数据库", |
| "warn_select_col": "请至少选择一个要转换的字段。", |
| "generating": "正在生成向量并保存至", |
| "initializing": "初始化中...", |
| "saving_chroma": "正在保存至 ChromaDB...", |
| "saving_faiss": "正在保存索引与元数据至 FAISS...", |
| "zipping": "正在压缩输出文件...", |
| "download_chroma": "下载 ChromaDB (ZIP)", |
| "download_faiss": "下载 FAISS DB (ZIP)", |
| "success_chroma": "成功转换为 ChromaDB！", |
| "success_faiss": "成功转换为 FAISS！", |
| "header_tab2": "将向量数据库转换为 CSV/Excel", |
| "source_db": "选择来源向量数据库：", |
| "upload_zip_info": "上传包含您的 {} 文件的 ZIP 压缩包。", |
| "upload_zip_btn": "上传数据库 ZIP 文件", |
| "no_collections": "在此 ChromaDB 中找不到任何集合。", |
| "select_collection": "选择集合：", |
| "extract_data": "提取数据", |
| "fail_read": "无法读取 ChromaDB：", |
| "no_index": "在上传的 ZIP 中找不到 .index 文件。", |
| "select_index": "选择 FAISS 索引：", |
| "extracted_preview": "提取数据预览", |
| "download_csv": "下载为 CSV", |
| "download_excel": "下载为 Excel", |
| "error_processing": "处理向量数据库时发生错误：", |
| "generating_progress": "正在生成向量：已处理 {} / {} 行", |
| "error_read_convert": "读取文件或转换时发生错误：", |
| "data_source": "数据来源：", |
| "file_upload": "文件上传 (CSV/Excel)", |
| "sql_db": "SQL 数据库", |
| "db_uri": "数据库连接字符串 (URI)", |
| "db_uri_help": "示例：sqlite:///my_database.db 或 mysql+pymysql://user:pass@localhost/dbname", |
| "connect_db": "连接", |
| "select_table": "选择数据表：", |
| "export_to_sql": "导出至 SQL 数据库", |
| "target_table": "目标数据表名称", |
| "save_to_db": "保存至数据库", |
| "db_success": "成功保存至数据库！" |
| }, |
| "en": { |
| "page_title": "VectorDB Converter", |
| "title": "VectorDB Converter: Table ↔ Vector Database", |
| "tab_table_to_db": "Table to Vector DB", |
| "tab_db_to_table": "Vector DB to Table", |
| "header_tab1": "Convert CSV/Excel to Vector Database", |
| "upload_file": "Upload CSV or Excel file", |
| "select_sheet": "Select Excel Sheet:", |
| "data_preview": "Data Preview", |
| "total_rows": "Total rows", |
| "total_cols": "Total columns", |
| "scroll_down": "Scroll down inside the table to see all rows.", |
| "select_columns": "Select one or more columns containing text to embed:", |
| "select_db": "Select Target Vector Database:", |
| "btn_convert": "Convert to Vector DB", |
| "warn_select_col": "Please select at least one column to embed.", |
| "generating": "Generating embeddings and saving to", |
| "initializing": "Initializing...", |
| "saving_chroma": "Saving to ChromaDB...", |
| "saving_faiss": "Saving index and metadata to FAISS...", |
| "zipping": "Zipping output...", |
| "download_chroma": "Download ChromaDB (ZIP)", |
| "download_faiss": "Download FAISS DB (ZIP)", |
| "success_chroma": "Successfully converted to ChromaDB!", |
| "success_faiss": "Successfully converted to FAISS!", |
| "header_tab2": "Convert Vector Database to CSV/Excel", |
| "source_db": "Select Source Vector Database:", |
| "upload_zip_info": "Upload the ZIP file containing your {} files.", |
| "upload_zip_btn": "Upload DB ZIP file", |
| "no_collections": "No collections found in this ChromaDB.", |
| "select_collection": "Select Collection:", |
| "extract_data": "Extract Data", |
| "fail_read": "Failed to read ChromaDB:", |
| "no_index": "No .index file found in the uploaded ZIP.", |
| "select_index": "Select FAISS index:", |
| "extracted_preview": "Extracted Data Preview", |
| "download_csv": "Download as CSV", |
| "download_excel": "Download as Excel", |
| "error_processing": "Error processing the Vector DB:", |
| "generating_progress": "Generating embeddings: {} / {} rows processed", |
| "error_read_convert": "Error reading file or converting:", |
| "data_source": "Data Source:", |
| "file_upload": "File Upload (CSV/Excel)", |
| "sql_db": "SQL Database", |
| "db_uri": "Database Connection URI", |
| "db_uri_help": "Example: sqlite:///my_database.db or mysql+pymysql://user:pass@localhost/dbname", |
| "connect_db": "Connect", |
| "select_table": "Select Table:", |
| "export_to_sql": "Export to SQL Database", |
| "target_table": "Target Table Name", |
| "save_to_db": "Save to Database", |
| "db_success": "Successfully saved to Database!" |
| } |
| } |
|
|
| |
| try: |
| lang_str = st_javascript("window.navigator.userLanguage || window.navigator.language;") |
| if not lang_str or lang_str == 0: |
| lang_str = "zh-TW" |
| except: |
| lang_str = "zh-TW" |
|
|
| lang_str = str(lang_str).lower() |
| if "zh-cn" in lang_str or "zh-hans" in lang_str: |
| lang_code = "zh-CN" |
| elif "en" in lang_str: |
| lang_code = "en" |
| else: |
| lang_code = "zh-TW" |
|
|
| t = TRANSLATIONS[lang_code] |
|
|
| |
| default_ef = embedding_functions.DefaultEmbeddingFunction() |
|
|
| def get_embeddings_batched(texts, progress_bar=None, log_func=None): |
| batch_size = 32 |
| all_embeddings = [] |
| total = len(texts) |
| if log_func: log_func(f"Starting batch embedding generation (Total rows: {total}, Batch size: {batch_size})") |
| for i in range(0, total, batch_size): |
| batch = texts[i:i+batch_size] |
| emb = default_ef(batch) |
| all_embeddings.extend(emb) |
| if progress_bar is not None: |
| progress = min(1.0, (i + len(batch)) / total) |
| progress_bar.progress(progress, text=t.get("generating_progress", "Progress: {}/{}").format(i + len(batch), total)) |
| if log_func: log_func(f"Processed embeddings: {min(i + len(batch), total)} / {total}") |
| if log_func: log_func("Embeddings generation completed successfully.") |
| return np.array(all_embeddings, dtype=np.float32) |
|
|
| def table_to_chroma(df, db_path, collection_name, text_columns, progress_bar=None, log_func=None): |
| if log_func: log_func(f"Initializing ChromaDB client at: {db_path}") |
| try: |
| client = chromadb.PersistentClient(path=db_path) |
| collection = client.get_or_create_collection(name=collection_name) |
| except Exception as e: |
| if log_func: log_func(f"ERROR initializing ChromaDB: {e}") |
| raise e |
| |
| if log_func: log_func("Preparing text data for embedding...") |
| texts = [] |
| for _, row in df.iterrows(): |
| parts = [f"{col}: {row[col]}" for col in text_columns if pd.notna(row[col])] |
| texts.append("\n".join(parts) if parts else "") |
| |
| embeddings = get_embeddings_batched(texts, progress_bar, log_func) |
| |
| if log_func: log_func("Preparing metadata payload...") |
| if progress_bar is not None: |
| progress_bar.progress(1.0, text=t.get("saving_chroma", "Saving to ChromaDB...")) |
| |
| metadatas = [] |
| for _, row in df.iterrows(): |
| meta = {} |
| for col in df.columns: |
| val = row[col] |
| if pd.isna(val): val = "" |
| elif not isinstance(val, (str, int, float, bool)): val = str(val) |
| meta[col] = val |
| metadatas.append(meta) |
| ids = [str(i) for i in range(len(df))] |
| |
| batch_size = 200 |
| total = len(df) |
| if log_func: log_func(f"Adding data to ChromaDB (Batch size: {batch_size})...") |
| for i in range(0, total, batch_size): |
| collection.add( |
| embeddings=embeddings[i:i+batch_size].tolist(), |
| documents=texts[i:i+batch_size], |
| metadatas=metadatas[i:i+batch_size], |
| ids=ids[i:i+batch_size] |
| ) |
| if log_func: log_func(f"Saved to DB: {min(i + batch_size, total)} / {total}") |
| if log_func: log_func("Successfully finished ChromaDB conversion.") |
| return True |
|
|
| def chroma_to_table(db_path, collection_name): |
| client = chromadb.PersistentClient(path=db_path) |
| collection = client.get_collection(name=collection_name) |
| data = collection.get(include=["documents", "metadatas"]) |
| documents = data.get("documents", []) |
| metadatas = data.get("metadatas", []) |
| if not metadatas: return pd.DataFrame({"text": documents}) |
| return pd.DataFrame(metadatas) |
|
|
| def faiss_to_table(index_path): |
| import pandas as pd |
| import os |
| meta_path = index_path + ".meta.pkl" |
| if os.path.exists(meta_path): |
| return pd.read_pickle(meta_path) |
| else: |
| raise Exception(f"Metadata file not found: {meta_path} - Ensure the .meta.pkl file is in the same directory as the .index file.") |
|
|
| def table_to_faiss(df, index_path, text_columns, progress_bar=None, log_func=None): |
| if log_func: log_func("Preparing text data for embedding...") |
| texts = [] |
| for _, row in df.iterrows(): |
| parts = [f"{col}: {row[col]}" for col in text_columns if pd.notna(row[col])] |
| texts.append("\n".join(parts) if parts else "") |
| |
| embeddings = get_embeddings_batched(texts, progress_bar, log_func) |
| |
| if progress_bar is not None: |
| progress_bar.progress(1.0, text=t.get("saving_faiss", "Saving to FAISS...")) |
| |
| if log_func: log_func("Creating FAISS index...") |
| dimension = embeddings.shape[1] |
| index = faiss.IndexFlatL2(dimension) |
| |
| if log_func: log_func(f"Adding {len(embeddings)} vectors to index...") |
| index.add(embeddings) |
| |
| if log_func: log_func(f"Writing index to {index_path}...") |
| faiss.write_index(index, index_path) |
| |
| meta_path = index_path + ".meta.pkl" |
| if log_func: log_func(f"Saving metadata to {meta_path}...") |
| df.to_pickle(meta_path) |
| |
| if log_func: log_func("Successfully finished FAISS conversion.") |
| return True |
|
|
| st.title(t["title"]) |
|
|
| tab1, tab2 = st.tabs([t["tab_table_to_db"], t["tab_db_to_table"]]) |
|
|
| |
| with tab1: |
| st.header(t["header_tab1"]) |
| |
| data_source = st.radio(t.get("data_source", "Data Source:"), ["📁 " + t.get("file_upload", "File Upload"), "🗄️ SQLite File", "🌐 Remote SQL"], horizontal=True) |
| df = None |
| |
| if data_source.startswith("📁"): |
| uploaded_file = st.file_uploader(t["upload_file"], type=["csv", "xlsx", "xls"], key="upload_table") |
| if uploaded_file is not None: |
| try: |
| if uploaded_file.name.endswith('.csv'): |
| df = pd.read_csv(uploaded_file) |
| else: |
| xl = pd.ExcelFile(uploaded_file) |
| sheet = st.selectbox(t["select_sheet"], xl.sheet_names) |
| df = xl.parse(sheet) |
| except Exception as e: |
| st.error(f"{t.get('error_read_convert', 'Error')} {str(e)}") |
| |
| elif data_source.startswith("🗄️"): |
| sqlite_file = st.file_uploader("Upload SQLite Database (.db, .sqlite) / 上傳 SQLite 檔案", type=["db", "sqlite"], key="upload_sqlite") |
| if sqlite_file is not None: |
| temp_db_path = os.path.join("/data/temp_extract", f"temp_{hash(sqlite_file.name)}.db") |
| os.makedirs("/data/temp_extract", exist_ok=True) |
| with open(temp_db_path, "wb") as f: |
| f.write(sqlite_file.getvalue()) |
| db_uri = f"sqlite:///{temp_db_path}" |
| |
| try: |
| from sqlalchemy import create_engine, inspect |
| engine = create_engine(db_uri) |
| inspector = inspect(engine) |
| tables = inspector.get_table_names() |
| if tables: |
| selected_table = st.selectbox(t.get("select_table", "Select Table:"), tables) |
| if selected_table: |
| df = pd.read_sql_table(selected_table, engine) |
| else: |
| st.warning("No tables found in this database.") |
| except Exception as e: |
| st.error(f"Database error: {e}") |
| |
| else: |
| with st.container(): |
| col1, col2 = st.columns(2) |
| sql_type = col1.selectbox("DB Type / 資料庫類型", ["MySQL", "PostgreSQL"]) |
| host = col2.text_input("Host / 伺服器", value="localhost") |
| port = col1.text_input("Port / 埠號", value="3306" if sql_type=="MySQL" else "5432") |
| db_name = col2.text_input("Database Name / 資料庫名稱") |
| user = col1.text_input("Username / 帳號") |
| password = col2.text_input("Password / 密碼", type="password") |
| |
| if host and db_name and user: |
| if sql_type == "MySQL": |
| db_uri = f"mysql+pymysql://{user}:{password}@{host}:{port}/{db_name}" |
| else: |
| db_uri = f"postgresql://{user}:{password}@{host}:{port}/{db_name}" |
| |
| try: |
| from sqlalchemy import create_engine, inspect |
| engine = create_engine(db_uri) |
| inspector = inspect(engine) |
| tables = inspector.get_table_names() |
| if tables: |
| selected_table = st.selectbox(t.get("select_table", "Select Table:"), tables) |
| if selected_table: |
| df = pd.read_sql_table(selected_table, engine) |
| else: |
| st.warning("No tables found in this database.") |
| except Exception as e: |
| st.error(f"Database error: {e}") |
|
|
| if df is not None: |
| try: |
| |
| st.write(f"### {t['data_preview']} ({t['total_rows']}: {df.shape[0]}, {t['total_cols']}: {df.shape[1]})") |
| st.write(t["scroll_down"]) |
| st.dataframe(df, height=600, use_container_width=True) |
| |
| text_columns = st.multiselect(t["select_columns"], df.columns, default=[df.columns[0]]) |
| db_type = st.radio(t["select_db"], ["ChromaDB", "FAISS"]) |
| |
| if st.button(t["btn_convert"]): |
| if not text_columns: |
| st.warning(t["warn_select_col"]) |
| else: |
| with st.spinner(f"{t['generating']} {db_type}..."): |
| output_dir = "/data/output_db" |
| os.makedirs(output_dir, exist_ok=True) |
| |
| progress_bar = st.progress(0, text=t["initializing"]) |
| |
| st.write(f"### Execution Logs / 執行日誌") |
| log_placeholder = st.empty() |
| log_msgs = [] |
| def app_logger(msg): |
| import datetime |
| import html |
| ts = datetime.datetime.now().strftime("%H:%M:%S") |
| log_msgs.append(f"[{ts}] {msg}") |
| safe_logs = html.escape("\n".join(log_msgs)) |
| |
| log_html = f""" |
| <div style=" |
| height: 220px; |
| overflow-y: auto; |
| background-color: #1e1e1e; |
| color: #4af626; |
| padding: 10px; |
| border-radius: 5px; |
| font-family: 'Courier New', monospace; |
| font-size: 13px; |
| white-space: pre-wrap; |
| border: 1px solid #444; |
| display: flex; |
| flex-direction: column-reverse; |
| "> |
| <div>{safe_logs}</div> |
| </div> |
| """ |
| log_placeholder.markdown(log_html, unsafe_allow_html=True) |
| |
| session_id = uuid.uuid4().hex |
| |
| if db_type == "ChromaDB": |
| db_path = os.path.join(output_dir, f"chroma_db_{session_id}") |
| os.makedirs(db_path, exist_ok=True) |
| |
| collection_name = "my_collection" |
| table_to_chroma(df, db_path, collection_name, text_columns, progress_bar, app_logger) |
| |
| progress_bar.progress(1.0, text=t["zipping"]) |
| |
| base_name = os.path.join(output_dir, f"chroma_db_{session_id}") |
| shutil.make_archive(base_name, 'zip', db_path) |
| zip_path = base_name + ".zip" |
| |
| with open(zip_path, "rb") as f: |
| st.download_button( |
| label=t["download_chroma"], |
| data=f, |
| file_name="chroma_db.zip", |
| mime="application/zip" |
| ) |
| progress_bar.empty() |
| st.success(t["success_chroma"]) |
| |
| elif db_type == "FAISS": |
| faiss_dir = os.path.join(output_dir, f"faiss_db_{session_id}") |
| os.makedirs(faiss_dir, exist_ok=True) |
| |
| index_path = os.path.join(faiss_dir, "vector.index") |
| table_to_faiss(df, index_path, text_columns, progress_bar, app_logger) |
| |
| progress_bar.progress(1.0, text=t["zipping"]) |
| |
| base_name = os.path.join(output_dir, f"faiss_db_{session_id}") |
| shutil.make_archive(base_name, 'zip', faiss_dir) |
| zip_path = base_name + ".zip" |
| |
| with open(zip_path, "rb") as f: |
| st.download_button( |
| label=t["download_faiss"], |
| data=f, |
| file_name="faiss_db.zip", |
| mime="application/zip" |
| ) |
| progress_bar.empty() |
| st.success(t["success_faiss"]) |
| |
| except Exception as e: |
| st.error(f"{t['error_read_convert']} {str(e)}") |
|
|
| |
| with tab2: |
| st.header(t["header_tab2"]) |
| |
| db_type_reverse = st.radio(t["source_db"], ["ChromaDB", "FAISS"], key="reverse_db") |
| |
| st.info(t.get("upload_zip_info", "Upload ZIP").format(db_type_reverse)) |
| uploaded_zip = st.file_uploader(t.get("upload_zip_btn", "Upload ZIP"), type=["zip"], key="upload_zip") |
| |
| if uploaded_zip is not None: |
| extract_dir = os.path.join("/data/temp_extract", f"zip_{hash(uploaded_zip.name)}") |
| if not os.path.exists(extract_dir): |
| os.makedirs(extract_dir, exist_ok=True) |
| with zipfile.ZipFile(uploaded_zip, 'r') as zip_ref: |
| zip_ref.extractall(extract_dir) |
| |
| try: |
| if db_type_reverse == "ChromaDB": |
| try: |
| client = chromadb.PersistentClient(path=extract_dir) |
| collections = client.list_collections() |
| if not collections: |
| st.error(t.get("no_collections", "No collections found.")) |
| else: |
| col_names = [c if isinstance(c, str) else c.name for c in collections] |
| selected_col = st.selectbox(t.get("select_collection", "Select Collection"), col_names) |
| |
| if st.button(t.get("extract_data", "Extract Data")): |
| st.session_state['df_extracted'] = chroma_to_table(extract_dir, selected_col) |
| except Exception as e: |
| st.error(f"{t.get('fail_read', 'Failed to read')} {e}") |
| |
| elif db_type_reverse == "FAISS": |
| index_files = [f for f in os.listdir(extract_dir) if f.endswith(".index")] |
| if not index_files: |
| st.error(t.get("no_index", "No .index found")) |
| else: |
| selected_index = st.selectbox(t.get("select_index", "Select Index"), index_files) |
| if st.button(t.get("extract_data", "Extract Data")): |
| index_path = os.path.join(extract_dir, selected_index) |
| st.session_state['df_extracted'] = faiss_to_table(index_path) |
| |
| if 'df_extracted' in st.session_state and st.session_state['df_extracted'] is not None: |
| df_to_show = st.session_state['df_extracted'] |
| st.write(f"### {t.get('extracted_preview', 'Extracted Data Preview')}") |
| st.dataframe(df_to_show, height=300) |
| |
| col1, col2, col3, _ = st.columns([1.2, 1.2, 1.5, 3]) |
| |
| csv_data = df_to_show.to_csv(index=False).encode('utf-8') |
| col1.download_button( |
| label=t.get("download_csv", "Download CSV"), |
| data=csv_data, |
| file_name="extracted_data.csv", |
| mime="text/csv" |
| ) |
| |
| excel_buffer = io.BytesIO() |
| df_to_show.to_excel(excel_buffer, index=False) |
| excel_buffer.seek(0) |
| col2.download_button( |
| label=t.get("download_excel", "Download Excel"), |
| data=excel_buffer, |
| file_name="extracted_data.xlsx", |
| mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" |
| ) |
| |
| |
| temp_db = os.path.join("/data/temp_extract", f"export_{uuid.uuid4().hex}.db") |
| from sqlalchemy import create_engine |
| engine_sqlite = create_engine(f"sqlite:///{temp_db}") |
| df_to_show.to_sql("extracted_data", con=engine_sqlite, if_exists='replace', index=False) |
| with open(temp_db, "rb") as f_db: |
| sqlite_data = f_db.read() |
| |
| col3.download_button( |
| label="Download SQLite DB", |
| data=sqlite_data, |
| file_name="extracted_data.db", |
| mime="application/x-sqlite3" |
| ) |
| |
| st.write("---") |
| st.write(f"### Push to Remote SQL Server / 推送至遠端資料庫") |
| st.info("Push data directly to a Remote SQL Server (MySQL / PostgreSQL)") |
| col3, col4 = st.columns(2) |
| exp_host = col3.text_input("Host / 伺服器", value="localhost", key="exp_host") |
| exp_port = col4.text_input("Port / 埠號", value="3306", key="exp_port") |
| exp_user = col3.text_input("Username / 帳號", key="exp_user") |
| exp_pass = col4.text_input("Password / 密碼", type="password", key="exp_pass") |
| exp_db = col3.text_input("Database / 資料庫名稱", key="exp_db") |
| exp_table = col4.text_input("Target Table / 匯出目標表", value="extracted_data", key="exp_table") |
| |
| if st.button(t.get("save_to_db", "Save to Database")): |
| if exp_host and exp_user and exp_db and exp_table: |
| try: |
| db_uri_export = f"mysql+pymysql://{exp_user}:{exp_pass}@{exp_host}:{exp_port}/{exp_db}" |
| from sqlalchemy import create_engine |
| engine = create_engine(db_uri_export) |
| df_to_show.to_sql(name=exp_table, con=engine, if_exists='replace', index=False) |
| st.success(t.get("db_success", "Successfully saved to Database!")) |
| except Exception as e: |
| st.error(f"Database error: {e}") |
| |
| except Exception as e: |
| st.error(f"{t.get('error_processing', 'Error')} {str(e)}") |
