使用Pandas操作SQLite添加新列时替换表报错问题排查

问题原因 #

SQLite采用文件锁与严格的事务机制，使用原生sqlite3连接读取数据后，若未提交事务，表会处于锁定状态。此时pandas的if_exists='replace'逻辑中尝试删除原表的操作会失败，后续创建新表时就会触发"table already exists"错误。

解决方案 #

1. 配置连接自动提交或手动提交事务

原生sqlite3连接默认开启事务，需确保读取操作的事务已提交，避免表锁定：

import sqlite3
import pandas as pd

# 方式一：设置连接自动提交
conn = sqlite3.connect('School.sqlite', isolation_level=None)
df = pd.read_sql_query('SELECT * FROM Student', conn)
df['Address'] = ''  # 新增Address列
df.to_sql('Student', conn, if_exists='replace', index=False)
conn.close()

# 方式二：手动提交事务
conn = sqlite3.connect('School.sqlite')
df = pd.read_sql_query('SELECT * FROM Student', conn)
df['Address'] = ''
# 提交读取操作的事务，释放表锁
conn.commit()
df.to_sql('Student', conn, if_exists='replace', index=False)
conn.commit()
conn.close()

2. 手动删除表后再写入数据

如果自动替换逻辑仍有问题，可显式删除原表后再写入（注意先读取数据再删表，避免数据丢失）：

import sqlite3
import pandas as pd

conn = sqlite3.connect('School.sqlite')
# 先读取原表数据
df = pd.read_sql_query('SELECT * FROM Student', conn)
df['Address'] = ''
# 显式删除原表
conn.execute('DROP TABLE IF EXISTS Student')
conn.commit()
# 写入新表
df.to_sql('Student', conn, index=False)
conn.commit()
conn.close()

3. 使用SQLAlchemy连接替代原生sqlite3连接

pandas对SQLAlchemy连接的支持更完善，能自动处理事务和锁的问题：

from sqlalchemy import create_engine
import pandas as pd

engine = create_engine('sqlite:///School.sqlite')
with engine.connect() as conn:
    df = pd.read_sql_query('SELECT * FROM Student', conn)
    df['Address'] = ''
    df.to_sql('Student', conn, if_exists='replace', index=False)

内容的提问来源于stack exchange，提问作者Amar Singh Raghuvanshi