如何用脚本自动化实现CSV更新SQLite3数据库：删旧表重建同架构表

嘿，这个问题我熟！确实，.mode csv和.import是sqlite3命令行工具的专属元命令，不是标准SQL语句，所以纯SQL脚本里根本没法跑。不过有几个靠谱的自动化方案，帮你搞定删除旧表、建新表、导入CSV的全流程：

方案1：用Shell/Batch脚本封装sqlite3命令行调用 #

这是最直接的方案，利用sqlite3命令行工具支持元命令的特性，把SQL操作和导入逻辑串在一起。

Linux/Mac下的Shell脚本示例 #

#!/bin/bash

# 替换成你的实际路径和表名
DB_FILE="your_database.db"
CSV_FILE="updated_data.csv"
TABLE_NAME="target_table"

# 调用sqlite3执行一系列命令
sqlite3 "$DB_FILE" <<EOF
-- 删除旧表（如果存在）
DROP TABLE IF EXISTS $TABLE_NAME;
-- 创建新表（替换成你原表的架构）
CREATE TABLE $TABLE_NAME (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    score INTEGER,
    join_date DATE
);
-- 切换CSV模式并导入数据
.mode csv
.import "$CSV_FILE" $TABLE_NAME
EOF

Windows下的Batch脚本示例 #

@echo off
set DB_FILE=your_database.db
set CSV_FILE=updated_data.csv
set TABLE_NAME=target_table

sqlite3.exe %DB_FILE% ^
"DROP TABLE IF EXISTS %TABLE_NAME%;" ^
"CREATE TABLE %TABLE_NAME% (id INTEGER PRIMARY KEY, username TEXT NOT NULL, score INTEGER, join_date DATE);" ^
".mode csv" ^
".import %CSV_FILE% %TABLE_NAME%"

这个方案轻量无依赖，只要你的系统里装了sqlite3命令行工具就能跑。

方案2：用Python脚本实现跨平台自动化 #

如果需要跨平台支持（Windows/Linux/Mac通吃），或者要处理更复杂的逻辑（比如动态读取原表架构），Python脚本是更好的选择。

import sqlite3
import csv

# 配置参数，替换成你的实际信息
DB_PATH = "your_database.db"
CSV_PATH = "updated_data.csv"
TABLE_NAME = "target_table"

# 连接数据库
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()

# 1. 删除旧表
cursor.execute(f"DROP TABLE IF EXISTS {TABLE_NAME}")

# 2. 创建新表（这里可以替换成原表的架构，或者动态获取）
# 如果你想动态获取原表架构，可以从备份库或原库查询：
# cursor.execute(f"SELECT sql FROM sqlite_master WHERE type='table' AND name='{TABLE_NAME}'")
# create_table_sql = cursor.fetchone()[0]
create_table_sql = f"""
CREATE TABLE {TABLE_NAME} (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    score INTEGER,
    join_date DATE
)
"""
cursor.execute(create_table_sql)

# 3. 读取CSV并批量插入数据
with open(CSV_PATH, 'r', encoding='utf-8') as csv_file:
    csv_reader = csv.reader(csv_file)
    # 跳过CSV表头（如果你的CSV有表头的话）
    next(csv_reader)
    # 构造批量插入的占位符
    column_count = len(next(csv_reader))
    csv_file.seek(0)  # 重置文件指针到开头
    next(csv_reader)  # 再次跳过表头
    placeholders = ', '.join(['?'] * column_count)
    insert_sql = f"INSERT INTO {TABLE_NAME} VALUES ({placeholders})"
    # 批量插入，效率比单条插高很多
    cursor.executemany(insert_sql, csv_reader)

# 提交更改并关闭连接
conn.commit()
conn.close()

print("数据表更新完成！")

这个方案灵活性拉满，还能处理各种边缘情况（比如编码问题、数据校验）。

方案3：用SQLite的CSV虚拟表（适合高版本SQLite） #

如果你的SQLite版本在3.39.0及以上，可以用内置的csv虚拟表，这样就能在纯SQL里完成导入了：

-- 删除旧表
DROP TABLE IF EXISTS target_table;
-- 创建新表（原架构）
CREATE TABLE target_table (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    score INTEGER,
    join_date DATE
);
-- 加载CSV扩展并导入数据
.load csv
-- header=1表示你的CSV文件包含表头，会自动匹配列
INSERT INTO target_table
SELECT * FROM csv('updated_data.csv', 'header=1');

注意：如果是用编程方式连接数据库（比如Python），需要先启用扩展加载：conn.enable_load_extension(True)，否则会报错。

内容的提问来源于stack exchange，提问作者Josh Sharkey