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.

Using sqlite3, the Command Line Interface

sqlite> .open E:/my_db/my_db.db
sqlite> .tables
Output of above code is student


We will delete all students of class Four.
sqlite> DELETE FROM student WHERE class='Four';
sqlite> SELECT changes();
Output is 9 as 9 rows or records are deleted.


sqlite> UPDATE student set mark=mark+10 WHERE class='Six';
sqlite> SELECT changes();
Output is 7


sqlite> INSERT INTO `student`
   ...> (`id`, `name`, `class`, `mark`, `gender`) VALUES
   ...> (40,'ABCD','Five',55,'Male');
sqlite> SELECT changes();
Output is 1
To get all the changes since the connection is established we can use TOTAL_CHANGES()
After deleting 9 records we get these outputs by using changes() and total_changes().
sqlite> DELETE FROM student WHERE class='Four';

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.
$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();
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
$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();
Number of rows added : 2
Delete all records
$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();
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
  #print("No of Records deleted : ",r_set.rowcount)
  print("Number of records deleted : ",my_conn.total_changes)
except sqlite3.Error as my_error:
  print("error: ",my_error)

Sample script using PHP , SQLite3 connection and SQLite database : plus2net_sqlite3_v1

These scripts are developed by using PHP SQLite3 connection to learn different queries and how to execute to mange SQLite database.
Download sample scripts using SQLite3 with instructions on how to use.

SQLite SQLite3 & PHP
Subscribe to our YouTube Channel here


* indicates required
Subscribe to plus2net


    Post your comments , suggestion , error , requirements etc here

    PHP video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer