[FIXED] Getting lastrowid with sqlite in flask app

Issue

I am having a Flask app with a factory pattern that just uses Sqlite.

When trying to do "last_row = db.lastrowid" id i get the error:

AttributeError: 'sqlite3.Connection' object has no attribute 'lastrowid'

When i am using this sqlite function "last_inserted_rowid" https://www.w3resource.com/sqlite/core-functions-last_insert_rowid.php

i get the error:

"TypeError: 'sqlite3.Cursor' object is not subscriptable"

def get_db():
    if 'db' not in g:
        g.db = sqlite3.connect(
            current_app.config['DATABASE'],
            detect_types=sqlite3.PARSE_DECLTYPES
        )
        # return rows that behave like dicts. This allows accessing the columns by name.
        g.db.row_factory = sqlite3.Row

    return g.db
@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()
            # Get the ID of the last inserted row??
            #this dont work
            #last_row = db.execute('SELECT last_insert_rowid()')

            last_row = db.lastrowid
            print(last_row, "LAST ROW")
            if last_row:
                last_inserted_id = last_row[0]
                print("Last inserted row ID:", last_inserted_id)

            return redirect(url_for('strategy.index'))

Solution

Here is a way to get the last row id, using the last_insert_rowid() function as w3resource.com suggests.

import sqlite3
import random

db_file = "db.sqlite3"
conn = sqlite3.connect(db_file)
cur = conn.cursor()

cur.execute("INSERT INTO my_table VALUES({}, 'some text here')".format(random.randrange(100000)))
conn.commit()

cur.execute('SELECT last_insert_rowid()')
last_row = cur.fetchall()
print("last insert row id: {}".format(last_row[0]))

I suggest you take a look at the examples in the official documentation about the differences of running manipulation statements (insert, update, delete for example) and select statements.

Answered By - ilias-sp