在Flask应用中使用SQLite获取最后插入行ID的问题

问题原因分析 #

1. AttributeError: 'sqlite3.Connection' object has no attribute 'lastrowid'
   你调用的db.lastrowid错误，因为db是SQLite的Connection对象，而lastrowid是执行INSERT操作的Cursor对象专属属性，Connection本身没有这个属性。

2. TypeError: 'sqlite3.Cursor' object is not subscriptable
   直接对db.execute('SELECT last_insert_rowid()')的返回值下标访问会报错，因为execute返回的是Cursor对象，必须先调用fetchone()/fetchall()获取查询结果后，才能下标访问。

两种正确实现方式 #

方式一：利用INSERT语句的Cursor获取lastrowid（推荐，更高效）

保存执行INSERT时返回的Cursor对象，直接读取它的lastrowid属性，不需要额外查询：

@bp.route('/createstrat', methods=('GET', 'POST'))
@login_required
def createstrat():
    if request.method == 'POST':
        strategy_name = request.form['strategy_name']
        info = request.form['info']
        exchange = request.form['exchange']
        error = None

        if not strategy_name:
            error = 'strategy_name is required.'

        if error is not None:
            flash(error)
        else:
            db = get_db()
            # 保存execute返回的cursor对象
            cursor = db.execute(
                'INSERT INTO strategies (strategy_name, info, fk_user_id, fk_exchange_id)'
                ' VALUES (?, ?, ?, ?)',
                (strategy_name, info, g.user['id'], exchange)
            )
            db.commit()
            # 直接从cursor获取最后插入ID
            last_inserted_id = cursor.lastrowid
            print("Last inserted row ID:", last_inserted_id)
            return redirect(url_for('strategy.index'))

方式二：使用last_insert_rowid()函数查询

如果想用SQL函数，必须调用Cursor的fetchone()方法获取结果后再取值：

@bp.route('/createstrat', methods=('GET', 'POST'))
@login_required
def createstrat():
    if request.method == 'POST':
        strategy_name = request.form['strategy_name']
        info = request.form['info']
        exchange = request.form['exchange']
        error = None

        if not strategy_name:
            error = 'strategy_name is required.'

        if error is not None:
            flash(error)
        else:
            db = get_db()
            db.execute(
                'INSERT INTO strategies (strategy_name, info, fk_user_id, fk_exchange_id)'
                ' VALUES (?, ?, ?, ?)',
                (strategy_name, info, g.user['id'], exchange)
            )
            db.commit()
            # 执行查询并获取结果
            cursor = db.execute('SELECT last_insert_rowid()')
            last_inserted_id = cursor.fetchone()[0]
            print("Last inserted row ID:", last_inserted_id)
            return redirect(url_for('strategy.index'))

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