Python脚本中SQLite表date字段格式更新无效问题排查

问题描述 #

尝试将finances数据库register表中的date字段转换为日期格式。Python脚本运行无报错，但其中的UPDATE语句未生效；在SQLite Studio中执行相同的UPDATE语句则能正常生效，不清楚原因。

相关代码 #

Python脚本 #

import sqlite3
import pandas as pd

conn = sqlite3.connect('C:\SQLite\finances.db')
cursor = conn.cursor()
cursor_obj = conn.cursor()

# 读取CSV文件
df = pd.read_csv('C:\SQLite\finance_register.csv')

# 写入SQLite数据库
df.to_sql("register", conn, if_exists="replace", index=False)

# 修改列名
cursor.execute('''alter table register rename 'Category Group' to category_group''')
cursor.execute('''alter table register rename 'Account' to account''')
cursor.execute('''alter table register rename 'Flag' to flag''')
cursor.execute('''alter table register rename 'Date' to date''')
cursor.execute('''alter table register rename 'Payee' to payee''')
cursor.execute('''alter table register rename 'Category Group/Category' to category_group_category''')
cursor.execute('''alter table register rename 'Category' to category''')
cursor.execute('''alter table register rename 'Memo' to memo''')
cursor.execute('''alter table register rename 'Outflow' to outflow''')
cursor.execute('''alter table register rename 'Inflow' to inflow''')
cursor.execute('''alter table register rename 'Cleared' to cleared''')

# 转换date字段格式
cursor.execute('''update register set date = substr(date, 7, 4) || '-' || substr(date, 1, 2) || '-' || substr(date, 4, 2)''')
    
conn.close()

SQLite Studio中执行的SQL语句 #

update register set date=substr(date, 7, 4) || '-' || substr(date, 1, 2) || '-' || substr(date, 4, 2);

原因分析 #

SQLite的Python接口默认开启事务机制，执行UPDATE这类修改操作后，如果没有主动提交事务，所有修改会在连接关闭时自动回滚，导致修改不生效。而SQLite Studio默认开启自动提交模式，执行语句后会立即将修改写入数据库，所以语句能正常生效。

解决方法 #

有两种可行的解决方式：

方式1：手动提交事务 #

在执行完UPDATE语句后，调用conn.commit()提交事务，再关闭连接。修改后的关键代码如下：

# 转换date字段格式
cursor.execute('''update register set date = substr(date, 7, 4) || '-' || substr(date, 1, 2) || '-' || substr(date, 4, 2)''')
# 提交事务
conn.commit()
conn.close()

方式2：开启自动提交模式 #

在连接数据库时设置isolation_level=None，让所有操作自动提交：

conn = sqlite3.connect('C:\SQLite\finances.db', isolation_level=None)

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