sqlite3_changes()

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.

Create a database and add the student table. Details are available here.
OR , you can download the sample SQLite database with student table from here also.
https://www.plus2net.com/python/download/my_db.db

Using sqlite3, the Command Line Interface

sqlite> .open E:/my_db/my_db.db
sqlite> .tables

Output of above code is student

Delete

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.

Update


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

Output is 7

Insert

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()

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)

Download the full Python source code from Github or run the code in your Google colab platform
https://github.com/plus2net/Python-basics/blob/main/SQLite_remote_db_colab_sqlite3_changes.ipynb

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

SQLite3 changes() : Number of rows modified, deleted or inserted

Using sqlite3, the Command Line Interface

Delete

Update

Insert

Confirming the changes

PHP Script using changes()

Python Code using total_changes

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

Subscribe