Last insert rowid 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 cursor s, 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 

Solution 2

All credits to @Martijn Pieters in the comments:

The last_insert_rowid() function returns the ROWID of the last row insert from the database connection which invoked the function. The last_insert_rowid() SQL function is a wrapper around the sqlite3_last_insert_rowid() C/C++ interface function.

Update: you can use RETURNING in SQLite 3.35:

create table users ( id integer primary key, first_name text, last_name text ); insert into users (first_name, last_name) values ('Jane', 'Doe') returning id; 

Источник

How to retrieve inserted id after inserting row in sqlite using python?

When working with databases in Python, it’s common to need to retrieve the unique identifier of a row that you’ve just inserted. In SQLite, this is often referred to as the «last inserted row ID». In this guide, we’ll explore several methods to retrieve the last inserted row ID after inserting a new row into a SQLite database using Python.

Method 1: Using the cursor.lastrowid Attribute

To retrieve the inserted ID after inserting a row in SQLite using Python, you can use the cursor.lastrowid attribute. Here’s how to do it step by step:

import sqlite3 conn = sqlite3.connect('mydatabase.db')

cursor.execute("INSERT INTO mytable (column1, column2) VALUES (?, ?)", (value1, value2))

inserted_id = cursor.lastrowid

Here’s the complete code example:

import sqlite3 conn = sqlite3.connect('mydatabase.db') cursor = conn.cursor() cursor.execute("INSERT INTO mytable (column1, column2) VALUES (?, ?)", (value1, value2)) inserted_id = cursor.lastrowid conn.commit() cursor.close() conn.close()

Note that you need to commit the transaction using the commit() method of the connection object before closing the cursor and the connection.

Method 2: Using the sqlite3.last_insert_rowid() Function

To retrieve the inserted id after inserting a row in SQLite using Python, you can use the last_insert_rowid() function provided by the sqlite3 module. Here’s how to do it:

import sqlite3 conn = sqlite3.connect('mydatabase.db')

cursor.execute("INSERT INTO mytable (column1, column2) VALUES (?, ?)", (value1, value2))

1. After executing the insert statement, you can retrieve the id of the last inserted row using the last_insert_rowid() function:

last_row_id = cursor.lastrowid

Here’s the complete code example:

import sqlite3 conn = sqlite3.connect('mydatabase.db') cursor = conn.cursor() cursor.execute("INSERT INTO mytable (column1, column2) VALUES (?, ?)", (value1, value2)) last_row_id = cursor.lastrowid conn.commit() conn.close()

Note that you need to call commit() to save your changes to the database, and close() to close the database connection.

Method 3: Executing a SELECT Statement After Inserting

To retrieve the inserted id after inserting a row in SQLite using Python, you can execute a SELECT statement after the INSERT statement. Here’s an example code:

import sqlite3 conn = sqlite3.connect('example.db') c = conn.cursor() c.execute("INSERT INTO my_table (name, age) VALUES (?, ?)", ('John', 25)) c.execute("SELECT last_insert_rowid()") result = c.fetchone() print(result[0]) c.close() conn.close()

In this example, we first connect to the SQLite database using the sqlite3 module. Then we create a cursor object to execute SQL statements.

We execute the INSERT statement using the execute() method on the cursor object. We use placeholders ( ? ) in the SQL statement and pass the values as a tuple to the execute() method.

After executing the INSERT statement, we execute a SELECT statement to retrieve the last inserted row id using the last_insert_rowid() function. We then fetch the result using the fetchone() method.

Finally, we print the inserted id and close the cursor and connection.

Note that the last_insert_rowid() function is specific to SQLite and may not work with other database systems. Also, it is important to use placeholders in the SQL statement to prevent SQL injection attacks.

Источник

SQLite INSERTING records

To our student table we will use SQL INSERT to add one row or record.

We have 5 columns in our table but we are using NULL as data for first column ( id ). As id column is an auto incremented id column , SQLite will add next highest number to this column while adding the row. If it is the first record then the ID value will be 1, for the next record added the ID value will be 2 and so on..

my_query="INSERT INTO student values(null,'New Name', 'Four', 65, 'female')" my_conn.execute(my_query)

x=my_conn.execute('''select last_insert_rowid()''') id=x.fetchone() print(id[0])

r_set=my_conn.execute(q,my_data) print(r_set.lastrowid)

Using parameter

Always use parameterized query when the data is coming from unknown sources. Use ? as placeholder and a provide a tuple to pass the value to query or execute() method. This is required to prevent injection attack.

We have used placeholders ( ? ) in our query and note that my_data is a tuple used for passing value to execute() method for our query.

my_data=(None,'Secon Name','Five',75,'male') my_query="INSERT INTO student values(. )" my_conn.execute(my_query,my_data)

x=my_conn.execute('''select last_insert_rowid()''') id=x.fetchone() print(id[0]) # 3 

Adding multiple records / rows

my_data=[(9, 'Tes Qry', 'Six', 78, 'male'), (10, 'Big John', 'Four', 55, 'female'), (11, 'Ronald', 'Six', 89, 'female'), (12, 'Recky', 'Six', 94, 'female'), (13, 'Kty', 'Seven', 88, 'female')] my_query="INSERT INTO student values(. )" my_conn.executemany(my_query,my_data)

x=my_conn.execute('''select last_insert_rowid()''') id=x.fetchone() print(id[0])

Number of rows added

my_data=[(18, 'Big John', 'Four', 55, 'female'), (19, 'Ronald', 'Six', 89, 'female'), (20, 'ONe more', 'Six', 89, 'female')] my_query="INSERT INTO student values(. )" curs=my_conn.executemany(my_query,my_data) print(curs.rowcount)

Printing Error Message

By using try except we can print the error message if any and that will help in debugging the script.

try: my_data=[(24, 'Big John', 'Four', 55, 'female'), (25, 'Ronald', 'Six', 89, 'female'), (26, 'ONe more', 'Six', 89, 'female')] my_query="INSERT INTO student values(. )" curs=my_conn.executemany(my_query,my_data) print(curs.rowcount()) except sqlite3.Error as my_error: print("error: ",my_error)

error: UNIQUE constraint failed: student.id

plus2net.com

Python programming Basics ⇩

Источник