my_query="INSERT INTO student values(null,'New Name', 'Four', 65, 'female')"
my_conn.execute(my_query)
We can also read the ID ( alow known as rowid ) of most recently added row.
x=my_conn.execute('''select last_insert_rowid()''')
id=x.fetchone()
print(id[0])
Output
2
Or
r_set=my_conn.execute(q,my_data)
print(r_set.lastrowid)
my_data=(None,'Secon Name','Five',75,'male')
my_query="INSERT INTO student values(?,?,?,?,?)"
my_conn.execute(my_query,my_data)
We can check the ROWID of this added record. ( code with output as 3 )
x=my_conn.execute('''select last_insert_rowid()''')
id=x.fetchone()
print(id[0]) # 3
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)
We can check the row id of the last inserted records by using last_insert_rowid().
x=my_conn.execute('''select last_insert_rowid()''')
id=x.fetchone()
print(id[0])
Output is
13
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)
Output
3
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)
Output ( as we already added the records with the above IDs )
error: UNIQUE constraint failed: student.id
lastrowid
lastrowid
feature makes this task straightforward. Let's see how this works with an example of a student database.
student
table. We'll define columns for id
, name
, class
, mark
, and gender
. The id
column is set as an integer primary key.
my_conn.execute('''CREATE TABLE IF NOT EXISTS student(id integer primary key,
name text,
class text,
mark integer,
gender text )''')
lastrowid
.
my_query = "INSERT INTO student(name,class,mark,gender) \
values('New Name', 'Four', 65, 'Female')"
r_set = my_conn.execute(my_query)
print(r_set.lastrowid)
This snippet will insert a new student record and print the ID assigned to it by SQLite.
AUTOINCREMENT
keyword ensures each new record receives a unique ID that has never been used, even if previous records are deleted.
AUTOINCREMENT
, SQLite assigns the next highest available ID. However, with AUTOINCREMENT
, SQLite ensures the ID is always one higher than the highest ID ever used, guaranteeing uniqueness.
ROWID
is 5. If this record is deleted and a new one is added, the new ROWID
without AUTOINCREMENT
would be 5. With AUTOINCREMENT
, it would be 6.
my_conn.execute('''CREATE TABLE IF NOT EXISTS student(id integer primary key AUTOINCREMENT,
name text,
class text,
mark integer,
gender text )''')
This approach ensures data integrity and is crucial for maintaining unique identifiers throughout the database's lifespan.
lastrowid
attribute is incredibly useful for getting the ID of the last inserted row, particularly when you need to reference the new record immediately. Understanding the impact of using AUTOINCREMENT
helps in designing databases that ensure data consistency and integrity.
02-02-2023 | |
Thank you for this article. It offers more than all the other websites I have seen so far. |