How to Retrieve Inserted Id After Inserting Row in SQLite Using Python

How to retrieve inserted id after inserting row in SQLite using Python?

You could use cursor.lastrowid (see "Optional DB API Extensions"):

connection=sqlite3.connect(':memory:')
cursor=connection.cursor()
cursor.execute('''CREATE TABLE foo (id integer primary key autoincrement ,
                                    username varchar(50),
                                    password varchar(50))''')
cursor.execute('INSERT INTO foo (username,password) VALUES (?,?)',
               ('test','test'))
print(cursor.lastrowid)
# 1

If two people are inserting at the same time, as long as they are using different cursors, cursor.lastrowid will return the id for the last row that cursor inserted:

cursor.execute('INSERT INTO foo (username,password) VALUES (?,?)',
               ('blah','blah'))

cursor2=connection.cursor()
cursor2.execute('INSERT INTO foo (username,password) VALUES (?,?)',
               ('blah','blah'))

print(cursor2.lastrowid)        
# 3
print(cursor.lastrowid)
# 2

cursor.execute('INSERT INTO foo (id,username,password) VALUES (?,?,?)',
               (100,'blah','blah'))
print(cursor.lastrowid)
# 100

Note that lastrowid returns None when you insert more than one row at a time with executemany:

cursor.executemany('INSERT INTO foo (username,password) VALUES (?,?)',
               (('baz','bar'),('bing','bop')))
print(cursor.lastrowid)
# None

How can I capture the rowid of newly inserted row in SQLite/Flask?

Your problem is that you do execute directly on the connection and not the cursor.

Docs explain how that shortcut works:

execute(sql[, parameters])
This is a nonstandard shortcut that creates a cursor object by calling the cursor() method, calls the cursor’s execute() method with the parameters given, and returns the cursor.

https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.execute

See at the end. "returns the cursor". This means we can still get the use the Cursor.lastrowsid which you tried!

So just... save the returned cursor and get lastrowid from it. :)

cur = db.execute("INSERT INTO birds (name) VALUES (?)", newBird)
newBirdID = cur.lastrowid

How to retrieve inserted id after inserting row in SQLite using C#?

// IDbCommand cmd
cmd.ExecuteScalar();
return ((SQLiteConnection) cmd.Connection).LastInsertRowId;

Very easy :)

Can't get the last row id from SQLITE3 database

The SQL statement SELECT max(id) FROM table_name should give you the maximum id. If you're auto-incrementing then this would be the same as the last inserted.

Edit: To get the actual value in python means reading it from the cursor:

cursor = sqlite3.execute('SELECT max(id) FROM table_name')
max_id = cursor.fetchone()[0]

fetchone() returns the first row from the select statement as a tuple (unless a row_factory is used), so fetchone()[0] will, in this case, return the first (and only) column in the first (and only) row, i.e. the max(id).

See http://docs.python.org/2/library/sqlite3.html for more info.

Sqlite. How to get value of Auto Increment Primary Key after Insert, other than last_insert_rowid()?

The way you're doing it is valid. There won't be a problem if the above snipped is executed concurrently by two scripts. last_insert_rowid() returns the rowid of the latest INSERT statement for the connection that calls it. You can also get the rowid by doing g.db.lastrowid.

Inserting row into SQLite table

The cursor object has the last insert ID in its lastrowid attribute.

cursor.execute('INSERT ...')
new_id = cursor.lastrowid
db.commit()
return new_id

How to retrieve the last autoincremented ID from a SQLite table?

With SQL Server you'd SELECT SCOPE_IDENTITY() to get the last identity value for the current process.

With SQlite, it looks like for an autoincrement you would do

SELECT last_insert_rowid()

immediately after your insert.

http://www.mail-archive.com/sqlite-users@sqlite.org/msg09429.html

In answer to your comment to get this value you would want to use SQL or OleDb code like:

using (SqlConnection conn = new SqlConnection(connString))
{
    string sql = "SELECT last_insert_rowid()";
    SqlCommand cmd = new SqlCommand(sql, conn);
    conn.Open();
    int lastID = (Int32) cmd.ExecuteScalar();
}

---

Related Topics