In this article, we are going to see how to count total changes since the SQLite database connection is open using Python. To get the total number of changes we use the connection object’s total_changes property.
Class Instance: sqlite3.Connection
Syntax: <connection_object>.total_changes
Return Value: Total no. of rows inserted, deleted, updated since the database connection is open.
For the demonstration purpose, we would be using person table stored in Lazyroar.db database.
Table Definition:
CREATE TABLE person( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER NOT NULL);
Example:
Python3
# Complete python program to get # the total no. of change since the# beginning of the database connection.Â
# Import sqlite3 module to work with# SQLite using python.import sqlite3Â
# Create connection object by connecting# to the required database (here Lazyroar.db)con = sqlite3.connect('c:/sqlite/db/Lazyroar.db')Â
# SQL string to Create a database table# named person.create_table = '''CREATE TABLE person(                  id INTEGER PRIMARY KEY AUTOINCREMENT,                  name TEXT NOT NULL,                  age INTEGER NOT NULL                  );'''Â
# Execute the above SQL query.con.execute(create_table)Â
# Print the current total no. of changes.print("Total changes initially:")print(f'total_changes = {con.total_changes}\n')Â
# SQL string to insert records into # the table named person.insert_data = '''INSERT INTO person(name, age)                  VALUES ("Yogesh",21),                  ("Vishal", 22),                  ("Ajit",22),                  ("Ashish",21),                  ("Tanvi", 20);'''Â
# Execute the above SQL query.con.execute(insert_data)Â
# Print the current total no. of changes.print("Total changes after inserting 5 rows:")print(f'total_changes = {con.total_changes}\n')Â
# SQL string to Select (retrieve) records# from a database table named person.select_data = 'SELECT * FROMÂ person;'Â
# Execute the above SQL query.cursor = con.execute(select_data)Â
# Create a list of column names of the# database table named person.header = [d[0] for d in cursor.description]Â
# Print the column names separated # by a single space.print(*header)Â
# Print the retrieved data.for row in cursor:Â Â print(*row)print()Â
# SQL string to delete a record from a# database table named person.delete_data = 'DELETE FROM person WHERE name="Tanvi";'Â
# Execute the above SQL query.con.execute(delete_data)Â
# Print the current total no. of changes.print("Total changes after deleting a row:")print(f'\ntotal_changes = {con.total_changes}\n')Â
# Retrieve the modified (here deleted# one record/row) data from a database# table named person.cursor = con.execute('SELECT * FROM person;')Â
# Print the retrieved data.print(*header)for row in cursor:Â Â print(*row)print()Â
# Commit the changes to persist the# changes.con.commit()Â
# Close the database connection.con.close() |
Output:
