SQLite3 changes() : Number of rows modified, deleted or inserted
changes() function is a SQLite3 function that returns the number of rows that were modified, inserted, or deleted by the most recently completed INSERT, UPDATE, or DELETE statement.
Output is 1
To get all the changes since the connection is established we can use TOTAL_CHANGES()
SELECT TOTAL_CHANGES();
After deleting 9 records we get these outputs by using changes() and total_changes().
sqlite> SELECT CHANGES();
35
sqlite> DELETE FROM student WHERE class='Four';
sqlite> SELECT CHANGES();
9
sqlite> SELECT TOTAL_CHANGES();
44
Confirming the changes
The `sqlite3_changes()` function is a useful tool for working with SQLite3 databases. It can be used to verify that queries are executed successfully, to get the number of rows that were affected by a query, and to perform other tasks.
Here we expect a return of an integer 1 so we can check and confirm that the database updation was successful.
UPDATE table_name SET pw='new_password' WHERE user='user_id';
Here is one sample
if (changes != 1) {
// Handle error
}
PHP Script using changes()
SQLite3 changes() to get the number of rows affected by UPDATE, DELETE and INSERT Query - 20
Using UPDATE query.
<?php
$my_conn = new SQLite3('../my_db.db'); // connect to database
$sql="UPDATE student SET mark=mark+10 WHERE class='Four'"
$result = $my_conn->exec($sql);
if ($result) {
echo 'Number of rows modified: ', $my_conn->changes();
}
?>
Output
Number of rows modified: 9
When there is no change in table we will get output of changes() as 0.
In this query there is nothing wrong so we will get $result as True and output of $my_conn->changes() as 0 . ( In our student table there is no record matching class='One' )
$query="UPDATE student SET mark=mark+10 WHERE class='One'"
Using INSERT query to add record to table
<?php
$my_conn = new SQLite3('../my_db.db');
$sql="INSERT INTO `student`
(`id`, `name`, `class`, `mark`, `gender`) VALUES
(39, 'Abcd', 'Four', 88, 'Female'),
(40, 'XYZ', 'Five', 38, 'Male')";
$result = $my_conn->exec($sql);
if ($result) {
echo 'Number of rows added : ', $my_conn->changes();
}
?>
Output
Number of rows added : 2
Delete all records
<?php
$my_conn = new SQLite3('../my_db.db');
$sql="DELETE FROM student";
$result = $my_conn->exec($sql);
if ($result) {
echo 'Number of rows deleted : ', $my_conn->changes();
}
?>
Output
Number of rows deleted : 37
Python Code using total_changes
Here my_conn is the database connection object.
query="DELETE FROM student WHERE class='Four'" # Query to delete records
#query='DROP TABLE student' # Query to delete table
try:
r_set=my_conn.execute(query)
#print("No of Records deleted : ",r_set.rowcount)
print("Number of records deleted : ",my_conn.total_changes)
my_conn.commit()
except sqlite3.Error as my_error:
print("error: ",my_error)