Pandas to_sql向SQLite表追加数据时外键约束失效如何解决？

问题场景 #

使用Pandas的to_sql方法将DataFrame追加至SQLite表时，表的id_region列存在外键约束，仅允许值1、2。但当DataFrame包含无效值3时，数据被直接写入数据库且无报错，外键约束被忽略；而通过Navicat手动修改该值为3时，却能触发预期的约束错误。这说明SQLite的外键约束本身有效，但to_sql插入数据时未生效。

问题原因 #

SQLite默认关闭外键约束检查，to_sql方法不会自动开启该功能，因此插入数据时不会验证外键约束。

解决方法 #

在建立数据库连接后，显式执行PRAGMA foreign_keys = ON;开启外键约束检查，这样to_sql插入数据时就会遵守外键规则。

修改后的示例代码 #

import sqlite3
import pandas as pd

file_path = 'demo.sqlite'

id_region = pd.DataFrame([
    {'id': 1, 'label': 'foo'},
    {'id': 2, 'label': 'baa'},
])
id_region.set_index(['id'], inplace=True)

data = pd.DataFrame([
    {'id': 1, 'id_region': 3, 'value': 1}
])
data.set_index(['id'], inplace=True)

create_data_table_query = 'CREATE TABLE `data` (' +\
               'id integer PRIMARY KEY NOT NULL, ' +\
               'id_region integer NOT Null, ' +\
               'value real NOT NULL, ' + \
               'FOREIGN KEY (id_region) REFERENCES id_region(id)' + \
               ')'

with sqlite3.connect(file_path) as connection:
    # 开启SQLite外键约束检查
    connection.execute("PRAGMA foreign_keys = ON;")
    
    id_region.to_sql('id_region', connection, index_label='id')

    cursor = connection.cursor()
    cursor.execute(create_data_table_query )
    # 此时插入无效id_region值会触发IntegrityError
    data.to_sql('data', connection, index_label='id', if_exists='append')

效果验证 #

修改后运行代码，会抛出sqlite3.IntegrityError: FOREIGN KEY constraint failed，符合预期的约束校验结果。

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