Python操作sqlite3如何在指定id数据行后插入新增SQL数据

你的数据库中价格表包含id、date、symbol、open、close五个字段，存量数据如下：

id   date                symbol          open   close
1   2022-06-13 10:00:00 TADAWUL:TASI    12278   12286
2   2022-06-14 10:00:00 TADAWUL:TASI    12038   12114
3   2022-06-15 10:00:00 TADAWUL:TASI    12036   12131
4   2022-06-02 10:00:00 TADAWUL:8070    18      18  
5   2022-06-05 10:00:00 TADAWUL:8070    18      18  
6   2022-06-06 10:00:00 TADAWUL:8070    18      18  
7   2022-06-07 10:00:00 TADAWUL:8070    18      18

需求为：分别在id=3、id=7的数据行之后各新增3行数据，基于Python的sqlite3模块实现。

• 由于sqlite自增主键默认会在表尾追加数据，如果需要严格保证新行的id顺序在指定行之后，必须先将插入点之后的原有记录id向后偏移，腾出对应数量的id空位，避免主键冲突
• 操作时优先处理id值更大的插入点（先处理id=7的位置，再处理id=3的位置），避免先偏移小id区间导致大id的目标行位置变化，无法准确定位
• 全流程使用事务包裹，任意步骤出错立即回滚，不会损坏原有数据

import sqlite3
from datetime import datetime, timedelta

# 替换为你的实际数据库文件路径
conn = sqlite3.connect('price_data.db')
cursor = conn.cursor()

try:
    conn.execute("BEGIN TRANSACTION;")
    # 先处理靠后的id=7插入点，避免id偏移影响目标行定位
    # 将id>7的原有记录统一后移3位，腾出空位
    cursor.execute("UPDATE your_table_name SET id = id + 3 WHERE id > 7;")
    # 读取id=7的基准行数据，作为新插入行的参考值
    cursor.execute("SELECT date, symbol, open, close FROM your_table_name WHERE id = 7;")
    base_date_7, symbol_7, open_7, close_7 = cursor.fetchone()
    # 插入3行新数据，示例中日期按天顺延，交易数据和基准行一致，可按需替换
    for offset in range(1, 4):
        new_id = 7 + offset
        new_date = (datetime.strptime(base_date_7, "%Y-%m-%d %H:%M:%S") + timedelta(days=offset)).strftime("%Y-%m-%d %H:%M:%S")
        cursor.execute(
            "INSERT INTO your_table_name (id, date, symbol, open, close) VALUES (?, ?, ?, ?, ?);",
            (new_id, new_date, symbol_7, open_7, close_7)
        )

    # 再处理id=3的插入点
    # 将id>3的所有记录（包含之前插入的id=7后的新行）统一后移3位，腾出空位
    cursor.execute("UPDATE your_table_name SET id = id + 3 WHERE id > 3;")
    # 读取id=3的基准行数据
    cursor.execute("SELECT date, symbol, open, close FROM your_table_name WHERE id = 3;")
    base_date_3, symbol_3, open_3, close_3 = cursor.fetchone()
    # 插入3行新数据
    for offset in range(1, 4):
        new_id = 3 + offset
        new_date = (datetime.strptime(base_date_3, "%Y-%m-%d %H:%M:%S") + timedelta(days=offset)).strftime("%Y-%m-%d %H:%M:%S")
        cursor.execute(
            "INSERT INTO your_table_name (id, date, symbol, open, close) VALUES (?, ?, ?, ?, ?);",
            (new_id, new_date, symbol_3, open_3, close_3)
        )

    conn.commit()
    print("数据插入完成")
except Exception as e:
    conn.rollback()
    print(f"操作失败，已回滚所有变更：{str(e)}")
finally:
    conn.close()

• 代码中的表名your_table_name需要替换为你实际使用的数据库表名
• 示例中新插入行的字段值默认沿用对应基准行的标的、开盘价、收盘价，日期按天顺延，你可以根据业务需求替换为实际要写入的值
• 如果不需要id值严格连续，仅需要查询时数据顺序符合预期，可以跳过id偏移更新的步骤，直接插入新数据，查询时使用ORDER BY date, symbol即可，性能更高
• 执行操作前建议先备份数据库文件，避免误操作导致数据丢失

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