Python与SQLite3：向已有数据库导入数据的功能实现问题

Hey there, the issue you're hitting comes down to how SQLite's iterdump() generates export scripts. By default, it outputs CREATE TABLE statements without the IF NOT EXISTS clause—so when your target database already has that table, the CREATE line throws an error, and the rest of the script (the INSERTs that add your new data) never runs.

To get the "append data regardless of table existence" behavior you want, here are two straightforward solutions:

Option 1: Modify the Export Function to Generate Compatible SQL #

Update your export logic to replace every CREATE TABLE with CREATE TABLE IF NOT EXISTS as you write the script:

def exportDB(): 
    filePath, ok = QFileDialog.getSaveFileName(self, "Export file", "./exports", "SQL files (*.sql)") 
    if ok: 
        with open(filePath, 'w') as file: 
            for line in connection.iterdump(): 
                # Add IF NOT EXISTS to CREATE TABLE statements
                adjusted_line = line.replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS")
                file.write(f"{adjusted_line}\n")

Now any exports you create will safely run even if the tables already exist, and the INSERT statements will append new rows to your existing tables.

Option 2: Adjust the Import Function to Rewrite the SQL on the Fly #

If you don't want to change how you export data, you can modify the import logic to rewrite the SQL script before executing it:

def importDB(): 
    filePath, ok = QFileDialog.getOpenFileName(self, "Import file", "./exports", "SQL files (*.sql)") 
    if ok: 
        with open(filePath, 'r') as file: 
            raw_sql = file.read() 
            # Replace CREATE TABLE with CREATE TABLE IF NOT EXISTS
            modified_sql = raw_sql.replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS")
            cursor.executescript(modified_sql)

This works with existing exports made via the original iterdump() method, so you don't have to re-export old data.

Bonus: Precise Replacement with Regex #

If you're worried about accidental replacements (e.g., a table name that includes "CREATE TABLE"—super rare, but possible), use regex to target only valid CREATE TABLE statements:

import re

def importDB(): 
    filePath, ok = QFileDialog.getOpenFileName(self, "Import file", "./exports", "SQL files (*.sql)") 
    if ok: 
        with open(filePath, 'r') as file: 
            raw_sql = file.read() 
            # Only match CREATE TABLE followed by a table name
            modified_sql = re.sub(r'CREATE TABLE (\w+)', r'CREATE TABLE IF NOT EXISTS \1', raw_sql)
            cursor.executescript(modified_sql)

Quick Note on Schema Compatibility #

Make sure the schema of the imported table matches your existing table! If there are mismatches (like different column counts or data types), the INSERT statements will fail. You'll need to handle schema validation separately if that's a concern for your use case.

内容的提问来源于stack exchange，提问作者Bilbo Baggins